Inventory Database 2

[bjarvis]

I have created a database to keep track of the maintence that we perform on our trucks. On The form I Show the part numbers for the specific truck that is selected. I then have a combo box to select if a part has been changed or greased. In my inventory I want it to automatically update if the combo box equals changed. Any ideas on how to do this would be appreciated. If you need me to send you the database I can.

 

[JoeMiller]

You would like what to update to what when you change the combo box? We need a little more information here, and table names, field names, control names, examples are very helpful!! Joe Miller
joe.miller@flotech.net

 

[bjarvis]

What I have is a form that the user enters in information on the maintence that was performed on a anhydrous tank. I have a combo box at top that the user chooses the tank number that they're working on. Then below that there is some text boxes that automatically fill with part numbers for the tank that was selected. These text boxes are labeled Outer Bearing#, Inner Bearing#, Outer Race#, Inner Race# and Seal#. Then there is a tab control with 4 tabs labeled Left Front Tire, Right Front Tire, Left Rear Tire and Right Rear Tire. In each of these tabs there is six combo boxes labeled Outer Bearing, Inner Bearing, Outer Race, Inner Race, Tire and Seal. With These combo boxes you can either choose Changed or Greased. When the user selects change I want to be able to automatically take one off the inventory for that part number. For example, the user selects change for the outer bearing and then that bearing numbers inventory is deducted one for the on hand inventory and if the user selects change for inner bearing then it does the same thing for that part number. I hope this makes it a little clearer.

 

[JoeMiller]

Much! You need to make an update query that modifies your Inventory Qty. This could be run using VBA in the AfterUpdate event of your combo box. The thing that makes it hard is you have so many combo boxes to set the event on and you need to know the combo currently being worked on in order to get the correct part number to be updated.

I've made a function that you'll have to change the field table names of the SQL statement, but other that that is ready for you. What you'll do is paste this function into your form and in the AfterUpdate event of each control you can call this function and have it update the inventory. The function will ask if you want to update, and if you say yes it will. Why do I make you answer yes or no? Because if the user were to sit there and change Changed/Greased over and over they could deplete the inventory because it would happen every time they modified the combo box. This doesn't prevent that, just makes the user aware that there changes do something.

Here's the function:
[tt]
Function UpdInvQty(ControlName As String)
Dim Response

Response = MsgBox("Update inventory quantity?", vbYesNo, "Question?&quot
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Inventory SET Inventory.Qty = [Qty]-1" _
& "WHERE (((Inventory.ID)=" & Me(ControlName) & &quot)"
DoCmd.SetWarnings True
End If
End Function
[/tt]

Items in red need to be changed to match your database table names and fields. Then you'll use an if statement in the after update event of each combo like this:

[tt]
If Me.MyComboName = "Changed" Then
UpdInvQty("MyComboName&quot
End If
[/tt]

This is only a way to do this and by no means "THE way." Make sure that you back up before using this stuff should something go wrong.

HTH Joe Miller
joe.miller@flotech.net

 

[sko]

What does your inventory table look like?
More information on your tables and fields would be helpful. For example

tblTanks
TankNum <- Primary Key

tblWorkOrder
WorkOrderNum <- Primary Key
TankNum <-Linked to tblTanks.TankNum
Date

tblWorkDetail
RecordNum <- Primary Key, Autonumber
WorkOrderNum <-Linked to tblWorkOrder.WorkOrderNum
Component <- Valid values would be Left Front Tire etc.
WorkDone <- Changed, Greased
PartsUsed <- Valid values would be OuterBearing# etc.

The idea would be a one to many relationship between the work order and the work order detail table so...

WorkOrderNum 1
TankNum 200
Date Friday the 13th

WorkOrderDetails
RecordNum 1,WorkOrderNum1, L.F.Tire, Changed, OuterBearing#
RecordNum 2,WorkOrderNum1, L.F. Tire, Changed, InnerRace#
RecordNum 3,WorkOrderNum1, R.F. Tire, Greased, InnerBearing#

 

[bjarvis]

I wrote the function and then used the if statement in the event procedure. When I selected changed from the combo box it came back with an error message &quot;Invalid use of Me keyword.&quot;

 

[bjarvis]

Here's my code in the event procedure.
Private Sub Left_Front_Inner_Bearing_AfterUpdate()
If Me.Left_Front_Inner_Bearing = &quot;Changed&quot; Then
UpdInvQty (&quot;Left Front Inner Bearing&quot
End If
End Sub

 

[JoeMiller]

Try this instead.. your not passing the correct control name to the function:

Private Sub Left_Front_Inner_Bearing_AfterUpdate()
If Me.Left_Front_Inner_Bearing = &quot;Changed&quot; Then
UpdInvQty (&quot;Left_Front_Inner_Bearing&quot
End If
End Sub Joe Miller
joe.miller@flotech.net

 

[bjarvis]

It is still giving me an invalid use of me keyword. Here is my function code.
Function UpdInvQty(ControlName As String)
Dim Response

Response = MsgBox(&quot;Update inventory quantity?&quot;, vbYesNo, &quot;Question?&quot
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;Update Inventory Set Inventory.Quantity = [Quantity]-1&quot; _
& &quot;Where (((Inventory.PartNumber)=&quot; & Me(ControlName) & &quot)&quot;
DoCmd.SetWarnings True
End If
End Function

 

[JoeMiller]

OK, I was missing a space in there!! I'm so sorry.. here's the updated function:

Function UpdInvQty(ControlName As String)
Dim Response

Response = MsgBox(&quot;Update inventory quantity?&quot;, vbYesNo, &quot;Question?&quot
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;Update Inventory Set Inventory.Quantity = [Quantity]-1&quot; _
& &quot; Where (((Inventory.PartNumber)=&quot; & Me(ControlName) & &quot)&quot;
DoCmd.SetWarnings True
End If
End Function

Joe Miller
joe.miller@flotech.net

 

[bjarvis]

I'm still getting the same error message. Here's my function code, it looks the same as the one before.
Function UpdInvQty(ControlName As String)
Dim Response

Response = MsgBox(&quot;Update inventory quantity?&quot;, vbYesNo, &quot;Question?&quot
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;Update Inventory Set Inventory.Quantity = [Quantity]-1&quot; _
& &quot; Where (((Inventory.PartNumber)=&quot; & Me(ControlName) & &quot)&quot;
DoCmd.SetWarnings True
End If
End Function

 

[JoeMiller]

That code works, I tested it myself as if I were you using the same kinda setup. SO is there any chance you can send me a copy of the db so I can see where we're going wrong?

Joe Miller
joe.miller@flotech.net