Select record in a listbox

[sebastico]

Hello

John W. Vinson helped me with a code to search records in a Table to show in
lsbAut

Private Sub CmdA_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT AuID, AutName FROM TAut WHERE AutName LIKE 'A*' _
& " ORDER BY AutName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbAut.RowSource = strSQL
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Now I would like to do is:

1. In lsbAut select a record by AutID
2. Search selected record in TAutObID
‘There is at least one record in TAutObID
‘TAutObID has only two fields AuID and ObID
3.If AutID record is found show the record with AuID, ObId Fields in lbsObID

I tried walking the listbox collection looking for similar application. I
could not find any. I found this “You could also use an unbound list box to
find a record based on the value you select in the list boxâ€. However, I
could not found a code to select a row in a listbox.

Could you suggest me a code.
Any help is appreciated

 

[Daryl S]

Sebastico -

I assume you put your listbox on a form, and the listbox is unbound. The
recordsource for your form should be the table or query that has the data you
want to view or update.

Then in the AfterUpdate event from your listbox, you should have code like
this:

' Find the record that matches the control.
Dim rs As Object

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

which should navigate to the record you have chosen.

 

[sebastico]

Dary

I did what you said, also with the query in the recordsource

I tested the code in the same form having the lsbAutObr, but the new list
box displays all the records.

I tested the code in a new form with same results

The code means that I must navigated through the lisbox to find AutObID
fields, or selecting a record in the lsbAut the code will show in the lsbAuOb
only the records I had chosen.

Thanks for your help

Sebastico -

I assume you put your listbox on a form, and the listbox is unbound. The
recordsource for your form should be the table or query that has the data you
want to view or update.

Then in the AfterUpdate event from your listbox, you should have code like
this:

' Find the record that matches the control.
Dim rs As Object

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

which should navigate to the record you have chosen.

--
Daryl S

Hello

John W. Vinson helped me with a code to search records in a Table to show in
lsbAut

Private Sub CmdA_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT AuID, AutName FROM TAut WHERE AutName LIKE 'A*' _
& " ORDER BY AutName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbAut.RowSource = strSQL
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Now I would like to do is:

1. In lsbAut select a record by AutID
2. Search selected record in TAutObID
‘There is at least one record in TAutObID
‘TAutObID has only two fields AuID and ObID
3.If AutID record is found show the record with AuID, ObId Fields in lbsObID

I tried walking the listbox collection looking for similar application. I
could not find any. I found this “You could also use an unbound list box to
find a record based on the value you select in the list boxâ€. However, I
could not found a code to select a row in a listbox.

Could you suggest me a code.
Any help is appreciated

 

[Daryl S]

Sebastico -

Not sure which query and which recordsource you changed. You said you
already had the listbox recordsource working, so you should not have changed
that. I understood you needed to be able to display the record from TAutObID
based on what was selected in your listbox. If so, then the form that has
the unbound listbox on it should have the recordsource as either the TAutObID
table or a query based on this table. Then the code I suggested would bring
up the selected record in the form.

When I re-read your original posting based on your new posting, it sounds
like you want a second listbox to show the new data, not the form. If this
is the case, then scratch what I said before, and do a similar thing with the
second listbox as you did with the first, but the recordsource for the second
listbox would be this:
strSQL = "SELECT AuID, ObID FROM TAutObID WHERE AuID = '" & Me.lsbAut(0)
& "';"

Is that what you are looking for?
--
Daryl S

Dary

I did what you said, also with the query in the recordsource

I tested the code in the same form having the lsbAutObr, but the new list
box displays all the records.

I tested the code in a new form with same results

The code means that I must navigated through the lisbox to find AutObID
fields, or selecting a record in the lsbAut the code will show in the lsbAuOb
only the records I had chosen.

Thanks for your help

Sebastico -

I assume you put your listbox on a form, and the listbox is unbound. The
recordsource for your form should be the table or query that has the data you
want to view or update.

Then in the AfterUpdate event from your listbox, you should have code like
this:

' Find the record that matches the control.
Dim rs As Object

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

which should navigate to the record you have chosen.

--
Daryl S

Hello

John W. Vinson helped me with a code to search records in a Table to show in
lsbAut

Private Sub CmdA_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT AuID, AutName FROM TAut WHERE AutName LIKE 'A*' _
& " ORDER BY AutName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbAut.RowSource = strSQL
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Now I would like to do is:

