Using append queries

[kpryan]

Hi all,
I have 2 append queries which rely on an ID to be inserted into the criteria of the query.
I have a command button on a form which uses the on click event to action both of these append queries.
However the criteria I need to use is the insertion of an ID in the query. But I need to input this ID 2x for both append queries to work. Is there a way to be able to just input the ID just once for both append queries to work?
Many thanks,

Ken

 

[Remou]

You can use a form to supply the ID, or you can run the queries from code.

 

[kpryan]

Hi Remou,
Thanks for that, could yo ugive me some more info on how to do it.

Many thanks,

Ken

 

[TheAceMan1]

Howdy kpryan . . .

To [blue]rocket this thread[/blue], post the two append queries! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kpryan]

Hi the Aceman1,
Many thanks for your reply. I hope this is whay you need.

Append query 1
INSERT INTO AccountsAll ( Invoices, BrokerStaffID, InvoiceNumber, DateRaised, DueDate, DateRcvd, RecordID, ClientID, Firstname, Lastname, FileNo, Dept, EntBy, Companyname, Contact, ATTN, Date1, Client1, Staff1, Start1, D1, N1, PN1, SS1, Sun1, PW1, PH1, PPHR1, GST1, La1, Date2, Client2, Staff2, Start2, D2, N2, PN2, SS2, Sun2, PW2, PH2, PPHR2, GST2, La2, Date3, Client3, Staff3, Start3, D3, N3, PN3, SS3, Sun3, PW3, PH3, PPHR3, GST3, La3, Date4, Client4, Staff4, Start4, D4, N4, PN4, SS4, Sun4, PW4, PH4, PPHR4, GST4, La4, Date5, Client5, Staff5, Start5, D5, N5, PN5, SS5, Sun5, PW5, PH5, PPHR5, GST5, La5, Date6, Client6, Staff6, Start6, D6, N6, PN6, SS6, Sun6, PW6, PH6, PPHR6, GST6, La6, Date7, Client7, Staff7, Start7, D7, N7, PN7, SS7, Sun7, PW7, PH7, PPHR7, GST7, La7, Date8, Client8, Staff8, Start8, D8, N8, PN8, SS8, Sun8, PW8, PH8, PPHR8, GST8, La8, Date9, Client9, Staff9, Start9, D9, N9, PN9, SS9, Sun9, PW9, PH9, PPHR9, GST9, La9, Date10, Client10, Staff10, Start10, D10, N10, PN10, SS10, Sun10, PW10, PH10, PPHR10, GST10, La10, Date11, Client11, Staff11, Start11, D11, N11, PN11, SS11, Sun11, PW11, PH11, PPHR11, GST11, La11, Date12, Client12, Staff12, Start12, D12, N12, PN12, SS12, Sun12, PW12, PH12, PPHR12, GST12, La12, DTotal, NTT, PNT, SST, SUNT, PWTT, PHT, PPHRT, WORKHRS, Item1, Un1, Uc1, UT1, Item2, Un2, Uc2, UT2, Item3, Un3, Uc3, UT3, Totals, GST, UnitT, Contrib, SubTotal, InvoiceTotal, BalDue, AmmtRcvd, Notes, NONPrintNotes, CreditN, CreditT, BDR, BNR, BPwknR, BPWndR, BSatr, BSunr, BPHR, BSpec, ACS, Yes1, Yes2, Yes3, InvGST, BillingAddress, CHK, ChqNo, Bal, PD )
SELECT Sales08Paid.Invoices, Sales08Paid.BrokerStaffID, Sales08Paid.InvoiceNumber, Sales08Paid.DateRaised, Sales08Paid.DueDate, Sales08Paid.DateRcvd, Sales08Paid.RecordID, Sales08Paid.ClientID, Sales08Paid.Firstname, Sales08Paid.Lastname, Sales08Paid.FileNo, Sales08Paid.Dept, Sales08Paid.EntBy, Sales08Paid.Companyname, Sales08Paid.Contact, Sales08Paid.ATTN, Sales08Paid.Date1, Sales08Paid.Client1, Sales08Paid.Staff1, Sales08Paid.Start1, Sales08Paid.D1, Sales08Paid.N1, Sales08Paid.PN1, Sales08Paid.SS1, Sales08Paid.Sun1, Sales08Paid.PW1, Sales08Paid.PH1, Sales08Paid.PPHR1, Sales08Paid.GST1, Sales08Paid.La1, Sales08Paid.Date2, Sales08Paid.Client2, Sales08Paid.Staff2, Sales08Paid.Start2, Sales08Paid.D2, Sales08Paid.N2, Sales08Paid.PN2, Sales08Paid.SS2, Sales08Paid.Sun2, Sales08Paid.PW2, Sales08Paid.PH2, Sales08Paid.PPHR2, Sales08Paid.GST2, Sales08Paid.La2, Sales08Paid.Date3, Sales08Paid.Client3, Sales08Paid.Staff3, Sales08Paid.Start3, Sales08Paid.D3, Sales08Paid.N3, Sales08Paid.PN3, Sales08Paid.SS3, Sales08Paid.Sun3, Sales08Paid.PW3, Sales08Paid.PH3, Sales08Paid.PPHR3, Sales08Paid.GST3, Sales08Paid.La3, Sales08Paid.Date4, Sales08Paid.Client4, Sales08Paid.Staff4, Sales08Paid.Start4, Sales08Paid.D4, Sales08Paid.N4, Sales08Paid.PN4, Sales08Paid.SS4, Sales08Paid.Sun4, Sales08Paid.PW4, Sales08Paid.PH4, Sales08Paid.PPHR4, Sales08Paid.GST4, Sales08Paid.La4, Sales08Paid.Date5, Sales08Paid.Client5, Sales08Paid.Staff5, Sales08Paid.Start5, Sales08Paid.D5, Sales08Paid.N5, Sales08Paid.PN5, Sales08Paid.SS5, Sales08Paid.Sun5, Sales08Paid.PW5, Sales08Paid.PH5, Sales08Paid.PPHR5, Sales08Paid.GST5, Sales08Paid.La5, Sales08Paid.Date6, Sales08Paid.Client6, Sales08Paid.Staff6, Sales08Paid.Start6, Sales08Paid.D6, Sales08Paid.N6, Sales08Paid.PN6, Sales08Paid.SS6, Sales08Paid.Sun6, Sales08Paid.PW6, Sales08Paid.PH6, Sales08Paid.PPHR6, Sales08Paid.GST6, Sales08Paid.La6, Sales08Paid.Date7, Sales08Paid.Client7, Sales08Paid.Staff7, Sales08Paid.Start7, Sales08Paid.D7, Sales08Paid.N7, Sales08Paid.PN7, Sales08Paid.SS7, Sales08Paid.Sun7, Sales08Paid.PW7, Sales08Paid.PH7, Sales08Paid.PPHR7, Sales08Paid.GST7, Sales08Paid.La7, Sales08Paid.Date8, Sales08Paid.Client8, Sales08Paid.Staff8, Sales08Paid.Start8, Sales08Paid.D8, Sales08Paid.N8, Sales08Paid.PN8, Sales08Paid.SS8, Sales08Paid.Sun8, Sales08Paid.PW8, Sales08Paid.PH8, Sales08Paid.PPHR8, Sales08Paid.GST8, Sales08Paid.La8, Sales08Paid.Date9, Sales08Paid.Client9, Sales08Paid.Staff9, Sales08Paid.Start9, Sales08Paid.D9, Sales08Paid.N9, Sales08Paid.PN9, Sales08Paid.SS9, Sales08Paid.Sun9, Sales08Paid.PW9, Sales08Paid.PH9, Sales08Paid.PPHR9, Sales08Paid.GST9, Sales08Paid.La9, Sales08Paid.Date10, Sales08Paid.Client10, Sales08Paid.Staff10, Sales08Paid.Start10, Sales08Paid.D10, Sales08Paid.N10, Sales08Paid.PN10, Sales08Paid.SS10, Sales08Paid.Sun10, Sales08Paid.PW10, Sales08Paid.PH10, Sales08Paid.PPHR10, Sales08Paid.GST10, Sales08Paid.La10, Sales08Paid.Date11, Sales08Paid.Client11, Sales08Paid.Staff11, Sales08Paid.Start11, Sales08Paid.D11, Sales08Paid.N11, Sales08Paid.PN11, Sales08Paid.SS11, Sales08Paid.Sun11, Sales08Paid.PW11, Sales08Paid.PH11, Sales08Paid.PPHR11, Sales08Paid.GST11, Sales08Paid.La11, Sales08Paid.Date12, Sales08Paid.Client12, Sales08Paid.Staff12, Sales08Paid.Start12, Sales08Paid.D12, Sales08Paid.N12, Sales08Paid.PN12, Sales08Paid.SS12, Sales08Paid.Sun12, Sales08Paid.PW12, Sales08Paid.PH12, Sales08Paid.PPHR12, Sales08Paid.GST12, Sales08Paid.La12, Sales08Paid.DTotal, Sales08Paid.NTT, Sales08Paid.PNT, Sales08Paid.SST, Sales08Paid.SUNT, Sales08Paid.PWTT, Sales08Paid.PHT, Sales08Paid.PPHRT, Sales08Paid.WORKHRS, Sales08Paid.Item1, Sales08Paid.Un1, Sales08Paid.Uc1, Sales08Paid.UT1, Sales08Paid.Item2, Sales08Paid.Un2, Sales08Paid.Uc2, Sales08Paid.UT2, Sales08Paid.Item3, Sales08Paid.Un3, Sales08Paid.Uc3, Sales08Paid.UT3, Sales08Paid.Totals, Sales08Paid.GST, Sales08Paid.UnitT, Sales08Paid.Contrib, Sales08Paid.SubTotal, Sales08Paid.InvoiceTotal, Sales08Paid.BalDue, Sales08Paid.AmmtRcvd, Sales08Paid.Notes, Sales08Paid.NONPrintNotes, Sales08Paid.CreditN, Sales08Paid.CreditT, Sales08Paid.BDR, Sales08Paid.BNR, Sales08Paid.BPwknR, Sales08Paid.BPWndR, Sales08Paid.BSatr, Sales08Paid.BSunr, Sales08Paid.BPHR, Sales08Paid.BSpec, Sales08Paid.ACS, Sales08Paid.Yes1, Sales08Paid.Yes2, Sales08Paid.Yes3, Sales08Paid.InvGST, Sales08Paid.BillingAddress, Sales08Paid.CHK, Sales08Paid.ChqNo, Sales08Paid.Bal, Sales08Paid.PD
FROM Sales08Paid
WHERE (((Sales08Paid.ClientID)=[Enter Client ID]));



