Append query returning too many rows

[choaspes]

Dear All

I'd be very grateful for some help with what feels like a humiliating simple problem.

I want an append query to create a new record in a table and populate that record with fields from a form. Some of these fields are free text, others are from combo boxes (which is, I suspect, where the problem might lie).

The append query adds vastly too many records (albeit all populated with precisely the same information) - and the number it adds seems to depend on which combo box options I choose.

The confusing thing is that I have a similar append query which pulls information from earlier records in the table, and combines that with free text and combo boxes on a near-identical form, which works fine and always just adds one row - despite being quite a lot more complicated.

Any ideas?

 

[pbaldy]

What is the SQL of the query?

 

[choaspes]

I've found a rather inelegant workaround - some of the text boxes on the form now write to a table that temporarily holds that data, the append query pulls some of the fields from that table and the rest from the form, then the temporary record in the table gets deleted.

It works, I only get one record appended now, but it seems a very inefficient way of going about the task.

 

[choaspes]

Paul it was simply a list of expressions defined as text/combo boxes in the form and a few temporary variables.

It seems that as long as ONE field is derived from a table then the append query works perfectly. Probably another of those database fundamentals that I've somehow contrived not to know.

 

[David R]

I'm with pbaldy. What is/was the SQL of your append query? It shouldn't do that under most circumstances... sounds like maybe your query has a cartesian join in it.

 

[choaspes]

Completely standard append query with two data sources:

- ExprX: [Forms]![Form]![TextBox or ComboBox]
- ExprY: [TempVars]![MyVariable]

Now I have added a third data source:

- ExprZ:

.[Field]

...and hey presto it works.

 

[pbaldy]

That's not the SQL, but as long as it works.

 

[choaspes]

Sorry Paul, I appreciate that that wasn't the SQL, I just couldn't immediately gather what you'd learn from the SQL that I hadn't already described.

Thank you for responding.

 

[pbaldy]

I would have thought that 2 people asking for it would indicate that something might have been learned from it, even though you might not have been aware of what it might be. In any case, you have it working, which is what matters.

 

[choaspes]

Thank you Paul.

 

[pbaldy]

Happy to help, but you did all the work.