Append more values

[RobertPlant via AccessMonster.com]

Dear EXPERTS,

Is there any chance to append more values into other table? let say, i am
going to append 10 rows of product details such as productname, productcode,
partnumber, dateenter, supplier, companyname etc. i have a command button
which enable them to append 10 rows in product details, based on user's
criteria, user will enter in the textbox did he/she received.

I tried the below code: but it show only product code

Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, item_number)"

LSQL = LSQL & " values ("

LSQL = LSQL & "'" & ProductCode & "', " & LCntr & ")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub


Thanks and more power to you experts....

 

[tina]

i couldn't see any problem with the SQL statement itself, though of course i
could have missed something. my next thought is the variable. LCntr is
declared as an Integer. is the item_number field in table ImportLicense a
Number data type? and if so, is it an Integer field size, or larger? if the
field size is Byte, try changing the LCntr variable from Integer to Byte.

hth

 

[RobertPlant via AccessMonster.com]

Tina,
LCntr is running, i don't have any problems with it, but the main problem is
everytime i am trying to included fields say, "companyname" or
"invoicenumbernumber" etc etc., the query got stuck, what i want is i've just
wanted to include some other fields asides from "prodcutcode".

thanks. robert

---------------------------------------------------------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr & ")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

i couldn't see any problem with the SQL statement itself, though of course i
could have missed something. my next thought is the variable. LCntr is
declared as an Integer. is the item_number field in table ImportLicense a
Number data type? and if so, is it an Integer field size, or larger? if the
field size is Byte, try changing the LCntr variable from Integer to Byte.

hth

Dear EXPERTS,

[quoted text clipped - 37 lines]

Thanks and more power to you experts....

 

[RobertPlant via AccessMonster.com]

Tina,
For example, a user received 10 equipments, let say 10 mulitemeter, since
multimeter having the same model but different serial number, same product
code or invoice number but different serial number. it is not practical to
enter all these 10 multimeters when you received them in your warehouse. the
best solution there is to create an append query in order to create 10
records of multimeter based on quantity received, then finally, you can edit
each record by typing their own multimeter serial number, in that scenario,
you saved time and effort.

i have a text box which user type quantity received and i have the commnad
button which accepts whatever the user type in numeric form, then it will
generate a series of numbers and product code:
let say, the user typed 10 numbers of multimeter that he/she received
(depends upon the order, if the order is 20, then it must generate 20 rows so
on and so forth...)

ItemNo ProductCode InvoiceNumber SerialNo.
PartNumber
1 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
2 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
3 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
4 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
5 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
6 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
7 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
8 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
9 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
10 FLKMM220V INV09-00010
MM2500WCapacityUSMADE


You see the above scenario? it is not practical to enter all these
informations, what we are going to do is to build an append query that
genarate a series of numbers BASED ON QUANTITY THAT THEY RECEIVED.

Once they generate a series of records, then the user will finally type/enter
unique serial number, so the user saved a lot of time in crunching / entering
into the database.

------------------------------------------------------------------------------
-------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr & ")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

------------------------------------------------------------------------------
------------

Tina,
LCntr is running, i don't have any problems with it, but the main problem is
everytime i am trying to included fields say, "companyname" or
"invoicenumbernumber" etc etc., the query got stuck, what i want is i've just
wanted to include some other fields asides from "prodcutcode".

thanks. robert

---------------------------------------------------------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr & ")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

i couldn't see any problem with the SQL statement itself, though of course i
could have missed something. my next thought is the variable. LCntr is

[quoted text clipped - 11 lines]

 

[Jeff Boyce]

Pardon my intrusion...

An alternate approach might be to provide the user with a <Make Me A Copy>
command button on the form, so after entering the first item, clicking the
button creates a copy. From your description, the only thing that would
need to be changed would be the Serial Number.

As yet another approach, if you recorded the Model/Manufacturer/... info
ONCE, in something like a "Product" table, you could then use the ProductID
as a foreign key in your [Unit] table, into which you only stored ProductID
and SerialNumber.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Tina,
For example, a user received 10 equipments, let say 10 mulitemeter, since
multimeter having the same model but different serial number, same product
code or invoice number but different serial number. it is not practical to
enter all these 10 multimeters when you received them in your warehouse.
the
best solution there is to create an append query in order to create 10
records of multimeter based on quantity received, then finally, you can
edit
each record by typing their own multimeter serial number, in that
scenario,
you saved time and effort.

i have a text box which user type quantity received and i have the commnad
button which accepts whatever the user type in numeric form, then it will
generate a series of numbers and product code:
let say, the user typed 10 numbers of multimeter that he/she received
(depends upon the order, if the order is 20, then it must generate 20 rows
so
on and so forth...)

