Update Second Table from Form

[Guest]

Hello,

I would like to update my Vendor table when I add a product in the product
form(based on product table). Is this possible?

For example: when I enter a new number (not already in the vendor table, I
get an error message stating that "You cannot add or change a record because
a related record is required in table "Vendors"". It would be nice if it
asked me if I want to add a new record.

???

Thanks,

Stephanie

 

[Guest]

You must have a relationship set up between your product and vendor tables
that is enforcing referential integrity i.e. every vendor in the product
table must exist in the vendor table. What you need to do is to update your
vendor table before Access tries to add the product record. You can put some
code in the BEFORE UPDATE event to query the vendor table and if the vendor
does not exist to insert it into the vendor table.

-Dorian

 

[BruceM]

You control what happens with Access, because you are creating a program.
If it would be nice to have Access do anything, you as the developer are the
one who can make it act "nicely". You could use a combo box Not In List
event to open a form bound to the Vendor table so that you can enter a new
vendor record, or to open a friendly message box asking if you want to
create a new vendor record, and either cancel or open the Vendor form from
there. You could probably devise code to append a new record to the vendor
table (although doing so is somewhat outside the range of my experience),
but since a vendor record typically consists of a number of fields you still
need to find a way to get vendor information into that table. Opening a
form in order to add a new record makes the most sense. After adding the
new record you can requery the combo box so that the new vendor appears in
the drop-down list.
All of this assumes the relationship between the Product table and the
Vendor table, as already mentioned in another response.
I assume from your description that you are typing the VendorID number into
a text box, which is an awkward way to go about that. A better choice may
be to have a combo box bound to VendorID, but displaying the VendorName. It
is much easier to select the name than to remember or look up the number.
The combo box wizard can help with that, or if you would like to find your
own way along that route, ask away, but I won't go any further with the
explanation unless I know you're interested.

 

[BruceM]

Opening the vendor form (you could open it to a new record to make things
easier) is almost certainly the best way in any case, whether you use a
command button or the combo box Not In List event or some other event. You
may need to requery the combo box on the Product form after adding a new
vendor, so that the new vendor appears on the list.