By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Product Location Stumbling Block

RobH
P: 56
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
Sep 15 '07 #1
Share this Question
Share on Google+
3 Replies


Scott Price
Expert 100+
P: 1,384
Sounds the like perfect situation to use an append query!

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 15 '07 #2

RobH
P: 56
Sounds the like perfect situation to use an append query!

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 15 '07 #3

Scott Price
Expert 100+
P: 1,384
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:

Expand|Select|Wrap|Line Numbers
  1.       Dim valSelect As Variant
  2.       Dim strValue As String ' just used for the demonstration
  3.  
  4.  
  5.           For Each valSelect In Me.listboxName.ItemsSelected
  6.               strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
  7.           Next valSelect
  8.  
  9.           ' to remove trailing comma
  10.           strValue = Left(strValue, Len(strValue)-2)
Regards,
Scott
Sep 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.