ItemNo ProductCode InvoiceNumber SerialNo.
PartNumber
1 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
2 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
3 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
4 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
5 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
6 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
7 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
8 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
9 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
10 FLKMM220V INV09-00010
MM2500WCapacityUSMADE


You see the above scenario? it is not practical to enter all these
informations, what we are going to do is to build an append query that
genarate a series of numbers BASED ON QUANTITY THAT THEY RECEIVED.

Once they generate a series of records, then the user will finally
type/enter
unique serial number, so the user saved a lot of time in crunching /
entering
into the database.

------------------------------------------------------------------------------
-------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr &
")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

------------------------------------------------------------------------------
------------

Tina,
LCntr is running, i don't have any problems with it, but the main problem
is
everytime i am trying to included fields say, "companyname" or
"invoicenumbernumber" etc etc., the query got stuck, what i want is i've
just
wanted to include some other fields asides from "prodcutcode".

thanks. robert

---------------------------------------------------------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number
etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr &
")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

i couldn't see any problem with the SQL statement itself, though of
course i
could have missed something. my next thought is the variable. LCntr is

[quoted text clipped - 11 lines]

 

[tina]

yes, i understood what you are attempting to do, and why, from your first
post.

I tried the below code: but it show only product code

the above statement, from your first post, led me to believe that the
records are appending - but only the ProductCode value in each appended
record, not the item_number value.

i don't doubt the the LCntr value is working *as a counter*. but your SQL
statement is attempting to append the LCntr value into the item_number
field. if that's not working, my only guess is that it's a data type
mismatch - though that was frankly a WAG, because i would have thought that
a value of 255 or under would append into a Byte size field, even if the
variable was declared as Integer.

so at this point, it's not clear if your Append is working *at all*. if not,
it's possible that there actually IS a syntax error in the SQL statement.
suggest you write a query to do the Append, used hard-coded dummy values,
then test it. if the query runs, then copy the SQL statement from the SQL
pane into your VBA module, and modify it there.

hth

Tina,
For example, a user received 10 equipments, let say 10 mulitemeter, since
multimeter having the same model but different serial number, same product
code or invoice number but different serial number. it is not practical to
enter all these 10 multimeters when you received them in your warehouse. the
best solution there is to create an append query in order to create 10
records of multimeter based on quantity received, then finally, you can edit
each record by typing their own multimeter serial number, in that scenario,
you saved time and effort.

i have a text box which user type quantity received and i have the commnad
button which accepts whatever the user type in numeric form, then it will
generate a series of numbers and product code:
let say, the user typed 10 numbers of multimeter that he/she received
(depends upon the order, if the order is 20, then it must generate 20 rows so
on and so forth...)

ItemNo ProductCode InvoiceNumber SerialNo.
PartNumber
1 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
2 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
3 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
4 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
5 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
6 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
7 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
8 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
9 FLKMM220V INV09-00010
MM2500WCapacityUSMADE
10 FLKMM220V INV09-00010
MM2500WCapacityUSMADE


You see the above scenario? it is not practical to enter all these
informations, what we are going to do is to build an append query that
genarate a series of numbers BASED ON QUANTITY THAT THEY RECEIVED.

Once they generate a series of records, then the user will finally type/enter
unique serial number, so the user saved a lot of time in crunching / entering
into the database.

-------------------------------------------------------------------------- ----
-------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr & ")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

-------------------------------------------------------------------------- ----
------------

Tina,
LCntr is running, i don't have any problems with it, but the main problem is
everytime i am trying to included fields say, "companyname" or
"invoicenumbernumber" etc etc., the query got stuck, what i want is i've just
wanted to include some other fields asides from "prodcutcode".

thanks. robert

---------------------------------------------------------------------------
Private Sub PostDetailsClick()

Dim LCntr As Integer

With quantity

LCntr = 1

Do Until LCntr = quantity + 1

LSQL = "INSERT INTO ImportLicense (ProductCode, invoicenumber,
airwaybillnumber, item_number)"

LSQL = LSQL & " values ("

'insert productcode, invoicenumber, companyname, airwaybill number etc
etc...
LSQL = LSQL & "'" & ProductCode & "', "& invoicenumber &" & LCntr & ")"

db.Execute LSQL

LCntr = LCntr + 1

Loop

Me.quantity.Locked = True

End With

Me.Requery

End Sub

i couldn't see any problem with the SQL statement itself, though of course i
could have missed something. my next thought is the variable. LCntr is

[quoted text clipped - 11 lines]

http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200903/

1