asked on

SQL / Query Help

Why does this code not work??? Not that it should I am new to VB Database programing sooooo... Basically I a trying to fill the txt boxes with a record that is selected via a double click on a name in a list box. Here is the code I have what am I missing or doing Wrong help please TIA

Private Sub List1_DblClick()
'Label1.Caption = List1.Text
Data1.Recordset.MoveFirst
Dim ListVal As String
ListVal = List1.Text
Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE Name = " & (ListVal)

'Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE OfficeID = 'Branmar'"
'Data1.Refresh ' Refresh The Table
While Not Data1.Recordset.EOF
txtSAName.Text = Data1.Recordset("Name")
txtOffice.Text = Data1.Recordset("OfficeID")
txtPhone.Text = Data1.Recordset("Phone") 'Field on SELECT
Data1.Recordset.MoveNext ' View all Field Content
Wend
End Sub

AJAY CHADHA

Private Sub List1_DblClick()
'Label1.Caption = List1.Text
Data1.Recordset.MoveFirst
Dim ListVal As String
ListVal = List1.Text
Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE Name = " & List1.List(List1.ListIndex)

While Not Data1.Recordset.EOF

txtSAName.Text = Data1.Recordset("Name")
txtOffice.Text = Data1.Recordset("OfficeID")
txtPhone.Text = Data1.Recordset("Phone")

Data1.Recordset.MoveNext
Wend
End Sub

ASKER CERTIFIED SOLUTION

LeifB

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dspavlik

ASKER

chadhaajay--- THat didn't work....

AJAY CHADHA

Private Sub List1_DblClick()
'Label1.Caption = List1.Text
Data1.Recordset.MoveFirst
Dim ListVal As String
ListVal = List1.Text
Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE Name = " & List1.Text
While Not Data1.Recordset.EOF

txtSAName.Text = Data1.Recordset("Name")
txtOffice.Text = Data1.Recordset("OfficeID")
txtPhone.Text = Data1.Recordset("Phone")

Data1.Recordset.MoveNext
Wend
End Sub

AJAY CHADHA

Private Sub List1_DblClick()
'Label1.Caption = List1.Text
Data1.Recordset.MoveFirst
Dim ListVal As String
ListVal = List1.Text
Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE Name = " & List1.Text
While Not Data1.Recordset.EOF

txtSAName.Text = Data1.Recordset("Name")
txtOffice.Text = Data1.Recordset("OfficeID")
txtPhone.Text = Data1.Recordset("Phone")

Data1.Recordset.MoveNext
Wend
End Sub

hes

As LeifB pointed out you are missing the single quotes around the string variable

Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE Name = '" & ListVal & "'"
(after Name = is single quote and a double quote)
(after ListVal & Double quote single quote Double quote)

TonyS020799

Hello chadhaajay,
Not sure if you meant to post it as an answer or not, it appears you had some trouble with the multiple postings. However, the questioner has indicated that your solution did not work.

TonyS
Community Support Moderator @ Experts Exchange

Hornet241

Try changing this

ListVal = List1.Text

to

ListVal = List1(List1.Listindex).List

Hornet241

Sorry wrong order, use this

ListVal = List1.List(List1.Listindex)

dspavlik

ASKER

This works a little. I clears the text out of the text boxes. but doesn't fill them with info. Any Ideas

'Label1.Caption = List1.Text
Data1.Recordset.MoveFirst
Dim ListVal As String
ListVal = List1.Text
Data1.RecordSource = "SELECT DISTINCT Name,OfficeID,Phone FROM Agents WHERE Name = '" & ListVal & "'"

While Not Data1.Recordset.EOF

txtSAName.Text = Data1.Recordset("Name")
txtOffice.Text = Data1.Recordset("OfficeID")
txtPhone.Text = Data1.Recordset("Phone")

Data1.Recordset.MoveNext
Wend

Hornet241

See my previous comment

ListVal = List1.List(List1.Listindex)

.text is not a valid property of a list box

cjswimmer

Moondancer

It's time to clean up this topic area and that means taking care of this question. Your options at this point are:

1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.

2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

3. Delete the question because it is of no value to you or to anyone else. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

If you elect for option 2 or 3, all you need to do is post right here as a comment, and I will take care of the rest. We also request that you review any other open questions you might have and deal with them as necessary.

PLEASE DO NOT AWARD THE POINTS TO ME.

____________________________________________

Hi Experts:

In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question. Likewise, you can also suggest that I PAQ or delete the question.

Experts, please do not add further "answer" information to this question. I will be back in about one week to finalize this question.

Thank you everyone.

Moondancer - Community Support Moderator @ Experts Exchange

costello

Homet241's comment will be accepted in 4 days if no objections are made.

costello
Community Support Moderator @ Experts-Exchange

Computer101

Cleaning up this questioners open questions.

Thank you
Computer101
Community Support Moderator