1. In lsbAut select a record by AutID
2. Search selected record in TAutObID
‘There is at least one record in TAutObID
‘TAutObID has only two fields AuID and ObID
3.If AutID record is found show the record with AuID, ObId Fields in lbsObID

I tried walking the listbox collection looking for similar application. I
could not find any. I found this “You could also use an unbound list box to
find a record based on the value you select in the list boxâ€. However, I
could not found a code to select a row in a listbox.

Could you suggest me a code.
Any help is appreciated

 

[sebastico]

Hello Daryl

Yes, I want a second listbox (in the same Form) to show the new data, not
the form.
I'm going to test your code and I let you know.

Many thanks

Sebastico -

Not sure which query and which recordsource you changed. You said you
already had the listbox recordsource working, so you should not have changed
that. I understood you needed to be able to display the record from TAutObID
based on what was selected in your listbox. If so, then the form that has
the unbound listbox on it should have the recordsource as either the TAutObID
table or a query based on this table. Then the code I suggested would bring
up the selected record in the form.

When I re-read your original posting based on your new posting, it sounds
like you want a second listbox to show the new data, not the form. If this
is the case, then scratch what I said before, and do a similar thing with the
second listbox as you did with the first, but the recordsource for the second
listbox would be this:
strSQL = "SELECT AuID, ObID FROM TAutObID WHERE AuID = '" & Me.lsbAut(0)
& "';"

Is that what you are looking for?
--
Daryl S

Dary

I did what you said, also with the query in the recordsource

I tested the code in the same form having the lsbAutObr, but the new list
box displays all the records.

I tested the code in a new form with same results

The code means that I must navigated through the lisbox to find AutObID
fields, or selecting a record in the lsbAut the code will show in the lsbAuOb
only the records I had chosen.

Thanks for your help

Sebastico -

I assume you put your listbox on a form, and the listbox is unbound. The
recordsource for your form should be the table or query that has the data you
want to view or update.

Then in the AfterUpdate event from your listbox, you should have code like
this:

' Find the record that matches the control.
Dim rs As Object

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

which should navigate to the record you have chosen.

--
Daryl S


:

Hello

John W. Vinson helped me with a code to search records in a Table to show in
lsbAut

Private Sub CmdA_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT AuID, AutName FROM TAut WHERE AutName LIKE 'A*' _
& " ORDER BY AutName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbAut.RowSource = strSQL
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Now I would like to do is:

1. In lsbAut select a record by AutID
2. Search selected record in TAutObID
‘There is at least one record in TAutObID
‘TAutObID has only two fields AuID and ObID
3.If AutID record is found show the record with AuID, ObId Fields in lbsObID

I tried walking the listbox collection looking for similar application. I
could not find any. I found this “You could also use an unbound list box to
find a record based on the value you select in the list boxâ€. However, I
could not found a code to select a row in a listbox.

Could you suggest me a code.
Any help is appreciated

 

[sebastico]

Daryl

In my form I have the lsbAut to show records from TAut table. Your code is
working well.

Now if I select a row in the lsbAut what I want is after clicking the row in
lsbAut a code will navigate TAuIDObIS Table to to find all records that match
AuID and show those records in lsbAuID. Both listboxes are in the same form.
Actually the code show all records in lsbAuIDObID clicking or not the row in
lsbAut.Howevwe, as I I said I want the lsbAuIDObID showing only records that
match AuID in TAuIDObIS Table.

I wonder If I can have two listboxes in the same form searching two tables.
If so, how can I have two recordsource in the same form? If not, how can link
the lsbAuIDObID in different form.

Perhaps I have syntax problem but I do not know what t is. I hope I'm
explaining myself correctly. If you can guide me in the right direction it
would be a big help. Please let me know step by step due to I am learning vba.

Thanks againg

Hello Daryl

Yes, I want a second listbox (in the same Form) to show the new data, not
the form.
I'm going to test your code and I let you know.

Many thanks

Sebastico -

Not sure which query and which recordsource you changed. You said you
already had the listbox recordsource working, so you should not have changed
that. I understood you needed to be able to display the record from TAutObID
based on what was selected in your listbox. If so, then the form that has
the unbound listbox on it should have the recordsource as either the TAutObID
table or a query based on this table. Then the code I suggested would bring
up the selected record in the form.

