Recordset Issues

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.

Thank you in advance with any help.

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.

Thank you in advance with any help.
.