Append query 2
INSERT INTO AccountsAll ( BrokerStaffID, InvoiceNumber, DateRaised, DueDate, DateRcvd, RecordID, ClientID, Firstname, Lastname, FileNo, Dept, EntBy, Companyname, Contact, ATTN, Date1, Client1, Staff1, Start1, D1, N1, PN1, SS1, Sun1, PW1, PH1, PPHR1, GST1, La1, Date2, Client2, Staff2, Start2, D2, N2, PN2, SS2, Sun2, PW2, PH2, PPHR2, GST2, La2, Date3, Client3, Staff3, Start3, D3, N3, PN3, SS3, Sun3, PW3, PH3, PPHR3, GST3, La3, Date4, Client4, Staff4, Start4, D4, N4, PN4, SS4, Sun4, PW4, PH4, PPHR4, GST4, La4, Date5, Client5, Staff5, Start5, D5, N5, PN5, SS5, Sun5, PW5, PH5, PPHR5, GST5, La5, Date6, Client6, Staff6, Start6, D6, N6, PN6, SS6, Sun6, PW6, PH6, PPHR6, GST6, La6, Date7, Client7, Staff7, Start7, D7, N7, PN7, SS7, Sun7, PW7, PH7, PPHR7, GST7, La7, Date8, Client8, Staff8, Start8, D8, N8, PN8, SS8, Sun8, PW8, PH8, PPHR8, GST8, La8, Date9, Client9, Staff9, Start9, D9, N9, PN9, SS9, Sun9, PW9, PH9, PPHR9, GST9, La9, Date10, Client10, Staff10, Start10, D10, N10, PN10, SS10, Sun10, PW10, PH10, PPHR10, GST10, La10, Date11, Client11, Staff11, Start11, D11, N11, PN11, SS11, Sun11, PW11, PH11, PPHR11, GST11, La11, Date12, Client12, Staff12, Start12, D12, N12, PN12, SS12, Sun12, PW12, PH12, PPHR12, GST12, La12, DTotal, NTT, PNT, SST, SUNT, PWTT, PHT, PPHRT, WORKHRS, Item1, Un1, Uc1, UT1, Item2, Un2, Uc2, UT2, Item3, Un3, Uc3, UT3, Totals, GST, UnitT, Contrib, SubTotal, InvoiceTotal, BalDue, AmmtRcvd, Notes, NONPrintNotes, CreditN, CreditT, BDR, BNR, BPwknR, BPWndR, BSatr, BSunr, BPHR, BSpec, ACS, Yes1, Yes2, Yes3, InvGST, BillingAddress, CHK, ChqNo, Bal, PD )
SELECT Accounts08.BrokerStaffID, Accounts08.InvoiceNumber, Accounts08.DateRaised, Accounts08.DueDate, Accounts08.DateRcvd, Accounts08.RecordID, Accounts08.ClientID, Accounts08.Firstname, Accounts08.Lastname, Accounts08.FileNo, Accounts08.Dept, Accounts08.EntBy, Accounts08.Companyname, Accounts08.Contact, Accounts08.ATTN, Accounts08.Date1, Accounts08.Client1, Accounts08.Staff1, Accounts08.Start1, Accounts08.D1, Accounts08.N1, Accounts08.PN1, Accounts08.SS1, Accounts08.Sun1, Accounts08.PW1, Accounts08.PH1, Accounts08.PPHR1, Accounts08.GST1, Accounts08.La1, Accounts08.Date2, Accounts08.Client2, Accounts08.Staff2, Accounts08.Start2, Accounts08.D2, Accounts08.N2, Accounts08.PN2, Accounts08.SS2, Accounts08.Sun2, Accounts08.PW2, Accounts08.PH2, Accounts08.PPHR2, Accounts08.GST2, Accounts08.La2, Accounts08.Date3, Accounts08.Client3, Accounts08.Staff3, Accounts08.Start3, Accounts08.D3, Accounts08.N3, Accounts08.PN3, Accounts08.SS3, Accounts08.Sun3, Accounts08.PW3, Accounts08.PH3, Accounts08.PPHR3, Accounts08.GST3, Accounts08.La3, Accounts08.Date4, Accounts08.Client4, Accounts08.Staff4, Accounts08.Start4, Accounts08.D4, Accounts08.N4, Accounts08.PN4, Accounts08.SS4, Accounts08.Sun4, Accounts08.PW4, Accounts08.PH4, Accounts08.PPHR4, Accounts08.GST4, Accounts08.La4, Accounts08.Date5, Accounts08.Client5, Accounts08.Staff5, Accounts08.Start5, Accounts08.D5, Accounts08.N5, Accounts08.PN5, Accounts08.SS5, Accounts08.Sun5, Accounts08.PW5, Accounts08.PH5, Accounts08.PPHR5, Accounts08.GST5, Accounts08.La5, Accounts08.Date6, Accounts08.Client6, Accounts08.Staff6, Accounts08.Start6, Accounts08.D6, Accounts08.N6, Accounts08.PN6, Accounts08.SS6, Accounts08.Sun6, Accounts08.PW6, Accounts08.PH6, Accounts08.PPHR6, Accounts08.GST6, Accounts08.La6, Accounts08.Date7, Accounts08.Client7, Accounts08.Staff7, Accounts08.Start7, Accounts08.D7, Accounts08.N7, Accounts08.PN7, Accounts08.SS7, Accounts08.Sun7, Accounts08.PW7, Accounts08.PH7, Accounts08.PPHR7, Accounts08.GST7, Accounts08.La7, Accounts08.Date8, Accounts08.Client8, Accounts08.Staff8, Accounts08.Start8, Accounts08.D8, Accounts08.N8, Accounts08.PN8, Accounts08.SS8, Accounts08.Sun8, Accounts08.PW8, Accounts08.PH8, Accounts08.PPHR8, Accounts08.GST8, Accounts08.La8, Accounts08.Date9, Accounts08.Client9, Accounts08.Staff9, Accounts08.Start9, Accounts08.D9, Accounts08.N9, Accounts08.PN9, Accounts08.SS9, Accounts08.Sun9, Accounts08.PW9, Accounts08.PH9, Accounts08.PPHR9, Accounts08.GST9, Accounts08.La9, Accounts08.Date10, Accounts08.Client10, Accounts08.Staff10, Accounts08.Start10, Accounts08.D10, Accounts08.N10, Accounts08.PN10, Accounts08.SS10, Accounts08.Sun10, Accounts08.PW10, Accounts08.PH10, Accounts08.PPHR10, Accounts08.GST10, Accounts08.La10, Accounts08.Date11, Accounts08.Client11, Accounts08.Staff11, Accounts08.Start11, Accounts08.D11, Accounts08.N11, Accounts08.PN11, Accounts08.SS11, Accounts08.Sun11, Accounts08.PW11, Accounts08.PH11, Accounts08.PPHR11, Accounts08.GST11, Accounts08.La11, Accounts08.Date12, Accounts08.Client12, Accounts08.Staff12, Accounts08.Start12, Accounts08.D12, Accounts08.N12, Accounts08.PN12, Accounts08.SS12, Accounts08.Sun12, Accounts08.PW12, Accounts08.PH12, Accounts08.PPHR12, Accounts08.GST12, Accounts08.La12, Accounts08.DTotal, Accounts08.NTT, Accounts08.PNT, Accounts08.SST, Accounts08.SUNT, Accounts08.PWTT, Accounts08.PHT, Accounts08.PPHRT, Accounts08.WORKHRS, Accounts08.Item1, Accounts08.Un1, Accounts08.Uc1, Accounts08.UT1, Accounts08.Item2, Accounts08.Un2, Accounts08.Uc2, Accounts08.UT2, Accounts08.Item3, Accounts08.Un3, Accounts08.Uc3, Accounts08.UT3, Accounts08.Totals, Accounts08.GST, Accounts08.UnitT, Accounts08.Contrib, Accounts08.SubTotal, Accounts08.InvoiceTotal, Accounts08.BalDue, Accounts08.AmmtRcvd, Accounts08.Notes, Accounts08.NONPrintNotes, Accounts08.CreditN, Accounts08.CreditT, Accounts08.BDR, Accounts08.BNR, Accounts08.BPwknR, Accounts08.BPWndR, Accounts08.BSatr, Accounts08.BSunr, Accounts08.BPHR, Accounts08.BSpec, Accounts08.ACS, Accounts08.Yes1, Accounts08.Yes2, Accounts08.Yes3, Accounts08.InvGST, Accounts08.BillingAddress, Accounts08.CHK, Accounts08.ChqNo, Accounts08.Bal, Accounts08.PD
FROM Accounts08
WHERE (((Accounts08.ClientID)=[Enter Client ID]));

Many thanks,

Ken

 

[PHV]

As already suggested by Remou, use a TextBox in a Form to provide the client id.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Something like this?

WHERE (((Accounts08.ClientID)=[b][COLOR=red] '" & me.txtClientID & "'"[/color][/b]))

Remove the single quotes if ClientID is numeric.



Randy

 

[Remou]

randy700
I think kpryan is using a query, rather than VBA, so I would suggest:

WHERE Accounts08.ClientID= Forms!frmForm!txtClientID

Yesno?