Synchronizing current record with list box

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
 
G

george 16-17

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...
 
A

Allen Browne

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...
 
G

george 16-17

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...
 
A

Allen Browne

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...
 
G

george 16-17

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Combo Box FindNext 7
VBA for NotInList return to old record or BeforeUpdate value 2
Combo box / Subform Question 7
combo box not updating accoring to record selector 2
Combo to find record does not auto expand 6
Combo box based on a query to navigate to a specific record 6
Search a large recordset 4
Search function with two combos and "and" 2

Top