Access 2000 Append Query

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I designed an append query that takes data from a loaded
form. The query than appends the data to the designated
table multiple times. (11 times). I'm lost with this
issue. Any help or advice is greatly appreciated.
 
Frank,

Do you mean the query uses the value in a control(s) on the form as
criteria? Do you mean the append happens 11 times and it is only
supposed to happen once and you don't know how to stop it? Or do you
mean it is supposed to happen 11 times but it doesn't? Whatever is
happening differently from what you want?

- Steve Schapel, Microsoft Access MVP
 
Steve,
Thanks for the reply. I only one record to get
recorded in the table. I now get 11 and don't know where
to stop that.
I have a form in the database where data is entered
or selected. I added a button to append the information to
the table. I get the records, I just get them a multiple
of 11 times. I've searched the properties to see if it
calls multiple occurances. Any advice is greatly
appreciated.
 
Frank,

Can you go to the design view of the Append Query, go to the View
menu, select SQL, and then copy/paste the SQL into a message to post
back here. This might help us to see what's happening.

- Steve Schapel, Microsoft Access MVP
 
Thanks Steve. I really appreciate your time and interest.


INSERT INTO PipeData ( ProjNum, PipeLineNumber, [Size],
Length, [Date] )
SELECT [Forms]![PipeEntry]![Project Number] AS Expr5,
[Forms]![PipeEntry]![Text10].[Value] AS Expr1, [Forms]!
[PipeEntry]![Combo12].[Value] AS Expr2, [Forms]!
[PipeEntry]![Text3].[Value] AS Expr3, [Forms]![PipeEntry]!
[Text5] AS Expr4
FROM PipeData;
 
Thanks Steve. I really appreciate your time and interest.


INSERT INTO PipeData ( ProjNum, PipeLineNumber, [Size],
Length, [Date] )
SELECT [Forms]![PipeEntry]![Project Number] AS Expr5,
[Forms]![PipeEntry]![Text10].[Value] AS Expr1, [Forms]!
[PipeEntry]![Combo12].[Value] AS Expr2, [Forms]!
[PipeEntry]![Text3].[Value] AS Expr3, [Forms]![PipeEntry]!
[Text5] AS Expr4
FROM PipeData;
Click to expand...

You're slecting the form references FROM PipeData - if PipeData has 11
records you'll get 11 new records; if it has a thousand, you'll get a
thousand new records!

