Solved

MS Access Customer Order Pickers Picking List Auto Choose Locations

Posted on 2016-10-16
55
72 Views
Last Modified: 2016-10-19
This might be a VERY broad question but will aid me in the right direction. After a customer orders there will be a picking list generated for the picker to collect the items on the list.


Our products could be in multiple locations with multiple quantities. (Generally no but could).  I have a table called ProdLocations. It has fields:
ProdLocID (Autonumber)
ProductId(found in the Products Table)
LocID (found in the Locations Table)
QtyLoc (Quantity)

I would like for Access to evaluate all the Locations and Quantity in those locations for that product on the order.

Then decide on a location (LocID) that best fits and place it on the pickers list.

Example:
Customer orders 5 of productID 32


ProdLocations Table

ProdLocID / ProductID / QtyLoc / LocID
___________________________________________

543.               32.                 6.              78
777.               32.                 2.              99
198.               32.                 4               12


Access would say in this scenario  ProductID 32 /Qty 5 / LocID 78

OR IF----

Order was for 8 of ProductID then access would suggest LocID 78 and 99 =8

I am trying to understand and figure out the best way to automatically remove product quantities from exact location when there could be multiple locations for a single product.

When a automatic quantity deduction is made we need to know where so we have accurate records.

Advice, Suggestions, etc......
Thank you all for the help!
0
Comment
Question by:Dustin Stanley
  • 32
  • 22
55 Comments
 

Author Comment

by:Dustin Stanley
ID: 41845814
Here is a link to a photo here on EE of my relationships:
https://filedb.experts-exchange.com/incoming/2016/10_w42/1122427/db91.jpg
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41845823
here is code you can put into a general module that will help set control RowSources depending on what is entered.
Option Compare Database
Option Explicit

Public Function SetControl_RowSource( _
   pCtl As Control _
   , Optional ByVal psWhere As String = "" _
   , Optional booClearValue As Boolean = False _
   , Optional booClearIfNotInList As Boolean = False _
   , Optional psOrderBy As String = "" _
   , Optional psFind As String = "" _
   , Optional psReplace As String = "" _
   , Optional pBooDontSet As Boolean = False _
   , Optional booSubqueryHasWhere As Boolean = False _
   ) As String
'131017 strive4peace, 131023, 1029, 1218, 140421, 141009 find/replace
   'ASSUMPTIONs:
   '  .Tag contains SQL for the control
   '     if there is a WHERE clause, it will be appended.
   '     if not, it will be added
   '
   ' CALLS
   '  GetSQL_WHERE
   '  GetSQL_ORDERBY
   '  booClearIfNotInList
   
   On Error GoTo Proc_Err
   
   Dim sSQL As String
   
   sSQL = pCtl.Tag
   If psOrderBy <> "" Then
      sSQL = GetSQL_ORDERBY(sSQL, psOrderBy)
   End If
   sSQL = GetSQL_WHERE(sSQL, psWhere, , booSubqueryHasWhere)
   If psFind <> "" And psReplace <> "" Then
      sSQL = Replace(sSQL, psFind, psReplace)
   End If
         
   With pCtl

      If pBooDontSet Then
         SetControl_RowSource = sSQL
      Else
         If .RowSource <> sSQL Then
   'Debug.Print pCtl.Name, psWhere
   Debug.Print sSQL
   
            .RowSource = sSQL
   '         On Error Resume Next
            .Requery
   '         On Error Resume Next
            If booClearValue Then
               .Value = Null
            ElseIf booClearIfNotInList Then
               If Not IsNull(.Value) Then
                  If CStr(Nz(.Value)) <> Nz(.Column(0)) Then
                     .Value = Null
                  End If
               End If
            End If
         End If
      End If
   End With 'pCtl
   
Proc_Exit:
   On Error Resume Next
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SQL_AddWhere"

   Resume Proc_Exit
   Resume
End Function




Public Function GetSQL_ORDERBY( _
      ByVal pSQL As String _
      , ByVal psOrderBy As String _
      , Optional ByVal pbooAdd As Boolean = False _
      ) As String
'strive4peace, 140421
'add/replace OrderBy clause of SQL string, if specified

