Problem adding new record to form

[Bob 4]

I have a continous form where the record source is:

Receipts (n..1) Taxpayers (1..n) Taxpayer Addresses (select only current
address)

It will not allow me to enter a new record to Receipts.

If I remove the Taxpayer Addresses table then I can enter a new record in
Receipts.

I've tried creating a query of Taxpayers and Taxpayer Addresses and then
joining the query to the Receipts table with no luck.

Any pointers?

Thanks, bob

 

[Allen Browne]

So your continuous form is based on a query with 3 tables, and the query
does not allow you to add new records to the Receipts table. The form will
need to be redesigned.

Presumably the Receipts table has a TaxpayerAddressID field where you select
one of the addresses for the taxpayer, to record where that receipt was sent
to? You might interface that a couple of ways.

One way would be to have a main form for the Taxpayer, and 2 subforms (one
for receipts; one for addresses.) You could use a combo box for the
taxpayerAddressID field. You would then use the events of the forms to load
just the relevant addresses into the combo: the Current event of the main
form, AfterInsert and AfterDelConfirm of the Addresses subform.

Alternatively, if you could place the 2 subforms side by side, it might make
sense to place a little button between then. The button assigns the current
record from the Addresses subform as the address to use in the current
record of the Receipts subform.

 

[Allen Browne]

If it's purely for display purposes, you might be able to put the current
address on the main form, or use a calculated control in the subform.
(Perhaps in the Form Footer of the subform.)

The text box would be bound to an expression such as this:
=DLookup("[Address] & "" "" & [City] & "" "" & [State] & "" "" & [Zip]",
[Taxpayer Addresses],
"([TaxpayerID] = " & Nz([TaxpayerID],0) & ") AND (IsCurrent = True)")

That's one line, and use your own field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Alan. Thanks for the very quick response.

These tables are all joined on TaxpayerID.
LST Distributions (n..1) Taxpayers (1..n) Taxpayer Addresses (current
address = Yes)

I don't need to associate Taxpayer address to the receipt. I only need to
show the current address to the user after she selects the Taxpayer's SSN
on
the Receipts continuous form so she knows that she is working with the
right
John Doe.

This two table query does work, but it doesn't allow me to display the
taxpayer's current address:

SELECT [LST Distributions].*, Taxpayers.SSN, Taxpayers.Exempt, [Last Name]
&
", " & [First Name] AS FullName
FROM Taxpayers INNER JOIN [LST Distributions] ON Taxpayers.TaxpayerID =
[LST
Distributions].TaxpayerID;

When I add the Taxpayer Addresses table (which allows me to keep track of
prior addresses) then the form won't let me add a new record. Here is the
3
table query.

SELECT [LST Distributions].*, Taxpayers.SSN, Taxpayers.Exempt, [Last Name]
&
", " & [First Name] AS FullName, [Taxpayer Addresses].Current, [Taxpayer
Addresses].[Street Address 1], [Taxpayer Addresses].City, [Taxpayer
Addresses].State, [Taxpayer Addresses].[Zip Code]
FROM (Taxpayers INNER JOIN [LST Distributions] ON Taxpayers.TaxpayerID =
[LST Distributions].TaxpayerID) INNER JOIN [Taxpayer Addresses] ON
Taxpayers.TaxpayerID = [Taxpayer Addresses].TaxpayerID
WHERE ((([Taxpayer Addresses].Current)=Yes));

Much appreciated. bob

So your continuous form is based on a query with 3 tables, and the query
does not allow you to add new records to the Receipts table. The form
will
need to be redesigned.

Presumably the Receipts table has a TaxpayerAddressID field where you
select
one of the addresses for the taxpayer, to record where that receipt was
sent
to? You might interface that a couple of ways.

One way would be to have a main form for the Taxpayer, and 2 subforms
(one
for receipts; one for addresses.) You could use a combo box for the
taxpayerAddressID field. You would then use the events of the forms to
load
just the relevant addresses into the combo: the Current event of the main
form, AfterInsert and AfterDelConfirm of the Addresses subform.

Alternatively, if you could place the 2 subforms side by side, it might
make
sense to place a little button between then. The button assigns the
current
record from the Addresses subform as the address to use in the current
record of the Receipts subform.