When I re-read your original posting based on your new posting, it sounds
like you want a second listbox to show the new data, not the form. If this
is the case, then scratch what I said before, and do a similar thing with the
second listbox as you did with the first, but the recordsource for the second
listbox would be this:
strSQL = "SELECT AuID, ObID FROM TAutObID WHERE AuID = '" & Me.lsbAut(0)
& "';"

Is that what you are looking for?
--
Daryl S

Dary

I did what you said, also with the query in the recordsource

I tested the code in the same form having the lsbAutObr, but the new list
box displays all the records.

I tested the code in a new form with same results

The code means that I must navigated through the lisbox to find AutObID
fields, or selecting a record in the lsbAut the code will show in the lsbAuOb
only the records I had chosen.

Thanks for your help

:

Sebastico -

I assume you put your listbox on a form, and the listbox is unbound. The
recordsource for your form should be the table or query that has the data you
want to view or update.

Then in the AfterUpdate event from your listbox, you should have code like
this:

' Find the record that matches the control.
Dim rs As Object

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

which should navigate to the record you have chosen.

--
Daryl S


:

Hello

John W. Vinson helped me with a code to search records in a Table to show in
lsbAut

Private Sub CmdA_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT AuID, AutName FROM TAut WHERE AutName LIKE 'A*' _
& " ORDER BY AutName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbAut.RowSource = strSQL
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Now I would like to do is:

1. In lsbAut select a record by AutID
2. Search selected record in TAutObID
‘There is at least one record in TAutObID
‘TAutObID has only two fields AuID and ObID
3.If AutID record is found show the record with AuID, ObId Fields in lbsObID

I tried walking the listbox collection looking for similar application. I
could not find any. I found this “You could also use an unbound list box to
find a record based on the value you select in the list boxâ€. However, I
could not found a code to select a row in a listbox.

Could you suggest me a code.
Any help is appreciated

 

[Daryl S]

See comments in line below...
--
Daryl S

Daryl

In my form I have the lsbAut to show records from TAut table. Your code is
working well. Great!

Now if I select a row in the lsbAut what I want is after clicking the row in
lsbAut a code will navigate TAuIDObIS Table to to find all records that match
AuID and show those records in lsbAuID. Both listboxes are in the same form.
Actually the code show all records in lsbAuIDObID clicking or not the row in
lsbAut.Howevwe, as I I said I want the lsbAuIDObID showing only records that
match AuID in TAuIDObIS Table.

You need to change the recordsource for lsbAuIDObID to be a query from that
table where the AuID is the one selected in the lsbAuID. That recordsource
would be:
strSQL = "SELECT AuID, ObID FROM TAutObID WHERE AuID = '" & Me.lsbAut(0) &
"';"

I wonder If I can have two listboxes in the same form searching two tables.
If so, how can I have two recordsource in the same form? If not, how can link
the lsbAuIDObID in different form.

You can have lots of listboxes with a different recordsource than the form's
recordsource. You want the form's recordsource to be the from the table or
query that will show what you want the user to see. The listboxes can have
different recordsources from the form, as shown above.

Perhaps I have syntax problem but I do not know what t is. I hope I'm
explaining myself correctly. If you can guide me in the right direction it
would be a big help. Please let me know step by step due to I am learning vba.

Based on the notes above, I would check the recordsources for the form
versus the listboxes. It sounds like you have the recordsource for the first
listbox correct. The second one is given above, and relies on the first
listbox. The recordsource for the form would be your base table or query
containing the data you want to see, but restricted to the objects in your
second listbox. Often this is done with a subform that shows all records
that match the criteria on the form. You would create this subform (like you
create any form) based on all the data in the tables you want to see. Then
you would add this subform to the main form, and at that time link the child
records (in the subform) to the parent record (from the second listbox), so
that the subform only displays the records you have selected from the
listboxes.

So, get your two listboxes working the way you want, then create a new form
(that will be used as the subform) to show the data you want the users to see
or work with - you can use the wizards for this. Then finally, add the
subform to the main form - again use the wizard to link the fields.

Hope that helps!

 

[Daryl S]

One thing else - after you make a selection in the first combo box, be sure
to requery the recordsource for the second combo box. This is done in the
AfterUpdate event of the first combo box (lsbAuID), and looks like this:
Me.lsbAuIDObID.Requery

 

[sebastico]

Many thanks Daryl

Finally with your code I was able to do it.