complex form design question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've searched this site for tips on how to do what I want to do but can't find anything as advanced as th
design I need. Kudos though to this user site, there's a lot of great advice and expertise

This is what I would like to do

We have product that follow different delivery ship paths. I am trying to create a database th
records the date the item was shipped to each location but also direct the user to the next appropriate locatio
in that product's ship path

Specifically

1) the user enters a product ID numbe
2) once the product number is entered, I would like the program to display to the user the next ship location tha
they need to ship the product t
3) the user then enters his/her employee ID# which once entered basically signifies that the product has is bein
shipped to the next location and records the date the employee sent the product off
4) when the user returns again to record the date he/she is sending it to the next location, the same proces
repeats, where the user enters the product ID number, the system displays the next ship location and prompts th
user to enter his employee# and the system date stamps the dat

I am an advanced user and am proficient at using/creating forms, tables, macros etc..but can not figure out ho
to approach this design challenge. I have sent up a product ID routing table where the system knows based upo
product ID number the specific ship location sequence, I can display this routing in a drop down menu format for
the user to select, BUT I DON'T WANT the user to select, rather I want the system to DIRECT the user to the nex
ship location. Also, I want to PREVENT the user from shipping the product out of sequence

Any suggestions from all you great experts out there on at least how to approach creating the basic architecture of the tables/forms for this would be greatly appreciated! So far, I can't find any tips on line for something this complex

Thanks in advance for your reponse
(e-mail address removed)
 
I've done something similar with internal routing of purchase ooders and the different lists of people who need to approve them and in what order

Make a table of locations (tblLocations) that are used i the various shipping paths, with a LocID field and any other informational fields you need for each location

Make a table of shipping paths (tblPaths) with fields for PathID, LocSort and LocID. Each path will have multiple records; the LocID should be related to the same field in tblLocations. The LocSort field is the order for locations in the various paths

For Example, an item shipped using Path 121 would travel to Location 23 (wherever that is), then Loc 41 then 7

PATH LocSort LocI
121 1 L2
121 2 L4
121 3 L0

When you enter a product to be shipped, specify (manually or programatically) the path it will take. This should add records to a shipping table (tblShipping) which has the product or shipment number, LocSort, LocID and Shipper fields (plus any more you feel are necessary)

On your data-entry form, use a DLookUp and a DMin function to find where the product should be or go next. Check the Help files for their details

' declare variable
Dim strShipmentID as String
Dim intNextLoc as Intege
Dim strNextLoc as Strin

' look up next locatio
strShipmentID = [AppropriateControlHere
intNextLoc = DMin("[LocSort]","tblShipments", "[ShipmentID]='" & strShipmentID & "' AND [Shipper] IS NOT NULL"
strNextLoc = DLookUp("[LocID]","tblShipments","[ShipmentID]='" & strShipmentID & "' AND [LocSort]=" & intNextLoc

Hope this helps

Howard Brod

----- mfg2529 wrote: ----

I've searched this site for tips on how to do what I want to do but can't find anything as advanced as th
design I need. Kudos though to this user site, there's a lot of great advice and expertise

This is what I would like to do

We have product that follow different delivery ship paths. I am trying to create a database th
records the date the item was shipped to each location but also direct the user to the next appropriate locatio
in that product's ship path

Specifically

1) the user enters a product ID numbe
2) once the product number is entered, I would like the program to display to the user the next ship location tha
they need to ship the product t
3) the user then enters his/her employee ID# which once entered basically signifies that the product has is bein
shipped to the next location and records the date the employee sent the product off
4) when the user returns again to record the date he/she is sending it to the next location, the same proces
repeats, where the user enters the product ID number, the system displays the next ship location and prompts th
user to enter his employee# and the system date stamps the dat

I am an advanced user and am proficient at using/creating forms, tables, macros etc..but can not figure out ho
to approach this design challenge. I have sent up a product ID routing table where the system knows based upo
product ID number the specific ship location sequence, I can display this routing in a drop down menu format for
the user to select, BUT I DON'T WANT the user to select, rather I want the system to DIRECT the user to the nex
ship location. Also, I want to PREVENT the user from shipping the product out of sequence

Any suggestions from all you great experts out there on at least how to approach creating the basic architecture of the tables/forms for this would be greatly appreciated! So far, I can't find any tips on line for something this complex

Thanks in advance for your reponse
(e-mail address removed)
 
Howard

Thanks so much for the help! I just opened this and haven't read it all yet
Would you mind if I asked you some further questions in case I am not clear on something

This is great!
Thanks so much
My direct email is (e-mail address removed)
 
