Update Second Table from Form

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

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

"steph44haf" wrote:

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

Yep - I have the relationship set up with enforced referential integrity.

But how do I query the vendor table and insert it? I am not sure how begin
the code.

"mscertified" wrote:

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

"steph44haf" wrote:

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

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.

"steph44haf" wrote in message
news
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

This was helpful.

For now I added a command box that opens the Vendor form so that you can add
a new vendor if needed.

Thanks,

"BruceM" wrote:

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.

"steph44haf" wrote in message
news
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

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.

"steph44haf" wrote in message
...
This was helpful.

For now I added a command box that opens the Vendor form so that you can
add
a new vendor if needed.

Thanks,

"BruceM" wrote:

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.

"steph44haf" wrote in message
news
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