Set Unbound ComboBox Value

  • Thread starter Thread starter Icy
  • Start date Start date
I

Icy

Hello, could anyone please help. I have a unbound combobox as a lookup tool.
user can click on the user name listed in the combobox and it will trigger
the (after_update) event and the information for the user will be populate on
the record field.
Private Sub Combobox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Nz(Me![Combobox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However, when I assign the value on the combobox via VBA.
User is the Main Form
Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single,
Y As Single)
Forms![User].Form![UserID].Value = Forms![User]![User subform].Form![ID].Value
Forms![User].Requery
End Sub

The value on the combo box changed, but the record field did not change, it
still has previous record even after Requery command. I have tried combobox
on_change, on_gotfocus events, no luck so far. I also found the statement
listed below form microsoft website.
"when you change or enter data in these controls in the user interface.
These events also don't occur if you set the value of the control by using
Visual Basic."

Is there anyway this could be achive? Any assistances would be appreciated.
 
typo sorry.
Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single,
Y As Single)
Forms![User].Form![Combobox].Value = Forms![User]![User subform].Form![ID].Value
Forms![User].Requery
End Sub
Click to expand...

Icy said:
Hello, could anyone please help. I have a unbound combobox as a lookup tool.
user can click on the user name listed in the combobox and it will trigger
the (after_update) event and the information for the user will be populate on
the record field.
Private Sub Combobox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Nz(Me![Combobox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However, when I assign the value on the combobox via VBA.
User is the Main Form
Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single,
Y As Single)
Forms![User].Form![UserID].Value = Forms![User]![User subform].Form![ID].Value
Forms![User].Requery
End Sub

The value on the combo box changed, but the record field did not change, it
still has previous record even after Requery command. I have tried combobox
on_change, on_gotfocus events, no luck so far. I also found the statement
listed below form microsoft website.
"when you change or enter data in these controls in the user interface.
These events also don't occur if you set the value of the control by using
Visual Basic."

Is there anyway this could be achive? Any assistances would be appreciated.
Click to expand...
 
Thank you for your reply, I tried, it gave me a error message saying Sub or
Function not defined. It's because I am calling a function in the Main Form
for the SubForm? please help.
Thanks!

Steve Schapel said:
Icy,

You can add a line of code like this:
Call Combobox_AfterUpdate

--
Steve Schapel, Microsoft Access MVP
Hello, could anyone please help. I have a unbound combobox as a lookup tool.
user can click on the user name listed in the combobox and it will trigger
the (after_update) event and the information for the user will be populate on
the record field.
Private Sub Combobox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Nz(Me![Combobox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However, when I assign the value on the combobox via VBA.
User is the Main Form
Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single,
Y As Single)
Forms![User].Form![UserID].Value = Forms![User]![User subform].Form![ID].Value
Forms![User].Requery
End Sub

The value on the combo box changed, but the record field did not change, it
still has previous record even after Requery command. I have tried combobox
on_change, on_gotfocus events, no luck so far. I also found the statement
listed below form microsoft website.
"when you change or enter data in these controls in the user interface.
These events also don't occur if you set the value of the control by using
Visual Basic."

Is there anyway this could be achive? Any assistances would be appreciated.
Click to expand...
Click to expand...
 
Icy,

So, the combobox is on the main form? And the main form is named
'User'? And the MouseDown procedure runs on a subform? And the subform
is named 'User subform'?
 
Back
Top