Add new record from a bound combo box

  • Thread starter Thread starter JoeJohn
  • Start date Start date
J

JoeJohn

Hello All,

I need a help with the form coding.
How to append a new record into a lookup table using a bound combo
box?
In my entry form, I have this cboAgencyID combo box with the rowsource
= "SELECT AGENCY.AgencyID, AGENCY.AgencyName FROM AGENCY;".

I want to enable this combo box for inserting a new record into AGENCY
table, so that whenever the user type a value which is not in the
list, the value will be automatically added as a new record.

Could anyone guide me with the steps and some code examples? It would
be highly appreciated. Many thanks in advance.
-Johanes-
 
Hi Johanes

You can use the NotInList event. Check it out in the online help.

Here is an example that should get you going - you get to write the error
handling code :-)

Private Sub cboAgencyID_NotInList(NewData As String, Response As Integer)
If MsgBox("Do you wish to add '" & NewData & "' to the agency list?", _
vbYesNo, "Unknown Agency") = vbYes Then
CurrentDb.Execute "Insert into Agency (AgencyName) " _
& "values (""" & NewData & """)", dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

I'm assuming that AgencyID is an AutoNumber primary key and there are no
more required fields.

Note that setting the Response argument to acDataErrAdded causes the combo
to be requeried automatically.
 
JoeJohn said:
I need a help with the form coding.
How to append a new record into a lookup table using a bound combo
box?
In my entry form, I have this cboAgencyID combo box with the rowsource
= "SELECT AGENCY.AgencyID, AGENCY.AgencyName FROM AGENCY;".

I want to enable this combo box for inserting a new record into AGENCY
table, so that whenever the user type a value which is not in the
list, the value will be automatically added as a new record.

Could anyone guide me with the steps and some code examples? It would
be highly appreciated. Many thanks in advance.
-Johanes-
Click to expand...


The usual way is to set the combo box's LimitToList property
to Yes and create a NotInList event procedure to add the new
record.

Private Sub cboAgencyID_NotInList(NewData As string,
Response As Integer)

Dim db As Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO AGENCY (AgencyName) " _
& "VALUES (""" & NewData & """)"
db.Execute strSQL, dbFailOnError

Response = acDataErrAdded
End Sub

Add the appropriate error handling, except when the data
fails to be added, set Response to acDataErrDisplay you
want Access to display its standard error message or
acDataErrContinue if you want to generate your own message.
 
Dear Marsh and Graham,

Thanks so much for all your help.
Didn't expect that simple though.... :-)

Cheers,
-Johanes-
 
Back
Top