creating entry in 2 tables

  • Thread starter Thread starter joan
  • Start date Start date
J

joan

Hi there,

I hope someone can help with this, I'm at my wits end. I
am trying to insert records into a Receipts and
ReceiptsLine tables based on the user pressing a cmd
button on the PO. I'd like to insert records only based
on the PONumber the user is currently on. What is
happening is I am getting a parameter window asking for
Me.PONumber for each of the following SQL statements.
Also the ReceiptLines statement is inserting in
additional records i.e. POLine has 1 record and the
receipt will have 3 all the same part?! I thought I had
this working, but every change brings on new issue!
Sorry for length of msg, just wanted to give all info I
could. HELP!!! thx in advance.

Function Receipt()

Dim SQLReceipt As String
Dim SQLReceiptLine As String



SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber " & _
"FROM tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
QtyRecd, PONumber, ItemDesc, ReceiptNo ) " & _
"SELECT tblPOLine.Description, tblPOLine.QtyOrdered,
tblPOLine.PONumber, tblItemMaster.Description,
tblReceipts.ReceiptNo " & _
"FROM tblReceipts, (tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber) INNER JOIN
tblItemMaster ON tblPOLine.Description =
tblItemMaster.ItemNo " & _
"WHERE (((tblPOLine.PONumber)= Me.PONumber))"

DoCmd.RunSQL SQLReceiptLine
 
Hi Joan,

I the problem is that you have your field reference
(Me.PONumber) inside your string. Try building your
string. As an example, listed below is part of your sql
string:

"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

try revising as follows:
"HAVING (((tblPOHeader.PONumber)= '" & Me.PONumber & "')
AND (([tblPOHeader]![Post])<>True))"

Of course, the above assumes PONumber is a string value,
if it is a number than the 's before and after would not
be needed.

Hope that helps.
 
Thank you for your help. It seems I still have a problem
though. I am getting Invalid use of keyword Me error.
When I go to help it says Me can be used in a class
module but not standard module. I'm not sure I know how
to determine the difference between the two.

How would I put this code in a class module?

Thx in advance for any help.
-----Original Message-----
Hi Joan,

I the problem is that you have your field reference
(Me.PONumber) inside your string. Try building your
string. As an example, listed below is part of your sql
string:

"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

try revising as follows:
"HAVING (((tblPOHeader.PONumber)= '" & Me.PONumber & "')
AND (([tblPOHeader]![Post])<>True))"

Of course, the above assumes PONumber is a string value,
if it is a number than the 's before and after would not
be needed.

Hope that helps.
-----Original Message-----
Hi there,

I hope someone can help with this, I'm at my wits end. I
am trying to insert records into a Receipts and
ReceiptsLine tables based on the user pressing a cmd
button on the PO. I'd like to insert records only based
on the PONumber the user is currently on. What is
happening is I am getting a parameter window asking for
Me.PONumber for each of the following SQL statements.
Also the ReceiptLines statement is inserting in
additional records i.e. POLine has 1 record and the
receipt will have 3 all the same part?! I thought I had
this working, but every change brings on new issue!
Sorry for length of msg, just wanted to give all info I
could. HELP!!! thx in advance.

Function Receipt()

Dim SQLReceipt As String
Dim SQLReceiptLine As String



SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber "
Click to expand...
&
_
"FROM tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
QtyRecd, PONumber, ItemDesc, ReceiptNo ) " & _
"SELECT tblPOLine.Description, tblPOLine.QtyOrdered,
tblPOLine.PONumber, tblItemMaster.Description,
tblReceipts.ReceiptNo " & _
"FROM tblReceipts, (tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber) INNER JOIN
tblItemMaster ON tblPOLine.Description =
tblItemMaster.ItemNo " & _
"WHERE (((tblPOLine.PONumber)= Me.PONumber))"

DoCmd.RunSQL SQLReceiptLine

