Recordset problem

[andybeanland]

Hello,

I've got an really annoying problem - I copied and pasted some code for opening a recordset but it won't work now - I can't find the problem. This is what I've got:
<code>
Dim conDatabase As ADODB.Connection
Dim rs As ADODB.Recordset

Set conDatabase = CurrentProject.Connection

Set rs = New Recordset
rs.Open SQL, conDatabase, 3, 3
</code>
If fails at this last line with the message
"No value given for one or more required parameters"

I've checked the SQL statement by printing it to a message box and it seems ok. Someone help me stop my stupid mistake!

Thanks

Andy

 

[RoyVidar]

Use Debug.Print, to print it to the immediate pane, then study it there (hit ctrl+g), or copy it to the SQL view of the QBE. If it is correct, it should probably run.

Look for typos in the name of the criteria fields, missing parameters and wrong delimiters for the criteria.

Roy-Vidar

 

[andybeanland]

I've checked the SQL in Access and it runs without problems but I couldn't get the Debug.Print to display anything in the immediate pane.

Can you suggest a different way of opening a recordset that I can try?

Thanks

Andy

 

[RoyVidar]

As far as I know the error relates to the SQL statement, not the way one opens the recordset. You should find other ways to open a recordset through search, but you'd probably get the same error anyway...

[tt]Debug.print strsql
Debug.print "select ..<rest of your statement>..."[/tt]

Roy-Vidar

 

[andybeanland]

Okay, I've done a debug.print of the SQL statement and this it what it gave me - all on one line - which I copied into the query builder in access and ran. No problems - and it returned what I was expecting....But when I'm still having the same problem when opening the recordset!

SELECT tbl_Child.Surname, tbl_Event.EventDescription, tbl_Monday.Attending AS tbl_Monday_Attending, tbl_Monday.StartTime AS tbl_Monday_StartTime, tbl_Monday.EndTime AS tbl_Monday_EndTime, tbl_Tuesday.Attending AS tbl_Tuesday_Attending, tbl_Tuesday.StartTime AS tbl_Tuesday_StartTime, tbl_Tuesday.EndTime AS tbl_Tuesday_EndTime, tbl_Wednesday.Attending AS tbl_Wednesday_Attending, tbl_Wednesday.StartTime AS tbl_Wednesday_StartTime, tbl_Wednesday.EndTime AS tbl_Wednesday_EndTime, tbl_Thursday.Attending AS tbl_Thursday_Attending, tbl_Thursday.StartTime AS tbl_Thursday_StartTime, tbl_Thursday.EndTime AS tbl_Thursday_EndTime, tbl_Friday.Attending AS tbl_Friday_Attending, tbl_Friday.StartTime AS tbl_Friday_StartTime, tbl_Friday.EndTime AS tbl_Friday_EndTime, tbl_Attendance.StartDate, tbl_Attendance.EndDate, tbl_Child.Forename, tbl_Attendance.Attendance_ID, tbl_Monday.ID As MonID, tbl_Tuesday.ID AS TueID, tbl_Wednesday.ID AS WedID, tbl_Thursday.ID AS ThuID, tbl_Friday.ID AS FriID FROM tbl_Child INNER JOIN (tbl_Wednesday INNER JOIN (tbl_Tuesday INNER JOIN (tbl_Thursday INNER JOIN (tbl_Monday INNER JOIN (tbl_Friday INNER JOIN (tbl_Event INNER JOIN tbl_Attendance ON tbl_Event.Event_ID = tbl_Attendance.Event_ID) ON tbl_Friday.ID = tbl_Attendance.Friday_ID) ON tbl_Monday.ID = tbl_Attendance.Monday_ID) ON tbl_Thursday.ID = tbl_Attendance.Thursday_ID) ON tbl_Tuesday.ID = tbl_Attendance.Tuesday_ID) ON tbl_Wednesday.ID = tbl_Attendance.Wednesday_ID) ON tbl_Child.Child_ID = tbl_Attendance.Child_ID WHERE (((tbl_Attendance.Attendance_ID) = [Forms]![frm_AttendingSelectAttendance].[AttendanceID]));

 

[RoyVidar]

When concatenating the string, you need to concatenate the value of the reference into the string, not the rereference itself:

[tt]...WHERE (((tbl_Attendance.Attendance_ID) = " & _
[Forms]![frm_AttendingSelectAttendance].[AttendanceID] & "));"[/tt]

This assumes Attendance_ID is numeric, for text, use single quotes.

Roy-Vidar

 

[andybeanland]

I just thought I would add that the SQL was created using the Query design view in Access - would this be a problem?

 

[andybeanland]

Roy, you genius! Thanks - I've been tearing my hair out with that for ages!

Right, I'm off to make some more stupid errors!

Cheers