Table 1 is for recording credits issued against an invoice. There may be
multiple credits for a given client.
Table 2 is for recording the use of those credits issued.
Table 1:
ID, autonumber, no dupes
ClientID, text, dupes ok
InvNum, number, dupes ok
Date
Amount
Table 2:
ID, autonumber, no dupes
InvNum, text, dupes ok
Date
Amount
When an invoice is created, the user sees the credit balance for the client.
They enter the amount they desire of the net balance - all credits (table
1) less all applications (table 2) to be consumed on the current invoice.
I am entering and applying credits on other forms just fine. However, I
want the user to see a summary of the client's credit and application
activity, credit-specific (table 1), details including the applications made
against that credit (table 2) and on the same form.
Subforms are out, because this summary form must be continuous.
I can successfully build the form for that summary, however -- the user
cannot edit any fields since the query is not updateable.
I am sure I can work around with a maintenance or utility off-form, but I
would really like to make it simpler than that.
So, back to the issue -- why won't these tables allow for an updateable
query when linked on InvNum?
Thanks.
--
Skunk
Jeff Boyce said:
Access needs to know which table.
If you'll describe a bit more about your data structure and why you have two
tables for the data...
If I recall, you mentioned that the [InvNum] could be a duplicate. Not only
can't Access tell which table, it can't even determine which row!
Maybe you need to be providing the underlying (unique) RowID information, so
Access knows where to stick it...
Regards
Jeff Boyce
Microsoft Office/Access MVP
Skunk said:
I wish to use this query as the recordsource for a form for my user to
upate
details that might come from either or both tables. InvNum would never be
updated.
Thanks.
--
Skunk
Jeff Boyce said:
Are you saying that you have two tables with [InvNum] fields, and that
your
query joins them?
Which table did you want Access to update?
Regards
Jeff Boyce
Microsoft Office/Access MVP
What am I missing? I have reviewed Allan Brownes probable causes to no
avail.
I have two tables, each with Primary key "ID", autonumber and
duplicates
not
allowed.
Each table has "InvNum" with duplicates allowed.
I am attempting to join the tables in a query on InvNum. I cannot
update
the query.
In an attempt to resolve, I have rebuilt the tables from scratch,
tested
them individually (with success) with other tables containing InvNum --
but I
cannot get past this.
Please help me with my lack of training and experience.
Thanks.
