append query not working

[Kathy R]

I apologize if this question has been asked at least a
million times on this forum; I haven't found an efficient
way to search (or rather to view) the postings.
I have a Purchase Order form that includes about 8
matching fields that are also in the Participants subform.
I want to enter the data for these fields in the Purchase
Order form and then run an append query to add the record
to the Particpants table, but it's not working. It gives
no error msg -- looks like it should work, but nothing
gets appended. I've included the SQL statements from under
the hood of my query design grid; my criteria is [Forms]!
[Purchase Order]![PurchaseID]. Thanks SO much in advance.

INSERT INTO Participants ( PurchaseID, FirstName,
LastName, DayPhone, EvePhone, Address, City, State, Zip,
Country, [HowDidYouFindUs?] )
SELECT [Purchase Order].PurchaseID, [Purchase
Order].FirstName, [Purchase Order].LastName, [Purchase
Order].DayPhone, [Purchase Order].EvePhone, [Purchase
Order].Address, [Purchase Order].City, [Purchase
Order].State, [Purchase Order].Zip, [Purchase
Order].Country, [Purchase Order].[HowDidYouFindUs?]
FROM [Purchase Order]
WHERE ((([Purchase Order].PurchaseID)=[Forms]![Purchase
Order]![PurchaseID]));

 

[Steve Schapel]

Kathy,

I can think of a couple of reasons why this doesn't work...
1. The newly entered Purchase Order data hasn't been saved yet as a
record before you try to use it as the basis of an Append
2. The Participants table has some other field where entry is
Required, or there is some Validation which is not satisfied.

However, I can think of an even more important reason why I would not
want to encourage you to make this Append Query work. Please
reconsider your database design! What you are doing has not been
asked a million times before, because it is essentially an invalid
thing to be doing in the first place. Your tables should be set up so
that this information is only stored once, in one place, in your
database. It is not really clear what is the relationship between
Participants and Purchases, but assuming it is possible for any given
Participant to have more than one Purchase Order, then it is correct
that you have separate tables for Participants and Purchases, but in
this case the Purchases table should only contain a ParticipantID and
no other participant-related data. This is the whole point of using a
database. Sorry, I hope this doesn't come across as too heavy, but I
absolutely guarantee you will save yourself a large amount of work,
complication, confusion, error, and headaches, if you correct this
problem.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Kathy,

I obviously don't know the full picture. For example, you mention "an
event" but I would imagine there may eventually be more than one
event?

Anyway, on the basis of what you have said, here's a start...

Table: People
PersonID
FirstName
LastName
Address
Phone
PurchaseOrderNumber
etc

Table: Purchase
PurchaseOrderNumber
PurchaserPersonID
PurchaseDate
etc

In this scenario, an entry in the PurchaseOrderNumber field in the
People table will "flag" the person as a Participant, i.e. this field
will be left blank in the case of people who are non-participating
purchasers, or in the database for some other reason (?organisers).
And the PersonID of the purchaser is used to identify the purchaser in
the Purchase table.

- Steve Schapel, Microsoft Access MVP

Hi Steve,

You've helped me before and I'm so grateful once again.
And I'm not at all offended by your critique of my db --
I'm here to learn for pete's sake! Btw, I basically
inherited this existing db, but it's my job now to do a
major overhaul. Here's the deal... This db is for an event
that both individuals and families will be registering
for. The Participants and Purchase Order tables were set
up this way on the basis that one purchaser could purchase
many "tickets", such as a family scenario - parents
purchasing for kids, etc. So the relationship
(potentially) is one purchase order to many participants.
The Purchase Order form contains only a few fields that
are also common to the Participants table (first, last
name, address, phone), and since in most cases the
purchaser and the participant will be one and the same, it
was set up so that after entering these first few fields
in the Purch Order form, you could click a button (run a
query) to add the purchaser as a participant, to save
entering that data twice.. I hope that makes sense. i
understand the basic and critical db design rule of having
data stored in only one place, but how would you suggest
getting around this particular situation? Say you have a
parent who purchases two tickets for his/her kids but
doesn't participate him/herself... so these fields are
actually different but will most often contain the same
data. Looking forward to your expert assistance!

kr

Kathy

-----Original Message-----
Kathy,

I can think of a couple of reasons why this doesn't work...
1. The newly entered Purchase Order data hasn't been saved yet as a
record before you try to use it as the basis of an Append
2. The Participants table has some other field where entry is
Required, or there is some Validation which is not satisfied.

However, I can think of an even more important reason why I would not
want to encourage you to make this Append Query work. Please
reconsider your database design! What you are doing has not been
asked a million times before, because it is essentially an invalid
thing to be doing in the first place. Your tables should be set up so
that this information is only stored once, in one place, in your
database. It is not really clear what is the relationship between
Participants and Purchases, but assuming it is possible for any given
Participant to have more than one Purchase Order, then it is correct
that you have separate tables for Participants and Purchases, but in
this case the Purchases table should only contain a ParticipantID and
no other participant-related data. This is the whole point of using a
database. Sorry, I hope this doesn't come across as too heavy, but I
absolutely guarantee you will save yourself a large amount of work,
complication, confusion, error, and headaches, if you correct this
problem.

- Steve Schapel, Microsoft Access MVP

I apologize if this question has been asked at least a
million times on this forum; I haven't found an efficient
way to search (or rather to view) the postings.
I have a Purchase Order form that includes about 8
matching fields that are also in the Participants subform.
I want to enter the data for these fields in the Purchase
Order form and then run an append query to add the record
to the Particpants table, but it's not working. It gives
no error msg -- looks like it should work, but nothing
gets appended. I've included the SQL statements from under
the hood of my query design grid; my criteria is [Forms]!
[Purchase Order]![PurchaseID]. Thanks SO much in advance.

INSERT INTO Participants ( PurchaseID, FirstName,
LastName, DayPhone, EvePhone, Address, City, State, Zip,
Country, [HowDidYouFindUs?] )
SELECT [Purchase Order].PurchaseID, [Purchase
Order].FirstName, [Purchase Order].LastName, [Purchase
Order].DayPhone, [Purchase Order].EvePhone, [Purchase
Order].Address, [Purchase Order].City, [Purchase
Order].State, [Purchase Order].Zip, [Purchase
Order].Country, [Purchase Order].[HowDidYouFindUs?]
FROM [Purchase Order]
WHERE ((([Purchase Order].PurchaseID)=[Forms]![Purchase
Order]![PurchaseID]));

.

 

[Steve Schapel]

Kathy,

Using the basic outline of tables as I suggested before, I would do it
like this...

1. For clarity, rename the PurchaseOrderNumber field in the People
table to PeoplePON
2. Make a query including both tables, with a Left Join from Purchase
to People, on the PurchaseOrderNumber/PeoplePON field
3. Make a form based on this query, and use it to enter the
Purchase-related data, and the personal details of a non-participating
purchaser.
4. You will need some simple code on the AfterUpdate event of, for
example, the Surname textbox, such as...
If IsNull(Me.Surname) Then
Me.PeoplePON = Null
Else
Me.PeoplePON = Me.PurchaseOrderNumber
End If
5. Make another form, continuous view, based on the People table
6. Place this form on the other form as a subform, with
LinkChildFields and LinkMasterFields properties set to PersonID, and
use this to enter details of participants related to the current
purchase order.

Sweet as pie!

- Steve Schapel, Microsoft Access MVP