SQL headache

[Krij]

Hi!

A button on frmOrders will let user return an order. But...

The following SQL is giving me a headache:

strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
& "SELECT Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.Returned = True
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID;"

CurrentDb.Execute strPutBack, dbFailOnError

I have a similar construct in another button and it works just nice.

The error message I get is:

Error: #3061 Too few parameters. Expected 2

Now, if I change the last query filter to:

....AND tblOrderdetails.OrderID = " & Me!OrderID

I get: Error: #3061 Too few parameters. Expected 1

Can anyone see what obviously I don't?

Me.Name

Geir Smevig-Baardsen
MCP
http://www.geirb.info
*******************
*Don't reply to my
*email.
*Reply to group
*:-)
*
*
*******************

[Allen Browne]

Concatenate the values from the text boxes into the string:

strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
& "SELECT Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE (tblOrderdetails.Returned = True) " _
& ") AND (tblOrderdetails.ItemID = " &
Forms!frmOrders!frmOrderdetails!ItemID _
& ") AND (tblOrderdetails.OrderID = " & Forms!frmOrders!OrderID & ");"

Extra quotes needed if the fields are Text type.
Won't work correctly if the fields are null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Krij" <gs***@start.no> wrote in message
news:11**********************@b68g2000cwa.googlegr oups.com...

A button on frmOrders will let user return an order. But...

The following SQL is giving me a headache:

strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
& "SELECT Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.Returned = True
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID;"

CurrentDb.Execute strPutBack, dbFailOnError

I have a similar construct in another button and it works just nice.

The error message I get is:

Error: #3061 Too few parameters. Expected 2

Now, if I change the last query filter to:

...AND tblOrderdetails.OrderID = " & Me!OrderID

I get: Error: #3061 Too few parameters. Expected 1

Can anyone see what obviously I don't?

[Thelma Lubkin]

Allen Browne <Al*********@SeeSig.Invalid> wrote:
: Concatenate the values from the text boxes into the string:

: strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
: & "SELECT Amount,MyDate,Returned " _
: & "FROM tblOrderdetails " _
: & "WHERE (tblOrderdetails.Returned = True) " _
: & ") AND (tblOrderdetails.ItemID = " &
/\
|| I think that this is one parenthesis too many.
--thelma
---------------------------------------------

: Allen Browne - Microsoft MVP. Perth, Western Australia.
: Tips for Access users - http://allenbrowne.com/tips.html
: Reply to group, rather than allenbrowne at mvps dot org.

[Krij]

Hi!

Sorry...I made a mistake in pasting code. There are no parenthesis.
There are no textboxes. 'Amount' is an integer, 'MyDate' is Date/Time
and 'Returned' is a Yes/No field.

The fields are identical in both tables.

I was wondering....I'm running this ms access db 2000 on a machine with
Win XP Pro installed and Office XP. Could this be an issue?

I've had some other pecularities, so maybe others have had any
experience with this configuration?

Any suggestion to #Error 3061?

Thelma Lubkin skrev:

Allen Browne <Al*********@SeeSig.Invalid> wrote:
: Concatenate the values from the text boxes into the string:

: strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
: & "SELECT Amount,MyDate,Returned " _
: & "FROM tblOrderdetails " _
: & "WHERE (tblOrderdetails.Returned = True) " _
: & ") AND (tblOrderdetails.ItemID = " &
/\
|| I think that this is one parenthesis too many.
--thelma
---------------------------------------------

: Allen Browne - Microsoft MVP. Perth, Western Australia.
: Tips for Access users - http://allenbrowne.com/tips.html
: Reply to group, rather than allenbrowne at mvps dot org.

[Allen Browne]

There is not problem with your setup. The issue is that the Expression
Service (ES) evaluates the 2 parameters (the values in the text boxes on the
form) when you run the query from the query window, but Access does not
invoke the ES with the Execute method.

The simple solution is concatenate the actual values into the string.

The data type of the 3 fields does not matter. It is the ItemID and OrderID
that matters: these are the 2 fields receiving values from the text boxes in
the WHERE clause.

