ado query problem

[Guest]

I am working on a subform called Cheque, which forms part of a form used to
collect payments from clients.
the cheque subform is a continuous form as clients often pay using more than
one cheque and as such most of the details are the same but the amount and
and bankable date are different.

Im trying to write a sub which will import the rest of the details from one
record to the next if on the same subform and the record is empty.

Its giving me a data mismatch in criteria expression error and im not sure
why that is. Ive stopped the code and looked at the values and they seem to
make sense but its not running to the endpoint!

Ive included the code for the Form Current event if it helps:
<!--------------------
Private Sub Form_Current()

Dim strRec As String
Dim ConDatabase As ADODB.Connection
Dim rstCheque As ADODB.Recordset
Dim strSQL As String

'strRec = "Receipts"

'Me![ReceiptNo] = getreceiptNo()
' Initialize Connection object

'if there is no data in the current cheque then it must be a new record
'fill in the name and account information from the a previous cheque record
related to this receipt
'Specify current database as the Provider and then open the database
If IsNull(Me![Name]) Then
Set ConDatabase = CurrentProject.Connection
strSQL = "SELECT name, sortcode FROM cheque where ReceiptNo = '" &
Me![ReceiptNo] & "';"

Set rstCheque = New Recordset
rstCheque.Open strSQL, ConDatabase, adOpenForwardOnly, adLockReadOnly

Me![Name] = rstCheque.Fields(0).Value
Me![SortCode] = rstCheque.Fields(1).Value

' Close Connection object and destroy object variable.
rstFlat.Close
ConDatabase.Close

Set rstCheque = Nothing
Set ConDatabase = Nothing
Else
Exit Sub
End If

End Sub
-------->
With much thanks for your troubles

Amit

 

[Van T. Dinh]

If ReceiptNo is numeric, you should use:

strSQL = "SELECT name, sortcode FROM cheque where ReceiptNo = " & _
Me![ReceiptNo]

Also, change the statement:

Set rstCheque = New Recordset

to

Set rstCheque = New ADODB.Recordset

 

[Guest]

thanks Van,

so much common sense in that, i just gasped 'oh yeah' at the screen when i
read through your suggestion.