Thanks for telling me that. I have never used/been part of a newsgroup and was not aware of
the process.

I do have a question on your last suggestion, if it's ok to ask it here of you.
Although I am advanced in using MS Access (have created many data entry forms/dbs) I am not at
all familiar with inserting SQL statements such as the one you suggested.

I tried reading the instructions on how to use Dmin and Dlookup as you suggested in both the MS Access help
guide as well as the MS Access QUE guide I purchased but only instructs me how to use the DMin and DLookup
statement in a report and not how to use it in a form.

If it's not to much of a problem, would you be so kind to offer some clarification on this.

In the data entry form, I have a field that the user enters the product number in. I understand how the DMin and
DLookup works but am not clear how to insert it in my data entry form. Also, will this statement (if I can figure out
where to insert it into the code) display the next step for the user? I am fuzzy on what the end form response
should look like.

I really appreciate your suggestion, as I think this seem definitely like the method that will get me what I need.

Thanks in advance for your help!
 
I think I'd have the following controls on my form

cboShipmentID - assuming the same product can appear in multiple shipments to different customers (and different paths?), a Shipment or OrderID should be assigned to each customer orde

lblThisLoc - a non-updateable control that display the LocationID of the user (can be programatically set based on user ID

lblNextLoc - a non-updateable control that displays the LocationID of the next 'stop' for the shipmen

In VBA code, an apostrophe denotes the line as a comment and not code to be executed. I've used this for comments on the example
Try adapting something like this in the AfterUpdate event for cboShipmentID

Private Sub cboShipmentID_AfterUpdate(

' if the ComboBox is empty, end the su
If IsNull([cboShipmentID]) or [cboShipmentID]="" The
Exit Su
End I

' otherwise..

' declare variables your code will be usin
Dim strPath as Strin
Dim i as Intege
Dim m as Intege
Dim strNextLoc as Strin

' look up the path the shipment is following. This code assumes it
' is tracked in the tblShipments tabl
strPath = DLookUp("[PathID]","tblShipments","[ShipmentID]='" & cboShipmentID & "'"

' look up the LocSort number of the users location in the pat
i = DLookUp("[LocSort]","tblPaths","[LocID]='" & [lblThisLoc] & "'"

' look up the last LocSort number of the pat
i = DMax("[LocSort]","tblPaths","[LocID]='" & [lblThisLoc] & "'"

' if the current location is the last location, display a message and end the sub
' (And do anything else that needs to be done at the final path location
If i = m the
MsgBox "The selected shipment is intended for your location.
Exit Su
End I

' otherwise..

' look up the next location in the pat
strNextLoc = "[LocID]","tblPaths","[LocSort]=" & (i + 1)

' display the next location for the shipmen
[lblNextLoc] = strNextLo

End Su

You can also use the code to hide or enable appropriate controls, based on the nect location

This code assumes that the user's location is where the shipment is supposed to be

Another item to consider is how do you want to flag the item as 'shipped from this location?' I'd use a CommandButton to date/time/userstamp the appropriate field (run an UpdateQuery in the OnClick event) so a shipment could be looked up and looked at without automatically being flagged as done

Hope this helps clarify things a bit

Howard Brod



----- mfg2529 wrote: ----

Thanks for telling me that. I have never used/been part of a newsgroup and was not aware o
the process

I do have a question on your last suggestion, if it's ok to ask it here of you
Although I am advanced in using MS Access (have created many data entry forms/dbs) I am not at
all familiar with inserting SQL statements such as the one you suggested.

I tried reading the instructions on how to use Dmin and Dlookup as you suggested in both the MS Access hel
guide as well as the MS Access QUE guide I purchased but only instructs me how to use the DMin and DLooku
statement in a report and not how to use it in a form

If it's not to much of a problem, would you be so kind to offer some clarification on this

In the data entry form, I have a field that the user enters the product number in. I understand how the DMin an
DLookup works but am not clear how to insert it in my data entry form. Also, will this statement (if I can figure ou
where to insert it into the code) display the next step for the user? I am fuzzy on what the end form respons
should look like

I really appreciate your suggestion, as I think this seem definitely like the method that will get me what I need

Thanks in advance for your help
 
Thanks so much Howard

I just got this and will read thru it. I hope it helps too as I am really pulling my hair out on this on
with a deadline looming to boot! I have everything else created ok for this program and this is the only
glitch! I wish Microsoft had a sample form that does something similar to do this. I find it easier t
look at a sample program and translate it to what I need than to start from scratch

Thanks!
 
Back
Top