Error 3061 indicates that there is a name in the query that does not resolve
to a table/field name. In your case, the 2 names that it cannot identify as
table/field names are:
Forms!frmOrders!frmOrderdetails!ItemID
Forms!frmOrders!OrderID
This is because the ES is not available in the context of the Execute method
to tell Access how to read the values from the form.

Thanks, Thelma. There was an extra bracket.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Krij" <gs***@start.no> wrote in message
news:11*********************@r2g2000cwb.googlegrou ps.com...

Hi!

Sorry...I made a mistake in pasting code. There are no parenthesis.
There are no textboxes. 'Amount' is an integer, 'MyDate' is Date/Time
and 'Returned' is a Yes/No field.

The fields are identical in both tables.

I was wondering....I'm running this ms access db 2000 on a machine with
Win XP Pro installed and Office XP. Could this be an issue?

I've had some other pecularities, so maybe others have had any
experience with this configuration?

Any suggestion to #Error 3061?

Thelma Lubkin skrev:

Allen Browne <Al*********@SeeSig.Invalid> wrote:
: Concatenate the values from the text boxes into the string:

: strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
: & "SELECT Amount,MyDate,Returned " _
: & "FROM tblOrderdetails " _
: & "WHERE (tblOrderdetails.Returned = True) " _
: & ") AND (tblOrderdetails.ItemID = " &
/\
|| I think that this is one parenthesis too many.
--thelma
---------------------------------------------

[Lyle Fairfield]

Krij wrote:

strPutBack = "INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
& "SELECT Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.Returned = True
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID;"

I cannot understand why MS-Access continues to recommend such involved
syntax for refering to form values. Is frmOrderdetails a SubForm? In
that case, Forms!frmOrders!frmOrderdetails.Form.ItemID is required?

Regardless I never use such confusing code.

I ensure that each of my forms had the "HasModule" property set to True
(in older versions of Access it may be necessary to write a bit of code
for each form).

Then each property and control on the form can be referred to as
Form_FrmName.Property or Form_FrmName.Control whether the form is open
as main form or as subform (or open at all for that matter).

I would write (air) your code as

strPutBack = "INSERT INTO tblAmountItems " _
& "(Amount, MyDate, Returned) " _
& "SELECT Amount, MyDate, True " _
& "FROM tblOrderdetails " _
& "WHERE ItemID = " & Form_frmOrderdetails.ItemID.Value _
& " AND OrderID = " & OrderID.Value

Depending on circumstances I might modify the last two lines to

& "WHERE ItemID = " & Nz(Form_frmOrderdetails.ItemID.Value, 0) _
& " AND OrderID = " & Nz(OrderID.Value, 0)

I would also put a temporary break at this line (or right after) so
that I could see and evaluate strPutBack before using it.

If I were absolutely committed to doing things MS's convoluted, ugly
and inefficient way then I would try:
strPutBack = "INSERT INTO tblAmountItems " _
& "(Amount, MyDate, Returned) " _
& "SELECT Amount, MyDate, True " _
& "FROM tblOrderdetails " _
& "WHERE ItemID = " & Forms!frmOrders!frmOrderdetails.Form.ItemID _
& " AND OrderID = " & OrderID"

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: I would write (air) your code as

: strPutBack = "INSERT INTO tblAmountItems " _
: & "(Amount, MyDate, Returned) " _
: & "SELECT Amount, MyDate, True " _
: & "FROM tblOrderdetails " _
: & "WHERE ItemID = " & Form_frmOrderdetails.ItemID.Value _
: & " AND OrderID = " & OrderID.Value

I'm not sure how airy this statement was meant to be. Does
specifying what fields to insert into determine which fields
are selected--what would happen in this case if tblOrderdetails
had more than one Boolean field, since the statement as written
doesn't include "Returned = True" in its WHERE clause? Even if
there is only one, does it know enough to choose records with
the Boolean field true?

--thelma

[Lyle Fairfield]

Thelma Lubkin wrote:

Lyle Fairfield <ly***********@aim.com> wrote:
: I would write (air) your code as

