Help Highlighting List Box Item with Code

S

scs

I have found bits of code that helped me make a navigation system on a form.
Basically it'consists of a text box, a list box, and two buttons. The list
box holds all customers names and the text box allows you to type part of a
customer name. You click a search button and it narrows down the list box
selection based on what you typed in the text box. The user then clicks on
a name in the list box and it displays that customers record.

The user also wants to be able to just use a regular combo box to navigate
to the customer. I have created this also.

The screen looks somewhat confusing though. At any time the list box shows
names, the text box can show part of a name, and the combo box displays a
name, and the form displays a record. I'd like to get them all to sort of
sync so that when a user selects a name in the combo box it automatically is
highlighted in the list box. And if a user is using the list box to select
a customer it should either make the combo appear blank or display the
customer. Also it would be nice if it would clear the text box. Can
someone please help with the code to clean this up? Any help would be
appreciated.

Private Sub lstNames_Click()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![lstNames], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

lstNames record source = SELECT qryCustomer.CustomerID, [FirstName] & " " &
[LastName] AS [Full Name] FROM qryCustomer WHERE ((([FirstName] & " " &
[LastName]) Like "*" & Forms!frmCustomerMain.txtSearch & "*")) ORDER BY
[FirstName] & " " & [LastName];
-----------------------------------------------------------------
Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![cboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

cboName record source = SELECT qryCustomer.CustomerID, [FirstName] & " " &
[LastName] AS Name FROM qryCustomer;
-----------------------------------------------------------------
Private Sub cmdSearch_Click()
Me.lstNames.Requery
End Sub
-----------------------------------------------------------------
Private Sub cmdClear_Click()
Me.txtSearch = ""
Me.lstNames.Requery
End Sub


TIA
Steve
 
T

tina

just add code to the combo box's AfterUpdate event procedure, as

Me!ListboxName = Me!ComboboxName
Me!TextboxName = Null

and to the listbox's AfterUpdate event procedure, as

Me!ComboboxName = Me!ListboxName
Me!TextboxName = Null

hth
 
S

scs

Thanks very much!

tina said:
just add code to the combo box's AfterUpdate event procedure, as

Me!ListboxName = Me!ComboboxName
Me!TextboxName = Null

and to the listbox's AfterUpdate event procedure, as

Me!ComboboxName = Me!ListboxName
Me!TextboxName = Null

hth


scs said:
I have found bits of code that helped me make a navigation system on a form.
Basically it'consists of a text box, a list box, and two buttons. The list
box holds all customers names and the text box allows you to type part of a
customer name. You click a search button and it narrows down the list
box
selection based on what you typed in the text box. The user then clicks on
a name in the list box and it displays that customers record.

The user also wants to be able to just use a regular combo box to
navigate
to the customer. I have created this also.

The screen looks somewhat confusing though. At any time the list box shows
names, the text box can show part of a name, and the combo box displays a
name, and the form displays a record. I'd like to get them all to sort
of
sync so that when a user selects a name in the combo box it automatically is
highlighted in the list box. And if a user is using the list box to select
a customer it should either make the combo appear blank or display the
customer. Also it would be nice if it would clear the text box. Can
someone please help with the code to clean this up? Any help would be
appreciated.

Private Sub lstNames_Click()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![lstNames], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

lstNames record source = SELECT qryCustomer.CustomerID, [FirstName] & " " &
[LastName] AS [Full Name] FROM qryCustomer WHERE ((([FirstName] & " " &
[LastName]) Like "*" & Forms!frmCustomerMain.txtSearch & "*")) ORDER BY
[FirstName] & " " & [LastName];
-----------------------------------------------------------------
Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![cboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

cboName record source = SELECT qryCustomer.CustomerID, [FirstName] & " "
&
[LastName] AS Name FROM qryCustomer;
-----------------------------------------------------------------
Private Sub cmdSearch_Click()
Me.lstNames.Requery
End Sub
-----------------------------------------------------------------
Private Sub cmdClear_Click()
Me.txtSearch = ""
Me.lstNames.Requery
End Sub


TIA
Steve
Click to expand...
Click to expand...
 
T

tina

you're welcome :)


scs said:
Thanks very much!

tina said:
just add code to the combo box's AfterUpdate event procedure, as

Me!ListboxName = Me!ComboboxName
Me!TextboxName = Null

and to the listbox's AfterUpdate event procedure, as

Me!ComboboxName = Me!ListboxName
Me!TextboxName = Null

hth


scs said:
I have found bits of code that helped me make a navigation system on a form.
Basically it'consists of a text box, a list box, and two buttons. The list
box holds all customers names and the text box allows you to type part
Click to expand...
of
a
customer name. You click a search button and it narrows down the list
box
selection based on what you typed in the text box. The user then
Click to expand...
clicks
on
a name in the list box and it displays that customers record.

The user also wants to be able to just use a regular combo box to
navigate
to the customer. I have created this also.

The screen looks somewhat confusing though. At any time the list box shows
names, the text box can show part of a name, and the combo box displays a
name, and the form displays a record. I'd like to get them all to sort
of
sync so that when a user selects a name in the combo box it
Click to expand...
automatically
is
highlighted in the list box. And if a user is using the list box to select
a customer it should either make the combo appear blank or display the
customer. Also it would be nice if it would clear the text box. Can
someone please help with the code to clean this up? Any help would be
appreciated.

Private Sub lstNames_Click()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![lstNames], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

lstNames record source = SELECT qryCustomer.CustomerID, [FirstName] & "
Click to expand...
"
&
[LastName] AS [Full Name] FROM qryCustomer WHERE ((([FirstName] & " " &
[LastName]) Like "*" & Forms!frmCustomerMain.txtSearch & "*")) ORDER BY
[FirstName] & " " & [LastName];
-----------------------------------------------------------------
Private Sub cboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![cboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

cboName record source = SELECT qryCustomer.CustomerID, [FirstName] & " "
&
[LastName] AS Name FROM qryCustomer;
-----------------------------------------------------------------
Private Sub cmdSearch_Click()
Me.lstNames.Requery
End Sub
-----------------------------------------------------------------
Private Sub cmdClear_Click()
Me.txtSearch = ""
Me.lstNames.Requery
End Sub


TIA
Steve
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

access runtime error 3314 Me.Bookmark = rs.Bookmark 0
Combo Box 2
rs.FindFirst to find last and first name 2
Combo box help 1
Desparately need help 5
Combo Box error 2
Wayne-In-Manchester 12
Cobo Box lookup record on a form 1

Top