'strive4peace, 140121
   On Error GoTo Proc_Err
   Dim iPos As Integer

   If Not Len(psOrderBy) > 0 Then
      'no change
      GetSQL_ORDERBY = pSQL   'same as what was sent
      Exit Function
   End If

   pSQL = Trim(pSQL)

   'look for ORDER BY
   iPos = InStr(pSQL, "ORDER BY ")

   'look for ORDER BY
   If iPos > 0 Then
      If pbooAdd Then
         If Len(psOrderBy) > 0 Then
            'add to beginning of ORDER BY clause
            pSQL = Replace(pSQL, "ORDER BY " _
                              , " ORDER BY " & psOrderBy & ", ")
         End If
      Else
         If Len(psOrderBy) > 0 Then
            'replace ORDER BY clause
            pSQL = Left(pSQL, iPos + 8) & psOrderBy & ";"
         Else
            'remove ORDER BY clause
            pSQL = Left(pSQL, iPos - 1) & ";"
         End If
         
      End If
   
   Else 'no ORDER BY clause in the SQL
      If Len(psOrderBy) > 0 Then
         'add to end
         iPos = Len(pSQL)
         If Right(pSQL, 1) = ";" Then
            iPos = iPos - 1
         End If
         pSQL = Left(pSQL, iPos) _
                      & " ORDER BY " & psOrderBy & ";"
      Else
         'no change
      End If
   End If
         
   GetSQL_ORDERBY = pSQL
Proc_Exit:
      On Error Resume Next
      Exit Function

Proc_Err:
   '   MsgBox Err.Description, , _
       "ERROR " & Err.Number _
       & "   GetSQL_ORDERBY"
   GetSQL_ORDERBY = pSQL
   Resume Proc_Exit
   Resume
End Function

         
Public Function GetSQL_WHERE( _
      ByVal pSQL As String _
      , ByVal psWhere As String _
      , Optional pbooAdd As Boolean = False _
      , Optional booSubqueryHasWhere As Boolean = False _
      ) As String
'strive4peace
'add or replace criteria to/in the WHERE clause of an SQL string, if specified
'future: remove Where if not specified

'strive4peace ... 131204, 140120
'add criteria to the WHERE clause of an SQL string. Create if Where is not there.
'will FAIL if fieldname ends with 'where', 'group by', ' having', 'order by'
   On Error GoTo Proc_Err
   Dim iPos As Integer _
       , ipos2 As Integer

'   If Not Len(psWhere) > 0 Then
'      'no change
'      GetSQL_WHERE = pSQL   'same as what was sent
'      Exit Function
'   End If

   pSQL = Trim(pSQL)

   'look for WHERE
   iPos = InStr(pSQL, "WHERE ")
   If booSubqueryHasWhere Then
      iPos = InStr(iPos + 1, pSQL, "WHERE ")
   End If
   
   If iPos > 0 Then
      If pbooAdd Then
         'add to beginning of WHERE clause
         If Len(psWhere) > 0 Then
            pSQL = Replace(pSQL, "WHERE " _
                              , " WHERE (" & psWhere & ")" & " AND ")
         End If
      Else
         'replace WHERE clause
         ipos2 = InStr(iPos + 1, pSQL, "GROUP BY ")
         If Not ipos2 > 0 Then
            ipos2 = InStr(iPos + 1, pSQL, "HAVING ")
            If Not ipos2 > 0 Then
               ipos2 = InStr(iPos + 1, pSQL, "ORDER BY ")
            End If
         End If
         If Not ipos2 > 0 Then
            ipos2 = Len(pSQL)
            If Right(pSQL, 1) = ";" Then
               ipos2 = ipos2 - 1
            End If
         End If
         If Len(psWhere) > 0 Then
            pSQL = Left(pSQL, iPos + 5) _
                        & psWhere & " " & Mid(pSQL, ipos2)
         Else
            'remove WHERE clause
            pSQL = Left(pSQL, iPos - 1) _
                        & Mid(pSQL, ipos2)
         End If
      End If
   Else
      If Len(psWhere) > 0 Then
         'create WHERE clause
         'look for GROUP BY
         If (InStr(pSQL, "GROUP BY ")) > 0 Then
            'put before 'GROUP BY'
            pSQL = Replace(pSQL, "GROUP BY " _
                                 , " WHERE " & psWhere & " GROUP BY ")
            'look for HAVING
         ElseIf (InStr(pSQL, "HAVING")) > 0 Then
            'put before 'Having'
            pSQL = Replace(pSQL, "HAVING " _
                                 , " WHERE " & psWhere & " HAVING ")
         Else
            'look for ORDER BY
            If (InStr(pSQL, "ORDER BY ")) > 0 Then
               pSQL = Replace(pSQL, "ORDER BY " _
                                    , " WHERE " & psWhere & " ORDER BY ")
            Else
               'add to end
               iPos = Len(pSQL)
               If InStr(pSQL, ";") > 0 Then
                  pSQL = Replace(pSQL, ";", " WHERE " & psWhere & ";")
               End If
