Form Update from ComboBox

[Barry Moses]

Can anyone see why when I select a 'second' person with the same
lastname...why the form below doesn't change...however it does when I select
a different lastname.

Private Sub Combo73_AfterUpdate()
Dim rst As Recordset
'Find the Last Name Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[LastName] = '" & Me.Combo73 & "'"
Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing
End Sub


Private Sub Form_Current()
Me!Combo73 = Me!LastName
End Sub

 

[Rob Parker]

Although you select a 'second' person in the combobox, your code is finding
the first occurrence of the selection (in the rst.FindFirst "[LastName] = '"
& Me.Combo73 & "'" ) line. For it to work the way (I think) you want, you
need to have the combobox bound to a unique field in your recordset, and
search for that. Your combobox does not have to display the unique field
(eg. PersonID); you can simply set the column width for that field to 0.

So, for example, if your combobox has a row source of:
SELECT PersonID, LastName, FirstName FROM tblPeople ORDER BY LastName,
FirstName
and is bound to Column 1, you can display only the name fields by setting
the Column Count to 3 and the Column Width to 0cm;5cm;5cm. Then if you
change your code to:
...
rst.FindFirst "[PersonID] = '" & Me.Combo73 & "'"
...
it will work as you expect. Note that if PersonID is numeric you will not
need the single quote delimiters; they are only required if PersonID is a
text field.

The code in the Form_Current event will need to reflect this change:
Me!Combo73 = Me!PersonID

And, of course, the form's recordsource must include the unique field (in
this example, PersonID).

HTH,

Rob

 

[Barry Moses]

Thank you, thank you, thank you!!!!

Although you select a 'second' person in the combobox, your code is
finding
the first occurrence of the selection (in the rst.FindFirst "[LastName] =
'"
& Me.Combo73 & "'" ) line. For it to work the way (I think) you want, you
need to have the combobox bound to a unique field in your recordset, and
search for that. Your combobox does not have to display the unique field
(eg. PersonID); you can simply set the column width for that field to 0.

So, for example, if your combobox has a row source of:
SELECT PersonID, LastName, FirstName FROM tblPeople ORDER BY LastName,
FirstName
and is bound to Column 1, you can display only the name fields by setting
the Column Count to 3 and the Column Width to 0cm;5cm;5cm. Then if you
change your code to:
...
rst.FindFirst "[PersonID] = '" & Me.Combo73 & "'"
...
it will work as you expect. Note that if PersonID is numeric you will not
need the single quote delimiters; they are only required if PersonID is a
text field.

The code in the Form_Current event will need to reflect this change:
Me!Combo73 = Me!PersonID

And, of course, the form's recordsource must include the unique field (in
this example, PersonID).

HTH,

Rob

Can anyone see why when I select a 'second' person with the same
lastname...why the form below doesn't change...however it does when I select
a different lastname.

Private Sub Combo73_AfterUpdate()
Dim rst As Recordset
'Find the Last Name Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[LastName] = '" & Me.Combo73 & "'"
Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing
End Sub


Private Sub Form_Current()
Me!Combo73 = Me!LastName
End Sub