updating data from one table to insert into a scond table

[C Parkins]

I am relatively new to VBA and access. I have existing access tables that I
use for invetory. I have a barcode system for tag # and I track coil
inventory. What I need to do is query and paste coil data from a table or the
form called NEW into a table called SLIT NEW. when I insert the data in the
SLIT NEW table I need to add a suffix letter to the Tag # from the original
table, so tag # 12345 will be 12345A in the slit new table. Any help would
great and thanks in advance for your help.

 

[C Parkins]

Yes, that does work but I need more fucitionality. for one coil tag # I have
numerous suffixes i.e. 12345A,12345B.... Also I need to change weight and
size information. I will try to simplify my need. I need to select a coil
base on tag # cut it to multiple coils and update the slit new table with
multiple records for each slit coil. Each coil record has about 10 data
fields but I need to change tag #, weight, and width. along with date in as I
bring the data from the new table to the slit new table. I think that is as
best as I can describe it. I need to assign a alpha suffix to the tag #
repetatively as this is the current method and I have a lot of existing data.

Thanks for your help.
--
C Parkins

There may be an easier way to get where you are going. Look at Append Query
and Update Query in the Hekp file. You can use an Append Query to append the
data in Table A into Table B. Once the data is in Table B, you vcan use an
Update Query to change all Tag# of 12345 to 12345A by updating the Tag#
field to [Tag#] & "A".

Steve

I am relatively new to VBA and access. I have existing access tables that I
use for invetory. I have a barcode system for tag # and I track coil
inventory. What I need to do is query and paste coil data from a table or
the
form called NEW into a table called SLIT NEW. when I insert the data in
the
SLIT NEW table I need to add a suffix letter to the Tag # from the
original
table, so tag # 12345 will be 12345A in the slit new table. Any help would
great and thanks in advance for your help.

 

[C Parkins]

Mark,
You are correct, I am cutting a large coil to several smaller coils. The
reason I need the suffix is the system was already inplace and I am trying to
find away to work with what I already have.
--
C Parkins

Please explain why you need suffixes. I have a feeling what you are trying
to do is incorrect. Are you taking a large coil #12345 and cutting it into
several small coils 12345A, 12345B, 12345C, etc?

Steve

Yes, that does work but I need more fucitionality. for one coil tag # I
have
numerous suffixes i.e. 12345A,12345B.... Also I need to change weight and
size information. I will try to simplify my need. I need to select a coil
base on tag # cut it to multiple coils and update the slit new table with
multiple records for each slit coil. Each coil record has about 10 data
fields but I need to change tag #, weight, and width. along with date in
as I
bring the data from the new table to the slit new table. I think that is
as
best as I can describe it. I need to assign a alpha suffix to the tag #
repetatively as this is the current method and I have a lot of existing
data.

Thanks for your help.
--
C Parkins

There may be an easier way to get where you are going. Look at Append
Query
and Update Query in the Hekp file. You can use an Append Query to append
the
data in Table A into Table B. Once the data is in Table B, you vcan use
an
Update Query to change all Tag# of 12345 to 12345A by updating the Tag#
field to [Tag#] & "A".

Steve


I am relatively new to VBA and access. I have existing access tables
that I
use for invetory. I have a barcode system for tag # and I track coil
inventory. What I need to do is query and paste coil data from a table
or
the
form called NEW into a table called SLIT NEW. when I insert the data in
the
SLIT NEW table I need to add a suffix letter to the Tag # from the
original
table, so tag # 12345 will be 12345A in the slit new table. Any help
would
great and thanks in advance for your help.

 

[C Parkins]

--
C Parkins

My suggestion for the design of your tables is:

TblCoilMaterial
CoilMaterialID
CoilMaterial

TblLargeCoil
LargeCoilID
LargeCoilTagNumber
CoilMaterialID
LargeCoilWeight
LargeCoilLength
LargeCoilDiameter
....etc...

TblSmallCoilFromLargeCoil
SmallCoilFromLargeCoilID
LargeCoilID
SmallCoilTagNumber
SmallCoilWeight
SmallCoilLength
SmallCoilDiameter
....etc...

Note: You don't need MaterialID in TblSmallCoilFromLargeCoil because all
small coils from the same large coil are the same material as the larger
coil.

Data entry would be a main form based on TblLargeCoil and a subform based on
TblSmallCoilFromLargeCoil. For the letter suffixes for SmallCoilTagNumber,
you would just need a bit of code or maybe use a default value expression
that would input [LargeCoilTagNumber] & "Suffix" when you entered a small
coil in the subform. You want the entry of SmallCoilTagNumber to be
automatic so it always corresponds to LargeCoilTagNumber.

Steve

Mark,
You are correct, I am cutting a large coil to several smaller coils. The
reason I need the suffix is the system was already inplace and I am trying
to
find away to work with what I already have.
--
C Parkins

Please explain why you need suffixes. I have a feeling what you are
trying
to do is incorrect. Are you taking a large coil #12345 and cutting it
into
several small coils 12345A, 12345B, 12345C, etc?

Steve


Yes, that does work but I need more fucitionality. for one coil tag # I
have
numerous suffixes i.e. 12345A,12345B.... Also I need to change weight
and
size information. I will try to simplify my need. I need to select a
coil
base on tag # cut it to multiple coils and update the slit new table
with
multiple records for each slit coil. Each coil record has about 10 data
fields but I need to change tag #, weight, and width. along with date
in
as I
bring the data from the new table to the slit new table. I think that
is
as
best as I can describe it. I need to assign a alpha suffix to the tag #
repetatively as this is the current method and I have a lot of existing
data.

Thanks for your help.
--
C Parkins


:

There may be an easier way to get where you are going. Look at Append
Query
and Update Query in the Hekp file. You can use an Append Query to
append
the
data in Table A into Table B. Once the data is in Table B, you vcan
use
an
Update Query to change all Tag# of 12345 to 12345A by updating the
Tag#
field to [Tag#] & "A".

Steve


I am relatively new to VBA and access. I have existing access tables
that I
use for invetory. I have a barcode system for tag # and I track coil
inventory. What I need to do is query and paste coil data from a
table
or
the
form called NEW into a table called SLIT NEW. when I insert the data
in
the
SLIT NEW table I need to add a suffix letter to the Tag # from the
original
table, so tag # 12345 will be 12345A in the slit new table. Any help
would
great and thanks in advance for your help.

 

[C Parkins]

Thanks Steve,
I agree with the small bit of code, but that is the sticking point. I have
tried what was suggested to me on other posts but I can't seem to get it
right. On the subject of table design, I am past that stage. I have existing
tables that I am stuck with. And to further muddy the water I can't do a
subform as you suggested because some of my slit coils arrive from outside
vendors, I am trying to add my coils I generate into the same table.
Currently all of this is manually enter and I am trying to automate the
process and save some time. I really am stuck with selecting a tag #,
updating or changing some data and adding a suffix into a different table.
Thanks for all of your help.

Chuck

--
C Parkins

My suggestion for the design of your tables is:

TblCoilMaterial
CoilMaterialID
CoilMaterial

TblLargeCoil
LargeCoilID
LargeCoilTagNumber
CoilMaterialID
LargeCoilWeight
LargeCoilLength
LargeCoilDiameter
....etc...

TblSmallCoilFromLargeCoil
SmallCoilFromLargeCoilID
LargeCoilID
SmallCoilTagNumber
SmallCoilWeight
SmallCoilLength
SmallCoilDiameter
....etc...

Note: You don't need MaterialID in TblSmallCoilFromLargeCoil because all
small coils from the same large coil are the same material as the larger
coil.

Data entry would be a main form based on TblLargeCoil and a subform based on
TblSmallCoilFromLargeCoil. For the letter suffixes for SmallCoilTagNumber,
you would just need a bit of code or maybe use a default value expression
that would input [LargeCoilTagNumber] & "Suffix" when you entered a small
coil in the subform. You want the entry of SmallCoilTagNumber to be
automatic so it always corresponds to LargeCoilTagNumber.

Steve

Mark,
You are correct, I am cutting a large coil to several smaller coils. The
reason I need the suffix is the system was already inplace and I am trying
to
find away to work with what I already have.
--
C Parkins

Please explain why you need suffixes. I have a feeling what you are
trying
to do is incorrect. Are you taking a large coil #12345 and cutting it
into
several small coils 12345A, 12345B, 12345C, etc?

Steve


Yes, that does work but I need more fucitionality. for one coil tag # I
have
numerous suffixes i.e. 12345A,12345B.... Also I need to change weight
and
size information. I will try to simplify my need. I need to select a
coil
base on tag # cut it to multiple coils and update the slit new table
with
multiple records for each slit coil. Each coil record has about 10 data
fields but I need to change tag #, weight, and width. along with date
in
as I
bring the data from the new table to the slit new table. I think that
is
as
best as I can describe it. I need to assign a alpha suffix to the tag #
repetatively as this is the current method and I have a lot of existing
data.

Thanks for your help.
--
C Parkins


:

There may be an easier way to get where you are going. Look at Append
Query
and Update Query in the Hekp file. You can use an Append Query to
append
the
data in Table A into Table B. Once the data is in Table B, you vcan
use
an
Update Query to change all Tag# of 12345 to 12345A by updating the
Tag#
field to [Tag#] & "A".

Steve


I am relatively new to VBA and access. I have existing access tables
that I
use for invetory. I have a barcode system for tag # and I track coil
inventory. What I need to do is query and paste coil data from a
table
or
the
form called NEW into a table called SLIT NEW. when I insert the data
in
the
SLIT NEW table I need to add a suffix letter to the Tag # from the
original
table, so tag # 12345 will be 12345A in the slit new table. Any help
would
great and thanks in advance for your help.

 

[Tony Toews [MVP]]

On the subject of table design, I am past that stage. I have existing
tables that I am stuck with.

For once I agree with Steve. I'm always adding fields, tables,
indexes and relationships to delivered systems. I use lots of VBA
code to do the work. Now if you are onsite and only one copy of the
backend in use then you can manually make those changes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[C Parkins]

Tony,
I do agree that I am constantly changing my database. But, I am at a place
right now that is working for me. Also, my table structure is similar to what
Steve suggested already. I have 2 tables that contain the same fields but the
data is different slightly between the two. I would like one input form that
feeds which of the two forms needs the data but right now what I need to do
is be able to select a 5 digit tag# from the first table and paste or insert
it into the second table and add a suffix to it and repeat this process for n
times. I am getting frustrated because I get advice on restructuring data but
nothing useful to get where I want to be now. I purchased a book on VBA to
learn but I need to get this accomplished before I finish the book.
Thanks for your help.