Synchronizing current record with list box

  • Thread starter Thread starter george 16-17
  • Start date Start date
G

george 16-17

Greetings all,

I have a three column list box (lstEmpNames) that contains a employee's last
name, first name, and EmpID. I use this list box to find records on my main
form. The bound column is (2) - EmpID.

Here is the code, based on Allen Browne's http://allenbrowne.com/ser-03.html:

'start code************************************
Private Sub lstEmpNames_AfterUpdate()
'Finds record on main form when list box name is clicked
Dim rs As DAO.Recordset

If Not IsNull(Me.lstEmpNames) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[EmpID] = " & Me.lstEmpNames.Column(2)
If rs.NoMatch Then
MsgBox "No matching records", vbOKOnly, "Try again"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
'end code***********************

The code works great, but when I use the form's navigation buttons, the
listbox loses focus and does not highlight the current record. Is there
method to synchronize the list box when the navigation buttons are used? I
searched the discussion groups without finding a solution.

Thanks in advance and any direction is appreciated,
george
 
Hi Allen,

Thanks for taking a look, as your advice is very much appreciated. I just
tried to set the value as suggested, but I am admittedly too new at Access to
figure it out on my own.

I tried this with no success:

Private Sub Form_Current()
Me.lstEmpNames = Me.lstEmpNames.Column(2, Me.txtEmpID.Value)
End Sub

Could you assist with the syntax?

Much appreciated,
george

PS: Your website has been of great help learning Access over the past few
months. I visited it regularly.

Allen Browne said:
Use the Current event of the form to assign a value to the list box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

george 16-17 said:
Greetings all,

I have a three column list box (lstEmpNames) that contains a employee's
last
name, first name, and EmpID. I use this list box to find records on my
main
form. The bound column is (2) - EmpID.

Here is the code, based on Allen Browne's
http://allenbrowne.com/ser-03.html:

'start code************************************
Private Sub lstEmpNames_AfterUpdate()
'Finds record on main form when list box name is clicked
Dim rs As DAO.Recordset

If Not IsNull(Me.lstEmpNames) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[EmpID] = " & Me.lstEmpNames.Column(2)
If rs.NoMatch Then
MsgBox "No matching records", vbOKOnly, "Try again"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
'end code***********************

The code works great, but when I use the form's navigation buttons, the
listbox loses focus and does not highlight the current record. Is there
method to synchronize the list box when the navigation buttons are used? I
searched the discussion groups without finding a solution.

Thanks in advance and any direction is appreciated,
george
Click to expand...
Click to expand...
 
Private Sub Form_Current()
Me.lstEmpNames = Me.txtEmpID
End Sub

That's assuming:
a) The form has a (hidden?) text box named EmpID.
b) The list box is unbound, and its Bound Column is set to the (hidden?)
EmpID value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

george 16-17 said:
Hi Allen,

Thanks for taking a look, as your advice is very much appreciated. I just
tried to set the value as suggested, but I am admittedly too new at Access
to
figure it out on my own.

I tried this with no success:

Private Sub Form_Current()
Me.lstEmpNames = Me.lstEmpNames.Column(2, Me.txtEmpID.Value)
End Sub

Could you assist with the syntax?

Much appreciated,
george

PS: Your website has been of great help learning Access over the past few
months. I visited it regularly.

Allen Browne said:
Use the Current event of the form to assign a value to the list box.

george 16-17 said:
Greetings all,

I have a three column list box (lstEmpNames) that contains a employee's
last
name, first name, and EmpID. I use this list box to find records on my
main
form. The bound column is (2) - EmpID.

Here is the code, based on Allen Browne's
http://allenbrowne.com/ser-03.html:

'start code************************************
Private Sub lstEmpNames_AfterUpdate()
'Finds record on main form when list box name is clicked
Dim rs As DAO.Recordset

If Not IsNull(Me.lstEmpNames) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[EmpID] = " & Me.lstEmpNames.Column(2)
If rs.NoMatch Then
MsgBox "No matching records", vbOKOnly, "Try again"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
'end code***********************

The code works great, but when I use the form's navigation buttons, the
listbox loses focus and does not highlight the current record. Is there
method to synchronize the list box when the navigation buttons are
used? I
searched the discussion groups without finding a solution.
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen,

Thanks again for your response. I appreciate your time.

My bound EmpID text box is not hidden. The list box is unbound, but the
rowsource is set to a table and not the EmpID text box, so the code does not
work.

Any thoughts?

Again, thanks...
george

Allen Browne said:
Private Sub Form_Current()
Me.lstEmpNames = Me.txtEmpID
End Sub

That's assuming:
a) The form has a (hidden?) text box named EmpID.
b) The list box is unbound, and its Bound Column is set to the (hidden?)
EmpID value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

george 16-17 said:
Hi Allen,

Thanks for taking a look, as your advice is very much appreciated. I just
tried to set the value as suggested, but I am admittedly too new at Access
to
figure it out on my own.

I tried this with no success:

Private Sub Form_Current()
Me.lstEmpNames = Me.lstEmpNames.Column(2, Me.txtEmpID.Value)
End Sub

Could you assist with the syntax?

Much appreciated,
george

PS: Your website has been of great help learning Access over the past few
months. I visited it regularly.

Allen Browne said:
Use the Current event of the form to assign a value to the list box.

Greetings all,

