Structure for inventory with multiple allowances

[Soupy8728]

Hello,



I am trying to build an inventory database and I'm running into an issue that I'm not sure how to approach. We have different locations and each location is allowed a certian amount of each item. In my example table below, Item A and Item B have fixed allowances of 10 and 15 respectively. The issue I'm having is Item C, D, and E have an allowance of 30 but it can be any combination of those items to make 30. How do I do this? Thank you for your time.


tblINVENTORY

ITEM ALLOWANCE
ITEM A 10
ITEM B 15
ITEM C
ITEM D
ITEM E

[ranman256]

on the entry form , after every entry, lookup the limit

sub form_AfterUPdate()
iEntryCount = Dcount("*","tEntryTbl","[item]='" & txtItem & "'")
iLimit = Dlookup("[Allowance]","tblInventory","[item]='" & txtItem & "'")

if iEntryCount > iLimit then
   msgbox "You are over the limit

    'delete record here?
endif
end sub

[Bulzie]

So you are saying C,D,E can only have a combined limit of 30?

[Welshgasman]

I would write a function to do that.
Function could have a Select Case
For A & B just DLookup() as normal
For anything else
Process the recordsetclone and sum the amounts for C,D & E.
I would not hard code the limit, but store them in a table in case they change.

Then Dlookup() that limit, so C would be 30 atm and would count for C,D & E

[Bob Fitz]

Hello,

I am trying to build an inventory database and I'm running into an issue that I'm not sure how to approach. We have different locations and each location is allowed a certian amount of each item. In my example table below, Item A and Item B have fixed allowances of 10 and 15 respectively. The issue I'm having is Item C, D, and E have an allowance of 30 but it can be any combination of those items to make 30. How do I do this? Thank you for your time.


tblINVENTORY

ITEM ALLOWANCE
ITEM A 10
ITEM B 15
ITEM C
ITEM D
ITEM E

I think you may need to structure your tables differently. See attached

[Soupy8728]

Yes, exactly. It could be 30 of C and 0 for D and E or 10 each....like that

[Soupy8728]

So you are saying C,D,E can only have a combined limit of 30?

Yes, exactly. It could be 30 of C and 0 for D and E or 10 each....like that

[CJ_London]

Is there some other factor you haven’t mentioned such as a shelf location? The location you mention could be anything from a shelf to a country

think we need to better understand your business model to make some sort of sense of the requirement. Why can’t you have say 5 of item A and 5 each of C,D and E for example. Is the limits set based on weight or dimensions or some other factor? Are all locations the same spec? So any location can only have a max of 10 item A’s? Or could a different location hold 12 or 20 or 100?

[Soupy8728]

I think you may need to structure your tables differently. See attached

Thanks Bob, that's what I was originally thinking but I just couldn't put it together in my head. This helps a lot!!

[Bob Fitz]

Thanks Bob, that's what I was originally thinking but I just couldn't put it together in my head. This helps a lot!!

Always glad to help, if I can. Post back if you have any questions about the db that I posted.

[Soupy8728]

Always glad to help, if I can. Post back if you have any questions about the db that I posted.

Hello Bob, is there a macro in your database? I'm trying to open it at work but I get an error "macros in this document have been disabled by your enterprise administrator for security reasons". Thank you.

[Bob Fitz]

Hello Bob, is there a macro in your database? I'm trying to open it at work but I get an error "macros in this document have been disabled by your enterprise administrator for security reasons". Thank you.

No. I very seldom use macros. I prefer to use vba code which I have done in three event procedures in the form.
I googled the error message and the results make me think that your problem is to do with the Trust Center. Perhaps you need to move the db to a "Trusted" folder.

[Soupy8728]

No. I very seldom use macros. I prefer to use vba code which I have done in three event procedures in the form.
I googled the error message and the results make me think that your problem is to do with the Trust Center. Perhaps you need to move the db to a "Trusted" folder.

Thank you. Appreciate your help!!

[Bob Fitz]

Thank you. Appreciate your help!!

You're welcome. Can you confirm my suspicions regarding the trust issue.