Error running Query in form

  • Thread starter Thread starter Bomber
  • Start date Start date
B

Bomber

Hi all,

I'm getting an error message: "Run Time error '3061': , Too few parameters.
Expected 1."

When trying to run this piece of code:

Set TextRS = db.OpenRecordset("SELECT tblConditions_Build.Rec_Id,
tblConditions_Build.AppNo, tblConditions_Build.Condition_Id,
tblConditions_Build.Print_Yes_or_No, tblConditions.Condition_Text FROM
tblConditions_Build LEFT JOIN tblConditions ON
tblConditions_Build.Condition_Id = tblConditions.ID WHERE
(((tblConditions_Build.AppNo)=([forms]![frmMain]![txtAppNo])) AND
((tblConditions_Build.Print_Yes_or_No)=Yes));")

I expect it's failing at the ([forms]![frmMain]![txtAppNo]) ?

If I cut and paste this code in a the sql part of the query builder it works
fine. Any ideas ?

cheers,

Adam
 
Adam,

It may work fine in the SQL view of a query, but in code you have to leave
your references to forms outside the quotes, or Access will treat them as
text rather than references! Change that part to:

(((tblConditions_Build.AppNo)=(" & [forms]![frmMain]![txtAppNo] & ")) AND

HTH,
Nikos
 
Thanks,

Run-Time error '3464' data type mismatch in criteria expression.

When I debug the code below is highlighted:

Set TextRS = db.OpenRecordset("SELECT tblConditions_Build.Rec_Id,
tblConditions_Build.AppNo, tblConditions_Build.Condition_Id,
tblConditions_Build.Print_Yes_or_No, tblConditions.Condition_Text FROM
tblConditions_Build LEFT JOIN tblConditions ON
tblConditions_Build.Condition_Id = tblConditions.ID WHERE
(((tblConditions_Build.AppNo)=(" & [Forms]![frmMain]![txtAppNo] & ")) AND
((tblConditions_Build.Print_Yes_or_No)=Yes));")

Any ideas,

cheers,

Adam


Nikos Yannacopoulos said:
Adam,

It may work fine in the SQL view of a query, but in code you have to leave
your references to forms outside the quotes, or Access will treat them as
text rather than references! Change that part to:

(((tblConditions_Build.AppNo)=(" & [forms]![frmMain]![txtAppNo] & ")) AND

HTH,
Nikos

Bomber said:
Hi all,

I'm getting an error message: "Run Time error '3061': , Too few parameters.
Expected 1."

When trying to run this piece of code:

Set TextRS = db.OpenRecordset("SELECT tblConditions_Build.Rec_Id,
tblConditions_Build.AppNo, tblConditions_Build.Condition_Id,
tblConditions_Build.Print_Yes_or_No, tblConditions.Condition_Text FROM
tblConditions_Build LEFT JOIN tblConditions ON
tblConditions_Build.Condition_Id = tblConditions.ID WHERE
(((tblConditions_Build.AppNo)=([forms]![frmMain]![txtAppNo])) AND
((tblConditions_Build.Print_Yes_or_No)=Yes));")

I expect it's failing at the ([forms]![frmMain]![txtAppNo]) ?

If I cut and paste this code in a the sql part of the query builder it works
fine. Any ideas ?

cheers,

Adam
Click to expand...
Click to expand...
 
Adam,

I assumed field AppNo is numeric; is it text?, If that's the case, then
change that bit of the expression to:

(((tblConditions_Build.AppNo)=('" & [forms]![frmMain]![txtAppNo] & "')) AND

(single quotes inside the double ones).

Also: I use this technique of copying the SQL string from a query's SQL view
as well, and I've always found it itends to add too many parentheses, which
make debugging harder in VB. I usually remove the unnecessary ones. If you
still get problems, try this:

Set TextRS = db.OpenRecordset("SELECT tblConditions_Build.Rec_Id,
tblConditions_Build.AppNo, tblConditions_Build.Condition_Id,
tblConditions_Build.Print_Yes_or_No, tblConditions.Condition_Text FROM
tblConditions_Build LEFT JOIN tblConditions ON
tblConditions_Build.Condition_Id = tblConditions.ID WHERE
tblConditions_Build.AppNo= '" & [forms]![frmMain]![txtAppNo] &"' AND
tblConditions_Build.Print_Yes_or_No)=Yes")

One more thing I just noticed: What type is field Print_Yes_or_No? If it's
Yes/No (Boolean), then the value in the where clause should be True, not
Yes, i.e. tblConditions_Build.Print_Yes_or_No)=True", and the type mismatch
error could be coming from here.

Finally, make sure the whole expression is in one line in the VB editor, or
properly concatenated if on separate lines, not like above!

HTH,
Nikos

Bomber said:
Thanks,

Run-Time error '3464' data type mismatch in criteria expression.

When I debug the code below is highlighted:

Set TextRS = db.OpenRecordset("SELECT tblConditions_Build.Rec_Id,
tblConditions_Build.AppNo, tblConditions_Build.Condition_Id,
tblConditions_Build.Print_Yes_or_No, tblConditions.Condition_Text FROM
tblConditions_Build LEFT JOIN tblConditions ON
tblConditions_Build.Condition_Id = tblConditions.ID WHERE
(((tblConditions_Build.AppNo)=(" & [Forms]![frmMain]![txtAppNo] & ")) AND
((tblConditions_Build.Print_Yes_or_No)=Yes));")

Any ideas,

cheers,

Adam


Nikos Yannacopoulos said:
Adam,

It may work fine in the SQL view of a query, but in code you have to leave
your references to forms outside the quotes, or Access will treat them as
text rather than references! Change that part to:

(((tblConditions_Build.AppNo)=(" & [forms]![frmMain]![txtAppNo] & ")) AND

HTH,
Nikos

Bomber said:
Hi all,

I'm getting an error message: "Run Time error '3061': , Too few parameters.
Expected 1."

When trying to run this piece of code:

Set TextRS = db.OpenRecordset("SELECT tblConditions_Build.Rec_Id,
tblConditions_Build.AppNo, tblConditions_Build.Condition_Id,
tblConditions_Build.Print_Yes_or_No, tblConditions.Condition_Text FROM
tblConditions_Build LEFT JOIN tblConditions ON
tblConditions_Build.Condition_Id = tblConditions.ID WHERE
(((tblConditions_Build.AppNo)=([forms]![frmMain]![txtAppNo])) AND
((tblConditions_Build.Print_Yes_or_No)=Yes));")

I expect it's failing at the ([forms]![frmMain]![txtAppNo]) ?

If I cut and paste this code in a the sql part of the query builder it works
fine. Any ideas ?

cheers,

Adam
Click to expand...
Click to expand...
Click to expand...
 
Back
Top