Im having syntax problems!

[Benjamin Bolduc]

Hi there,

I don't know what I'm doing wrong. Pat Hartman gave me some great code to take a value from a listbox and apply to a where statment of a report. The code works fine untill I try to tweak with it a little.

Private Sub TheList_DblClick(Cancel As Integer)
Dim stWhere As String
Dim stWhere2 As String
Dim Switch As String
Switch = ChoiceList.Value

Select Case Switch
Case 1
stWhere2 = "[Supplier]=" & Me.TheList.Column(1)
DoCmd.OpenReport "Dueordersreport", acViewPreview, , stWhere
Case 2
stWhere = "[Transaction#]=" & Me.TheList.Column(0)
DoCmd.OpenReport "Dueordersreport", acViewPreview, , stWhere
End Select


End Sub


ok, now the second case works fine. (This is what Pat gave me).It only sends one record to the report because [Transaction#] is the primary key.

It's Case 1 that I'm having trouble with. (This is the one I tried to tweak myself). This one should send more than one value to the report, ( All records with similar Suppliers), but all it does is open the report with all of the records.

Do i need to take a different approach for case 1? or is it just a syntax error?

Any insight would be greatly appreciated.

Thanks!
Ben

 

[Travis]

What does Me.TheList.Column(1) return: a number or a string?

if it is a number then, does the field [supplier] appear in the Reports recordset?

if it is a string then modify the line as follows:

stWhere = "[Supplier]='" & Me.TheList.Column(1) & "'"


Or

the problem could be that you are populating the stWhere2 variable but using the stWhere variable in your open report line.

 

[Benjamin Bolduc]

I modified the line as you said, and it works perfectly now. It was just a syntax problem. Someday, Ill catch on to all these crazy syntax's

Thanks!
Ben