Debug.Print pSQL
            End If
         End If
      End If
   End If
   GetSQL_WHERE = pSQL
Proc_Exit:
      On Error Resume Next
      Exit Function

Proc_Err:
      '   MsgBox Err.Description, , _
          "ERROR " & Err.Number _
          & "   GetSQL_WHERE"
      GetSQL_WHERE = pSQL
      Resume Proc_Exit
      Resume
End Function

Open in new window

ProdLocID will be a combobox that stores ProdLocID and shows Location, Qty, (and maybe Product too), in different columns. On the GotFocus event of ProdLocID:  
   'dimension variables   
   dim sWhere as string
   'initialize values
   sWhere = ""
   with me.ProductID
      if isnull(value) then 
         .setFocus
         msgbox "You must enter a product",,"Missing data"
         exit sub
      endif
      sWhere = "ProdLocations.ProductID=" & .value
   end with
   call SetControl_RowSource(me, "ProdLocID", sWhere)

Open in new window

on the LostFocus event, reset list to show everything again:
      call SetControl_RowSource(me, "ProdLocID", "")

Open in new window

important: in order for this to work, the SQL statement that shows everything needs to be in the TAG property. If you change the RowSource, remember to change the TAG too.

As always, Debug, Compile, and then Save before testing any code.

basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
0
 
LVL 13

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 250 total points
ID: 41845852
the key phrase is " decide on a location" ....well this has two possibilities
1. A rather long list of IFs where you evaluate each case to see how to get the best locations according to some criteria
2.Machine Learning....
0
 
LVL 19
ID: 41845855
will the same product be stored in multiple locations? if yes, then why?

if there is not sufficient quantity for what they ordered, will what is left be pulled and then later they will be sent the rest? or if an order cannot be completely filled, will the whole order wait until it can? or the rest be filled and just that item wait? will substitutions be made if they want something you don't have?

obviously, the structure needs to be expanded ...
0
 

Author Comment

by:Dustin Stanley
ID: 41845970
yes the same product Could be stored in seperate locations. Not usually but could happen. Why because that's life LOL. Sometimes they may be stored on shelves that are joined but there is not enough space on just one. or we could have some new ones in receiving area that haven't been put away yet.

Orders can only be made for what we have in stock. No more. No back orders and no special orders. If they want what we do have great of not then the order would be cancelled.

I do have a table set up for substitutes called PartsAlternatives
0
 
LVL 19
ID: 41845983
> "Orders can only be made for what we have in stock. "
okay ... -- so you buy closeouts or something, not have what someone orders made?

> "PartsAlternatives"
I noticed that ... and I would have named those fields SkuIDmain and SkuIDalt, just so they are alphabetically next to each other.  I did not comment again on your reposted rel diagram since I am a bit confused ... wouldn't alt products also have a condition?

What if the condition ordered is not what you have? but you have another one?

when customers order something, do they also specify condition or is that something you determine based on what you have?

what is you have some of what they wanted but not all? For instance , if they want 10 and you only have 5 but have 15 substitutes?

do you sometime have more than one alternate product? If yes, then perhaps a list of the best substitution, then the second best, etc?
0
 

Author Comment

by:Dustin Stanley
ID: 41845989
yes we buy something like closeouts. We buy surplus. Basically when businesses go under or are trying to reduce stock or they are upgrading equipment and are getting rid of the old equipment. Thats when we come in.  
The PartsAlternative table is really just as our refrence guide. No condition needed. FocusSkuID is the part we would be focused on. What the customer or I am concerned with. The AltSkuID is any substitute parts. 2waycomp is a yes/no. (Can FocusSkuID also substitute the AltSkuID?)

If we have part of the order or a different condition then that is up to the customer. We can just say this is what we have and it's their call.
0
 

Author Comment

by:Dustin Stanley
ID: 41845991
oh yeah and any online orders they will only be allowed to order what we have in stock. The website has the stock quantities stored in it. So it will say sorry or call us.
0
 
LVL 19
ID: 41845993
you need to make allowances for that (can't sell what you don't have*) in your data structure -- take your best guess and start another question  ... hint: OrderDetails will have >1 Qty field

* but since you said some might still be in receiving ... might more come in? Do you need to put a flag to re-evaluate order in X days?

you did not answer ALL of my questions. Take your time, I am doing something else and may be tied up for awhile (recording a video but I am currently paused) ~
0
 

Author Comment

by:Dustin Stanley
ID: 41846005
>"okay ... -- so you buy closeouts or something, not have what someone orders made?

correct!


