Using a form to build a record in another table

[Chip]

Howdy!!

I am some-what new to Access and VB but I have been given a task started by
fellow employee (we will call Bob) that left our company abruptly for a
better job. Anyways to my problem:

I have Purchase Order (PO) table that is basically a table of a product#,
the Quantity Ordered and the Quantity Received for material that we are
ordering from a vendor.

Now the tricky part, Bob told me to build a "form" to inpout the PO
information ( this has been with already) then to build another "form" to
access this PO information when the material is received from the vendor and
a field to put in the Quantity Received then use a "Button" on the form to
add a record to our Inventory table (that has fields of Product# and
"Quantity-onhand") of the Product and the Quantity Received AND it will have
to see if there is already a record in the Inventory table then this button
should just add the quantity received to the existing "Quantity on-hand"
field.

I currently know no way to make a button to look at another table to see if
there is an existing record to add the quantity to it and sure do not know
how to make the button "ADD" a record to this other table (Inventory table)
if one is not already there.

This may be easy for someone with more experience with ACCESS but this is
really advanced for me.

Any help or guidance on this matter would be greatly, greatly appreciated as
our management has made Bob mad now and he is not offering any more offsite
assistance to me for this matter.

AS ALWAYS, THANKS IN ADVANCE!!!
Chip

 

[Duncan Bachen]

Howdy!!

I am some-what new to Access and VB but I have been given a task started by
fellow employee (we will call Bob) that left our company abruptly for a
better job. Anyways to my problem:

I have Purchase Order (PO) table that is basically a table of a product#,
the Quantity Ordered and the Quantity Received for material that we are
ordering from a vendor.

Now the tricky part, Bob told me to build a "form" to inpout the PO
information ( this has been with already) then to build another "form" to
access this PO information when the material is received from the vendor and
a field to put in the Quantity Received then use a "Button" on the form to
add a record to our Inventory table (that has fields of Product# and
"Quantity-onhand") of the Product and the Quantity Received AND it will have
to see if there is already a record in the Inventory table then this button
should just add the quantity received to the existing "Quantity on-hand"
field.

I currently know no way to make a button to look at another table to see if
there is an existing record to add the quantity to it and sure do not know
how to make the button "ADD" a record to this other table (Inventory table)
if one is not already there.

This may be easy for someone with more experience with ACCESS but this is
really advanced for me.

Any help or guidance on this matter would be greatly, greatly appreciated as
our management has made Bob mad now and he is not offering any more offsite
assistance to me for this matter.

AS ALWAYS, THANKS IN ADVANCE!!!
Chip

You've got yourself a bit of a mess on your hands, if you'll excuse me
for being honest. This particular problem is relatively easy to deal
with, but you've got long term problems if someone in your company isn't
going to learn Access. You can't solve it just by using wizards.

I mean, I can give you the concepts here that you'll need to do, but
we're going to write every line of code for you. If something's not
working right, we'll expect you to post the code you already have.

Ok, so here it is:
1) Buy yourself a Getting Started with Access type book.
2) Create a command button without using the wizard (because you're
going to tell it what to do.)
3) Edit the click event of the command button. The button should use the
Dlookup function to check if the item being received is on the inventory
table. If it is, execute a Update SQL statement (docmd.runsql) against
the Inventory table which increases the on-hand by the qty received. If
it's not, execute an Append SQL statement which adds the product and
sets the qty equal to the qty received.

Is your receiving form bound to the products table? Meaning, can you
only receive products which are already in the system? This would be a
good thing as you can use the ProductID in your DLookup and SQL statments.