Re: Transportation For Data !!

In that case you'll want to investigate using DAO to programically copy the detail record. The following code should get you started. I wrote it because I need the capability of adding master records to one table and then optionally add the same records to another table in a one-many relationship. Think of it as adding products to tblProducts and then dynamically adding them directly to an invoice at the same time.

Sub AddClientsToTransfer()
Dim targetRecordSet As DAO.Recordset
Dim sourceRecordset As DAO.Recordset
'Grab the records to copy
Set sourceRecordset = Me.RecordsetClone
'Open up the table that the records will be added to
Set targetRecordSet = CurrentDb.OpenRecordset("tblTransferGuests")
'Loop through the records to be copied and copy them to the table
While Not sourceRecordset.EOF
targetRecordSet.AddNew
targetRecordSet("lngTransportId") = sourceRecordset("lngTransportID")
targetRecordSet("txtAirline") = sourceRecordset("cboAirlineDefault")
targetRecordSet("txtFlightNumber") = sourceRecordset("txtFlightNumberDefault")
targetRecordSet("dteFlightTime") = sourceRecordset("dteFlightTimeDefault")
targetRecordSet("txtCity") = sourceRecordset("txtCityDefault")
targetRecordSet("lngClientId") = sourceRecordset("lngClientId")
targetRecordSet.Update
sourceRecordset.MoveNext
Wend
Set targetRecordSet = Nothing
Set sourceRecordset = Nothing
Me.Requery
End Sub

If you need to ensure that any particular line item is added to an invoice only once, you'll use a DCount() statement to the effect of

If DCount("ID", "tblInvoiceDetail", "invoiceNo = " & Me![invoiceNo] & " AND productNo = " & sourceRecordset("productNo") & ")" = 0 then

You'll obviously have to change the parameters as needed to reflect your specific DB schema.

David H


kennykee wrote:
Yaya. Finally, got it. Yeap, that's what i want and one more thing, only one copy is allowed.

How to do it programmatically or non-programmatically (any solution shall do)

Thanks.

Kennykee



"David C. Holley" wrote:


So you need to copy line items from 1 Invoice to another? Almost like a copy or duplicate function?

kennykee wrote:

Let say I have 2 forms. Both are Invoices. A manufacturing company should has Marketing section and Industry section.

For example: (Microsoft Industries Limited) and (Microsoft Marketing Limited) As you know (Microsoft Industries Limited) cant sell goods directly to customer because manufacturing company that is (Microsoft Industries Limited) doesn't pay the salesman salary and office clerk salary. Hence, (Microsoft Marketing Limited) is establish to handle this fees.

It means (Microsoft Industries Limited) produce goods and sell to (Microsoft Marketing Limited). Then , (Microsot Marketing Limited) sell the goods to customers. Of course the price will be higher when it reaches customers hand to pay the salesman and clerk salary. 

Question:

I got two forms. Both are invoice. (Microsoft Industries Limited) bill to (Microsoft Marketing Limited). Then, (Microsoft Marketing Limited) bill to customers

Microsoft Industries Limited ==> Microsoft Marketing Limited ==>Customer

I cant just make the same invoice forms for both of the Marketing and Industries because some of the products are not from Marketing.

The [forms] are made based on [queries]

Normally, i will use form [Invoice Marketing Bill to Customer]
So, i use filter and all the product from [Invoice Marketing Bill to Customer] will be filtered and only products manufactured by Microsoft Industries Limited will be transferred from [Invoice Marketing Bill to Customer] to [Invoice Industries Bill to Customer]. Each page in [Invoice Marketing Bill to Customer] for each page in [Invoice Industries Bill to Customer].

In short: How to use transfer text or add data in a form based that based on a query to another form that based on a different query? And automatically when i add any data in [Invoice Marketing Bill to Customer].

Programmatically.

I am sure you got questions/blurs. Please send me message if you are not clear.

Any solutions?

Thanks in advance.

Kennykee




"David C. Holley" wrote:



I didn't quite follow what your trying to. What's the bigger picture? What information specifically are you trying to get out the data?

kennykee wrote:


I got a form based on a query table.

Question: how to TRANSFER all the data that are filtered based on a filter to another query which have the exactly same field but different ID

eg. query1 = [Marketing to Customer]
   query2 = [Industry to Marketing]

whenever (Marketing bill to a Customer), an exact bill will done from (Industry to Marketing)

But sometimes, the two bills may differ from each other(item bought).

In short, how to transfer data from a query to another query or from a table

from another table when I click "transfer data" command button.

The both tables or queries wont affect each other when I change any values in it except if I click the command button.

Any solutions? 

Thanks in advance.

Kennykee 

.