Select and Append Queries... at the same time?

[ajetrumpet]

Yes, the SPACE in a string of anykind is ALWAYS enclosed in brackets [] or quotes ""

If it's not, it is read as two strings. And, that is another reason why you would get a parameter popup.

 

[Colin@Toyota]

I tried to change the name of the field, and it asked if i wanted to overwrite changes another user made? What does this mean?

Is it easier to use the brackets? if so, how would I go about it? Around the whole string, or just the field name?

 

[ajetrumpet]

Colin,

I think you might be making this a bit more complicated than it is (just telling you the truth). I'm not sure what other systems you're using or what your situation is, but if you're strictly working with an Access database, everything I have told you should work. I can offer to look over it again if you would like to provide the following...

*Report Name, *Underlying Query Name, *TMC Invoice # Text Box Name, *Underlying Query SQL statement (found in SQL view of query),
*ACTUAL code you are now using on the report event (of your choice), *Other Visual Basic Code associated with the Report

From above...

Private Sub CommandButtonName_Click

  DoCmd.RunSQL "UPDATE YourQuery SET YourQuery.TMCinvoicefield = " & _
  
  "Reports!YourReportName!TMCtextBoxName"

End Sub

 

[Colin@Toyota]

Oh, I probably am...

Report Name: rptNewTMC

Query Name: qryNewTMC

SQL Statement: SELECT qryInvoiceDetails.ID, qryInvoiceDetails.[SETR ID], qryInvoiceDetails.[Reference File], qryInvoiceDetails.[TMC Invoice], qryInvoiceDetails.[Date Paid], qryInvoiceDetails.[Invoice ID (File #)], qryInvoiceDetails.Vendor, qryInvoiceDetails.[Vendor Invoice #], qryInvoiceDetails.[Vendor Date Paid], qryInvoiceDetails.[Amount Paid], qryInvoiceDetails.[Taxes (GST)], qryInvoiceDetails.Parts, qryInvoiceDetails.Labour, qryInvoiceDetails.Sublet, qryInvoiceDetails.[Vendor Invoice Date], qryInvoiceDetails.VIN, qryInvoiceDetails.Mileage, qryInvoiceDetails.[Reg Date], qrySETRsub.[Sum Of Parts], qrySETRsub.[Sum Of Labour], qrySETRsub.[Sum Of Sublet], qrySETRsub.[Sum Of Recovered ($)]
FROM qryInvoiceDetails INNER JOIN qrySETRsub ON qryInvoiceDetails.[SETR ID] = qrySETRsub.[SETR ID]
WHERE (((qryInvoiceDetails.[SETR ID])=[SETR]) AND ((qryInvoiceDetails.[TMC Invoice]) Is Null))
ORDER BY qryInvoiceDetails.ID;

Field Name: TMC Invoice

Text Box Name: txtTMCinvoice

Here is the current code:
Private Sub cmdUpdateTMC_Click()

DoCmd.RunSQL "UPDATE qryNewTMC SET qryNewTMC.TMC Invoice = " & _

"Reports!rptNewTMC!txtTMCinvoice"

End Sub

All I can say is I'm sure glad it is Friday, and I don't have to worry about it all weekend. Thank you so much for your help!

 

[ajetrumpet]

Here is the current code:

Private Sub cmdUpdateTMC_Click()
    
    DoCmd.RunSQL "UPDATE qryNewTMC SET qryNewTMC.[color=red][size=5][[/color][/size]TMC Invoice[color=red][size=5]][/color][/size] = " & _

    "Reports!rptNewTMC!txtTMCinvoice"

End Sub

I found an error... Without the above [ ] in the code, you will certainly get a parameter pop-up. I'm guessing it said something like...Enter Parameter - "qryNewTMC.TMC"?? Change this, and see it if fixes the problem...

Also, what does the controlsource of your text box say??

 

[Colin@Toyota]

Hmm...

Well, it stopped asking to Enter Parameter - qryNewTMC.TMC, but it is still asking for the project number when I click the command button. Could this be because it is a parameter query, and that when i try to use the Update function, it needs to re-ask the parameter?

I tried re-entering the project number, but an error came up...
'Run-time error 3073. Operation must use an updateable query.'

I guess I kinda answered my own question there... I just had to actually read the error message... haha

 

[ajetrumpet]

Well, it stopped asking to Enter Parameter - qryNewTMC.TMC, but it is still asking for the project number when I click the command button. Could this be because it is a parameter query

Colin, per your post # 20...

When I open the report, it prompts for the project number (this criteria applies to the query that the report is based on)

So, there you go. Is that the same popup you are talking about??

Also, I'm not sure what you're wanting now. Is everything working now?? Still got a few speedbumps?? The last post was a little vague.

 

[Colin@Toyota]

Sorry... no, it's not working properly. I get the runtime error when I try. I don't think access likes me trying to update a parameter query. Maybe it is also because the other criteria for the query is that TMC Invoice is null?


Yes, it is the same popup box prompting for the project number, but it should already have this value, shouldn't it? This seems like it is trying to re-run the query...?

Also, to answer your question from a couple posts ago, the control source for the text box containing the TMC Invoice # is,
Control Source: =[TMC Invoice]

Would it help if I post a copy of the database?

 

[Colin@Toyota]

Well... I got it to work... by creating a separate update query.

Turns out that the query I used to find un-billed invoices for each project was based on two other queries... I guess there were just to many layers of queries for this code to work?

Anyways, this new update query I am using is tied directly to the applicable table. I have to re-enter the project and TMC invoice number again, but oh well.

I am still interested in getting the code to work though... and thank you for all the time and effort you spend trying to help me figure this out ajetrumpet.

Cheers,

Colin

 

[ajetrumpet]

No problem...too bad it's not completely automated though.

If you want to post the database, I would be happy to take a look.

Glad to hear you got it working too (somehow, anyway!!).