> "I noticed that ... and I would have named those fields SkuIDmain and SkuIDalt, just so they are alphabetically next to each other.  I did not comment again on your reposted rel diagram since I am a bit confused ... wouldn't alt products also have a condition? 

No condition needed.

>"What if the condition ordered is not what you have? but you have another one?

They can't order anything other than what we have. BUT if there is a different condition we will suggest it and they can decide.

>"when customers order something, do they also specify condition or is that something you determine based on what you have?

On the website every SKU has a Web page of its own and on that page it displays the four conditions we have available. They then can click on the condition of the SKU they want and that is the PRODUCT they order. The website only shows and allows orders for the quantities we have in stock.

>"What is you have some of what they wanted but not all? For instance , if they want 10 and you only have 5 but have 15 substitutes?

Contact the customer and suggest what we have. Either go with the substitute or cancel the order. Customers choice.

>"do you sometime have more than one alternate product? If yes, then perhaps a list of the best substitution, then the second best, etc?

Yes and good Idea.

>" * but since you said some might still be in receiving ... might more come in? Do you need to put a flag to re-evaluate order in X days?

Not sure. But I think no. Items are not available until processed and assessed by us.
0
 
LVL 19
ID: 41846014
Hi Dustin,

> "No condition needed."

this contradicts your earlier statement that ALL products have a condition. Perhaps you need ConditionID=0 and CondCode = "No Condition" in Conditions
(as you probably can tell, I like to specify the IDs for irregular values -- and they will obviously need to be excluded from the regular choices in the RowSource of the combo box)

> "we will suggest it and they can decide."
so that needs to be tracked

> "On the website every SKU has a Web page of its own"

so you should also store in the SKU table:
URL, text, 255

> "Contact the customer and suggest what we have. Either go with the substitute or cancel the order. Customers choice."

In OrderDetails, there needs to be:
QtyOrig
QtyAvail
QtySent

also:
dtmCustChg, date/time -- date/time customer was sent an option to change order based on what is available
dtmCustResp, date/time -- date/time customer responded with changed order

but, obviously, since an email was sent and responded to, quantities may have changed! requiring additional correspondence and resolution so perhaps a related table with these exchanges needs to be created.

I am beginning to see why you aren't happy with what is currently being used...
0
 

Author Comment

by:Dustin Stanley
ID: 41846025
When I say no conditions needed for the PartsAlternative  Table what I mean is that that specific table is for Internal use only. A part can replace a part no matter what the condition is. We just need a way to refrence this for our convenience. Yes one day down the road I could see this table playing a better part as in automatically making suggestions for the customers for us. I will set up the 0 no condition. Thats a good ideal.


I agree we do need to be able to track customers choices. These are some of the finer details I was planning for the future of this database.

I will add the URL field.

I will add all these suggestions in.


<" I am beginning to see why you aren't happy with what is currently being used...

I am pretty easy going and I do have a vision. I am very persistent upon reaching that vision. BUT if you knew what I was originally working with you would know this is a God Send. I am VERY thankful for this and ALL your help
0
 
LVL 19
ID: 41846033
> "this is a God Send"
thanks, Dustin

> "We just need a way to refrence this for our convenience.... that specific table is for Internal use only"

ok .. so when someone orders a part, they are actually ordering an SKU and you then decide what to give them? So then SkuID SHOULD be in OrderDetail. And then OrderDetails needs a child table that can be split with how you fill the order that includes ProductID if you might use different ones.

Structuring data is an iterative task -- as you see better ways, change structure and anything built on top of it that is impacted -- this will serve you best in the long run.

Q: do you ever fill the same order with parts with >1 condition if they order >1?

> "I do have a vision"

that is important! Visualizing ... I see that you are also flexible and open to bending if you see a better idea than what you imagine, which will benefit the success of your project.  You have come a long way in a short time.  Access usually longer to assimilate. You must be smart ;) ~

you have some thinking to do -- on this and your other thread. I gave you some elaborate code.  All you need to be concerned with there is that (1) you can put it in, (2) it compiles ok, and (3) most important, you can use (call) it.

> "some of the finer details"
when planning a database, even if you don't implement everything now, it is important to provision for everything you see
0
 

Author Comment

by:Dustin Stanley
ID: 41846051
No SKU is ever purchased just the PRODUCT. They decide when purchasing

Yes we fill same orders all the time with same SKU (SkuID)  multiple conditions (ConditionID) which comes out as multiple products (ProdID) if that makes sense. ProdID is what we sell.

We sell to a lot of businesses that Mark up the products and resell them themselves. So they like to buy all the conditions of a certain SkuID we have.
0
 
