Put data in text boxes from data selected in listbox (1 Viewer)

[jpl458]

I have a form that looks like a data entry form but is not. It looks like this:

The query behind the button:

SELECT Suspects.Suspect_ID, Suspects.[First Name], Suspects.[Last Name], Suspects.Company, Suspects.CallCompany
FROM Suspects;

What I want to happen is after the query fills the listbox, I want to double click a row and have the data from that row fill out the above text boxes


I have the following code in the double click event in a listbox:

  Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "ID = " & Me.SusQryResultlb
If Not rs.NoMatch Then
     Me.Bookmark = rs.Bookmark
End If
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "ID = " & Me.SusQryResultlb
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

The problem is that I don't want to load the data into the textboxes based on the key, but rather a different field, Company,
I think the problem is with this line of code:

rs.FindFirst "ID = " & Me.SusQryResultlb

I think it should look something like this:

rs.FindFirst "CallCompany = " & Me.SusQryResultlb(Somthing mysterious goes here)

Or, it's like a foreign join and you can't do that right here.

Thanks

 

[The_Doc_Man]

The USUAL way you do this is that you have multiple columns in the combo box but use the column width to visually hide the data. But of course hiding it visually doesn't hide it digitally. SO... if your user clicks a record in the combo box, you can have code behind the click event to extract values from the combobox's columns.

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

The selected row is, of course, defined by .ListIndex, which is the (0-based) column selector for the combobox.

ComboBox.ListIndex property (Access)

Office VBA reference topic

learn.microsoft.com

So what you would do is on the click event, you would use .ListIndex to figure out the selected row and then use the column number to pick out a particular field. And you would load the values one column at a time from that selected row.

 

[MajP]

You did not tell us what fields are in the listbox.
I assume the bound field of the listbox is CallCompany.
You do not need all that code. Simply

Me.recordset.findfirst "CallCompany = '" & me.SusQryResultlb & "'"

I assume CallCompany is a string and thus 'NameOfCompay'

However that is returning all the records then moving to that record. You could do this in the doubleclick and just return the single record.

Me.Rowsource = "
SELECT Suspects.Suspect_ID, Suspects.[First Name], Suspects.[Last Name], Suspects.Company, Suspects.CallCompany
FROM Suspects WHERE CallCompany = '" & me.SusQryResultlb & "'"

 

[jpl458]

You did not tell us what fields are in the listbox.
I assume the bound field of the listbox is CallCompany.
You do not need all that code. Simply

Me.recordset.findfirst "CallCompany = '" & me.SusQryResultlb & "'"

I assume CallCompany is a string and thus 'NameOfCompay'

However that is returning all the records then moving to that record. You could do this in the doubleclick and just return the single record.

Me.Rowsource = "
SELECT Suspects.Suspect_ID, Suspects.[First Name], Suspects.[Last Name], Suspects.Company, Suspects.CallCompany
FROM Suspects WHERE CallCompany = '" & me.SusQryResultlb & "'"

I was confused, it was late, I was tired. For what I wanted to do, I didn't need a second table. I just needed the one table. Once I figured that out I just copied the code that worked on another form and changed a name and it worked perfectly. Here is the code that works:

 Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "Suspect_ID = " & Me.SusQryResultlb '(Nothing mystical needs to go here)
If Not rs.NoMatch Then
     Me.Bookmark = rs.Bookmark
End If

It uses the key, (Since using only one table) not the company name. Sorry to waste your time. Appreciate your help, especially the rapid responses. (Note, that it was the responses that led me to the right answer)

 

[MajP]

Me.recordset.FindFirst "Suspect_ID = " & Me.SusQryResultlb

You can do much shorter if you want. That does the same.

 

[jpl458]

You can do much shorter if you want. That does the same.

MajP, that works well. Replaced 6 lines of code with one.

Thanks, yet again.

 

[jpl458]

AND, it helped me again.