SQL headache

**Allen Browne** · Jun 25 '06, 06:35 AM

Re: SQL headache

Concatenate the values from the text boxes into the string:

strPutBack = "INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
& "SELECT Amount,MyDate,R eturned " _
& "FROM tblOrderdetails " _
& "WHERE (tblOrderdetail s.Returned = True) " _
& ") AND (tblOrderdetail s.ItemID = " &
Forms!frmOrders !frmOrderdetail s!ItemID _
& ") AND (tblOrderdetail s.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" <gsb58@start.no > wrote in message
news:1151216789 .081808.169590@ b68g2000cwa.goo glegroups.com.. .[color=blue]
>
> 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,R eturned) " _
> & "SELECT Amount,MyDate,R eturned " _
> & "FROM tblOrderdetails " _
> & "WHERE tblOrderdetails .Returned = True
> AND tblOrderdetails .ItemID = Forms!frmOrders !frmOrderdetail s!ItemID
> AND tblOrderdetails .OrderID = Forms!frmOrders !OrderID;"
>
> CurrentDb.Execu te 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?[/color]

**Thelma Lubkin** · Jun 25 '06, 06:45 AM

Re: SQL headache

Allen Browne <AllenBrowne@Se eSig.Invalid> wrote:
: Concatenate the values from the text boxes into the string:

: strPutBack = "INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
: & "SELECT Amount,MyDate,R eturned " _
: & "FROM tblOrderdetails " _
: & "WHERE (tblOrderdetail s.Returned = True) " _
: & ") AND (tblOrderdetail s.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** · Jun 25 '06, 07:05 AM

Re: SQL headache

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:
[color=blue]
> Allen Browne <AllenBrowne@Se eSig.Invalid> wrote:
> : Concatenate the values from the text boxes into the string:
>
> : strPutBack = "INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
> : & "SELECT Amount,MyDate,R eturned " _
> : & "FROM tblOrderdetails " _
> : & "WHERE (tblOrderdetail s.Returned = True) " _
> : & ") AND (tblOrderdetail s.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.[/color]

**Allen Browne** · Jun 25 '06, 07:55 AM

Re: SQL headache

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 !frmOrderdetail s!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" <gsb58@start.no > wrote in message
news:1151218947 .284705.43080@r 2g2000cwb.googl egroups.com...[color=blue]
> 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:
>[color=green]
>> Allen Browne <AllenBrowne@Se eSig.Invalid> wrote:
>> : Concatenate the values from the text boxes into the string:
>>
>> : strPutBack = "INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
>> : & "SELECT Amount,MyDate,R eturned " _
>> : & "FROM tblOrderdetails " _
>> : & "WHERE (tblOrderdetail s.Returned = True) " _
>> : & ") AND (tblOrderdetail s.ItemID = " &
>> /\
>> || I think that this is one parenthesis too many.
>> --thelma
>> ---------------------------------------------[/color][/color]

**Lyle Fairfield** · Jun 25 '06, 08:45 AM

Re: SQL headache

Krij wrote:[color=blue]
> strPutBack = "INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
> & "SELECT Amount,MyDate,R eturned " _
> & "FROM tblOrderdetails " _
> & "WHERE tblOrderdetails .Returned = True
> AND tblOrderdetails .ItemID = Forms!frmOrders !frmOrderdetail s!ItemID
> AND tblOrderdetails .OrderID = Forms!frmOrders !OrderID;"[/color]

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 !frmOrderdetail s.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.Pr operty or Form_FrmName.Co ntrol 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_frmOrderde tails.ItemID.Va lue _
& " AND OrderID = " & OrderID.Value

Depending on circumstances I might modify the last two lines to

& "WHERE ItemID = " & Nz(Form_frmOrde rdetails.ItemID .Value, 0) _
& " AND OrderID = " & Nz(OrderID.Valu e, 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 !frmOrderdetail s.Form.ItemID _
& " AND OrderID = " & OrderID"

**Thelma Lubkin** · Jun 25 '06, 03:25 PM

Re: SQL headache

Lyle Fairfield <lylefairfield@ 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_frmOrderde tails.ItemID.Va lue _
: & " 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** · Jun 25 '06, 04:55 PM

Re: SQL headache

Thelma Lubkin wrote:[color=blue]
> Lyle Fairfield <lylefairfield@ 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_frmOrderde tails.ItemID.Va lue _
> : & " 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[/color]

Airy enough to have omitted a line.

**Lyle Fairfield** · Jun 25 '06, 06:55 PM

Re: SQL headache

I conjectured from the nature of:

"INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
& "SELECT Amount,MyDate,R eturned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails .Returned = True
AND tblOrderdetails .ItemID = Forms!frmOrders !frmOrderdetail s!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 !frmOrderdetail s!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_frmOrderde tails.ItemID.Va lue _
& " AND OrderID = " & OrderID.Value

Thanks, for pointing this out.

**Krij** · Jun 26 '06, 06:45 AM

Re: SQL headache

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:
[color=blue]
> I conjectured from the nature of:
>
> "INSERT INTO tblAmountItems( Amount,MyDate,R eturned) " _
> & "SELECT Amount,MyDate,R eturned " _
> & "FROM tblOrderdetails " _
> & "WHERE tblOrderdetails .Returned = True
> AND tblOrderdetails .ItemID = Forms!frmOrders !frmOrderdetail s!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 !frmOrderdetail s!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_frmOrderde tails.ItemID.Va lue _
> & " AND OrderID = " & OrderID.Value
>
> Thanks, for pointing this out.[/color]

**Lyle Fairfield** · Jun 26 '06, 06:55 AM

Re: SQL headache

Krij wrote:
[color=blue]
> Lyle Fairfield skrev:[/color]

Dim ThelmaIsAbout as Boolean

If ThelmaIsAbout Then
Be Careful with Your Skreving
End If

**Krij** · Jun 26 '06, 07:05 AM

Re: SQL headache - no longer a headache :-)

Hi!

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

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

Lyle Fairfield skrev:
[color=blue]
> Krij wrote:
>[color=green]
> > Lyle Fairfield skrev:[/color]
>
> Dim ThelmaIsAbout as Boolean
>
> If ThelmaIsAbout Then
> Be Careful with Your Skreving
> End If[/color]

**Krij** · Jun 26 '06, 12:35 PM

Re: SQL headache - no longer a headache :-)

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,M yDate,Returned) " _
& "SELECT ItemID,Amount,M yDate,Returned " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails .Returned = True
AND tblOrderdetails .ItemID = Forms!frmOrders !frmOrderdetail s!ItemID
AND tblOrderdetails .OrderID = Forms!frmOrders !OrderID;"

CurrentDb.Execu te strPutBack, dbFailOnError

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

Me.Name

Krij skrev:
[color=blue]
> Hi!
>
> I solved the problem anyway with the help I received. :-)
>
> Skreving? A new english word or...??????
>
> Lyle Fairfield skrev:
>[color=green]
> > Krij wrote:
> >[color=darkred]
> > > Lyle Fairfield skrev:[/color]
> >
> > Dim ThelmaIsAbout as Boolean
> >
> > If ThelmaIsAbout Then
> > Be Careful with Your Skreving
> > End If[/color][/color]

SQL headache

SQL headache

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment