This is hard to explain

[Bobby.Dannels]

I have a form "Input" that is linked to a table "Data Table". On the
form is a command button "IssuePart". When I click this button I want
it to subtract the number in the text box "IssueParts" from the O/H
field of the current record. There is another Table "QtyUsed". I want
a new record made in that table. I want the fields "NSN"
"Nomenclature" and "LOC" copied from the current record, and I want
the number in "IssueParts" to be put in the field "Q/U" and I want the
current date and time to be added to the "Date" field. Can someone
please help me with the code for this.

 

[Allen Browne]

Bobby, there's quite a bit more to handling quantity-on-hand than just
subtracting the quantity from a field in that record.

For a discussion of some of the issues and a suggested solution, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

If you want something more than that, I suggest you buy 'Building Ms Access
Applications' by John L Viscas (Microsoft Press, 2005.) The book comes with
several applications on the CD, one of which is for inventory management.
Effectively you get a free database for just the cost of the documentation.

(I do not benefit from John's sales.)

 

[John W. Vinson]

I have a form "Input" that is linked to a table "Data Table". On the
form is a command button "IssuePart". When I click this button I want
it to subtract the number in the text box "IssueParts" from the O/H
field of the current record. There is another Table "QtyUsed". I want
a new record made in that table. I want the fields "NSN"
"Nomenclature" and "LOC" copied from the current record, and I want
the number in "IssueParts" to be put in the field "Q/U" and I want the
current date and time to be added to the "Date" field. Can someone
please help me with the code for this.

Private Sub IssuePart_Click()
Dim strSQL As String
Dim db As DAO.Database
On Error GoTo Proc_Error
If IsNull (Me!Issueparts) Then
MsgBox "No parts issued! Cannot proceed."
Else
' update the control bound to [O/H], I'll assume it's named txtOnHand
Me!txtOnHand = Me!txtOnHand - Me!IssueParts
strSQL = "INSERT INTO QtyUsed (NSN, Nomenclature, LOC, [Q/U], [Date])" _
& " VALUES ('" & Me!NSN & "','" & Me!Nomenclature & "','" & Me!LOC & "'," _
& Me!IssueParts & ", #" & Now() & "#);"
db.Execute strSQL
End If

Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in IssuePart_Click:" & vbCrLf & _
Err.Description
Resume Proc_Exit
End Sub

Some suggestions:

Don't use / in fieldnames (or any other special characters).
Don't use Date as a fieldname, it's a reserved word.
Consider carefully whether you in fact want to store calculated fields such as
the quantity on hand in your table, or whether you want to store data
redundantly in two tables. If NSN is a part number and Nomenclature is a part
description, only the NSN should be in the QtyUsed table, for instance.

John W. Vinson [MVP]

 

[Bobby.Dannels]

Thank you for the code John, it is recieving an error on "dim db as
DAO.Database". Could this be because I am using an older version of
Access? If so, is there a way to convert it to work in Access 2000?

 

[Bobby.Dannels]

Cancel that last reply, I figured it out. Now it says "Error 91 in
IssuePart_Click: Object variable or With block variable not set"

 

[John W. Vinson]

Cancel that last reply, I figured it out. Now it says "Error 91 in
IssuePart_Click: Object variable or With block variable not set"

Please post your code.

John W. Vinson [MVP]

 

[John W. Vinson]

Cancel that last reply, I figured it out. Now it says "Error 91 in
IssuePart_Click: Object variable or With block variable not set"

Also.... heed Allen Browne's suggestion. You could be both reinventing the
wheel, and inventing it square instead of round.

John W. Vinson [MVP]

 

[Bobby.Dannels]

Also.... heed Allen Browne's suggestion. You could be both reinventing the
wheel, and inventing it square instead of round.

             John W. Vinson [MVP]

I used the code you posted ....

 

[Bobby.Dannels]

I appreciate Mr. Browne's input, unfortunately I neither understand
what he is talking about nor do I feel that the database I am creating
needs the be that complex. I am a beginer at Access, and know very
little about VBA. I am using this database to track parts. I do not
sell these parts, they are commonly used parts that my company uses. I
am trying to make a database to track how many we have so that when we
start to run low I can order more, and so It makes it easy to locate
the parts that are needed quickly and effectively.

 

[John W. Vinson]

Cancel that last reply, I figured it out. Now it says "Error 91 in
IssuePart_Click: Object variable or With block variable not set"

Sorry: did overlook one line. Should be

Private Sub IssuePart_Click()
Dim strSQL As String
Dim db As DAO.Database
On Error GoTo Proc_Error
Set db = CurrentDb ' <<<< add this line
If IsNull (Me!Issueparts) Then
MsgBox "No parts issued! Cannot proceed."
Else
' update the control bound to [O/H], I'll assume it's named txtOnHand
Me!txtOnHand = Me!txtOnHand - Me!IssueParts
strSQL = "INSERT INTO QtyUsed (NSN, Nomenclature, LOC, [Q/U], [Date])" _
& " VALUES ('" & Me!NSN & "','" & Me!Nomenclature & "','" & Me!LOC & "'," _
& Me!IssueParts & ", #" & Now() & "#);"
db.Execute strSQL
End If

Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in IssuePart_Click:" & vbCrLf & _
Err.Description
Resume Proc_Exit
End Sub

John W. Vinson [MVP]

 

[Bobby.Dannels]

Thanks for the code John, works great!

 

[Bob Quintal]

(e-mail address removed) wrote in
(e-mail address removed)
m:

I appreciate Mr. Browne's input, unfortunately I neither
understand what he is talking about nor do I feel that the
database I am creating needs the be that complex. I am a beginer
at Access, and know very little about VBA. I am using this
database to track parts. I do not sell these parts, they are
commonly used parts that my company uses. I am trying to make a
database to track how many we have so that when we start to run
low I can order more, and so It makes it easy to locate the parts
that are needed quickly and effectively.

You are a beginner at Access, which is why you "don't feel that the

database I am creating needs the be that complex."

Take the time to reread Allen's comments and understand a little.
Otherwise you'll always be a beginner.

My company's database contains 45,000 diffreent part numbers that we
use. when we need to reorder is based on the formula
Dsum("qty_rcvd","Receipts","Item = '" & me.txtItem & "'")
- Dsum("qty_used","Disbursements","Item = '" & me.txtItem & "'")

When we receive new orders, all I have to do is enter the item
number and the quantity received.
Access does all the calculations for me.

Your way involves you finding the existing part number record, then
you adding the quantity received to the existing quantity received.
You do all the calculations.

You are human, and you certainly will err entering stock quantities.
Access doesn't make clerical mistakes adding and subtracting.