Copy records to 2nd table

[Sally*]

Hi all,

I'm getting myself into a bit of a muddle.

I have four tables:
tblinvoice / tblinvparts
tblquotes / tblquoteparts


when I open the tblinvoice form it has tblinvparts as a subform.
tblinvoice has a listbox that lists quote numbers linked to vehiclereg.

I have a button on the tblinvoice that will open a popup tblquoteparts continuous form linked to the quoteid on tblinvoice. These filtered records have a select button so I can select all or some.

I have now got stuck as I need a button on tblquoteparts popup that will copy the selected records from tblquoteparts and paste them into tblinvparts where quote (on tblequoteparts)=quoteid in tblinvoice.

In a nutshell, I would like to copy selected records from tblquoteparts to tblinvparts.

I need to duplicate the records because only 10% of invoices are generated from a quote and the quote parts/prices may differ from the final invoice.

I do hope this is not too complicated but any guidance would be grateful.

Many thanks

 

[ted.martin]

You need an APPEND query but in the CRITERIA use this to filter to the correct records on tblquoteparts into tblinvparts

Criteria the Quoteid [Forms]![tblquoteparts form]![quoteid]

For this to work though, the form tblquoteparts is open.

 

[Sally*]

Thanks Ted,

I think I'm getting there. When I look at the append query in SQL it looks like:

INSERT INTO invparts ( partdescription, sell, qty )

SELECT quoteparts.partdescription, quoteparts.sell, quoteparts.qty

FROM quoteparts

WHERE (((quoteparts.quote)=[Forms]![INVOICE]![QUOTEID]) AND ((quoteparts.[SELECT])=-1));

When I look at the query as a datasheet it works and shows the correctly filtered records. When I try and run the query on the form, it recognises the filter and tells me it is about to append [**] records but I then get the following, rather long, message:

[database name] set 0 field(s) to Null due to a type conversion failure,and didn't add [*] records(s) to the table due to key violations, 0 records(s) due to lock violations, and 0 record(s) due to validation rule violations. Do you want to run the action query anyway? To ignore...

I can't see any mis-matches in the data type of each table and there are no validation rules in any table.

At the time of running the append query I have the invoice form open with the invpartssubform attached. I also have the quoteparts filtered popup form open and the append query is actioned with a button on this popup form.

It's probably something really obvious that I've missed.:banghead:

 

[GinaWhipp]

What is the Primary Key/Foreign Key relationship on those two tables to each other? Seems to me it's that that's being left NULL and that's why it won't append.

 

[Sally*]

The primary key for both tables is an autonumber (ID), so it should never be null.

If it helps, here are the fields for both tables:
quoteparts:- ID (autonumber), quote (number), partno (text), partdescription (text), buy (currency), sell (currency), qty (number).

invparts:- ID (autonumber), inv(number), partno (text), partdescription (text), buy (currency), sell (currency), qty (number).

There is no direct relationship between them but there is via the INVOICE table:
INVOICE[QUOTEID] (number)=QUOTEPARTS

(number)and
INVOICE[INV] (autonumber & primary key) = INVPARTS[INV] (number)

this link works to filter the records

I hope this helps

 

[GinaWhipp]

Then in your APPEND query where do you insert the NUMBER? If joined via Relationships window then that field can't be empty either.

 

[Sally*]

Hi, not quite sure what you mean by the NUMBER. Do you mean ID (autonumber).

These two tables have no direct relationship

 

[GinaWhipp]

Yes, that was me typing too fast. Okay, then what is the Primary Key? When you APPEND are you including that because it doesn't appear so.

 

[Sally*]

Just for the purpose of testing, I have removed the Primary Key from both tables but it still doesn't work.

The master/child link on the invparts subform is the invoice number, but I don't have this field on the quoteparts table. I had assumed that it would autofill, as it does when I manually enter items in the invparts subform.

Could this be the problem? Sorry for my ignorance. I am self taught so have big holes in my knowledge.

If I do need to mention the invoice number, where would this fit into the query?

 

[GinaWhipp]

Didn/t want you to remove them just ell me what they are. Also, do you have any Validation set on any field in the table?