Importing data from one form to other

[Gigaz]

Hi everybody,
I have following problem :
I want to import data from one form to other in same access program.
Example:
I have form 1 Quotation (in relation to Customer and Product items tables) and form 2 Invoices (in relation to the same tables)
I have created Quotation (nr 125,126,127) all with different data (customers and products).
I want to import only quotation nr 126 to Invoice form.
I tried different ways but every time I'm just copying
whole quotation form (all quotations 125,126,127) get in
Invoice form. I want only quotation 126 to go in Invoice form as only that one is approved for supply.
My Invoice form is with auto number PK so the Quotation form Id 126 will have to convert to next Invoice auto nr Id (could be Id 69 )
Tks for help
Gigaz

 

[Remou]

Have you tried an append query? For example:

[tt]strSQL="INSERT INTO tblInvoices (Description, Quantity) " _
& "SELECT (Description, Quantity) FROM tblQuotations " _
& "WHERE QuotationID=" & intQuotID
CurrentDB.Execute strSQL, dbFailOnError[/tt]

 

[Gigaz]

Hi Remou,
Tks for reply, I have tried append query, but than I'm
appending all product items from product table.
What I want is to append only selected items (ones that I have quoted in my quotation (exmpl quotation nr 223).
Like this if I have 200 items in my product table all 200 are append to my new invoice.
Exmpl: I have quotation nr 233 that have 36 product items.
I want to append only those 36 to my Invoice.
My product table have about 200 different items, appending product table gives me all those items in new invoice.
Tks Gigaz

 

[Remou]

Please post your SQL and / or code.

 

[Gigaz]

Hi,
I did try append query as I said before I'm appending whole table products. All items are copied to Invoice form in stead only items in Quotation (exampl 233)
Sql was
INSERT INTO tblInvoice ( Item, Qty )
SELECT tblQuotes.Item, tblQuotes.Qty
FROM tblQuotes;

 

[Remou]

You need a Where statement.

[tt]INSERT INTO tblInvoice ( Item, Qty )
SELECT tblQuotes.Item, tblQuotes.Qty
FROM tblQuotes
WHERE tblQuotes.Item=233[/tt]

I have guessed the Where statement, but you should get the general idea.

 

[Gigaz]

Hi,
I did try creating select query with criteria in tblQuotes but it was still appending all data from product list.
I will try WHERE now, and let you know how did I go.
Tks,
Gigaz

 

[Remou]

If you do not use a where statement, everything from the table will be inserted. "Where" means "only choose records where ...".

 

[Gigaz]

Hi,
I have tried all possible combinations with append query and sql statements and it is not working.
When I want to append form Quotation that have subform Products to form Invoice that have subform Products.
I have created query InvoiceProduct that have fields InvoiceId
ProductId ProductName. How can I append QuoationId ProductId ProductName? If I create append query I can append only table (Invoice or Product) but not query or form that contains InvoiceId ProductID ProdctName.
Tks

 

[Remou]

Please post your new SQL.

 

[Gigaz]

Hi,
I made sample database:

table Inv with fields
InvoiceId - autonumber
InvoiceDate - date

table Quo with fields
InvoiceId - autonumber
InvoiceDate - date

table Pro
ProductId - autonumber
ProductName - text

table InvPro
InvoiceId - number
ProductId - number

table QuoPro
InvoiceId - number
ProductId - number

Tables Inv and InvPro and Pro are in 1 to many relation ship
Tables Quo and QuoPro and Pro are in 1 to many relation ship

I created following Query InvProd
using tables InvPro and Pro
that contains InvoiceId ProductID and ProductName fields

Query QuoProd
using tables QuoPro and Pro
that contains InvoiceId ProductID and ProductName fields

I made forms:
Inv : InvoiceId, InvoiceDate
with subforms (query InvProd)

Quo : InvoiceId, InvoiceDate
with subforms (query QuoProd)
I populated booth forms and have in form Inv - invoice nr 1 and 3 products, form Quo invoice nr 1 and nr 2 booth with 5 and 6 product items.
What I want is to import from the form Quo invoice nr 2 to form Inv so that I receive in form Inv invoice nr 2 with 6 product items.
Meaning of is :
As i do quotation for some company, some of them get approved and some not, approved one will be realized so they have to go to Inv form, the other that they are not approved stay in quotation and they don't enter my bookkeeping side of program - they are not realized sales.

This is my Sql:
INSERT INTO InvPro ( InvoiceId, ProductId, ProductName )
SELECT [QuoProd].[InvoiceId], [QuoProd].[ProductId], [QuoProd].[ProductName]
FROM QuoProd
WHERE [QuoProd].[InvoiceId]=2;
Tks Gigaz