Advice needed on an append query

[DougW via AccessMonster.com]

I am a very sporadic Access user and could use some guidance for a new task.

I have an existing Access DB I am using to report on parts we have ordered.
There are many different part types.
We often order hundreds of copies of the same part (type) on one purchase
order.
I refer to those copies of parts as "Ordered Items".
I have one table for Purchase Orders and another table for Ordered Items.
The OrderedItems table
contains a unique record for each "copy" of each part. There is a one-to-
many relationship between PurchaseOrders and OrderedItems.

Now I need to add another layer of information, to allow tracking the status
of OrderedItems over time.
I want to be able to report on completion status of parts, and physical
location (such as when they are shipped to a warehouse). Over time there
will be multiple "tracking" records created for a given OrderedItem. Also
it is very likely that one tracking record will pertain to many OrderedItems.

My thought was to create a new table called OrdItemTracking. The records in
this table would include the date of the tracking record, the physical
location of the parts, the physical status, etc.
I also propose to add another new table called OrdItemTrackArray. The
records in this table would include the primary key from one OrderedItem
record and the primary key from one OrdItemTracking record. I thought this
Array table would allow me to link one Tracking record to any number of
OrderedItems records.

To clarify my question, I have 4 tables of interest.
PurchaseOrders
OrderedItems
OrdItemTracking
OrdItemTrackArray

Viewing it from left to right in my relationship window, I first have
PurchaseOrders.
To the right of PurchaseOrders is OrderedItems
Below OrderedItems is OrdItemTracking
To the right of OrderedItems is OrdItemTrackArray

There is a one-to-many relationship from the primary key of PurchaseOrders to
a foreign key in OrderedItems.
There is a one-to-many rel from the PK in OrderedItems to a FK in
OrdItemTrackArray
There is also a one-to-many rel from the PK in OrdItemTracking to a FK in
OrdItemTrackArray.

I think my table design makes sense (and please advise if it doesn't, I won't
be offended!)

Now I need a means to select a large number of similar OrderedItems from one
PurchaseOrder, create a single OrdItemTracking record, and "attach" it to all
of the selected OrderedItems by creation of a number of OrdItemTrackArray
records.

For example, PurchaseOrder A may link to 100 OrderedItems. I wish to create
one OrdItemTracking record and then select 35 of the 100 OrderedItems and
"attach" the Tracking record to all of them (via creation of 35 new records
in the Array table). This might reflect that those 35 OrderedItems were
shipped to Warehouse B on Sept 15 2008 (for an example).

As far as the data entry form, I would like to select the PurchaseOrder,
generating a list of OrderedItems, and then select multiple individual
OrderedItems from this list - create a single new OrdItemTracking record -
then (hopefully by clicking a single "action button"), create multiple new
records in OrdItemArray to link the individual OrderedItems to the new
Tracking record.

Searching previous threads on this site leads me to believe I may need to use
a form with an embedded subform (?)

I'm thinking the creation of the new Array records will be from an append
query.
I have done that before and am somewhat familiar with that technique.
It's not a show-stopper if I have to first create the new Tracking record
from it's own form.
For the moment my main concern is how to enable the multiple selection of
"some-but-not-all" of the OrderedItems from one particular PurchaseOrder, and
then make use of those multiple selections to drive my Append query.

I would appreciate any suggestions or maybe direction to a similar example
that might be out there.
I hope this wasn't too rambling - thanks in advance for your help!

-- Doug

 

[DougW via AccessMonster.com]

I think my first note was too lengthy. Here's what I really need to know:

If I create a form showing a listbox (or a subform), how can I select a
number of consecutive records from that listbox for a subsequent action? The
user will select X consecutive records and press an action button. The macro
from the action button will cause X new records to be appended to an existing
table. I assume I can do the appending action with an append query.

Any comments appreciated, thanks!

 

[Michel Walsh]

Something like:


Dim varItem As Variant
Dim db As Database : Set db = CurentDb

For Each varItem In MultipleSelectListBoxControl.ItemsSelected
CurrentDb.Execute "INSERT INTO someTable(fieldName) VALUES(" & _
MultipleSelectListBoxControl.ItemData(varItem) & " )"
Next varItem



Assuming the value is numerical. If the value is alphanumerical, add the
appropriate delimiters, kind of:


