Can I use a series of ComboBoxes to AddNew Record? 1

[tEkHEd]

Hey!

I am trying to create an "AddNewRecord" form, but I do not want to use a txt box...

Can I use a combo box to allow for a user selection AND/OR allow a user to type text and then add a new record from their input?

E.G.

I have 3 x combo boxes, that will display data dependant upon the selection of the previous box, by using the following code:

SELECT Product 
FROM tblMLSoftwareList GROUP BY Product, Manufacturer HAVING (((Manufacturer)=forms!frmNewSW!cboEntSWMan)) 
ORDER BY Product, Manufacturer;

I am having trouble allowing a user to enter their own data and then have this data written into a specific field to update the record, due to the fact that if I set the ControlSource to (in the example above) "Product"; the combobox shows "#Name?" and will not allow me to select an item from the list (although it does show the available choices!!)

I tried to add in a "beforeUpdate" for Me.AllowEdits True, but this didn't seem to work, I am prob on the wrong trakc there tho!

Any help would b appreciated...

 

[BSman]

Not sure what you're trying to do, but here goes....

I'm going to assume that the user is adding records to a table where you want the user to be able to either select an existing value (ex: manufacturer) or to enter a new manufacturer if necessary. And the same with the other two columns.

The data source for each combo box would be the same table that you are adding records to. Set the limit to list property of each combo box to No. The source for the combo box is the SAME table that you are adding records to. Just be sure that you use a grouping query for the source for each combo box. That way you will only list each existing value once in your combo boxes.

If you want to add records to a lookup table (that might be the source instead of the current table), then you should set up a variable for your form that indicates if there is any new data for the three combo boxes. If there is, then for the form property of before insert record you then check your variable and if something is new also add a new record(s) to your lookup table(s).

 

[tEkHEd]

I think that you understand what I am trying to do...

I have the following settings in the combobox 1 (manufacturer) properties:

Control Source: = tblMyTable!Manufacturer
Input Mask
Row Source Type: Table/Query
Row Source: SELECT tblMyTable.Manufacturer FROM tblMyTable GROUP BY tblMyTable.Manufacturer ORDER BY tblMyTable.Manufacturer; 
BoundColumn: 1
LimitToList:No
AutoExpand:Yes
Enabled:Yes
Locked:No

All other fields are left blank...

With the settings as above, the combo box produces the list, but you are unable to select an entry (although it shows the choices available) nor are you able to type a new entry into the system...

tkS :¬)

 

[BSman]

Change your form so the record source for the form is tblMyTable.

Then change the control source for the combo box to Manufacturer (by selecting that field name from the drop down list). (In other words, don't use "tblMyTable!Manufacturer".)

 

[tEkHEd]

Jackpot!

Thanks for your help BSman :¬)