Simple Query Help

[jplenhart]

I cannot seem to get this query to work no matter what I do. I was wondering if I was doing something wrong - I have checked the database and I know what I am typing in should return a record. My code is listed below



Option Explicit
Private cn As ADODB.Connection
Private rstLotNumber As ADODB.Recordset

Private Sub btnGetResults_Click()
Dim x As Integer
Dim strSQL As String

Set cn = New ADODB.Connection
Set rstLotNumber = New ADODB.Recordset

cn.Provider = "Microsoft.Jet.OLEDB.3.51"
cn.ConnectionString = "c:\Stack Utility\BBF_Test.mdb"
cn.Open

If frmDatabaseMang.txtLotNumber.Text <> "" And frmDatabaseMang.txtLotNumber.Text <> " " Then

LotNumber = frmDatabaseMang.txtLotNumber.Text

strSQL = "Select * " & _
"From tblLotNumber " & _
"Where LotNumber = '" & LotNumber & "'"

ElseIf frmDatabaseMang.txtDateCode.Text <> "" And frmDatabaseMang.txtDateCode.Text <> " " Then

DateCode = frmDatabaseMang.txtDateCode.Text

strSQL = "Select * " & _
"From tblLotNumber " & _
"Where DateCode = '" & DateCode & "'"

Else
MsgBox ("Please Enter a Value for Lot Number or Date Code!")
Exit Sub
End If

rstLotNumber.Source = strSQL

Set rstLotNumber.ActiveConnection = cn

'rstLotNumber.Open "tblLotNumber", cn, adOpenKeyset, adLockOptimistic, adCmdTable

rstLotNumber.Open

frmDatabaseMang.lstResults.AddItem rstLotNumber.RecordCount


For x = 1 To rstLotNumber.RecordCount

frmDatabaseMang.lstResults.AddItem rstLotNumber.Fields("DateCode")
frmDatabaseMang.lstResults.AddItem rstLotNumber.Fields("NumberofDevices")
frmDatabaseMang.lstResults.AddItem rstLotNumber.Fields("CommitDate")
If x <> rstLotNumber.RecordCount Then
rstLotNumber.MoveNext
End If
Next x

rstLotNumber.Close

End Sub

[Lothar Haensler]

If I were you, I'd try the following:
- before executing the query in VB, print out the SQLStatement in the immediate window
- copy and paste it to an Access Query window
- execute it there
- see what the results and errors are
- correct it in Access
- Copy and paste back the SQLstatement to your vb program.

You might even want to consider doing that from the beginning and start using stored queries!