Display Value in combo box onload or afterupdate

[JFo]

Attachment 4654



OK here it is if you select the client and then contact on frmMainMenu from combo lists and select edit contact it should open with selected contact and display all details

At the moment this exact function has 2 issues
1) It does not care what is in combo it simply opens first record in database
2) it does not display values in combos as discussed

Thanks

[stmoong]

I am not able to run it, I tried several times and it kept crashing my Access

You should turn on Auto Indent for readability. From the VBA editor, go to Tools -> Options, then check the Auto Indent, and set the Tab Width to 4.

Anyway, the lines highlighted in red don't make much sense. The code is passing the ClientID to the combo box containing the list of cities?

Private Sub cmdEditClientsContact_Click() 'Edit Contacts
Dim stLinkCriteria As String
Dim SID As Integer
stLinkCriteria = "Me![ContactID]=' " & Forms![frmMainMenu]![cmbClientsContact] 'Open Form In Edit Mode
DoCmd.OpenForm "frmContact", acNormal, , , acFormEdit, , stLinkCriteria

SID = Me![cmbClients]
Forms![frmContact]![cmbCities] = SID 'Pass City
End Sub

It looks to me that it's trying to select the city by matching the city id from the list with the client id. And also, it's overriding selection after you open the form, so selecting in Form Load event won't work in this case.


I am not able to test changes to the frmMainMenu as it crashes my Access when I try to save.

Try to do this:
- Comment the 2 lines above.
- In the frmContact Form Load event, add the following:

With Me.cmbCities
.RowSource = _
"SELECT tblCities.CityID, tblCities.CityName, tblCities.Postcode, tblCities.StateID" & _
" FROM tblCities" & _
" ORDER BY tblCities.CityName;" 'Here, we just query for the list of available cities, might be good to put it in a Query, improve performance slightly
.Requery
.Value = Me.CityID 'Assuming the value here is correct, basically we tell Access to select the city matching the textbox value
End With

'For your combo box of states, it will be somewhat similar. The rowsource will list state based on the city -> does this actually make sense? Would there be one city appearing in more than one states - I'm not sure, maybe there are? Anyway, you will have to figure it out

[JFo]

Sorry about that I converted it to an old version of access as I use 2010 I have saved it in the current format for you and hopefully it works now. Also Auto Indent is on I have not changed any of the settings.

Attachment 4667

Thanks for your solution that seems to work for what I wanted my only problem now is that I still cannot open the form in edit mode depending on what element is selected in my combo box I am using this code and I thought this should work however it only opens the first record in the table every time

Dim stLinkCriteria As String

stLinkCriteria = "Me![ContactID]=' " & Forms![frmMainMenu]![cmbClientsContact] 'Open Form In Edit Mode
DoCmd.OpenForm "frmContact", acNormal, , , acFormEdit, , stLinkCriteria

What else is required in the stLinkCriteria to open the form at the record selected in the combo box?

Thanks for all your help

[stmoong]

Well, I'm not able to open the Access 2010 file as I'm using Access 2007. But doesn't matter, I created a test form and extracted out the 2 combo boxes and edit button from the mdb.

Here's the thing. In the click event of the Edit button, I modified the code as such.

 
Private Sub cmdEditClientsContact_Click()
    Dim stLinkCriteria As String
    Dim SID As Integer
 
    stLinkCriteria = "ContactID=" & Forms![frmMainMenu]![cmbClientsContact] 'Open Form In Edit Mode
    DoCmd.OpenForm "frmContact", acNormal, , stLinkCriteria, acFormEdit
 
    ' SID = Me![cmbClients]
    ' Forms![frmContact]![cmbCities] = SID 'Pass City
End Sub

Previously, you're passing the link criteria as the open arguments instead of the where conditions. You can check what are the input parameters for the command by doing so. Right-click on OpenForm of the DoCmd, then select Quick Info from the pop-up window. As you navigate along, you will see what are the parameters that you need to pass in.

Also, just ContactID will be fine in the where condition, no need Me![ContactID].

[JFo]

Ha I knew it was close to what I had when it was working, thanks so much for all your help and your helpful tips

[stmoong]

You're welcome.

The open arguments are useful to pass in additional values which you can use to display in text box or select the value in the combo box of the form being called, instead of setting it from the Click event of the main form.

Ref: http://msdn.microsoft.com/en-us/library/ff836583.aspx

You can pass in delimited string, and then tokenize it using Split function in the Form Load event of the second form.

One more thing, you should set the Record Selectors and Navigation Buttons to No for the Edit form.

[JFo]

Thanks again