Converting Seek into a String Problem

[George]

Hi,

I would like to change the following seek line of code

MyInfo.Index = "ApprovalUpdate": MyInfo.Seek "=",
cboLoanNumber.Column(1), cboLoanNumber, txtInvoice, Me
("lblOption" & ItemCount), Me("lblAmount" & ItemCount),
Null

Into a string I'm not exactly sure how to handle the
Me("lblOption" & ItemCount), Me("lblAmount" & ItemCount),
Null part.
So far, I have the following:

strsql = "SELECT * FROM tblOverAllowableBids WHERE
(((tblOverAllowableBids.ClientNumber)=" _
& "'" & cboLoanNumber.Column(1) & "'" & ") AND " _
& "(InvoiceNumber = " & "'" & txtInvoice & "'" & " And "
_& "(Description = " & "'" & lblOption & ItemCount & "'"
& " And " _
& "(Dollar = " & "'" & lblAmount & ItemCount & "'"
& "))));"
Set MyInfo = MyDB.OpenRecordset(strsql)

1. Does the string that I have do the same thing as the
seek method?
2. Is the syntax correct in my string?


Thanks very much for your help.

George
..

 

[Marshall Barton]

I would like to change the following seek line of code

MyInfo.Index = "ApprovalUpdate": MyInfo.Seek "=",
cboLoanNumber.Column(1), cboLoanNumber, txtInvoice, Me
("lblOption" & ItemCount), Me("lblAmount" & ItemCount),
Null

Into a string I'm not exactly sure how to handle the
Me("lblOption" & ItemCount), Me("lblAmount" & ItemCount),
Null part.
So far, I have the following:

strsql = "SELECT * FROM tblOverAllowableBids WHERE
(((tblOverAllowableBids.ClientNumber)=" _
& "'" & cboLoanNumber.Column(1) & "'" & ") AND " _
& "(InvoiceNumber = " & "'" & txtInvoice & "'" & " And "
_& "(Description = " & "'" & lblOption & ItemCount & "'"
& " And " _
& "(Dollar = " & "'" & lblAmount & ItemCount & "'"
& "))));"
Set MyInfo = MyDB.OpenRecordset(strsql)

1. Does the string that I have do the same thing as the
seek method?
2. Is the syntax correct in my string?

No, that's not going to work. You should first determine
the type of the fields that the Where clause is comparing
to. The reason this is critcally important is that you
should have quotes around Text type fields, # signs around
date values, and nothing around numbers. Taking a guess at
your data types:

WHERE (ClientNumber=" & Me.cboLoanNumber.Column(1) _
& ") AND (InvoiceNumber = " & Me.txtInvoice _
& ") AND (Description = '" & Me(lblOption & ItemCount) _
& "') AND (Dollar = " & Me(lblAmount & ItemCount) &")"