Add Item to 2 Combo Boxes 1

[befine]

This one is probably easy but I’m stuck.
I have 2 combo boxes on a form. One for state (using tblState) and one for county (using tblCounty). When I enter a new value in either, I have pop-up form for people to enter a new state code, full state name and county.
I’m stuck trying to figure out how to update the tables and requery the combo boxes.
I’d appreciate any help.

Thanks
befine

 

[jdegeorge]

Befine

I do this with the dropdown box's NotInList property and limit the user to the list. In the NIL property, I have the following code:

Dim DB As DAO.Database, RS As DAO.Recordset, strMsg As String, NewTester As Variant

strMsg = "'" & NewData & "' is not a known TESTER Name"
strMsg = strMsg & "@Do you want to associate the new TESTER Name to the current incident?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
NewTester = NewData
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Tester?&quot = vbNo Then
Response = acDataErrContinue
Else
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tblPassword", dbOpenDynaset)
On Error Resume Next
RS.AddNew
RS!User = NewTester
RS.Update
[Forms]![frmIncidentInput].Visible = False
DoCmd.OpenForm "frmAddTester_Maintenance"

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

This opens a 2nd form that, in your case, would be based on your STATE table, with the value of the "new" state as the default. The user would enter the other required information for the STATE. On the CLOSE button for this 2nd form there is this code:

DoCmd.Close acForm, "frmAddTester_Maintenance", acSaveYes

The user gets pushed back to the first form to continue entering the other information. No requerying is required.

You can modify this code for your field, form and table names. It may not be code that you can easily cut and paste, but hopefully the concept is clear.

Hope this helps.

Jim

"Get it right the first time, that's the main thing..."

 

[befine]

Thanks Jim
It gives me the clues I needed to make it work.
Would this also work if I needed to add 2 fields for the new name? i.e. If they type in a new state code can I use an input box to ask for the full state name and add both to the db at the same time??

Thanks
Befine

 

[jdegeorge]

Befine:

Jumping to the new form is on the individual field's AfterUpdate property, so I'd have to say no. Do you think that you can determine which of the 2 fields is more important to link this code to and then have the user enter the other information on the 2nd form?

I don't know what your application is about, but it looks like it starts with the user selecting a state code from a dropdown box (such as NJ, NY, etc.). If they type in a code that isn't in a the dropdown list, the code will ask if you want to add this state. If so, the new form can open with the state abbreviation, and on that form your user can enter the full state name and county name.

Is this close?

Jim

"Get it right the first time, that's the main thing..."

 

[jdegeorge]

Befine....

Forgot to thank you for the star!

Jim

"Get it right the first time, that's the main thing..."

 

[befine]

Thanks Jim
Your code gave me a starting point to understand how Access worked with combo boxes. I combined your code and some other code I had been left with (added an inputbox to get more data) and used the NotInList to add two fields at the same time.

I added:
With rst
'Start new record
.AddNew
![State] = inputValue1
![FullState] = newState
.Update
End With

Thanks again
Brian

 

[jdegeorge]

Brian

Very nice! Glad to have pointed you in the right direction.

Jim

"Get it right the first time, that's the main thing..."