.
Click to expand...
.
Click to expand...
 
Along with prev msg re: Class module/Standard module. I
looked into help on the two above and I was able to move
my code to a class module I created. But I don't know
what it is looking for to initialize the class module.
I'm not sure I should be using this type of module? When
I try to reassign my macro to 'runcode' the Function
Receipt is not showing under my Class Module, is this
because it's not initialized. I am new to all this, if
anyone has an idea for me...or a better way to accomplish
me getting the info into my tables, please help.

thank you all in advance.
-----Original Message-----
Hi Joan,

I the problem is that you have your field reference
(Me.PONumber) inside your string. Try building your
string. As an example, listed below is part of your sql
string:

"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

try revising as follows:
"HAVING (((tblPOHeader.PONumber)= '" & Me.PONumber & "')
AND (([tblPOHeader]![Post])<>True))"

Of course, the above assumes PONumber is a string value,
if it is a number than the 's before and after would not
be needed.

Hope that helps.
-----Original Message-----
Hi there,

I hope someone can help with this, I'm at my wits end. I
am trying to insert records into a Receipts and
ReceiptsLine tables based on the user pressing a cmd
button on the PO. I'd like to insert records only based
on the PONumber the user is currently on. What is
happening is I am getting a parameter window asking for
Me.PONumber for each of the following SQL statements.
Also the ReceiptLines statement is inserting in
additional records i.e. POLine has 1 record and the
receipt will have 3 all the same part?! I thought I had
this working, but every change brings on new issue!
Sorry for length of msg, just wanted to give all info I
could. HELP!!! thx in advance.

Function Receipt()

Dim SQLReceipt As String
Dim SQLReceiptLine As String



SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber "
Click to expand...
&
_
"FROM tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
QtyRecd, PONumber, ItemDesc, ReceiptNo ) " & _
"SELECT tblPOLine.Description, tblPOLine.QtyOrdered,
tblPOLine.PONumber, tblItemMaster.Description,
tblReceipts.ReceiptNo " & _
"FROM tblReceipts, (tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber) INNER JOIN
tblItemMaster ON tblPOLine.Description =
tblItemMaster.ItemNo " & _
"WHERE (((tblPOLine.PONumber)= Me.PONumber))"

DoCmd.RunSQL SQLReceiptLine

.
Click to expand...
.
Click to expand...
 
-----Original Message-----
Hi Joan,

I the problem is that you have your field reference
(Me.PONumber) inside your string. Try building your
string. As an example, listed below is part of your sql
string:

"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

try revising as follows:
"HAVING (((tblPOHeader.PONumber)= '" & Me.PONumber & "')
AND (([tblPOHeader]![Post])<>True))"

Of course, the above assumes PONumber is a string value,
if it is a number than the 's before and after would not
be needed.

Hope that helps.
-----Original Message-----
Hi there,

I hope someone can help with this, I'm at my wits end. I
am trying to insert records into a Receipts and
ReceiptsLine tables based on the user pressing a cmd
button on the PO. I'd like to insert records only based
on the PONumber the user is currently on. What is
happening is I am getting a parameter window asking for
Me.PONumber for each of the following SQL statements.
Also the ReceiptLines statement is inserting in
additional records i.e. POLine has 1 record and the
receipt will have 3 all the same part?! I thought I had
this working, but every change brings on new issue!
Sorry for length of msg, just wanted to give all info I
could. HELP!!! thx in advance.

Function Receipt()

Dim SQLReceipt As String
Dim SQLReceiptLine As String



SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber "
Click to expand...
&
_
"FROM tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
QtyRecd, PONumber, ItemDesc, ReceiptNo ) " & _
"SELECT tblPOLine.Description, tblPOLine.QtyOrdered,
tblPOLine.PONumber, tblItemMaster.Description,
tblReceipts.ReceiptNo " & _
"FROM tblReceipts, (tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber) INNER JOIN
tblItemMaster ON tblPOLine.Description =
tblItemMaster.ItemNo " & _
"WHERE (((tblPOLine.PONumber)= Me.PONumber))"

