Combo box

[Nathan]

I set up a combo box to be used as a drop down list with its contents being the primary key of a table. Is it possible that if a value typed into the combo box does not exist in this table, that it could be automatically added? Thanks

 

[chuckgoss]

Nathan:
I receintly go some help from this board that covered a very similar need I had. I'll paste the entire code, you modify as needed. It works great!

Private Sub Combo40_NotInList(NewData As String, Response As Integer)

[(A)Shelf Quantity] = [ctlUpdatedOrigLocQty]
Dim dbsMyDatabase As Database
Dim rstMyRecordset As Recordset
Dim resp As Byte
resp = MsgBox("You are about to create a new record", vbOKCancel, "Making new location")
Response = acDataErrContinue
If resp = vbOK Then
'Open the table and add the NewData value.
Set dbsMyDatabase = CurrentDb()
Set rstMyRecordset = CurrentDb().OpenRecordset("All Field Table (A)")
'Posts control values into New Records Fields
rstMyRecordset.AddNew
rstMyRecordset![(A)Location] = NewData
rstMyRecordset![Description] = [UBDescription]
rstMyRecordset![APart Number] = [UBPartNumber]
rstMyRecordset![Machine Type] = [UBMachineType]
rstMyRecordset![Model] = [UBModel]
rstMyRecordset![(A)Shelf Quantity] = [ctlRelocatingQuantity]
rstMyRecordset.Update
Combo40 = rstMyRecordset![(A)Location]
DeadStop.SetFocus

End If

chuck

 

[Nathan]

Thanks Chuck, but my Visual Basic knowledge is very limited, is there any other way around it, if not, I can always try to make the code work. Oh well, thanks again.