Product Location Stumbling Block

[RobH]

I have a couple of tables.

tbl-SID - This Holds An ID for the different States/Country. (currently 20 ID's exist)
[StateID],[Country],[State]
0,Al,Al - This is aimed to be a single Item to depict all locations.
1,AU,ACT
2,AU,NSW
3,AU,VIC
4,AU,QLD
5,AU,SA
.
.
9,NZ,Auckland
10,NZ,Bay of Plenty

tbl-PL - This is a table that hold the location of a part
[PartID],[StateID]
1000,1
1000,2
1000,3
1000,4
.
.
.

I need to make a form that allows the Selection of StateID's for each part- However some parts are constant across all StateID's and in the event of adding a new StateID having to fo back across all parts to add these differenct StateID/PartID to the tbl-PL would be difficult.

I was thinking of having an "All" entry with a StateID =0 in State ID

My problem is how do i depict this on a form..
I was thinking in the form headers - having some fields to select the PartID
Then in the Details have a series of radio buttons or Check buttons for the 20 options - but im stumpted in how to to get the buttons to populate (which i could do Dlookups for) but then one the changes are made how would I re-allocate them? (other than do a sql cmd to remove all for the part and then another to add for each location selected - (this is where i'm Stumped)

Regards

[Scott Price]

Sounds the like perfect situation to use an append query!

INSERT INTO tbl-PL SELECT tbl-SID.StateID, tbl-Parts.PartID FROM tbl-SID, tbl-Parts WHERE tbl-Parts.PartID = [WhateverPart#]

Test your select query first to make sure it's returning all StateID's, then add the INSERT INTO tbl-PL.

Regards,
Scott

[RobH]

Sounds the like perfect situation to use an append query!

INSERT INTO tbl-PL SELECT tbl-SID.StateID, tbl-Parts.PartID FROM tbl-SID, tbl-Parts WHERE tbl-Parts.PartID = [WhateverPart#]

Test your select query first to make sure it's returning all StateID's, then add the INSERT INTO tbl-PL.

Regards,
Scott

That would Work if I wanted to add every StateID into the Product Location Table - But I need to be able each one only if selected. or If All is selected - just the entry for all. On the form is a Check Box for each StateID location

[Scott Price]

Have you thought of using a multi-select list box to choose which states to add? You could put this on a pop-up form, sized to show all 20 states. When selecting one or more than one, place a command button to run the append query.

You'll then only need to catch the values returned by the list box, and pass them into the WHERE criteria of the append query.

This is a code snippet provided by our very own mmcarthy on how to retrieve values from a multiselect list box:

      Dim valSelect As Variant
      Dim strValue As String ' just used for the demonstration
 
 
          For Each valSelect In Me.listboxName.ItemsSelected
              strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
          Next valSelect
 
          ' to remove trailing comma
          strValue = Left(strValue, Len(strValue)-2)

Regards,
Scott