dlookup replacement

[Andrew Glennie]

Hi All,

I have a continuous form which uses a query to provide its main data, but
there are a couple of checkboxes which pick up their data from another query
via a dlookup but they are very slow to display. From another post, I created
a DAO recordset but keep getting Run Time Error 3061, Too Few Parameters.

The code is as follows:

Dim rsLevel As DAO.Recordset
Dim LevelSql As String

LevelSql = "SELECT AttendeeID, [L2 Attended], [L4 Attended] FROM
[Attendance List]" & _
"WHERE AttendeeID=[Forms]![Attendees List]![AttendeeID]"

Set rsLevel = CurrentDb.OpenRecordset(LevelSql)
Me.L2Attended = rsLevel![L2 Attended]
Me.L4Attended = rsLevel![L4 Attended]

rsLevel.Close

The code falls over at the Set rsLevel line.

Any suggestions gratefully received.

Regards

Andrew

 

[Allen Browne]

You need to concenate the value from the form into the string:

LevelSql = "SELECT AttendeeID, [L2 Attended], [L4 Attended] " & _
"FROM [Attendance List] " & _
"WHERE AttendeeID = " & [Forms]![Attendees List]![AttendeeID]

That won't work if the text box is null.
It also needs delimiters if the AttendeeID field is other than Number.

Also, here's a replacement DLookup():
http://allenbrowne.com/ser-42.html

 

[Andrew Glennie]

Thanks heaps Allen. Solved the problem nicely.

Andrew

You need to concenate the value from the form into the string:

LevelSql = "SELECT AttendeeID, [L2 Attended], [L4 Attended] " & _
"FROM [Attendance List] " & _
"WHERE AttendeeID = " & [Forms]![Attendees List]![AttendeeID]

That won't work if the text box is null.
It also needs delimiters if the AttendeeID field is other than Number.

Also, here's a replacement DLookup():
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi All,

I have a continuous form which uses a query to provide its main data, but
there are a couple of checkboxes which pick up their data from another
query
via a dlookup but they are very slow to display. From another post, I
created
a DAO recordset but keep getting Run Time Error 3061, Too Few Parameters.

The code is as follows:

Dim rsLevel As DAO.Recordset
Dim LevelSql As String

LevelSql = "SELECT AttendeeID, [L2 Attended], [L4 Attended] FROM
[Attendance List]" & _
"WHERE AttendeeID=[Forms]![Attendees List]![AttendeeID]"

Set rsLevel = CurrentDb.OpenRecordset(LevelSql)
Me.L2Attended = rsLevel![L2 Attended]
Me.L4Attended = rsLevel![L4 Attended]

rsLevel.Close

The code falls over at the Set rsLevel line.

Any suggestions gratefully received.

Regards

Andrew