Display Value in combo box onload or afterupdate

[stmoong]

Sometimes when you stare too hard, you'll miss it.



[tblStates].StateID=" & Me.cmbCities

should be

[tblStates].CityID=" & Me.cmbCities

[JFo]

Perfect, thanks very much... I am silly

it should actually be [tblCities].CityID=" & Me.cmbCities

but that was the problem, thanks for all your help


Now for the slightly more difficult second part of my question trying to start the combos in display mode from the values from the main form (edit mode).

I will have a go at this now with the code I have and mark this thread as solved if I can figure it out

Cheers again

[stmoong]

You're welcome.

[JFo]

Yes I have struggled on and on for many hours and I cannot do this from form to form, this is the main thing I want it to do, I am lost

When a form opens how can it be so difficult (for me anyway) to display the value without having to select it from a list?

[stmoong]

Just set the values of the controls in the Form Load event.

[JFo]

That is what I have tried to do many times however it does not work for me using


Private Sub Form_Load()

With Me.cmbCities
.RowSource = _
"SELECT [tblCities].CityID, [tblCities].CityName, [tblContact].CityID FROM tblCities" & _
" RIGHT JOIN tblContact ON tblContact.CityID=[tblCities].CityID" & _
" WHERE [tblCities].CityID =" & Forms!frmMainMenu![cmbClientsContact] & " ORDER BY [tblCities].CityName;"
.Requery
.Value = .ItemData(0)
.Visible = True
End With
End Sub

[JFo]

The value works to be selected from list if i use

SELECT tblCities.CityID, tblCities.CityName, tblCities.Postcode, tblCities.StateID FROM tblCities WHERE (((tblCities.[CityID])=[Forms]![frmContact]![CityID] OR [Forms]![frmContact]![CityID] Is Null)) ORDER BY tblCities.CityName;

in row source otherwise I am stuck on this one


Thanks

[stmoong]

That is what I have tried to do many times however it does not work for me using


Private Sub Form_Load()

With Me.cmbCities
.RowSource = _
"SELECT [tblCities].CityID, [tblCities].CityName, [tblContact].CityID FROM tblCities" & _
" RIGHT JOIN tblContact ON tblContact.CityID=[tblCities].CityID" & _
" WHERE [tblCities].CityID =" & Forms!frmMainMenu![cmbClientsContact] & " ORDER BY [tblCities].CityName;"
.Requery
.Value = .ItemData(0)
.Visible = True
End With
End Sub

Is the control name highlighted correct?

If it is correct, then you will need to set the value for [cmbClientsContact] first before setting this [cmbCities] control.

[JFo]

Yes the name is correct here is the SQL that populates this combo box in the main form

With Me.cmbClientsContact
.RowSource = _
"SELECT [tblContact].ContactID, [tblContact].ContactName, [tblContact].CityID, [tblContact].ContactSurName FROM tblClients" & _
" RIGHT JOIN tblContact ON tblClients.ClientID=[tblContact].ClientID" & _
" WHERE [tblContact].ClientID=" & Me.cmbClients & " ORDER BY [tblContact].ContactName;"
.Requery
.Value = .ItemData(12)
.Visible = True
End With

From this selection that is what populates entire contact form and all text boxes work it's just the combos don't display

Thanks again

[stmoong]

Change this ".Value = .ItemData(12)" to ".Value = .ItemData(0)" to select the first value. Why did you select the 11th row?

Forms!frmMainMenu![cmbClientsContact] change to Me.cmbClientsContact for consistency (unless I misunderstood that this control is on another form?)

[JFo]

Change this ".Value = .ItemData(12)" to ".Value = .ItemData(0)" to select the first value. Why did you select the 11th row? Sorry yes this was 12 because I was lazy deleting my table

Forms!frmMainMenu![cmbClientsContact] change to Me.cmbClientsContact for consistency (unless I misunderstood that this control is on another form?) yes it is on another form

Basically on main form I select the contact name from combo box and click command button to edit client and it opens contact form that is populated with all contact details. This works for everything except I want to display the combo result without selecting it from list

[stmoong]

Oh, now it's clearer.

I haven't tested this. The query will give you a list of city. Then you just need to set the value to the same as the one from frmMainMenu.

With Me.cmbCities
.RowSource = _
"SELECT [tblCities].CityID, [tblCities].CityName, [tblContact].CityID FROM tblCities" & _
" RIGHT JOIN tblContact ON tblContact.CityID=[tblCities].CityID" & _
" ORDER BY [tblCities].CityName;"
.Requery
.Value = Forms!frmMainMenu![cmbClientsContact]
.Visible = True
End With

Both the cmbClientsContact and cmbCities should be bounded to CityID for it to work, otherwise, you need to reference the correct column to get the matching data.

[JFo]

Thanks for your reply I was going to reply and say that your method didn't work however I have broken my own design which may be the problem as it was working and can't fix it

trying to open in edit mode the command button I had something like this

Private Sub cmdEditClientsContact_Click()
Dim stLinkCriteria As String
Dim SID As Integer

stLinkCriteria = "Me![ContactID]=' " & Forms![frmMainMenu]![cmbClientsContact]

DoCmd.OpenForm "frmContact", acNormal, , , acFormEdit, , stLinkCriteria


but now this is delivering same person every time independent of what is selected in the combo

[JFo]

It is selecting first entry in table every time to edit, regardless

[stmoong]

The only thing I can think of is you need to set .Value to be what was selected in the main form. Previously, it was selecting the first item via .ItemData(0)

Maybe better if you could post a database up.