Hi George
All the fields are text boxes. The person who created
this gave them label prefixes for some reason. I changed
the part that you told me to, and tried to take out some
parentheses, but its still not working. I currently have:
My question was not: "are all the controls on the form textboxes?" Rather,
I was asking: "are all the fields in the table that you are comparing text
fields?".
I suspect, for example, that the field [Dollar] is currency, not text, and
therefore any value you compare it with should not be enclosed in quotes.
Also, I assume you have a control or variable named "ItemCount", and that is
the current value of ItemCount is, say, 3, then you have textboxes named
"lblOption3" and "lblAmount3".
That said, and assuming [Dollar] is currency and all the other fields are
text, try the following:
strsql = "SELECT * FROM tblOverAllowableBids WHERE " _
& "ClientNumber=" & "'" & Me!cboLoanNumber.Column(1) & "'" _
& " and " _
& "InvoiceNumber=" & "'" & Me!txtInvoice & "'" _
& " and " _
& "Description=" & "'" & Me("lblOption" & ItemCount) & "'" _
& " and " _
& "Dollar=" & Me("lblAmount" & ItemCount) & ";"
I've laid it out with an excessive number of concatenations to make the
whole thing clearer. Note the absence of parentheses <g>.
If this doesn't work, then use the following debug technique:
1) put a breakpoint on the next line of code
2) run the code normally
3) when it stops at the breakpoint, type the following in the Immediate
window:
?strsql <enter>
4) your sql string (SELECT * FROM...) will be displayed - select the entire
string and copy it.
5) create a new, empty query and switch it from design view to SQL view
6) paste the SQL string into the SQL window, replacing anything that is
there
7) run the query
Hopefully this will give you an error message which will help you diagnose
the exact problem with the SQL string.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
-----Original Message-----
Hi George
Where you have:
& "'" & lblOption & ItemCount & "'" &
it should be:
& "'" & Me("lblOption" & ItemCount) & "'" &
Same thing with lblAmount.
This assumes that all the fields you are comparing are text fields, because
you are enclosing all the comparison values in quotes. Numeric fields (and
currency) do not need the quotes.
You also seem to have a large number of unnecessary parentheses, and I
haven't checked that they all match up. You'd be better off removing some
to simplify the expression.
Also, is Me("lblOption" & ItemCount) a label, as is suggested by its name?
If so then you've got another problem because a label does not have a Value
property. If you want the text of the label, then use:
Me("lblOption" & ItemCount).Caption
I must say it is most unusual to display data in labels.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
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
.
