Inventory Total in a table

[CINQUEFOIL22]

Okay. I have a table we use when we receive in inventory. (Table is "InventoryReceivingList") It is all tracked by a 3 letter trace code (which the product is marked with). I have another table where the guys simply type in the 3 letter trace code and the qty they are using (Table is "ProductUsed").
What do I need to be able to do is run a form that takes the amount used and subtract it from the total received. I have tried like a thousand different ways and yet I am still confused?
Any suggestions?

 

[JamesMcS]

Have you tried an update SQL statement?

 

[CINQUEFOIL22]

Have you tried an update SQL statement?

I have to be honest in that I don't know what you're talking about....

 

[JamesMcS]

Well, in your form you'd have your text box to enter the 3 letter identifier, and a text box for quantity, and a button to say "Update stock". In the on click of the button you'll want to have

docmd.runsql "UPDATE Productused SET [quantity field]=[quantity field]-" & me.[qty_textboxname] & " WHERE [identifier]=" & me.[ident_textboxname].value

This will update the table, subtracting the amount entered in the qty textbox from the quantity field in the table, where the 3 letter identifier equals whatever they entered in the identifier text box.

 

[CINQUEFOIL22]

Well, in your form you'd have your text box to enter the 3 letter identifier, and a text box for quantity, and a button to say "Update stock". In the on click of the button you'll want to have

docmd.runsql "UPDATE Productused SET [quantity field]=[quantity field]-" & me.[qty_textboxname] & " WHERE [identifier]=" & me.[ident_textboxname].value

This will update the table, subtracting the amount entered in the qty textbox from the quantity field in the table, where the 3 letter identifier equals whatever they entered in the identifier text box.

And as usual in my case, I am getting errors...

I am assuming (Please correct me if I am wrong) that I needed to replace parts of your code with my field names...So this is what I typed in:

docmd.runsql "UPDATE Productused SET [quantity field]=[qtyused]-" & me.[qty_used] & " WHERE [TraceCode]=" & me.[ident_Qty/LengthReceived].value

 

[JamesMcS]

Replace quantity field with qtyused.

Have you got a control name that long? I'd avoid having special characters in control or field names to be honest (the /). But that's the name of the control that users enter the trace code yeah? That's what it should be

 

[CINQUEFOIL22]

James,
Firstly, I really want you to know I do appreciate the help you are giving me. If not for this forum, I would still be at square one!!

When I click the button icon to add to my form it gives me a bunch of options. I am using the form operations selection,but i have to be honest in that I am not sure which other choice I am supposed to use...
Any input on that? Am I going about it wrong?

 

[JamesMcS]

No worries, I got the same help from everyone when I started - that's why I'm posting back these days.

The reason it's coming up with options is because you've got "Use control wizards" selected I expect. Unselect that first and then try creating the new button. All you need to do is click on the button in design view, go to properties, events, on click and click the 3-dot button next to it. Assuming the on click box is empty it'll come up with a window prompting you to choose, amongst others, the code builder.

When the VBA window opens paste the code above in, go to form view, click the button and see what happens.

 

[CINQUEFOIL22]

This is the error I am receiving:

"Microsoft Office Access can't find the field "I" referred to in your expression."

It really isn't an I, it's a line like an I without the top or bottom...
I am so confused....

 

[CINQUEFOIL22]

Okay...kinda found a way around my issue....I had the report give me a sum total on the trace code footer....I then inserted a text box with a code that subracts the tracecode total from the tracecode total qty/length received....
A bizzare way around it I guess, but it will work...Do you forsee problems with it being like that in the future though?

 

[JamesMcS]

Hmm not a great way to work around it. The | (bar, shift and backslash) just means we're not referring to the controls in the right way.

Is the command button on the same form that users enter identifier and quantity into?

 

[CINQUEFOIL22]

yes, it is....I am so confused....I don't understand...Even though I add the button, where is the data supposed to even show up at that shows the available quantity?

 

[Brianwarnock]

Ok first off I've never done an inventory DB so I'm not going to comment on the approach/design, however I would have thought that you are trying to update the Qty/LengthReceived in the Inventoryreceivinglist Table, therefore I think that your code on the Command18 button should be.

It is not a good idea to have objects with the same name eg a Table and a Form, I always prefix my objects with eg tbl frm rpt qry but the naming convention is a matter of taste but stick to it.

Brian

Private Sub Command18_Click()
Dim strSQL As String

strSQL = "UPDATE inventoryreceivinglist SET [Qty/LengthReceived] = [Qty/LengthReceived] -" & Me.[QtyUsed] & " WHERE [TraceCode]= '" & Me.TraceCode & "'"

DoCmd.RunSQL strSQL

End Sub

 

[CINQUEFOIL22]

Again, thank you for your input, both of you. Maybe I am confusing you? Which, given my nature, I don't think that statement it toooo far from the truth!!

We want to keep the total quantity received in the receiving list as it was when it came in. Simply for reference.

I have the form where we input what we receive in, the form that we input what we have taken out. What I would like to do is be able to simply run a report that will show me what I have in stock (anything over a quantity of zero.) Is that possible?

 

[CINQUEFOIL22]

Again, thank you for your input, both of you. Maybe I am confusing you? Which, given my nature, I don't think that statement it toooo far from the truth!!

We want to keep the total quantity received in the receiving list as it was when it came in. Simply for reference.

I have the form where we input what we receive in, the form that we input what we have taken out. What I would like to do is be able to simply run a report that will show me what I have in stock (anything over a quantity of zero.) Is that possible?

Please disregard my last post...Although it would be nice to have the original entered, I did try what Brian said. I can simply run my report off of the Inventory Receiving List and roll with it that way....It is doing exactly what I need it to do as far as total inventory available...So, either way, I am sure boss man will be happy.
Besides, that is what we having Purchase Orders and Packing Slips for...To tell us what we ordered and what we received in...Many Thanks for your help!!