I have a three column list box (lstEmpNames) that contains a employee's
last
name, first name, and EmpID. I use this list box to find records on my
main
form. The bound column is (2) - EmpID.

Here is the code, based on Allen Browne's
http://allenbrowne.com/ser-03.html:

'start code************************************
Private Sub lstEmpNames_AfterUpdate()
'Finds record on main form when list box name is clicked
Dim rs As DAO.Recordset

If Not IsNull(Me.lstEmpNames) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[EmpID] = " & Me.lstEmpNames.Column(2)
If rs.NoMatch Then
MsgBox "No matching records", vbOKOnly, "Try again"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
'end code***********************

The code works great, but when I use the form's navigation buttons, the
listbox loses focus and does not highlight the current record. Is there
method to synchronize the list box when the navigation buttons are
used? I
searched the discussion groups without finding a solution.
Click to expand...
Click to expand...
Click to expand...
 
Use a query as the RowSource.

Typically it would be like this:
SELECT EmpID, EmpName
FROM tblEmployee
ORDER BY EmpName

Then you need properties like this so it shows the name and not the ID:
Column Count 2
Column Widthds 0
Bound Column 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

george 16-17 said:
Thanks again for your response. I appreciate your time.

My bound EmpID text box is not hidden. The list box is unbound, but the
rowsource is set to a table and not the EmpID text box, so the code does
not
work.

Any thoughts?

Again, thanks...
george

Allen Browne said:
Private Sub Form_Current()
Me.lstEmpNames = Me.txtEmpID
End Sub

That's assuming:
a) The form has a (hidden?) text box named EmpID.
b) The list box is unbound, and its Bound Column is set to the (hidden?)
EmpID value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

george 16-17 said:
Hi Allen,

Thanks for taking a look, as your advice is very much appreciated. I
just
tried to set the value as suggested, but I am admittedly too new at
Access
to
figure it out on my own.

I tried this with no success:

Private Sub Form_Current()
Me.lstEmpNames = Me.lstEmpNames.Column(2, Me.txtEmpID.Value)
End Sub

Could you assist with the syntax?

Much appreciated,
george

PS: Your website has been of great help learning Access over the past
few
months. I visited it regularly.

:

Use the Current event of the form to assign a value to the list box.

Greetings all,

I have a three column list box (lstEmpNames) that contains a
employee's
last
name, first name, and EmpID. I use this list box to find records on
my
main
form. The bound column is (2) - EmpID.

Here is the code, based on Allen Browne's
http://allenbrowne.com/ser-03.html:

'start code************************************
Private Sub lstEmpNames_AfterUpdate()
'Finds record on main form when list box name is clicked
Dim rs As DAO.Recordset

If Not IsNull(Me.lstEmpNames) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[EmpID] = " & Me.lstEmpNames.Column(2)
If rs.NoMatch Then
MsgBox "No matching records", vbOKOnly, "Try again"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
'end code***********************

The code works great, but when I use the form's navigation buttons,
the
listbox loses focus and does not highlight the current record. Is
there
method to synchronize the list box when the navigation buttons are
used? I
searched the discussion groups without finding a solution.
Click to expand...
Click to expand...
Click to expand...
 
Got it working!

Thanks again for your time and expertise.

george

Allen Browne said:
Use a query as the RowSource.

Typically it would be like this:
SELECT EmpID, EmpName
FROM tblEmployee
ORDER BY EmpName

Then you need properties like this so it shows the name and not the ID:
Column Count 2
Column Widthds 0
Bound Column 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

george 16-17 said:
Thanks again for your response. I appreciate your time.

My bound EmpID text box is not hidden. The list box is unbound, but the
rowsource is set to a table and not the EmpID text box, so the code does
not
work.

Any thoughts?

Again, thanks...
george

Allen Browne said:
Private Sub Form_Current()
Me.lstEmpNames = Me.txtEmpID
End Sub

That's assuming:
a) The form has a (hidden?) text box named EmpID.
b) The list box is unbound, and its Bound Column is set to the (hidden?)
EmpID value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for taking a look, as your advice is very much appreciated. I
just
tried to set the value as suggested, but I am admittedly too new at
Access
to
figure it out on my own.

I tried this with no success:

Private Sub Form_Current()
Me.lstEmpNames = Me.lstEmpNames.Column(2, Me.txtEmpID.Value)
End Sub

Could you assist with the syntax?

Much appreciated,
george

PS: Your website has been of great help learning Access over the past
few
months. I visited it regularly.

:

Use the Current event of the form to assign a value to the list box.

Greetings all,

I have a three column list box (lstEmpNames) that contains a
employee's
last
name, first name, and EmpID. I use this list box to find records on
my
main
form. The bound column is (2) - EmpID.

Here is the code, based on Allen Browne's
http://allenbrowne.com/ser-03.html:

'start code************************************
Private Sub lstEmpNames_AfterUpdate()
'Finds record on main form when list box name is clicked
Dim rs As DAO.Recordset

If Not IsNull(Me.lstEmpNames) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[EmpID] = " & Me.lstEmpNames.Column(2)
If rs.NoMatch Then
MsgBox "No matching records", vbOKOnly, "Try again"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
'end code***********************

The code works great, but when I use the form's navigation buttons,
the
listbox loses focus and does not highlight the current record. Is
there
method to synchronize the list box when the navigation buttons are
used? I
searched the discussion groups without finding a solution.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top