Select and Append Queries... at the same time? (1 Viewer)

[Colin@Toyota]

I've done some searching, and haven't found any threads that match what I am attempting...

I have a database that tracks expenses that are incurred under different ongoing projects. I currently have a report based on a query that asks the user for the project number, and shows all the expense invoices that have not been paid for that project. This report is basically an internal invoice that gets assigned to a department, and so it is assigned an internal "invoice" number. Currently, the user types in this internal number before the report is generated and it is displayed on the report for printing purposes.

Here's the tricky part (for me anyways): I would like to be able to filter the invoice records by project number, and then append the user-entered internal invoice number to the applicable records. There is already a field in the table for this data, and it is currently being entered on individual records after the report is printed. My goal is to be able to do this all in one step.

Any help will be greatly appreciated... Thanks guys!

 

[ajetrumpet]

This sounds like an interesting problem Colin, I just have one question about this...

Currently, the user types in this internal number before the report is generated and it is displayed on the report for printing purposes.

where are they typing this number?? In the corresponding record of the table, after the query is run, but before the report is generated??

Sounds like a manually process. Is this correct??

 

[Colin@Toyota]

It is a user defined parameter that is linked to a text box on the report...
Control Source: = [TMC Invoice #]

Then, once the report has been printed, we use a modified version of the vendor invoicing form to add this TMC Invoice number to each applicable record. So, yes... it is manually entered. Very manually entered...

I am thinking the best way to go about this might be to create a pre-report form that has two fields; a drop-down to select the project identifier number, and a text box to input the TMC Invoice #... I just don't know quite how to go about it... Well actually, I just don't want to waste time doing it the wrong way first!

 

[ajetrumpet]

Just another question...

If the field on the report has a control source, that means that the report is pulling the value from the table when the report is generated. If this is the case, the internal invoices number cannot be a "parameter".

When you say "user defined parameter", do you mean, "the user enters this value into the table based on other information, or just randomly"??

You can probably do this when a few lines of Basic code, but I just want to be clear about the above question first...

 

[Colin@Toyota]

Ok... Let me back up a second here...

When I want to generate the report, a query runs where the user defines the project number, AND where the internal invoice field values are blank. Then a user prompt pops up asking for a TMC Invoice Number (which is a unique, alpha-numeric designation defined by the user - it follows logic, but is from outside access).

So the report lists all the invoices under that project that have not been assigned to an internal invoice (TMC Invoice), and in the header displays the TMC Invoice # they are being assigned to (from the user).

After this report has been printed, we have to go back in through another form to add this TMC Invoice # to the applicable field in each record that was previously blank (the field the query used to filter project invoices).

I hope that makes it a little bit clearer than mud for you...

Thanks!

 

[KeithG]

Can one project have more than one invoice? if not I would store the invoice number in the project table. An Update SQL statemment should do the trick. Can you post an example db?

 

[Colin@Toyota]

Each project has many vendor invoices, which will be grouped into a TMC invoice. Each project may also have more than one TMC Invoice...

I will post a sample db tomorrow, since it is officially quittin' time, and my brain is already at home on the couch with a cold beer.

Cheers!

 

[ajetrumpet]

Colin,

I don't think there is any need to post your database for this. There is a clear picture now of what needs to be done...

Because your report is running from a query, the query (at runtime (or even while the report is open) will be holding all of the records that have no TMC number, but need to be updated with one. Thus, you could put a button on your report (or anywhere else for that matter) that will UPDATE all "TMC Invoice #" values in the query based on the TMC Invoice # that has been passed to the report's control (I'm assuming a text box).

If this is all right, you only need to write one command...

Private Sub CommandButtonName_Click

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

End Sub

You don't need to specify criteria in the SQL because all of the records that are sitting in the query's recordset need to be updated with the same value.

Also, Updating records in queries are permanent in their underlying table sources, because they get their data from the tables. In other words, data entry works both ways when it comes to tables and queries...

 

[Colin@Toyota]

Thank you very much! That is exactly what I need this thing to do... I will insert that code this afternoon, and let you know how it turns out.

 

[Colin@Toyota]

I tried, unsucessfully to put a command button on the report... so instead I went into the report's properties, and inserted the code in OnClose. I hope this will still work... I am about to try...

Also, VB didnt like the _ between the ampersand and the textbox location, so I deleted it, and it accepted it... Does that sound correct?

 

[Colin@Toyota]

Didn't work. Runtime error... Hit debug, and it higlighted the whole line. I guess I will go back to trying to use a command button.

I think part of the problem is, I originally designed this program following the standard naming procedures... frm pre-fix for forms, tbl for tables, etc. Then while I was in another department, someone decided to come in and redo my entire program, who knew even less about access than I do! So all the names have spaces in them, and whatnot, making it quite confusing. I am going to go in and rename all the components and try again.

If anyone has any pointers, please let me know!
Thanks!

 

[Colin@Toyota]

Got it to accept the code, but now it is prompting me for all these parameters, when it is supposed to just be taking the data from a text box and applying it to the records' corresponding field... Help?!?!

Oh well, it's lunchtime now...

Thanks in advance!

 

[ajetrumpet]

Colin,

The prompt popups happen for a number of reasons...a few of them are listed below...

1) You are using brackets [] incorrectly
2) You are NOT using brackets around strings where their should be some
3) The SQL statement has unknown strings in it (i.e. referring to a control by typing "Me.ID" in the SQL string when the control's name is really "cboID") - in this instance, you would get a an input box popup that says "Me.ID"

Also, regarding your other comment Colin...

VB didnt like the _ between the ampersand and the textbox location, so I deleted it, and it accepted it... Does that sound correct?

It didn't agree because you wrote the entire code I gave you on ONE line.

& _ is a continuation notation that tells the engine "this line of code is continued on the next line". In order words, all three of the following codes are read the same by Access....

1)  "SELECT table.field1, table.field2 FROM table WHERE table.field1 = 'my criteria'"

