Access - DoCmd.OpenForm Pass variable OpenArgs problem

Good Eve everyone

Have a Form where if there is a New Zip Code being input I want it to open a form to add the new Zip Code to a ZipCode table.....

I am getting it to open the form etc but it's not passing the State's FIPSStateCode from the first form to the Second form....

Here is my code from the AfterUpdate Event where the user selects the State after entering the zipcode

Private Sub cboFIPSState_AfterUpdate()
    Dim LResponse As Integer, lngFIPSState
    
    
    Me.cboCity.RowSource = "SELECT [CityNames].[CityID], [CityNames].[CityName], [CityNames].[FIPSStateCode] FROM [CityNames] WHERE [CityNames].[FIPSStateCode]=" & Me.cboFIPSState.Column(0)
    Me.cboCity = Me.cboCity.ItemData(0)
    lngFIPSState = Me.cboFIPSState.Column(0)
    MsgBox " " & lngFIPSState, vbOKOnly
    
    
    If (Me.cboCity.ListCount) = 0 Then
        LResponse = MsgBox("There are no cities for the selected State.  If you have selected the correct State, do you want to add a New City?", vbYesNo, "NO CITIES FOR THE SELECTED STATE.  ADD NEW CITY?")
        If LResponse <> vbYes Then
            Exit Sub
        Else
            DoCmd.OpenForm "frm_CityName_AddFromZipCodeAdd", acNormal, , , acFormAdd, , OpenArgs:="FIPSStateCode.column(0) =" & lngFIPSState
            
        End If
    End If
 
End Sub

Open in new window

wlwebbAsked:
Who is Participating?
 
mbizupCommented:
Simply passing OpenArgs isn't quite enough.  You need to actually place the value in the appropriate textbox or combo.

This can actually be done without openArgs:

Private Sub cboFIPSState_AfterUpdate()
    Dim LResponse As Integer, lngFIPSState
    
    
    Me.cboCity.RowSource = "SELECT [CityNames].[CityID], [CityNames].[CityName], [CityNames].[FIPSStateCode] FROM [CityNames] WHERE [CityNames].[FIPSStateCode]=" & Me.cboFIPSState.Column(0)
    Me.cboCity = Me.cboCity.ItemData(0)
    lngFIPSState = Me.cboFIPSState.Column(0)
    MsgBox " " & lngFIPSState, vbOKOnly
    
    
    If (Me.cboCity.ListCount) = 0 Then
        LResponse = MsgBox("There are no cities for the selected State.  If you have selected the correct State, do you want to add a New City?", vbYesNo, "NO CITIES FOR THE SELECTED STATE.  ADD NEW CITY?")
        If LResponse <> vbYes Then
            Exit Sub
        Else
            ' Open the form
            DoCmd.OpenForm "frm_CityName_AddFromZipCodeAdd", acNormal, , , acFormAdd

            ' Place the value in the combo
            Forms!frm_CityName_AddFromZipCodeAdd.FIPSStateCode = lngFIPSState
            
        End If
    End If
 
End Sub

Open in new window

0
 
IrogSintaCommented:
You should pass it this way
DoCmd.OpenForm "frm_CityName_AddFromZipCodeAdd", acNormal, , , acFormAdd, , OpenArgs:= lngFIPSState
'or
DoCmd.OpenForm "frm_CityName_AddFromZipCodeAdd", acNormal, , , acFormAdd, , lngFIPSState

Open in new window

0
 
wlwebbAuthor Commented:
Tried both....... neither one worked.......

When it Opens that new form.  there are 3 fields on that form....

CityID (long autonumber)
CityName (Text field)
FIPSStateCode (combobox)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
wlwebbAuthor Commented:
Thanks Mbiz... that did the trick.....  I am curious though if it can't be done with the one code line using OpenArgs????????????
0
 
IrogSintaCommented:
All OpenArgs would do is pass the variable to the other form.  You could use it for example in your other form's OnLoad event with Me.FIPSStateCode = Me.OpenArgs
0
 
mbizupCommented:
You CAN do it with OpenArgs, but you would need a line of code in your other form's Open Event to populate the combo with the Open Args.


I recommended the other method however, because I wasn't sure if this form would ever be used for editing existing records - in which case you wouldnt want to overwrite an existing combo value with the OpenArgs.

Clear as mud?  ;-)
0
 
wlwebbAuthor Commented:
Yep...!!!! Thanks to both of you!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.