I presume you have some good reason not to use a bound Form (which
wouldn't need any code or append queries at all); if you want to use
an append query I'd suggest creating a little one-row, one-field table
(it's called DUAL in Oracle for some reason) and change FROM PipeData
to FROM Dual so that you get only one row created. Alternatively, open
a Recordset based on PipeData and insert the new record using the
AddNew method, in code, rather than using an append query at all.
 
Try the alternative Insert Query structure. I _believe_ that your current SQL
will try to insert one record for each existing record.

Alternative looks something like:
INSERT INTO PipeData (List of Fields)
Values(List of Values)

INSERT INTO PipeData (ProjNum, PipeLineNumber, [Size],
Length, [Date] )
VALUES ( [Forms]![PipeEntry]![Project Number] ,
[Forms]![PipeEntry]![Text10].[Value] ,
[Forms]![PipeEntry]![Combo12].[Value],
[Forms]![PipeEntry]![Text3].[Value],
[Forms]![PipeEntry]![Text5])

Thanks Steve. I really appreciate your time and interest.

INSERT INTO PipeData ( ProjNum, PipeLineNumber, [Size],
Length, [Date] )
SELECT [Forms]![PipeEntry]![Project Number] AS Expr5,
[Forms]![PipeEntry]![Text10].[Value] AS Expr1, [Forms]!
[PipeEntry]![Combo12].[Value] AS Expr2, [Forms]!
[PipeEntry]![Text3].[Value] AS Expr3, [Forms]![PipeEntry]!
[Text5] AS Expr4
FROM PipeData;
-----Original Message-----
Frank,

Can you go to the design view of the Append Query, go to the View
menu, select SQL, and then copy/paste the SQL into a message to post
back here. This might help us to see what's happening.

- Steve Schapel, Microsoft Access MVP




.
Click to expand...
Click to expand...
 
John,
I'm not quite sure what you mean by "You're slecting
the form references FROM PipeData - if PipeData has 11
records you'll get 11 new records; if it has a thousand,
Click to expand...
you'll get a thousand new records!" More importantly I
need to research and understand the "Bound Form." As you
can see I'm in a continuing phase of understanding Access
and its features. I'd be happy to hear any advice you
have. Thanks for your help.

P.S. The reply from John Spencer seems to work. I'll
continue to test other approaches.
Thanks Steve. I really appreciate your time and interest.


INSERT INTO PipeData ( ProjNum, PipeLineNumber, [Size],
Length, [Date] )
SELECT [Forms]![PipeEntry]![Project Number] AS Expr5,
[Forms]![PipeEntry]![Text10].[Value] AS Expr1, [Forms]!
[PipeEntry]![Combo12].[Value] AS Expr2, [Forms]!
[PipeEntry]![Text3].[Value] AS Expr3, [Forms]! [PipeEntry]!
[Text5] AS Expr4
FROM PipeData;
Click to expand...

You're slecting the form references FROM PipeData - if PipeData has 11
records you'll get 11 new records; if it has a thousand, you'll get a
thousand new records!

I presume you have some good reason not to use a bound Form (which
wouldn't need any code or append queries at all); if you want to use
an append query I'd suggest creating a little one-row, one-field table
(it's called DUAL in Oracle for some reason) and change FROM PipeData
to FROM Dual so that you get only one row created. Alternatively, open
a Recordset based on PipeData and insert the new record using the
AddNew method, in code, rather than using an append query at all.


.
Click to expand...
 
Thanks John. This query structures appears to work.
-----Original Message-----
Try the alternative Insert Query structure. I _believe_ that your current SQL
will try to insert one record for each existing record.

Alternative looks something like:
INSERT INTO PipeData (List of Fields)
Values(List of Values)

INSERT INTO PipeData (ProjNum, PipeLineNumber, [Size],
Length, [Date] )
VALUES ( [Forms]![PipeEntry]![Project Number] ,
[Forms]![PipeEntry]![Text10].[Value] ,
[Forms]![PipeEntry]![Combo12].[Value],
[Forms]![PipeEntry]![Text3].[Value],
[Forms]![PipeEntry]![Text5])

Thanks Steve. I really appreciate your time and interest.

INSERT INTO PipeData ( ProjNum, PipeLineNumber, [Size],
Length, [Date] )
SELECT [Forms]![PipeEntry]![Project Number] AS Expr5,
[Forms]![PipeEntry]![Text10].[Value] AS Expr1, [Forms]!
[PipeEntry]![Combo12].[Value] AS Expr2, [Forms]!
[PipeEntry]![Text3].[Value] AS Expr3, [Forms]! [PipeEntry]!
[Text5] AS Expr4
FROM PipeData;
-----Original Message-----
Frank,

Can you go to the design view of the Append Query, go
Click to expand...
to
the View
menu, select SQL, and then copy/paste the SQL into a message to post
back here. This might help us to see what's happening.

- Steve Schapel, Microsoft Access MVP
Click to expand...
wrote:

Steve,
Thanks for the reply. I only one record to get
recorded in the table. I now get 11 and don't know where
to stop that.
I have a form in the database where data is entered
or selected. I added a button to append the
Click to expand...
information
to
the table. I get the records, I just get them a multiple
of 11 times. I've searched the properties to see if it
calls multiple occurances. Any advice is greatly
appreciated.

.
Click to expand...
Click to expand...
.
Click to expand...
 
John Spencer's VALUES() approach is indeed the right way to go about
this - thanks John!
John,
I'm not quite sure what you mean by "You're slecting
the form references FROM PipeData - if PipeData has 11
you'll get a thousand new records!"
Click to expand...

That's how Queries work. Take your original APPEND query and turn back
into just a simple Select query. You'll see that it's selecting every
record in the PipeData table - eleven records in this case.

If you then turn it back into an Append query, it will append just
that many records. You're telling Access to select every single record
in PipeData and do something (in this case, append data from a Form)
with it. The fact that you're not actually doing anything with the
fields in the existing PipeTable records is immaterial; you're
selecting 11 records from the table, and each one of them will be
treated as a new record by the append query.
More importantly I
need to research and understand the "Bound Form." As you
can see I'm in a continuing phase of understanding Access
and its features. I'd be happy to hear any advice you
have. Thanks for your help.
Click to expand...

Try creating a new Form using the Form Wizard, based on the PipeData
table. This Form will present you with a textbox for each field in
PipeData. You can simply type data into these fields, and that data
will be written automatically to the table. No code is needed at all.

This is called a "bound" form because the Form is "bound" to the table
- it displays the data that's in the table, and lets you update
existing records or add new records to the table.

Your technique, using an Append query, is in fact a valuable method if
there is some good reason NOT to use the default bound form method;
for instance, the Table might be stored externally in SQL/Server or
some other database, or there might be a lot of contention among users
for access to the table. But it's an *advanced technique*. You might
want to experiment with the simpler bound forms first, and decide
whether you need to go to the extra complexity!
 
Hello, all. Thanks _so_ much for this code. I have been fighting a similar problem, knew it was doable and simple, just didn't know how. I have my append query running now on one form, but would like it to run on all 4 forms without having to recreate it with each formname (all field names are the same). Can I do that somehow? Ex, I have

Forms![Contracts TR]!AHEmail.Valu

Could I leave out the formname? Also, can you fill me in on the .Value part. Some lines had it, others not. Seems to work either way

Thanks!!!! (You don't know how much

Cat
 
Back
Top