SQL and Show some record by setting some criteria

[chuanwang]

Dear all,

I have one Access database table (tblTable) as following

id contents Field1 Field2 Field3

1 C1 A1 B1 D1
1 C2 A2 B2 D2
2 C3 A1 B2 D1
2 C4 A3 B1 D3
5 C5 A2 B3 D2
6 C6 A3 B3 D3
7 C7 A1 B2 D4

id and contents are primary key. What I want to get is: when I put id and contents into two textboxes, then the corresponding value in Field can be shown in the third textbox.

On my form, I have three textboxes (two to input value which must match id and contents fields, one to display the value of Field), one data control with its database property set to my database, one cmdButton.

I put the following code in the form
-----------------------------------
Dim mySQL As String
mySQL = "SELECT tblTable.Field1.Value FROM tblTable Where ((tblTable.id) like '" & Text1.Text & "*'And (tblTable.contents) like '" & Text2.Text & "*')"
data1.RecordSource = mySQL
data1.Refresh
----------------------------------------

I take one example, when I put 1 in Text1, C2 in Text2, when I press cmdButton, in Text3 it is supposed to appear A2. The problem is:
when I run it, one message appear saying
--------------
Run-time error '3601':
Too few parameters, Expected 1.
-------------

When I press Debug, it shows that there is something wrong with the code "datGradient.Refresh"

I hope someone can help me with this question.

Thank you very much for any help!
Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[edderic]

Why Like?

mySQL = "SELECT * FROM tblTable Where id = '" & Text1.Text & "'"
mySQL =mySQL & " And contents = '" & Text2.Text & "'"
Eric De Decker
vbg.be@vbgroup.nl

 

[RalphBrown]

I wouldn't use SQL for this. It seems a bit complicated when FindFirst will do the job.
Instead I'd try:

with data1
.RecordSource = "tblTable"
.Refresh

myCriteria = "id = '" & Text1.Text & "'"
myCriteria = myCriteria & " And contents = '" & Text2.Text & "'"
.findfirst myCriteria
if not .recordset.nomatch then
text3.text = .recordset("Field1&quot
endif
end with

Hope this helps,
Ralph

 

[RalphBrown]

...however, if you prefer to use SQL, begin your statement:
"SELECT Field1 FROM tblTable" etc.,
instead of "SELECT tblTable.Field1.Value FROM tblTable"

Ralph