[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access - DoCmd.OpenForm Pass variable OpenArgs problem

Posted on 2012-09-21
7
Medium Priority
?
817 Views
Last Modified: 2012-09-21
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

0
Comment
Question by:wlwebb
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38423949
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38423952
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
 

Author Comment

by:wlwebb
ID: 38423955
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:wlwebb
ID: 38423957
Thanks Mbiz... that did the trick.....  I am curious though if it can't be done with the one code line using OpenArgs????????????
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38423962
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38423967
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
 

Author Comment

by:wlwebb
ID: 38423970
Yep...!!!! Thanks to both of you!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question