NotInList Combo Box for Form (1 Viewer)

[swift_ingress]

Hi,

I'm creating a simple Country/State/City/Street database as a test bed for other applications. The Country/State/City/Street are each their own tables. State has StateID(PK), StateName and CountryID(FK). City and Street are identical (they only hold the ID(FK) for direct parent).

On the form (frm_Locations), I want the user to either select an existing value from a combobox, or if that value does not exist, they type the new value, and receive a prompt to confirm the addition of the new value to the relevant table. What currently works:

-The combobox shows a list of existing values which the user can select
-If the user types a new value, a prompt asks for confirmation
-If the user clicks NO on the prompt, they are returned to the form to try again

It's the YES part that I'm having issues with. The lines of code that aren't working are in bold. The code is as follows:

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

'On Error GoTo ErrLine - this code does not seem to work. Removing it fixed an error. What was it supposed to do? Do I still need it?

Dim MsgBoxAnswer As Variant

Response = acDataErrContinue

'Confirm the user wishes to create a new entry for the country they have just typed
'Enable a message box pop-up where the user can select either Yes or No
MsgBoxAnswer = MsgBox(NewData & " does not exist. Would you like to create it?", _
vbQuestion + vbYesNo)

'if no then return the user to the country name field so they can select an existing country or type something else
If MsgBoxAnswer = vbNo Then
Me.cboCountry = Null
DoCmd.GoToControl "cboCountry"
'GoTo ExitLine - this code does not seem to work. Removing it fixed an error. What was it supposed to do? Do I still need it?

'if yes then create the new country in the associated table and return the user to the next field in the form
Else
DoCmd.OpenTable ("tbl_Countries") 'open the table where the data is to be stored
tbl_Countries.AddNew 'add new record. This line of code isn't working. Tried removing it (wasn't sure if AddNew was required)
tbl_Countries!CountryName = NewData 'store the data that was just typed. This line of code isn't working
DoCmd.Close acTable, "tbl_Countries" 'close the table
Forms![frm_Locations].SetFocus 'go back to the form
DoCmd.GoToControl "cboStateName" 'set the control to the next field
End If

End Sub

I also tried this line of code where the issues are:
Tables![tbl_Countries]![CountryName]=NewData 'store the data that was just typed

If I can get this working, I'll be able to replicate this for State/City/Street and add cascading functionality to the comboboxes (to only display valid child values).

Unfortunately I've only just started looking at VBA code for the first time a few weeks ago so I don't completely understand how to write "go here and do this". Also this is the second forum message I've written in my life, so, new to VBA, new to Access, and new to Forums, so if I post things in the wrong place, sorry in advance.

Any assistance would be greatly appreciated.

Also, if there is a better way to build this database in general, I'm happy to hear your thoughts.

Kind Regards,
Swift

 

[Dreamweaver]

This will do what you need

Login

Login

databasedreams.createaforum.com

 

[jdraw]

See this link for info on Not in List.

 

[swift_ingress]

Thanks Mick.

So I removed:
tbl_Countries.AddNew
tbl_Countries!CountryName = NewData

and added this code (actually I'll put all the code from the last working line):

dim strSQL as string

DoCmd.OpenTable ("tbl_Countries")
strSQL = "Insert Into tbl_Countries ([CountryName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError

DoCmd.Close acTable, "tbl_Countries"
Forms![frm_Locations].SetFocus
Me.cboCountry.Requery 'not working
DoCmd.GoToControl "cboStateName"
End If

End Sub

And that works... but now it's stuck in a loop. I think I just need to requery the form, because if I cancel out of debugging, I can see the new entry in the Countries table, and the table has closed, the focus has returned to the form, but it doesn't register the new value until I press "Refresh All". But what I'm writing to requery the form isn't working. I'm pretty sure that was how I used the requery code last time. So close!!!

 

[Dreamweaver]

Sorry I don't see what your trying to do my code would add a new entry or remove the text in the combo if user answers no I can't see that more there is to do.

mick

 

[Dreamweaver]

If you want to see how it works download The Master Template Open the customers screen and create a new customer adding both City And region
mick