LVL 19
ID: 41846053
it sounds like your Products are SKUs and your INVENTORY has conditions...

> "ProdID is what we sell"

according to what you just said, SKU would be what you sell ... -- since that is what customer orders

responding to other note too ...
0
 

Author Comment

by:Dustin Stanley
ID: 41846073
I am sorry if i worded  that wrong. They like to buy all the ProdIDs we have of a SkuID. I don't know if you remember us speaking about this at the beginning. But SKUs are the items we receive(purchase) we then evaluate them and add a condition to them and that is when they are considered a sellable Product and that is what we sell. Every SKU can have up to 4 different conditions. If we haven't rated it then it is unsellable.
0
 
LVL 19
ID: 41846076
repeating, it sounds like your Products are SKUs and your INVENTORY has conditions...
0
 

Author Comment

by:Dustin Stanley
ID: 41846090
Yes sorry if I misunderstand
0
 
LVL 19
ID: 41846099
no problem -- but the structure needs fixin ...

Receiving is not yet set up -- perhaps CondCode belongs in a related table to that.
0
 

Author Comment

by:Dustin Stanley
ID: 41846109
Please explain. Right now receiving (this is hard to say...)   It is more like a treasure hunt. Bring in totes full of VERY mixed products new and used. Sort the item in the totes out, test/clean items, grade them by condition, advertise them. That is 1st Floor Finished (receiving). Receiving is a thing of its own for me right now.
0
 

Author Comment

by:Dustin Stanley
ID: 41846145
I do just want to clarify my answer to your previous question of
Q: do you ever fill the same order with parts with >1 condition if they order >1?

I only fill the order with the condition they chose in the order. Each condition is a different price. If they order 3NSOP that's what they get. I don't just grab a mix and throw them in the order to fill it.

I just wanted to make that clear.

What I said before as a answer I was meaning to say. Customers a lot of times by all the different conditions (ProductID) we have of a SkuID that we have in stock.  They will buy them at seperate prices. NSOP this price and USSP at that price ect..... Different products (one used / one new) but same SKU (both the same Power Switch/part)
0
 
LVL 19
ID: 41846821
after more thought, there should be a related table for correspondence as you may have  multiple messages to and from customers. Based on those changes, orders can change. Perhaps you don't care what an order was originally ... or do you?

Orders need a Status

Please summarize what you still need for your original question on this thread, thanks
0
 

Author Comment

by:Dustin Stanley
ID: 41847410
important: in order for this to work, the SQL statement that shows everything needs to be in the TAG property. If you change the RowSource, remember to change the TAG too.

This is my ProdLocID Combobox control on my ordersDetails Subform correct?

Which part is the SQL statement that needs to go into the TAG on the property Sheet?

Will my row Source be blank?

Thanks.
0
 
LVL 19
ID: 41847412
you're welcome

> "This is my ProdLocID Combobox control on my ordersDetails Subform correct?"
yes

> "Which part is the SQL statement that needs to go into the TAG on the property Sheet?"

what is in the RowSource property WITHOUT any criteria

> "Will my row Source be blank?"

No, the RowSource will be what you want to see without any criteria

because this process (also sometimes called "cascading comboboxes") is so common, I wrote code to make it easy to call and use ;) ~
0
 

Author Comment

by:Dustin Stanley
ID: 41847427
what is in the RowSource property WITHOUT any criteria

SELECT ProdLocations.ProdLocID, Locations.LocNm, ProdLocations.QtyLoc, Products.ProductID, Products.ProdConSku, SKUs.SkuNm FROM SKUs INNER JOIN (Products INNER JOIN (Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID) ON Products.ProductID = ProdLocations.ProductID) ON SKUs.SkuID = Products.SkuID;
0
 

Author Comment

by:Dustin Stanley
ID: 41847436
Private Sub ProdLocID_GotFocus()
'dimension variables
   Dim sWhere As String
   'initialize values
   sWhere = ""
   With Me.ProductID
      If IsNull(Value) Then
         .SetFocus
         MsgBox "You must enter a product", , "Missing data"
         Exit Sub
      End If
      sWhere = "ProdLocations.ProductID=" & .Value
   End With
   Call SetControl_RowSource(Me, "ProdLocID", sWhere)


End Sub

Private Sub ProdLocID_LostFocus()
Call SetControl_RowSource(Me, "ProdLocID", "")
End Sub

Open in new window



Value Variable Not defined
0
 

Author Comment

by:Dustin Stanley
ID: 41847439
Call SetControl_RowSource

Expected Variable or Procedure not Module

