Unbound Combo box Value

[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![Combobox].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.

 

[Marshall Barton]

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![Combobox].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."

Since setting the value via a VBA procedure does not trigger
the usual event sequencem which is a good thing, you need to
call the event procedure when you change the value:

Private Sub Form_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Me!Combobox = Me![User subform].Form!ID
Me!Combobox.AfterUpdate
End Sub

But I am confused about where/why you are using the
MouseDown event. The Click event should be adequeate. My
shorter version of the code assumes the click in in the main
form and will not work if this click is in the subform. If
it is in the subform, then the code would be more like:

Private Sub Form_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Parent!Combobox = Me!ID
Parent!Combobox.AfterUpdate
End Sub

The AfterUpdate event procedure also baffles me. If the
record to be selected is already in the form's record
source, then I don't understand why you need to requery the
form. If the record is not in the form's record source,
then I don't understand the need to search for it.

 

[Icy]

Thank you so much for your answer, there is still problem. I am working on
creating a Log form, the Main form has a unbounded Combobox for user to pick
visitor name and record fields on the main form will populate visitor
information. The first subform will display Log_History (Data Sheet View) for
the visitor which contians date and sponor name. The second subform within
the Main form is acutally to display Sponsor_Detail (Single form view)
information and allow user to veiw and change and save it in the log history.
Reason why I used the mousedown it's because I tried Log_History subform
click event, I have to click on the form itself to activate the change, it
would not respond if I click on the Log_History cell. Maybe i did not code it
right.

Sponsor_Detail subform has a unbounded combobox1 to display sponsor
information just like on the main form as to Visitor, it's triggered by the
After_Update event. I want to be able to click on the Log_History subform and
set the combobox1.value to Sponsor ID at Log_History.
Forms![User]![Sponsor].Form![Combobox1].value=Me!Sponsor solved the first
problem. But just as you mentioned below I have inserted a line of code
Forms![User]![Sponsor].Form![Combobox1].AfterUpdate
when i ran it, i received a error message. "Run-Time error 438, Object
doesn't support this property ormethod. Please help, your assistance is
greatly appreciated.

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![Combobox].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."

Since setting the value via a VBA procedure does not trigger
the usual event sequencem which is a good thing, you need to
call the event procedure when you change the value:

Private Sub Form_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Me!Combobox = Me![User subform].Form!ID
Me!Combobox.AfterUpdate
End Sub

But I am confused about where/why you are using the
MouseDown event. The Click event should be adequeate. My
shorter version of the code assumes the click in in the main
form and will not work if this click is in the subform. If
it is in the subform, then the code would be more like:

Private Sub Form_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Parent!Combobox = Me!ID
Parent!Combobox.AfterUpdate
End Sub

The AfterUpdate event procedure also baffles me. If the
record to be selected is already in the form's record
source, then I don't understand why you need to requery the
form. If the record is not in the form's record source,
then I don't understand the need to search for it.

 

[Marshall Barton]

Thank you so much for your answer, there is still problem. I am working on
creating a Log form, the Main form has a unbounded Combobox for user to pick
visitor name and record fields on the main form will populate visitor
information. The first subform will display Log_History (Data Sheet View) for
the visitor which contians date and sponor name. The second subform within
the Main form is acutally to display Sponsor_Detail (Single form view)
information and allow user to veiw and change and save it in the log history.
Reason why I used the mousedown it's because I tried Log_History subform
click event, I have to click on the form itself to activate the change, it
would not respond if I click on the Log_History cell. Maybe i did not code it
right.

Sponsor_Detail subform has a unbounded combobox1 to display sponsor
information just like on the main form as to Visitor, it's triggered by the
After_Update event. I want to be able to click on the Log_History subform and
set the combobox1.value to Sponsor ID at Log_History.
Forms![User]![Sponsor].Form![Combobox1].value=Me!Sponsor solved the first
problem. But just as you mentioned below I have inserted a line of code
Forms![User]![Sponsor].Form![Combobox1].AfterUpdate
when i ran it, i received a error message. "Run-Time error 438, Object
doesn't support this property ormethod. Please help, your assistance is
greatly appreciated.

AFAIK, the MouseUp/Down and Click events all respond to
clicks in an area the same way. If the click is on a
control, the control event is used. If the click is on a
blank area of a section, the section's event is used. If
the click is on a record's record selector, the form's event
is used.

If you have one form calling a procedure in another form,
the procedure must be declared Public. Just change:

Private Sub Combobox1_AfterUpdate()
to
Public Sub Combobox1_AfterUpdate()

 

[Dale Fye]

Icy,

In addition to the comments made by Marshall, I recommend that you replace
the following line of code. Although the rs.EOF method seems to work, the
"proper" technique for working with the seek and find methods is to check the
NoMatch property of the recordset, not the EOF property.

Replace: If Not rs.EOF Then Me.Bookmark = rs.Bookmark
With : If Not rs.NoMatch then Me.Bookmark = rs.Bookmark

Additionally, although you technically don't have to close your recordset, I
would add the following two lines of code right before the End Sub line.

rs.Close
set rs = nothing

 

[Marshall Barton]

In addition to the comments made by Marshall, I recommend that you replace
the following line of code. Although the rs.EOF method seems to work, the
"proper" technique for working with the seek and find methods is to check the
NoMatch property of the recordset, not the EOF property.

Replace: If Not rs.EOF Then Me.Bookmark = rs.Bookmark
With : If Not rs.NoMatch then Me.Bookmark = rs.Bookmark

Additionally, although you technically don't have to close your recordset, I
would add the following two lines of code right before the End Sub line.

rs.Close
set rs = nothing

Good catch, Dale.

I completely missed the wizard generated EOF.

 

[Icy]

Thank you all so much, really appreciate your time and effort to make my work
easier. Thanks!

 

[Icy]

Sorry have to bother you all again, i just retry the code and change the sub
from private to public, afterupdate event still doesn't work. Run-time error
'438': Object doesn't support this property or method. help!

 

[Dale Fye]

Icy,

Copy your code and paste it here. I'll take a look.

Dale

 

[Marshall Barton]

Sorry have to bother you all again, i just retry the code and change the sub
from private to public, afterupdate event still doesn't work. Run-time error
'438': Object doesn't support this property or method. help!

Are you sure that you used Public for Combobox1's
AfterUpdate event procedure?

Are you sure that the Sponsor control on the datasheet
subform has the same value as Combobox1 on the Sponsor
subform? I am concerned that the datasheet value is a name
while Combobox1's value is an ID number.

I'm losing track of which combo box is being called from
which form/subform. I think you have the main form
searching itself through Combobox, but you also want the
datasheet subform to trigger Combobox1 on the Sponsor
subform. It might help me follow all this if you used the
real names of each form, subform and combo box along with
the code for each.

 

[hypertoaster]

Thank you so much for your answer, there is still problem. I am working on
creating a Log form, the Main form has a unbounded Combobox for user to pick
visitor name and record fields on the main form will populate visitor
information. The first subform will display Log_History (Data Sheet View) for
the visitor which contians date and sponor name. The second subform within
the Main form is acutally to display Sponsor_Detail (Single form view)
information and allow user to veiw and change and save it in the log history.
Reason why I used the mousedown it's because I tried Log_History subform
click event, I have to click on the form itself to activate the change, it
would not respond if I click on the Log_History cell. Maybe i did not code it
right.

Have you considered the F1 button? It may be asking a lot for you to
read the instructions for windows computing but, F1 generally displays
the help system.

It may equally a big ask for you to read the help file but, doing so
would wake you up to the fact that the AfterUpdate event is not used
in the way that you try to implement it. My understanding is that
Access would not even compile the code you have written, instead it
would scream errors at you. You should deal with the implications of
those errors, before you progress to MouseDown events.

Further, I find it shocking that you don't know how a database works
in terms of data persistance but, you are throwing around MouseDown
events and main/subforms at the drop of a hat.

I could try and be sarcastic all day but the truth is this: if you
don't understand the need to requery the form, you don't understand
database programming (and I mean, in the slightest) and should take
several steps backwards.

If you don't understand form events, then you should study the subject
of event properties, and not assume (too much) about them and stumble
on into another problem.

Shorter still - babies tend to walk, before they run.