2)  "SELECT table.field1, table.field2, FROM table " & _
     "WHERE table.field1 = 'my criteria'"

3)  "SELECT table.field1, table.field2 " & _ 
     "FROM table " & _
     "WHERE table.field1 = 'my criteria'"

Just to add a bit of fun to this too; Did you know that if you remove the SPACE from the end of continued lines, the code won't run?? This...

"SELECT table.field1, table.field2, FROM [color=red]table"[/color] & _
"WHERE table.field1 = 'my criteria'"

reads like this....

"SELECT table.field1, table.field2, FROM [color=red]tableWHERE[/color] table.field1 = 'my criteria'"

Hmmm, interesting...

 

[Colin@Toyota]

That WAS fun!
hahaha

Ok, I think I have got it... except for one thing I hope you can clear up for me; when you say I'm not using brackets around a string where there should be brackets... is the string you refer to something like the query.field or reports!reportname!textbox?... so that they should look like [query.field] and [reports!reportname!textbox]?

 

[ajetrumpet]

No, in your case, you don't need brackets. The list in my last post was just common causes of parameter popups. Those make it look like you are way off with your intentions, but in fact, most of the time, you are so close, that you don't notice the tiny error that is present.

Did you get everything to work then??

 

[Colin@Toyota]

I'm going to try again right now!

 

[Colin@Toyota]

I can't seem to put a button on the report... and it just keeps screwing up when I tried putting the code in the OnClose field. It keeps prompting me to enter the Project Number again, and then it pops up with another prompt for Reports!rptNewTMC!txtTMCinvoice, etc. I guess because there is no longer data in that textbox...?

Is there anyway to have this done automatically? maybe OnOpen?

 

[ajetrumpet]

Yes, having it done automatically is what the code will do.
Here's another question that might help with this...

How is the value of the text box getting there?? In other words, what process are you using to get the TMCinvoice value INTO the text box (e.g. explain more about the user entered value before previewing the report)??

The code may not work with the OnClose event because it is in the process of being closed, but I'm not sure. The EVENT ORDER of a form closing is...

Exit > Lost Focus > Unload > Deactivate > Close

That might play a part in it, but regardless, can you explain the process I mentioned above??

 

[ajetrumpet]

You can also test to see if the code works in other ways (JUST to see if it works, but make sure you backup your underlying table first (make a copy or something))...

e.g. Put another text box on the report - OnGotFocus Event = put code in. Then click into the text box. Something like that will work as a test anyway.

 

[Colin@Toyota]

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

Once I hit ok after entering the project number, another prompt pops up asking for the TMC Invoice # (this is supplied by the user, set up by the text box's control source = [TMC Invoice #], which is what requests input from the user)

I will try your suggestion right now...

Also, does it matter if there is a space in the field name? Could that be part of the problem?