But I thought this was put into a general module
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19
ID: 41847442
you have ProductID twice .... be SURE in the table design that the Display Control in table design (bottom pane of field properties, on the tab that is not displayed by default) is TEXTBOX, not something else.  Caveat: is it OK for display control to be checkbox for Yes/No fields
... but the field should NOT be stored in the table with a RowSource.

Also, the field should NOT have a caption.

These are things that mask the real values. Only a developer should open tables directly -- and developers need to see the real information.  Access lets, and even encourages, you to do many things that bite you later.
0
 
LVL 19
ID: 41847446
> "IsNull(Value)" -->
IsNull(.Value)

Open in new window

.Value needs to be preceded with . so Access knows it is a property of the object being used for WITH

> "But I thought this was put into a general module"

*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc.

IMPORTANT: do NOT name the module the same as any procedure. Give it a unique name like "mod_whatever" or "bas_whatever"

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

Option Explicit ' require variable declaration
0
 
LVL 19
ID: 41847452
> "you have ProductID twice"

actually, I said that wrong ...  ProductID should NOT be one of the columns -- and that is why I thought you might be using a combo for the display control.  The SQL statement needs to show  TEXT information after the first column (which will be hidden).
0
 

Author Comment

by:Dustin Stanley
ID: 41847474
Ok removed combo
SELECT ProdLocations.ProdLocID, Locations.LocNm, ProdLocations.QtyLoc, Products.ProductID, Products.ProdConSku, SKUs.SkuNm
FROM SKUs INNER JOIN (Products INNER JOIN (Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID) ON Products.ProductID = ProdLocations.ProductID) ON SKUs.SkuID = Products.SkuID;

Open in new window


call SetControl_RowSource(me, "ProdLocID", sWhere)

Open in new window


sWhere

Compile Invalid or unqualified reference


Would I use Forms!sbfrmqryOrderDetails_Test!ProductID for any of this?
0
 

Author Comment

by:Dustin Stanley
ID: 41847476
Sorry it would be Me!ProdlocID
0
 

Author Comment

by:Dustin Stanley
ID: 41847480
Option Compare Database
Option Explicit

Private Sub ProdLocID_GotFocus()
'dimension variables
   Dim sWhere As String
   'initialize values
   sWhere = ""
   With Me.ProdLocID
      If IsNull(.Value) Then
         .SetFocus
         MsgBox "You must enter a product", , "Missing data"
         Exit Sub
      End If
      sWhere = "ProdLocations.ProductID=" & .Value
   End With
   Call SetControl_RowSource(Me.ProdLocID, sWhere)


End Sub

Private Sub ProdLocID_LostFocus()
Call SetControl_RowSource(Me!ProdLocID, "")
End Sub

Open in new window


This is what I have and I guess it is functioning :)

I honestly don't know what to expect from it. I just select a product  and location in the drop combo.

What else is it supposed to do?
0
 
LVL 19
ID: 41847482
control NAME should be the same as control source for bound controls, in my opinion -- this is easiest and Access is good at knowing what you want.  the function needs the control name without me -- adding me makes it a control reference.
0
 

Author Comment

by:Dustin Stanley
ID: 41847486
Am I supposed to alter the Module RowSource code any?
0
 
LVL 19
ID: 41847495
> "Module RowSource code"

no such thing ... you mean control RowSource ? No just set it to show all values.  Each time you change it, copy the SQL statement to the TAG property.

when a product is picked, the rows for the combobox should be limited to locations with that product in them
0
 

Author Comment

by:Dustin Stanley
ID: 41847508
SELECT ProdLocations.ProdLocID, Locations.LocNm, ProdLocations.QtyLoc FROM Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID;

Open in new window



no such thing ... you mean control RowSource ? No just set it to show all values.  Each time you change it, copy the SQL statement to the TAG property.


I meant the function you gave me I named it Mod_SetControl_Rowsource
0
 

Author Comment

by:Dustin Stanley
ID: 41847511
Ok just so I can be on the same page. What exactly will this do when functioning correctly?
0
 

Author Comment

by:Dustin Stanley
ID: 41847519
when a product is picked, the rows for the combobox should be limited to locations with that product in them

I missed this part. Sorry!
0
 

Author Comment

by:Dustin Stanley
ID: 41847523
Ok I will have to work on it tomorrow. Thanks.
0
 

Author Comment

by:Dustin Stanley
ID: 41848170
Ok this is what I have configured now and it is bringing up just the locations for the product thank you. On the LostFocus part what should I expect if it is right and what should I expect if it is wrong? Also Please look  the call on the GotFocus and Lost Focus of my code. Is this correct. It seems to work.

