How to open the form in Design view from 'Notinlist' event?

[prabhafriend]

I need open the form in Design View from a combobox's Notinlist event. Any tricks?

[geekgirlau]

DoCmd.OpenForm "MyForm", acDesign

Personally I can't think of any reason that you would want a user to open a form in design mode.

[prabhafriend]

Geeky, You really thought that I didn't know what you said?!!! I am not a kid. My problem is I need to add an element to a combobox's valuelist which is unbound. It can be done only in design mode right? otherwise it will be added only temporarily. Otherwise tell me a way to add a element in a combobox's valuelist on it's notinlist event. Please don't suggest to change it to a bound control.

[geekgirlau]

This really depends on who the form is designed for. If this is for your own personal use, then go ahead and open in design mode. If anyone else needs to use this database, then I would seriously suggest that you rethink this decision.

Exactly why is it a problem to have a bound control? I don't think I've every come across a database structure that did not require at least a handful of tables just to supply values for lists. It's quite simple: you just need to make sure that you provide the user with a way to update that list.

If you are going to give any old user direct access to modify the design of your database, regardless of skill and experience, you are going to have some serious problems down the track.

[prabhafriend]

Someone please tell me a way to change the rowsource(valuelist) of an unbound combobox permanently in run-time. I still believe there must be a way. What is it? Please tell me.

[prabhafriend]

I discovered an option which lets the user to add values to a unbound combobox in Normal view itself. But I don't know how to automate this.
what is the option?
Just click an unbound combo box and dropdown it. After the last element, You will see a button. When you click that you will see a box in which you can add items.
My problem is how to do it in runtime?

[Imdabaum]

I don't have access to Office 2003, but wasn't that built in? Anytime you create a drop down box, you could select whether you wanted to allow the values to be inserted into the rowsource. In 2007, you change the property of Limit to List="yes" and it will automatically prompt you if you add a non existing value.

If memory serves me, (which at my age, it probably isn't serving as much as it did) it does the same thing in 2003. Just set the following properties:
Limit to List: Yes
Allow Value List Edits: Yes

DONE. No VBA, No hard feelings.
If you want to do it with VBA, then I think this should work


If MsgBox("You have entered a value that is not available." & vbNewLine & _
"Would you like to add it to the list of available items?", vbYesNo) = vbYes Then
Me.Combo2.RowSource = Me.Combo2.RowSource & ";" & NewData
Else
MsgBox "Please select a valid value, then", vbOKOnly
End If
If your rowsource is a table, then append the item into the table.

[HiTechCoach]

I need open the form in Design View from a combobox's Notinlist event. Any tricks?

Note: When you compile your database into an ACCDE ( MDE from 2003 and earlier) you can NOT enter design mode for a form.

I always use a split database with aACCDE/MDE front end for ALL users. I would urge you to do the same.

I would only recommend using a value list for a combo box if the list NEVER changes. I almost always use a table for the look up list values. IMHO, it is a "Best Practice" to use a table if the list can ever change.

[prabhafriend]

Imdabaum:
In your suggested way, the Newly added elements are only stored temporarily. If the user closes the form and opens it again, The combo box will be as it was. Is there any way to automate the functionality of the builtin 'Element Adding box' Because the builtin box Stores the value permanently. Kindly tell me a way.
Batman:
Thanks for that information. i never heard it before. Can't we open a form in Desing in a Compiled Database? So if we use a dynamic forms (createcontrols) then we cannot compile it. right?

[HiTechCoach]

Imdabaum:
Batman:
Thanks for that information. i never heard it before. Can't we open a form in Desing in a Compiled Database?
No, it is not possible. Forms, reports, and Module are all stripped of the VBA source code adn only have the compiled code. This helps protect your VBA code.


Imdabaum:
So if we use a dynamic forms (createcontrols) then we cannot compile it. right?
That is correct.

With a properly designed (normalized) database and Access forms and sub forms, you probably would never need to create a control at runtime.

If you are creating something to help a user design and build forms then I could see the need to open a form or report in design mode.

[Imdabaum]

Imdabaum:
In your suggested way, the Newly added elements are only stored temporarily. If the user closes the form and opens it again, The combo box will be as it was. Is there any way to automate the functionality of the builtin 'Element Adding box' Because the builtin box Stores the value permanently. Kindly tell me a way.


In Access 2007, the "Element Adding box" automaticaly opens when you set the properties as above. I don't have access to 2003 to test it there.

I'm in agreement with Batman though if you need to change the data, it's more effective to add the values into a table. It restricts people from just allowing random new entries from lazy users.

[prabhafriend]

Batman, I will tell you a scenario for the need of dynamic forms/reports. Assume you have to track an attendance.
Which is the best design for an attendance?
Tracking the presence/absence? absence Right? Less occurence so less data.
So we just need to have an absent table. Absent table having date and emp.
But how the user here wants it. He needs an attendance form where we have to list all the employees in the first column. The we have to list all the dates in that month as fields. To mark presence/absence the user wants checkboxes in the intersection of each employee/date. Obviously, all checkboxes are ticked by default. If the user wants to mark an absence he just needs to unmark that checkbox.
I believe the design is perfect but see the requirement, it's complex.
I had a submit button. After making all the entries for every employee for that month the user will click the Submit, then I will compare the values of the controls (combo boxes dynamically created) with recordset. If the value differs I will insert a record in the absent table.
I know its not ethical. But otherwise we have to compromise the Design. If we go for recording the presence then we will have much data. Whats your opinion in this scenario?

[prabhafriend]

Yes Imdabaum, In Access 2007, It does the exact thing I want. But not in Access 2003 I tried that. Is there any way through which we can replicate the same functionality in Access 2003 also? Kindly suggest. Becuase the user wants this tool only Access 2003.

[HiTechCoach]

Batman, I will tell you a scenario for the need of dynamic forms/reports. Assume you have to track an attendance.
Which is the best design for an attendance?
Tracking the presence/absence? absence Right? Less occurence so less data.
So we just need to have an absent table. Absent table having date and emp.
Not sure I agree with your logic. :dunno

If you are tracking attendance then why not store attendance NOT absence?

Note: The volume of data should not matter in your normalization of the data. The volume of data will determine which back end database engine to use.




But how the user here wants it. He needs an attendance form where we have to list all the employees in the first column. The we have to list all the dates in that month as fields. To mark presence/absence the user wants checkboxes in the intersection of each employee/date. Obviously, all checkboxes are ticked by default. If the user wants to mark an absence he just needs to unmark that checkbox.
I believe the design is perfect but see the requirement, it's complex.
I had a submit button. After making all the entries for every employee for that month the user will click the Submit, then I will compare the values of the controls (combo boxes dynamically created) with recordset. If the value differs I will insert a record in the absent table.
I know its not ethical. But otherwise we have to compromise the Design. If we go for recording the presence then we will have much data. Whats your opinion in this scenario?
from my experience, you have not described any needs that should require you to create controls dynamically at runtime.

I have done attendance systems that appear very much like a spreadsheet using check boxes. I used bound controls to a table and a form in datasheet mode. No need to create any objects on the form at runtime.

[prabhafriend]

Is there any way through which we can replicate the Combo box Element Adding Facility in Access 2003?