Updating a table in a Form

[Tubal]

Hi all,

I'm fairly new to Access. I'm using 2003. Here is my situation:

I have 2 tables: public_po, and local_po.

public_po is an ODBC source from our backend system that is not editable by
me (nor do I want it to be). It has a unique id field "order_no" and several
other fields that aren't important right now.

local_po is a local mdb table I created, with fields I want to be able to
input like "ship date", "confirm date", etc. It also has a unique id field
"order_no" that I have linked to public_po in relationships with a RIGHT JOIN.
This table is currently empty.

What I want to be able to do is pull up the data from public_po, and in the
same form, pull up and edit the data in the local_po table.

I've been able to pull up the data I want from the public_po, and also the
empty fields from the local_po in both a query and a form. The only problem
I'm having is that when I enter data into these local_po fields, since the
"order_no" field in the local_po is empty, it tries to update the public_po
"order_no" field to be whatever is in the local_po "order_no" field, and
errors out on permissions.

What I want to happen is that when I enter data into my form in one of the
local_po fields, it will update my local_po table with the order_no from my
public_po table, and save the data that I've entered into the local_po table.

Any help would be appreciated.

Thanks.

 

[Carl Rapson]

Hi all,

I'm fairly new to Access. I'm using 2003. Here is my situation:

I have 2 tables: public_po, and local_po.

public_po is an ODBC source from our backend system that is not editable
by
me (nor do I want it to be). It has a unique id field "order_no" and
several
other fields that aren't important right now.

local_po is a local mdb table I created, with fields I want to be able to
input like "ship date", "confirm date", etc. It also has a unique id
field
"order_no" that I have linked to public_po in relationships with a RIGHT
JOIN.
This table is currently empty.

What I want to be able to do is pull up the data from public_po, and in
the
same form, pull up and edit the data in the local_po table.

I've been able to pull up the data I want from the public_po, and also the
empty fields from the local_po in both a query and a form. The only
problem
I'm having is that when I enter data into these local_po fields, since the
"order_no" field in the local_po is empty, it tries to update the
public_po
"order_no" field to be whatever is in the local_po "order_no" field, and
errors out on permissions.

What I want to happen is that when I enter data into my form in one of the
local_po fields, it will update my local_po table with the order_no from
my
public_po table, and save the data that I've entered into the local_po
table.

Any help would be appreciated.

Thanks.

Have you tried setting up your main form bound to the public_po table, and
add your local_po table as a subform on this form? Set the link field to be
"order_no", and you shouldn't have any problem adding records to local_po
and the order_no should carry over.

Carl Rapson

 

[Tubal]

Hi all,

[quoted text clipped - 37 lines]

Thanks.

Have you tried setting up your main form bound to the public_po table, and
add your local_po table as a subform on this form? Set the link field to be
"order_no", and you shouldn't have any problem adding records to local_po
and the order_no should carry over.

Carl Rapson

I have tried that, but when I create the subform, it says I can't have a
"Continuous" form with a subform. I would like it to list out all of the
records in a spreadsheet type layout if possible. I also get the same
problem with it trying to update the public_po table even if I am adding data
in the subform.

 

[John W. Vinson]

What I want to happen is that when I enter data into my form in one of the
local_po fields, it will update my local_po table with the order_no from my
public_po table, and save the data that I've entered into the local_po table.

Sounds like you might do well to use an unbound Form with two Subforms, one on
each table. You could run an Append query to append one record from the linked
table into the local one in the afterupdate event of the local_po field.

John W. Vinson [MVP]