Option Compare Database
Option Explicit
Private Sub ProdLocID_GotFocus()
'dimension variables
   Dim sWhere As String
   'initialize values
   sWhere = ""
   With Me.ProductID
      If IsNull(.Value) Then
         .SetFocus
         MsgBox "You must enter a product", , "Missing data"
         Exit Sub
      End If
      sWhere = "ProdLocations.ProductID=" & .Value
   End With
   Call SetControl_RowSource(Me.ProdLocID, sWhere)


End Sub

Private Sub ProdLocID_LostFocus()
Call SetControl_RowSource(Me.ProdLocID, "")
End Sub

Open in new window


SELECT ProdLocations.LocID, Locations.LocNm, ProdLocations.QtyLoc
FROM Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID
WHERE (((ProdLocations.ProductID)=9230));

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41848196
Please summarize what you still need for your original question on this thread, thanks

Ok so I ASSUME.... The user placing the order in access has to decide which location to use here when placing the order. Not Access Automatically?

This Slims down the question some. Thank you! BUT I still have to have access deduct the quantity of the order from the location the user chose and some sort of picking list generated?

IF the code above looks good to you and nothing is wrong. I will open a new question for the Quantity Auto Deduction from the inventory location. But I still need to generate a pickers list from this question first.
0
 
LVL 19
ID: 41848406
> "Not Access Automatically?"
if there is only one choice, Access can do it. Does your combobox have a header row?

> "still have to have access deduct the quantity of the order from the location"
that should be done with a calculation until it is actually picked

> "generate a pickers list "
you should add another field to your ProdLocations table for the pick order -- call it PickOrdr (integer)
I assume sorting by shelf won't work real well since you have shelves on both sides of an aisle

> "On the LostFocus part what should I expect if it is right and what should I expect if it is wrong?"
If is is RIGHT then the comboboxes on the other rows should show their data again -- when you limit the combo for one record, it is limited for all

> " Is this correct. It seems to work."
it looks syntactically correct.  You should balance the indents though
1. all code in a procedure has an indent
2. code between With...End With has another indent
3. code between If...End If has another indent
4. add comments for yourself -- like at the top of each procedure to explain what is happening
5. put a blank line between each procedure
6. blank lines in a procedure are ok but if there is >1, delete to just one

> "I meant the function you gave me I named it Mod_SetControl_Rowsource"
no, that is the name of the MODULE. A module is a page of one or more procedures.
0
 

Author Comment

by:Dustin Stanley
ID: 41848482
Does your combobox have a header row?

I put one in but after you choose your location it just displays location not quantity


that should be done with a calculation until it is actually picked
Are you saying like a field called Available Quantity and another field called Total Quantity. Available Quantity is after the order but before the pick it is deducted. Total Quantity is the quantity in the building Total and will get deducted after the pick?


I assume sorting by shelf won't work real well since you have shelves on both sides of an aisle

Even though there are shelves on each aisle they all are named differently.
eg:  Aisle / V1 / W1 / Aisle / X1/Z1 / Aisle   (X1/Z1 and V1 / W1 are backed against each other)

If is is RIGHT then the comboboxes on the other rows should show their data again -- when you limit the combo for one record, it is limited for all

Ok please be a little more clear for me. Datasheet view or Single Form view?
Rows as in other records or other controls on the form?
I deleted the LostFocus event and it acts the same as normal. With or without it???
0
 
LVL 19
ID: 41848559
> "I put one in but after you choose your location it just displays location not quantity"

you can add calculated controls to echo information from other columns:
=ProdLocID.column(2)
where column 2 is really column 3 since indexing starts with 0. Name it: ProdLocID_QtyLoc

to pick 1st choice after the RowSource is changed:
   With Me.ProdLocID
      If .ListCount = 2 Then 'since you have a header row
         .Value = .ItemData(1) 'this is really row 2 since first row is header ... starts with 0
      End If
   End With

Open in new window

> "done with a calculation"
make a query that sums Qty for all ProdLocID where IsAdjust = 0 (false) -- call it, for instance, qProdLocID_QtyOrderedNotAdjusted

