By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,980 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Query Problem.. udating base file and unable to update it

P: n/a
Hi:

I have two tables, a setup table (TblSetup) and a purchase order table (tblPO).

When i construct a query with ONLY the tblPO shown, and a type in parameter for the PO
number, I an update the original tblPO. However, when I introduce the setup file into the
query I cannot update the result. THis is true if I make a link to Setup or not. In fact,
setup contains the client ID, which i wish to test against the client ID in the PO record,
and only access those POs that match. Then I want to be able to update the.

I am obviously doing something wrong but don't know what it is.

HELP!!

John Baker
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
TC
Each table must have a primary key, and the tables must be joined using an
INNER/OUTER/LEFT/RIGHT JOIN construct, not just by putting their names
togetherwith a comma (TblSetup, tblPO).

So you will not really get an answer unless you tell us what is the primary
key of each table, & what SQL have you written to join them.

HTH,
TC
"John Baker" <Ba******@Verizon.net> wrote in message
news:og********************************@4ax.com...
Hi:

I have two tables, a setup table (TblSetup) and a purchase order table (tblPO).
When i construct a query with ONLY the tblPO shown, and a type in parameter for the PO number, I an update the original tblPO. However, when I introduce the setup file into the query I cannot update the result. THis is true if I make a link to Setup or not. In fact, setup contains the client ID, which i wish to test against the client ID in the PO record, and only access those POs that match. Then I want to be able to update the.
I am obviously doing something wrong but don't know what it is.

HELP!!

John Baker

Nov 12 '05 #2

P: n/a
TC:

These are joined using a "find" construct. The total original table appears in the query,
and the setup table now appreas in the query window, not linked ( I am using Access Query
tabes...not raw SQL), and under the appropriate field I have put the condition
([tblSetup]![Setup PO ID]). By removing ALL linkage to the setup table (leaving it free
floating) I have reached the point where the query works fine, and I can update in
DATASHEET mode from the query. However, when I construct a form using the query results I
am still unable to use it to update.
regards

John Baker

"TC" <a@b.c.d> wrote:
Each table must have a primary key, and the tables must be joined using an
INNER/OUTER/LEFT/RIGHT JOIN construct, not just by putting their names
togetherwith a comma (TblSetup, tblPO).

So you will not really get an answer unless you tell us what is the primary
key of each table, & what SQL have you written to join them.

HTH,
TC
"John Baker" <Ba******@Verizon.net> wrote in message
news:og********************************@4ax.com.. .
Hi:

I have two tables, a setup table (TblSetup) and a purchase order table

(tblPO).

When i construct a query with ONLY the tblPO shown, and a type in

parameter for the PO
number, I an update the original tblPO. However, when I introduce the

setup file into the
query I cannot update the result. THis is true if I make a link to Setup

or not. In fact,
setup contains the client ID, which i wish to test against the client ID

in the PO record,
and only access those POs that match. Then I want to be able to update

the.

I am obviously doing something wrong but don't know what it is.

HELP!!

John Baker


Nov 12 '05 #3

P: n/a
So you are saying that the query is updatable, but a form that is based on
the >exact same query< is not updatable?

Check the AllowAdditions, AllowEdits & Allow Deletions properties of the
form. These must be true if you want the form to be able to add, edit or
delete records, respectively. (So for example, to edit records, the query
must be updatable >and< the form's allowedits property must be true.)

HTH,
TC
"John Baker" <Ba******@Verizon.net> wrote in message
news:fe********************************@4ax.com...
TC:

These are joined using a "find" construct. The total original table appears in the query, and the setup table now appreas in the query window, not linked ( I am using Access Query tabes...not raw SQL), and under the appropriate field I have put the condition ([tblSetup]![Setup PO ID]). By removing ALL linkage to the setup table (leaving it free floating) I have reached the point where the query works fine, and I can update in DATASHEET mode from the query. However, when I construct a form using the query results I am still unable to use it to update.
regards

John Baker

"TC" <a@b.c.d> wrote:
Each table must have a primary key, and the tables must be joined using anINNER/OUTER/LEFT/RIGHT JOIN construct, not just by putting their names
togetherwith a comma (TblSetup, tblPO).

So you will not really get an answer unless you tell us what is the primarykey of each table, & what SQL have you written to join them.

HTH,
TC
"John Baker" <Ba******@Verizon.net> wrote in message
news:og********************************@4ax.com.. .
Hi:

I have two tables, a setup table (TblSetup) and a purchase order table

(tblPO).

When i construct a query with ONLY the tblPO shown, and a type in

parameter for the PO
number, I an update the original tblPO. However, when I introduce the

setup file into the
query I cannot update the result. THis is true if I make a link to
Setupor not. In fact,
setup contains the client ID, which i wish to test against the client
IDin the PO record,
and only access those POs that match. Then I want to be able to update

the.

I am obviously doing something wrong but don't know what it is.

HELP!!

John Baker

Nov 12 '05 #4

P: n/a
John,

I don't know if this is along the lines you are looking for but here
goes.

I have a database which imports a list of customers and their
salesreps.

Unfortunately the salesreps in Excel do not match the tblsalesreps in
the database (usually typos!). What I do is firstly run a query which
selects only the names from the spreadsheet which don't match the
names in tblsalesreps. I then base a totals query on this first query
with just one field salesrepname and select Group By. This gives me a
small recordset of the incorrect names.

I have a form whose datasource is based on the totals query. On the
form I have a textbox containing the incorrect name and an unbound
combobox which lists all of the salesreps names from tblsalesreps.

The user selects the correct spelling/name from the combobox to match
the incorrect name then presses a command button which runs an update
query. The update query changes the incorrect name to the correct
one. Once the update query has run the form datasource is requiried
and the process repeated until there are no further incorrect names.

Hopefully this is similar to your scenario. If you need to see this
in action I can email it to you, but not till Monday. Please don't
use my email address as it is constantly spammed and I only use it to
log onto Google!

David Mitchell
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.