Undefined Function 2

[lars7]

hi,
I am trying to add a couple of columns in to this code.
The code was working fine with "lstbox4 and "lstbox5 but when I added the columns I got an "Undefined function" message box, does anyone know why.

Case 2
If Trim(Forms!frmreportandupdates!lstbox4 & lstbox5 & lstbox5.Column(2) & lstbox5.Column(3) & "") <> "" Then
DoCmd.OpenReport "rptgroupcode", acViewPreview, "", "[contact]=[Forms]![frmreportandupdates]![lstbox4] And [location]=[Forms]![frmreportandupdates]![lstbox5] and [grp_code]=[Forms]![frmreportandupdates]![lstbox5].column(2) and [Pay_Pt]=[Forms]![frmreportandupdates]![lstbox5].column(3)

 

[TheAceMan1]

how are ya lars7 . . .

Be aware: [blue]Column Index[/blue] for listbox starts at 0 (zero)!

See Ya! . . . . . .

 

[lars7]

Hi Ace,
I changed the column numbers but still the same message box, here it is in full.

Undefined function '[Forms]![frmreportandupdates]![lstbox5]. column' in expression.

and the new code I tried:

Case 2
If Trim(Forms!frmreportandupdates!lstbox4 & lstbox5 & lstbox5.Column(1) & lstbox5.Column(2) & "") <> "" Then
DoCmd.OpenReport "rptgroupcode", acViewPreview, "", "[contact]=[Forms]![frmreportandupdates]![lstbox4] And [location]=[Forms]![frmreportandupdates]![lstbox5] and [grp_code]=[Forms]![frmreportandupdates]![lstbox5].column(1) and [Pay_Pt]=[Forms]![frmreportandupdates]![lstbox5].column(2)

 

[PHV]

You may try this:
DoCmd.OpenReport "rptgroupcode", acViewPreview, "", "contact='" & lstbox4 & '" And location='" & lstbox5 & '" and grp_code='" & lstbox5.column(2) & "' and Pay_Pt='" & lstbox5.column(3) & "'"
For each field defined as numeric get rid of the corresponding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi Again,
I used your code PHV and after a little alteration it's putting the right information into the filter of the report. I got a syntac error originally and had to remove some of the single quotes although none of the fields are numeric and Ace was right about the column numbers. As I said although I'm getting the right info into the filter but the report isn't pulling the right info from the query but I think that's a seperate problem and I will have a look at that. Thanks to you both again for the help and for responding so quickly.

Case 2
DoCmd.OpenReport "rptgroupcode", acViewPreview, "", "contact='" & lstbox4 & " And location= " & lstbox5 & " and grp_code= " & lstbox5.Column(1) & "' and Pay_Pt='" & lstbox5.Column(2) & "'

 

[lars7]

Hi,
I final got the report to open with the required information. It wasn't the query as I had thought,It was something to do with the single quotes and the spaces between them. Here is what worked:

Case 2
DoCmd.OpenReport "rptgroupcode", acViewPreview, "", "contact='" & lstbox4 & "'and location='" & lstbox5 & "'and grp_code='" & lstbox5.Column(1) & "'and Pay_Pt='" & lstbox5.Column(2) & "'"

Thanks again for the help.

 

[PHV]

Oh, I see, sorry for the typos :~/

 

[lars7]

:-D