this query can be joined into the RowSource and a calculated field created to show what is available (QtyAvail -  IIF( IsNull( qProdLocID_QtyOrderedNotAdjusted.ProdLocID ), 0, [calculatedFieldName])

> "Datasheet view or Single Form view?"
both
instead of Datasheet view, continuous forms are better so you can have header/footer sections

> "I deleted the LostFocus event and it acts the same as normal."
you need to keep that ... what if you move to another record that already has it filled out? Don't you want to see the data? (and if you move to another record and are still in that same control, just click to another one). Alternately, you can set that row source on the ProductID AfterUpdate event and then reset it run that code on the form Current event but that would make it execute more often than necessary

> "shelves on each aisle they all are named differently"
Access should know the order you want to pick them so the list can be more useful
0
 

Author Comment

by:Dustin Stanley
ID: 41848635
make a query that sums Qty for all ProdLocID where IsAdjust = 0 (false) -- call it, for instance, qProdLocID_QtyOrderedNotAdjusted
I don't know if this right but:
SELECT ProdLocations.QtyLoc, ProdLocations.LocID, [sAdjust]=0 AS Expr1
FROM (Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID) INNER JOIN ProdMovements ON (Products.ProductID = ProdMovements.ProductID) AND (ProdLocations.ProdLocID = ProdMovements.ProdLocID);

Open in new window


> "I deleted the LostFocus event and it acts the same as normal."
you need to keep that ... what if you move to another record that already has it filled out? Don't you want to see the data? (and if you move to another record and are still in that same control, just click to another one). Alternately, you can set that row source on the ProductID AfterUpdate event and then reset it run that code on the form Current event but that would make it execute more often than necessary

Don't worry I put it back. I was just testing to see if I could tell if there was a difference.


Access should know the order you want to pick them so the list can be more useful

I will save this for another question so please keep an eye out ;)
0
 
LVL 19
ID: 41848662
>"I don't know if this right "

it is not. Add this field to OrderDetails:
dtmAdjust, date/time

Open in new window

SELECT ProdLocID, Sum(Qty) AS QtyOrderedNotAdjusted 
FROM OrderDetails
WHERE IsNull(dtmAdjust) 
GROUP BY ProdLocID;

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41848702
I have the fields dtmEdit. Is dtmAdjust, date/time different? Should I delete the dtmEdit?


I won't lye these queries are alittle bit of a learning curve for me. I see the power behind them and try to decipher them. Alot of times it works but sometimes I just don't get it.
0
 

Author Comment

by:Dustin Stanley
ID: 41848720
Now this query I have would I use it as a control source for a a form? Just anywhere I need to see Available quantity that hasn't been picked yet?

Also the IsAdjusted field really throws me off and that may be part of me not getting it. How would one use this field in a real life scenario?
0
 
LVL 19
ID: 41848723
> "sometimes I just don't get it."

after you put in the SQL, look at the design view

start a Word document with screen shots to help understand
1. Query Name
2. SQL Statement
3. Design View image
4. Datasheet View image
0
 

Author Comment

by:Dustin Stanley
ID: 41848732
I don't know if you seen this or not.

Now this query I have would I use it as a control source for a a form? Just anywhere I need to see Available quantity that hasn't been picked yet?

Also the IsAdjusted field really throws me off and that may be part of me not getting it. How would one use this field in a real life scenario?
0
 
LVL 19
ID: 41848745
sorry, this does need ProdMovements so there is no IsAdjusted field

this thread is talking about customer orders, right?

so there needs to be a field in Order Details whether or not the adjustment has actually happened.  It shouldn't be subtracted until you do it.  That is why the query to add up what an order has used that is not yet subtracted is needed.

That would be added to the ROW SOURCE for the combo to choose ProdLocID
(and why the query is grouped by that field)
0
 

Author Comment

by:Dustin Stanley
ID: 41848767
sorry, this does need ProdMovements so there is no IsAdjusted field

this thread is talking about customer orders, right?


I feel you! LOL!!! :)


I am going to focus on the other question until it is complete and I will come back.
0
 
LVL 19
ID: 41848775
ok, I have to get back to my work now ~ hopefully someone else will help

Add the following field to Locations:

PickOrdr, integer

and fill data.

I would suggest starting shelves on floor 1 with 1010, shelves on floor 2 with 2010, etc

numbers don't have to be every one, just sort right. I would skip numbers, like use every 5 or every 10 so it is easier to come back later and rearrange them.

These numbers will not need to be unique.

You might decide to make the shelves across from each other the same. Or maybe one side odd numbers and the other side even numbers
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41850807
Thanks.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Running Very Slowly on Windows 7 PC 27 61
excel file 5 53
DSum between dates 5 15
Minus first query 1 6
One of Google's most recent algorithm changes affecting local searches is entitled "The Pigeon Update." This update has dramatically enhanced search inquires for the keyword "Yelp." Google searches with the word "Yelp" included will now yield Yelp a…
Prime numbers are natural numbers greater than 1 that have only two divisors (the number itself and 1). By “divisible” we mean dividend % divisor = 0 (% indicates MODULAR. It gives the reminder of a division operation). We’ll follow multiple approac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now