Double clik item on list box to auto populate other controls

[Sara]

Hi everyone

I am working on a form that has a list box control of items, the
functionality that I need to add is that when the user double cliks on one
item of the list box the other controls that I have on my form should
autopopulate.
This listbox has InquiryId's which are the primary key of my table inquiries.
I am fairly new coding but so far this is what I've tried:

P.S.: "ViewInquiries" is the name of my listbox on the form, and the
"AccessNSUnionInquiries" that I am using on the Select Stament is a query.

Private Sub ViewInquiries_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim strSQL As String
Dim db As DAO.Database
Dim sArgs As String

For Each row In Me.ViewInquiries.ItemsSelected
sArgs = Me.ViewInquiries.Column(1, row)
Next row

sArgs = "Form=" & Me.Name & ";" & _
"InquiryID=" & sArgs & ";"

Set db = CurrentDb
strSQL = "SELECT * FROM AccessNSUnionInquiries" & _
"WHERE Inquiries.InquiryID = sArgs;"

Set rs = CurrentDb.OpenRecordset(strSQL)


With rs

Me.accessNSID = .Fields("RacfID")
Me.FirstName = .Fields("FirstName")
Me.LastName = .Fields("LastName")
Me.Email = .Fields("Email")
Me.Company = .Fields("Company")
Me.City = .Fields("City")
Me.State = .Fields("State")
Me.ProblemName = .Fields("ProblemCategory")
Me.ProblemDescription = .Fields("ProblemType")
Me.Terminal = .Fields("Terminal")
Me.Train = .Fields("TrainNo")
Me.Day = .Fields("Day")
Me.Unit = .Fields("Unit")
Me.Comments = .Fields("Comments")
Me.Frame = .Fields("Source")
End With
rs.Close
Set rs = Nothing
Me.Requery
db.Close
Set db = Nothing

End Sub

I don't even know if this code is close to being correct, I will provide
more details if needed, I would greatly appreciate any help!

 

[Carl Rapson]

Hi everyone

I am working on a form that has a list box control of items, the
functionality that I need to add is that when the user double cliks on one
item of the list box the other controls that I have on my form should
autopopulate.
This listbox has InquiryId's which are the primary key of my table
inquiries.
I am fairly new coding but so far this is what I've tried:

P.S.: "ViewInquiries" is the name of my listbox on the form, and the
"AccessNSUnionInquiries" that I am using on the Select Stament is a query.

Private Sub ViewInquiries_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim strSQL As String
Dim db As DAO.Database
Dim sArgs As String

For Each row In Me.ViewInquiries.ItemsSelected
sArgs = Me.ViewInquiries.Column(1, row)
Next row

sArgs = "Form=" & Me.Name & ";" & _
"InquiryID=" & sArgs & ";"

Set db = CurrentDb
strSQL = "SELECT * FROM AccessNSUnionInquiries" & _
"WHERE Inquiries.InquiryID = sArgs;"

Set rs = CurrentDb.OpenRecordset(strSQL)


With rs

Me.accessNSID = .Fields("RacfID")
Me.FirstName = .Fields("FirstName")
Me.LastName = .Fields("LastName")
Me.Email = .Fields("Email")
Me.Company = .Fields("Company")
Me.City = .Fields("City")
Me.State = .Fields("State")
Me.ProblemName = .Fields("ProblemCategory")
Me.ProblemDescription = .Fields("ProblemType")
Me.Terminal = .Fields("Terminal")
Me.Train = .Fields("TrainNo")
Me.Day = .Fields("Day")
Me.Unit = .Fields("Unit")
Me.Comments = .Fields("Comments")
Me.Frame = .Fields("Source")
End With
rs.Close
Set rs = Nothing
Me.Requery
db.Close
Set db = Nothing

End Sub

I don't even know if this code is close to being correct, I will provide
more details if needed, I would greatly appreciate any help!

You don't say what problem you're having. It appears that you only want to
process a single item from the listbox, so why do you have it set for
multiselect? You could change the Multiselect property to None and do the
following in the DblClick event:

strSQL = "SELECT * FROM AccessNSUnionInquiries" & _
"WHERE Inquiries.InquiryID = " & Me.ViewInquiries
Set rs = CurrentDb.OpenRecordset(strSQL)
' Populate your fields like you do now...

Carl Rapson

 

[Sara]

The problem is that the code doesn't work, when I double clcik an item from
the list box it automatically switchs to the visual basic code.
I use basically one item from the list (InquiryID) which is primary key to
the table from where I am retrieving all the information related to that ID.
I m sorry If I am not very clear, it is kind of a complex case for me a
beginner.

 

[Carl Rapson]

What do you mean, "switches to the visual basic code"? Do you mean the VBA
code window opens? If so, is a particular line of code highlighted? Does an
error message appear?

You might want to try my suggestion and see if it makes a difference. Just
replace everything from your

For each row...

line through (and including)

strSQL = "SELECT..."

with the code I posted, and see what happens. Note: you also don't need the

Set db = CurrentDb

and

db.Close
Set db = Nothing

lines, because you're not using the 'db' variable.

Carl Rapson