: strPutBack = "INSERT INTO tblAmountItems " _
: & "(Amount, MyDate, Returned) " _
: & "SELECT Amount, MyDate, True " _
: & "FROM tblOrderdetails " _
: & "WHERE ItemID = " & Form_frmOrderdetails.ItemID.Value _
: & " AND OrderID = " & OrderID.Value

I'm not sure how airy this statement was meant to be. Does
specifying what fields to insert into determine which fields
are selected--what would happen in this case if tblOrderdetails
had more than one Boolean field, since the statement as written
doesn't include "Returned = True" in its WHERE clause? Even if
there is only one, does it know enough to choose records with
the Boolean field true?

--thelma

Airy enough to have omitted a line.

[Lyle Fairfield]

I conjectured from the nature of:

"INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
& "SELECT Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.Returned = True
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID;"

that the field Returned in tblOrderdetails was being used only to
ensure that the inserted field Returned in tblAmountItems would be set
as True.

Clearly I was wrong. While I believe that it is unlikely, it certainly
may be that there are records in tblOrderdetails
WHERE Returned = False
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID

and that the user/developer does not want these records inserted into
tbAmountItems.

So another airy attempt is:

strPutBack = "INSERT INTO tblAmountItems " _
& "(Amount, MyDate, Returned) " _
& "SELECT Amount, MyDate, True " _
& "FROM tblOrderdetails " _
& "WHERE Returned " _
& "AND ItemID = " & Form_frmOrderdetails.ItemID.Value _
& " AND OrderID = " & OrderID.Value

Thanks, for pointing this out.

[Krij]

Hi!

I would like to give a BIG thank you to all who has contributed with
suggestions and answers.

Not having tried yet, I'm sure I'll solve this by the help I've
received.

Have a nice day! :-)

Me.Name
Lyle Fairfield skrev:

I conjectured from the nature of:

"INSERT INTO tblAmountItems(Amount,MyDate,Returned) " _
& "SELECT Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.Returned = True
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID;"

that the field Returned in tblOrderdetails was being used only to
ensure that the inserted field Returned in tblAmountItems would be set
as True.

Clearly I was wrong. While I believe that it is unlikely, it certainly
may be that there are records in tblOrderdetails
WHERE Returned = False
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID

and that the user/developer does not want these records inserted into
tbAmountItems.

So another airy attempt is:

strPutBack = "INSERT INTO tblAmountItems " _
& "(Amount, MyDate, Returned) " _
& "SELECT Amount, MyDate, True " _
& "FROM tblOrderdetails " _
& "WHERE Returned " _
& "AND ItemID = " & Form_frmOrderdetails.ItemID.Value _
& " AND OrderID = " & OrderID.Value

Thanks, for pointing this out.

[Lyle Fairfield]

Krij wrote:

Lyle Fairfield skrev:

Dim ThelmaIsAbout as Boolean

If ThelmaIsAbout Then
Be Careful with Your Skreving
End If

[Krij]

Hi!

I solved the problem anyway with the help I received. :-)

Skreving? A new english word or...??????

Lyle Fairfield skrev:

Krij wrote:

Lyle Fairfield skrev:

Dim ThelmaIsAbout as Boolean

If ThelmaIsAbout Then
Be Careful with Your Skreving
End If

[Krij]

Hi!

The problem with this SQL statement is that it doesn't 'know' which
record to put into.

Therefore:

strPutBack = "INSERT INTO
tblAmountItems(ItemID,Amount,MyDate,Returned) " _
& "SELECT ItemID,Amount,MyDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.Returned = True
AND tblOrderdetails.ItemID = Forms!frmOrders!frmOrderdetails!ItemID
AND tblOrderdetails.OrderID = Forms!frmOrders!OrderID;"

CurrentDb.Execute strPutBack, dbFailOnError

Now as the ItemID has been identified, it'll work OK :-)

Me.Name

Krij skrev:

Hi!

I solved the problem anyway with the help I received. :-)

Skreving? A new english word or...??????

Lyle Fairfield skrev:

Krij wrote:

Lyle Fairfield skrev:

Dim ThelmaIsAbout as Boolean

If ThelmaIsAbout Then
Be Careful with Your Skreving
End If