CurrentDb.Execute "INSERT INTO someTable(fieldName) VALUES(""" & _
MultipleSelectListBoxControl.ItemData(varItem) & """ )"


Sure, for debugging purposes, you can assign the string to a variable:


Dim str AS String
str="INSERT INTO someTable(fieldName) VALUES(""" & _
MultipleSelectListBoxControl.ItemData(varItem) & """ )"
CurrentDb.Execute str, dbFailOnError



where I also added a flag telling to generate a trappable error is the
command fails (dbFlagOnError).




Vanderghast, Access MVP

 

[DougW via AccessMonster.com]

Michel, thanks for the reply!
I am somewhat VBA-impaired so it will take me a while to digest this info.
I do have a unique numerical value (autonumber primary key) in each record so
I assume that's what I could use for "varItem".
I will study this and may come back with more questions.

Thanks!
- Doug

Something like:

Dim varItem As Variant
Dim db As Database : Set db = CurentDb

For Each varItem In MultipleSelectListBoxControl.ItemsSelected
CurrentDb.Execute "INSERT INTO someTable(fieldName) VALUES(" & _
MultipleSelectListBoxControl.ItemData(varItem) & " )"
Next varItem

Assuming the value is numerical. If the value is alphanumerical, add the
appropriate delimiters, kind of:

CurrentDb.Execute "INSERT INTO someTable(fieldName) VALUES(""" & _
MultipleSelectListBoxControl.ItemData(varItem) & """ )"

Sure, for debugging purposes, you can assign the string to a variable:

Dim str AS String
str="INSERT INTO someTable(fieldName) VALUES(""" & _
MultipleSelectListBoxControl.ItemData(varItem) & """ )"
CurrentDb.Execute str, dbFailOnError

where I also added a flag telling to generate a trappable error is the
command fails (dbFlagOnError).

Vanderghast, Access MVP

I think my first note was too lengthy. Here's what I really need to know:

[quoted text clipped - 110 lines]

 

[Michel Walsh]

ItemData allows to retrieve the 'bound' column (not necessary a visible
columns). To get other columns, see the example in the help file under (VBE
environment), topic:

ItemsSelected Property


which has example about how to get all columns of the selected lines in the
list box.


Basically, in your case, you don't Debut.Print those values, but probably
insert them in the VALUES list of the INSERT INTO query. The syntax for such
query is:

INSERT INTO tableName(ListOfFIeldToAppendTo) VALUES(
listOfValuesWhichWillBeAppended)


The two lists must have the same number of items. The values, in the second
list, must have the appropriate delimiters, such as " " for strings and # #
for date_time.

As example:


INSERT INTO myTable( numericalField, stringValueField) VALUES( 255,
"red" )

(That is what must be the final result, after concatenation). Building that
string, through code, may look like:

str = "INSERT INTO myTable( numericalField, stringValueField) VALUES( "
& ListBoxControl.Column(2, varItem) & ", """
& ListBoxControl.Column(7, varItem) & """)"


assuming the numerical value comes from column 2 and the string value comes
from column 7 of the listbox.

You insert one row at a time, that is why the statement is inside a
"foreach" loop, ie, looping over each selected line of the listbox, and
appending one (single) row each time.





Vanderghast, Access MVP

Michel, thanks for the reply!
I am somewhat VBA-impaired so it will take me a while to digest this info.
I do have a unique numerical value (autonumber primary key) in each record
so
I assume that's what I could use for "varItem".
I will study this and may come back with more questions.

Thanks!
- Doug

Something like:

Dim varItem As Variant
Dim db As Database : Set db = CurentDb

For Each varItem In MultipleSelectListBoxControl.ItemsSelected
CurrentDb.Execute "INSERT INTO someTable(fieldName) VALUES(" & _
MultipleSelectListBoxControl.ItemData(varItem) & " )"
Next varItem

Assuming the value is numerical. If the value is alphanumerical, add the
appropriate delimiters, kind of:

CurrentDb.Execute "INSERT INTO someTable(fieldName) VALUES(""" &
_
MultipleSelectListBoxControl.ItemData(varItem) & """ )"

Sure, for debugging purposes, you can assign the string to a variable:

Dim str AS String
str="INSERT INTO someTable(fieldName) VALUES(""" & _
MultipleSelectListBoxControl.ItemData(varItem) & """ )"
CurrentDb.Execute str, dbFailOnError

where I also added a flag telling to generate a trappable error is the
command fails (dbFlagOnError).

Vanderghast, Access MVP

I think my first note was too lengthy. Here's what I really need to
know:

[quoted text clipped - 110 lines]

-- Doug