DoCmd.RunSQL SQLReceiptLine

.
Click to expand...
.
Click to expand...
 
Hi Joan,

Yeah, I hadn't noticed that the code you had was in a
function. Usually a custom function is used to take
perform some functions on input values, and then often
returns a value. They are triggered any time the
function is called from an expression or another module.
In your case, if you wanted to use a function, you would
pass the value of Me.PONumber as an argument of the
function. In other words, when the function is called,
it would be called as Receipt(Me.PONumber), which would
pass along the value of Me.PONumber as the argument for
the function. At the other end, your function statement
for receipt would be defined as

Function Receipt(strPONumber as String)

Which would define the function and tell it to look for
one argument and assign it to the string variable
strPONumber (of course, if the PO No is numeric, you
would assign it to a numeric variable). From then on you
would use that variable to refer to the value that was
passed to the function.

In looking at your code though, it seems like your code
should be attached to the click event of your button
instead of a function. If you go to the properties of
the button and click the button next to the click event,
Access will create a class module that will run
automatically when the button is pressed. In that
module, you will be able to use Me to refer to the
current form.

Hopefully this helps, post back if you still can't get it
working.

-Ted
-----Original Message-----
Along with prev msg re: Class module/Standard module. I
looked into help on the two above and I was able to move
my code to a class module I created. But I don't know
what it is looking for to initialize the class module.
I'm not sure I should be using this type of module? When
I try to reassign my macro to 'runcode' the Function
Receipt is not showing under my Class Module, is this
because it's not initialized. I am new to all this, if
anyone has an idea for me...or a better way to accomplish
me getting the info into my tables, please help.

thank you all in advance.
-----Original Message-----
Hi Joan,

I the problem is that you have your field reference
(Me.PONumber) inside your string. Try building your
string. As an example, listed below is part of your sql
string:

"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

try revising as follows:
"HAVING (((tblPOHeader.PONumber)= '" & Me.PONumber & "')
AND (([tblPOHeader]![Post])<>True))"

Of course, the above assumes PONumber is a string value,
if it is a number than the 's before and after would not
be needed.

Hope that helps.
-----Original Message-----
Hi there,

I hope someone can help with this, I'm at my wits
Click to expand...
end.
I
am trying to insert records into a Receipts and
ReceiptsLine tables based on the user pressing a cmd
button on the PO. I'd like to insert records only based
on the PONumber the user is currently on. What is
happening is I am getting a parameter window asking for
Me.PONumber for each of the following SQL statements.
Also the ReceiptLines statement is inserting in
additional records i.e. POLine has 1 record and the
receipt will have 3 all the same part?! I thought I had
this working, but every change brings on new issue!
Sorry for length of msg, just wanted to give all info I
could. HELP!!! thx in advance.

Function Receipt()

Dim SQLReceipt As String
Dim SQLReceiptLine As String



SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber "
Click to expand...
&
_
"FROM tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= Me.PONumber) AND
(([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
QtyRecd, PONumber, ItemDesc, ReceiptNo ) " & _
"SELECT tblPOLine.Description, tblPOLine.QtyOrdered,
tblPOLine.PONumber, tblItemMaster.Description,
tblReceipts.ReceiptNo " & _
"FROM tblReceipts, (tblPOHeader INNER JOIN tblPOLine ON
tblPOHeader.PONumber = tblPOLine.PONumber) INNER JOIN
tblItemMaster ON tblPOLine.Description =
tblItemMaster.ItemNo " & _
"WHERE (((tblPOLine.PONumber)= Me.PONumber))"

DoCmd.RunSQL SQLReceiptLine

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
thanks for your help though, now I understand why it
wasn't working earlier. and your message gives more
information to use for next time.
 
Back
Top