Anyone have any ideas?
This is a discussion on Inputting data with combo box within the Microsoft Access forums, part of the Question Forums category; I have a main table which is linked to a products table. In the Products table there is the Products ...
I have a main table which is linked to a products table. In the Products table there is the Products ID(which is an autonumber), Product number and Product Name.
I have two drop down boxes (Products Number and Product Name) which are working fine, on the main table, on the Products Number.
However what I would like to do is make it so that you could add a Product Number and a Product Name in the combo box, and they will be added to the Products table, and that you will be able to select them in the drop down box on the form.
Any help is apreciated.
Anyone have any ideas?
I thought you said he Product Number was an Autonumber?
How can a user add a Product Number if that's the case?
To add a new Product Name look at the Limit to List property and the On Not in List event.
If posting code please use code tags.
Product ID is the Autonumber, I am thinking it may be easier just to delete it, as it seems uneeded.
And then making Product Number the Primary Key.
I will check out the Limit to list property and the not in list event.
Many thanks Norie
update: after browsing the internet i have found some code and modified it to fit my purpose, however it is giving this error message but I don't know why:
Run time error: 13
this is the line it get stuck on:
Set rstProd = db.OpenRecordset(strProd, dbOpenDynaset)
and here is the code
cant see what is wrong, as this is the first time ive really used VBA in access .Code:Private Sub Combo44_NotInList(NewData As String, Response As Integer) 'Suppress the default error message. Response = acDataErrContinue ' Prompt user to verify if they wish to add a new value. If MsgBox("The Product of " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then ' Set Response argument to indicate that data is being added. 'Open a recordset of the Products Table. Dim db As database Dim strProd As String Dim rstProd As Recordset Set db = CurrentDb() strProd = "Select * From Products" Set rstProd = db.OpenRecordset(strProd, dbOpenDynaset) 'Add a new Product with the value that is stored in the variable NewData. rstProd.AddNew rstProd![Product Name] = NewData rstProd.Update 'Inform the combo box that the desired item has been added to the list. Response = acDataErrAdded rstProd.Close 'Close the recordset End If End Sub