Recordset Issues

[Guest]

I have the following code to run some SQL in the load event. The SQL should return 1 row with 1 column

Dim ID As Intege
Dim Room As New ADODB.Recordse
Dim cnn As Objec
Dim cmd As New ADODB.Comman
Dim SQLState As Strin

ID = [Forms]![PatronSelection]![Combo0
SQLState = "SELECT ReservationData.RoomCode FROM ReservationData WHERE (((ReservationData.HostTable) = ID)) GROUP BY ReservationData.RoomCode;
Set cnn = New ADODB.Connectio
Set cnn = CurrentProject.Connectio
'Set Room = New ADODB.Recordse

Room.Open SQLState, cnn, adOpenStatic, adCmdTex

I get the following error
- No value given for one or more required parameters

I am trying to set a combobox with a value that will match the records in a subform

Thank you in advance with any help.

 

[Van T. Dinh]

You need to resolve the VBA Variable ID to explicit value
since the JET engine does not know the VBA Varaible.

Try (****untested****):

Dim Room As New ADODB.Recordset
Dim cnn As Object
Dim cmd As New ADODB.Command
Dim SQLState As String

SQLState = "SELECT ReservationData.RoomCode FROM " & _
"ReservationData WHERE (((ReservationData.HostTable)= " & _
[Forms]![PatronSelection]![Combo0] & ")) " & _
"GROUP BY ReservationData.RoomCode"

'Debug.Print SQLState
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
'Set Room = New ADODB.Recordset

Room.Open SQLState, cnn, adOpenStatic, adCmdText

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I have the following code to run some SQL in the load

event. The SQL should return 1 row with 1 column.

Dim ID As Integer
Dim Room As New ADODB.Recordset
Dim cnn As Object
Dim cmd As New ADODB.Command
Dim SQLState As String

ID = [Forms]![PatronSelection]![Combo0]
SQLState = "SELECT ReservationData.RoomCode FROM

ReservationData WHERE (((ReservationData.HostTable) = ID))
GROUP BY ReservationData.RoomCode;"

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
'Set Room = New ADODB.Recordset

Room.Open SQLState, cnn, adOpenStatic, adCmdText

I get the following error:
- No value given for one or more required parameters.

I am trying to set a combobox with a value that will

match the records in a subform.