Help with "simple" inventory system

[waltb]

We have an inventory system consisting of numbered boxes (1-....) and each box has 20 positions. To add an inventory tracking system to my current db, I'm assuming the need to create a table (BOX, with unique values) with a linked table (POSITION) which would contain numbers 1-20. The assignment portion would be done using combo box for the Box, and a LIST Box for position, both in a form related to the items. Here is my question: If a position (Box 1, Position 1) has been assigned for item "A", then when selecting a position for item "B", I only want to show positions 2-20 as available for Box 1, etc... Please advise. Thanks!

[June7]

What you are trying to implement is cascading or dependent combo & list boxes but with a bit of a twist. I can envision a number of ways to accomplish this. All involve VBA code to some degree.

The tables aren't absolutely necessary unless you have other info about the boxes and info about the positions you need to maintain. VBA code could construct the RowSource for each control without using tables as source of values.

1. Set the value of a global variable that is incremented with each record committed and is used to determine the start value for the positions.

2. Increment a textbox on the form and use it same as the global variable option

3. query the database for the last position number for a particular set of records and use that retrieved value as in options 1 and 2.

Whichever approach you choose, modifying the list box will require an event trigger. Deciding the correct event(s) to handle this is critical.

Another option may be to take building this ID value out of the hands of the user. Why does user select the box and position?

Will there be multiple simultaneous users of this db? Is it a split design?

[waltb]

Kevin, thanks for responding! Cascading combo/list boxes will be new to me! With our process, this 'inventory' is an archive so items will not be moving in and out of assigned boxes/positions. Basically a process dead-end that must be performed. I believe the trigger event must be at the point when a user assigns a box number from a combo list (whose numbers would be populated by the user as the boxes are generated). I like the idea of a loop (for i=1 to 20) to generate the list of positions in a unbound list box but only display those numbers (1-20) which have NOT already been associated with the current box value. I'll read up on cascading combo/list boxes. Any VBA coding appreciated!

[June7]

A consideration in writing code for the listbox is if users are given a range of values to choose from does that mean they can select from the middle of the range? If so this significantly complicates limiting the listbox list to only unused positions, say if user has selected positions 1 and 9 then the list would include numbers 2-8 and 10-20.

[waltb]

That would be the general idea. Build in flexibility so that any of the 20 positions could be selected at any given time. Though in practice we (there are two of us) will start with Position 1, then 2, etc., but not all 20 are generally used. I'm all for simple so if the coding forces us to always start with Position 1, that is OK! Right now, this inventory information is typed into a Word table as needed (one page per box, and printed), separate from the current product db (old VAX), so you see the need to add this functionality into our new database to eliminate redundant data entry, which I'm writing in Access. Thanks again.

[June7]

Yes, simplest might be to eliminate the listbox and to automate the next position assigned.

In the AfterUpdate event of combobox would need code to determine the next position. Something like (I only use VBA, not macros):

strLocation = Nz(DMax("field name", "table name", criteria),"")
If strLocation = "" Then
Me.Location = Me.BoxNo & "-" & 1
Else
Me.Location = Me.BoxNo & "-" & Right(strLocation, InStr(strLocation,"-") +1) + 1
End If

I don't know enough about your data to determine the criteria for the DMax search. This argument can have as many criteria as needed.

Problem with sorting text values that have numbers is that without placeholder 0's the sort will not work. For example, 11 will sort before 3. So the Box-Position ID should be structured like 001-01 through 001-20 in order to return the Max value.

Populating the combobox list as users input the box numbers will require more code.

[waltb]

I like the approach of using DMAX. I'll work on that approach. Since I want two distinct fields, Boxnumber and Position, my criteria to find the DMAX position value (two digit) would be limited to records with Boxnumber values equal to the current boxnumber value in my form. That should be easy. It also looks like you're concantenating boxnumber & position, but I don't want to do that. My report will be constructed to look similar to our current 'Word Table' such that it will return all items related to a single boxnumber, and sort the list of items by position numbers (01-20). Would you mind explaining the right half of your code and what it's attempting to do:

Me.Location = Me.BoxNo & "-" & Right(strLocation, InStr(strLocation,"-") +1) + 1

That will help alot. Thanks.

[June7]

I misunderstood your original post, thought you wanted to save the box and position as a single text identifier, like 001-11. If you are going to keep them in separate fields as numbers that could make finding the Max easier. My expression was to extract the position number from the combined identifier and construct the next identifier in the sequence. So adjust the code as needed.

[waltb]

Thanks for the explanation. I'll work on the DMAX statement. Also, found this site of coding examples, particularly one to exclude values from Listbox (see L listings) which is what I originally had in mind but the complexity for what I want to do is way beyond my technical expertise.
http://www.rogersaccesslibrary.com/TableOfContents3.asp

[waltb]

I've simplified the AfterUpdate coding in the BOX field as such:

Private Sub BOX_AfterUpdate()
Me.Position = Nz(DMax("POSITION", "tblAliquots", BOX = Me.BOX)) + 1
End Sub

Which as I understand to mean, "assign the value of POSITION in the current form where the maximum value of POSITION in my "table" for all records where the value of BOX = the current value of BOX in my form, and add 1".

As I manually enter the box number for each record, the result continues to increment the value of Position, even if I change BOX numbers, which is not the expected behavior (entering Box 2 should have reset Position to 1):

Record 1: Box 1, Position 1
Record 2: Box 1, Position 2
Record 3: Box 2, Position 3

[June7]

I am surprised you get anything at all. Need the alternate value for Nz and Box= needs to be in quotes with concatenation of the control value. Try:

Nz(DMax("POSITION", "tblAliquots", "BOX =" & Me.BOX),0) + 1

Is BOX the only criteria needed? Aren't BOX numbers repeated for other items? As I said, don't know enough about your data to determine needed criteria but you can have as many as needed in the DMax. Ex:

"BOX =" & Me.BOX & " AND Item=" & Me.Item

If a field is text datatype need apostrophe delimiters, date needs # character
"Item = '" & Me.Item & "'"

[waltb]

That was it! Thank you very much! Works like a charm.

POSITION = Nz(DMax("POSITION", "tblAliquots", "BOX =" & Me.BOX),0) + 1

Per your question, yes, BOX is the only criteria. In our process, the individual items are placed into box positions prior to data entry so this becomes a simple inventory record for electronic tracking purposes, rather than an "automated pre-placement" scheme.

I'll have to read up more on DMAX to better understand placement of quotes, and why "0" is present in the formula.

[June7]

The 0 is in the expression so that 1 will have something to add to if the DMax returns Null. Calculations with Null will return Null. The Nz function returns the 0 if DMax returns Null.

[waltb]

Thanks. At first I thought the use of Nz automatically meant to return a value of "0", but now I understand you can use it to return anything, even text.

Also, I modified the code so that if someone should decide to delete their BOX entry due to a mistake, they won't get an error code:

Private Sub BOX_AfterUpdate()
If IsNull(Me.BOX) Then
Me.POSITION = ""
Else
POSITION = Nz(DMax("POSITION", "tblAliquots", "BOX =" & Me.BOX), 0) + 1
End If
End Sub

[June7]

Very good, quick study there. Yes, Nz can return whatever you specify - number, text, empty string. Issue appears to be solved. Happy programming!