Macro Actions, sequence & passing fields

[ljungers]

Are the Action(s) in a Macro run in sequence? How would a OpenQuery pass it's results to a OpenForm that followed in the action list?

For example I have a form #1 that contains a couple of text boxes for a query and a Button with OnClick set to run a Macro. The Macro Action is OpenQuery with the Query Name set to an existing query. That query uses the parameters from the calling Form #1. This works when I look at the results in worksheet view.

What I want to do is add to the Action list a OpenForm as the next action. How would I pass the results of that 1st Query to the OpenForm that contails a combo or list box.

[MMcCarthy]

NeoPa sorry about all the post and confussion. I read your last post and you sound like your talking about the loading of the ListBox from the query in fornt of that step. The list box is getting loaded from the query with RowSourceType and RowSource set with the results from the query, and all looks good at that point.

What is left to be done is the Report that uses the selected rows from that list box.

The reason I refered to thoses different posts was because I have been told that it takes a function or routine to extract the selectd rows in a list box and that a query can not find the rows that have been selected in a list box.

so lets say that steps 2 of 3 are completed if that makes sence.

Thanks

The basic syntax is ...

Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[FieldName] IN ("
   For Each valSelect In Me.ListboxName.ItemsSelected
      strWhere = strWhere & Me.ListboxName.ItemData(ValSelect) & ", "
      'or for strings
      'strWhere = strWhere & "'" &  Me.ListboxName.ItemData(ValSelect) & "', "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
 
 

Mary

[MMcCarthy]

Sorry slight amendment after seeing the last post

Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.ListboxName.ItemsSelected
       strWhere = strWhere & Me.ListboxName.ItemData(ValSelect) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
 
   strWhere = strWhere & ")"
 
 

Mary

[NeoPa]

I kinda lost after reading the 2 post prior to my last. If I understand correctly we are adding VBA code to the button click that will read the list box.

Yes.

If I got it right, we are building a where clause that will use the bound and hidden column 4 that contains the rec_no.

I never knew which columns were hidden and which one was bound.
Now I do and it is as I would have recommended given the chance.

The report will be called for each selected row found in the list box and pass the rec_no in the built where statement.

Not exactly.
The WHERE clause (or WhereCondition parameter) will be built to include all the selected items, then the report will be called once to include all of these items.

See Mary's code for more detailed help on what code to use to build this clause. It should work fine for you but you will have to change 'ListboxName' to 'SelectItemForPrint' as per the name for your ListBox.
In fact I'll repost with those changes - just to make it easier for you to copy and paste it. It's clearly Mary's code though (that way - if it all goes wrong it's all her fault. J/K it's good code - I checked).

Private Sub PrintSelectedItemsReport_OnClick()
Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.SelectItemForPrint.ItemsSelected
       strWhere = strWhere & Me.SelectItemForPrint.ItemData(ValSelect) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
 
   strWhere = strWhere & ")"
End Sub

Let us know how you get on.

[MMcCarthy]

Just one thing if the bound column is column 4 then you will need to alter as follows:

Private Sub PrintSelectedItemsReport_OnClick()
Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.SelectItemForPrint.ItemsSelected
       strWhere = strWhere & Me.SelectItemForPrint.ItemData(ValSelect, 3) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
 
   strWhere = strWhere & ")"
End Sub

I think this is the correct syntax. If it doesn't work let me know and I'll check.

Mary

[NeoPa]

Now that one doesn't pass my checking :(.
The .ItemData collection (or is it an array - Property anyway) returns only the bound column.
The .Column gets more complicated but that's not required anyway so ...

[ljungers]

So far I think I'm on the right track but think I had better check before proceeding.

The last form that is used contains the list box named "SelectPrintItems" and button named "PrintSelectedItems" and in the code section I have placed the following:

Private Sub PrintSelectedItems()
Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.SelectPrintItems.ItemsSelected
       strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
 
   strWhere = strWhere & ")"
 
End Sub

Do I need to add this line to the above? strWhere = "([rec_no] In(1,57,2394)"

Do I now update the properties of the button, OnClick and give the name name "PrintSelectedItems" ?
Where do I give the name of the report to run that is to use this where statement? "Trans_memo_selected_Report"

[MMcCarthy]

Now that one doesn't pass my checking :(.
The .ItemData collection (or is it an array - Property anyway) returns only the bound column.
The .Column gets more complicated but that's not required anyway so ...

Sorry Adrian

I've been working too hard. Doh!

Mary

[MMcCarthy]

Do I need to add this line to the above? strWhere = "([rec_no] In(1,57,2394)"

No, this line is being created by the above code

Do I now update the properties of the button, OnClick and give the name name "PrintSelectedItems" ?

No, this has to be [Event Procedure]

Where do I give the name of the report to run that is to use this where statement? "Trans_memo_selected_Report"

Add the line

DoCmd.OpenReport "Trans_memo_selected_Report", , , strWhere

to then end of the code

Mary

[ljungers]

The bound column # 4 is the rec_no. That is the primary Key of this table. It can be used by the report to fetch the complete record if needed.

columns 1-3 last_name, first_name and med_rec_no do not have to be passed if that is a problem.

[MMcCarthy]

The bound column # 4 is the rec_no. That is the primary Key of this table. It can be used by the report to fetch the complete record if needed.

columns 1-3 last_name, first_name and med_rec_no do not have to be passed if that is a problem.

That's fine. Adrians last version of my code should work for you. Ignore my revision which wasn't necessary

Mary

[NeoPa]

The bound column # 4 is the rec_no. That is the primary Key of this table. It can be used by the report to fetch the complete record if needed.

columns 1-3 last_name, first_name and med_rec_no do not have to be passed if that is a problem.

The RecordSource of the report is adjusted automatically by the .OpenReport() function call. Nothing needs to be 'passed to the report' as such. That parameter will have the desired effect on its own.

[NeoPa]

Including Mary's last addition (the DoCmd.OpenReport line) you should actually be there.
Give it a try and let us know how well it works.

[ljungers]

OK, I'm able to click the button and I get the foollowing error:
"Method or Data member not found"
here is the final code.

Private Sub PrintSelectedItems_Click()
 
Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.SelectedPrintItems.ItemsSelected
       strWhere = strWhere & Me.SelectedPrintItems.ItemData(valSelect) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
 
   strWhere = strWhere & ")"
 
DoCmd.OpenReport "Trans_memo_selected_Report", , , strWhere
 
End Sub

I also change the OnClick to Event Procedure on the button

[NeoPa]

Which line is highlighted in yellow when the error message comes up?

[ljungers]

The 1st line Private Sub is highlighted in yellow and I notice that .SelectedPrintItems in the For each line is highlighted in a black backgroung as well

[ljungers]

This may help in case something may of got lost along the way.
list box name = SelectPrintItems (3 cols display, 4th hidden 'rec_no")
list box bound to col 4

Button name = PrintSelectedItems (OnClick = Event Procedure)

Report = trans_memo_selected_report

Is the correct "Private Sub PrintSelectedItems_Click()" correct. Access created the name and End Sub auto and all I did was paste the code given between the two lines, or should the "Private Sub ~~~~" be changed to match the button name?

[ljungers]

Thought it was going to work but I get the following:
Run-time error '3075':
Syntax error (missing operator) in query expression '([rec_no]In(,,,))',

with End Debug buttons ----- pressing Debug shows the code for

Private Sub PrintSelectedItems_Click()
 
Dim valSelect As Variant
Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.SelectPrintItems.ItemsSelected
       strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
 
   strWhere = strWhere & ")"
 
DoCmd.OpenReport "trans_memo_selected_report", , , strWhere
End Sub

The line that is highlighted in yellow is the last line, the DoCmd

Will check first thing in AM. Thanks

[NeoPa]

This may help in case something may of got lost along the way.
list box name = SelectPrintItems (3 cols display, 4th hidden 'rec_no")
list box bound to col 4

Button name = PrintSelectedItems (OnClick = Event Procedure)

Report = trans_memo_selected_report

Is the correct "Private Sub PrintSelectedItems_Click()" correct. Access created the name and End Sub auto and all I did was paste the code given between the two lines, or should the "Private Sub ~~~~" be changed to match the button name?

The code was written to match a different name you posted earlier (Post #42)?!?
Then you changed the name (Post #66). In the mean time you changed your code (neither Mary nor I posted the version you had problems with - post #63).
Assuming a name of 'SelectPrintItems' for the ListBox control :

Private Sub PrintSelectedItems_Click()
   Dim valSelect As Variant
   Dim strWhere As String
 
   strWhere = "[rec_no] In ("
   For Each valSelect In Me.SelectPrintItems.ItemsSelected
       strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
   Next valSelect
 
   strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
   strWhere = strWhere & ")"
 
   DoCmd.OpenReport "Trans_memo_selected_Report", , , strWhere
End Sub

Before you try to run the code you should (always) compile it. This is not necessary for the code to run but it is very helpful in ensuring you find and fix any basic compilation errors as early as possible.

[NeoPa]

Larry,

Although the debug marker stops on the DoCmd.OpenReport line, your problem is actually in the loop where it builds up the strWhere string.
I can't see where for now though. I will need to look at it in more detail.
That is to say, I have looked and it looks fine.
I'll try to check it out in more detail over lunch.

[NeoPa]

Larry, I've just created a whole new test database to test this code. It's set up to match your situation as closely as possible (names of controls are different) and the resultant string is perfect. It works exactly as anticipated.
Your code is working except for the fact that it's not picking up any of the values from the ListBox.
Do you know how to debug?

[NeoPa]

Can you post the following properties of your ListBox (SelectPrintItems) please :
Row Source Type
Row Source - If a QueryDef (saved query) then post in the SQL of the QueryDef as well as the name.
Column Count
Column Widths
Bound Column
Multi Select

[ljungers]

Morning, After a nice nights rest I'm ready with a clear head to finish this :)
Sorry about the name changes. I now know the importance of naming in properties. Was trying to make some sence as to functionality with the names.
As for the code, I got that from one of the post # 53, 54 or 56 not sure which one.

Not sure how to compile, as to what icon I click, and correct steps to follow. I'm sure that is something I need to look into in order to put this into production.

If I'm following the logic correctly, is this what we are trying to accomplish:

1) Results from 1st query are made available in list box. "SelectPrintItems"
2) Desired items are selected (click, multi allowed) in the list box.
3) When all desired rows have been selected for report printing the button is clicked. "PrintSelectedItems"
4) Because the properties of the button have OnClick=Event Procedure set, Access will look for and run "PrintSelectedItems_Click" VBA code ( I take it that the naming convention is Object Name, _ and the type action that occured, in this case a Click happened)
5) The "PrintSelectedItems_Click" will loop thru the list box looking for rows that have been selected. When a row has been selected it builds a Where SQL statement.
6) After the Where statement is built a DoCmd is run that opens the report "trans_memo_selected report" for each matching rec_no found that came from the list box and passed by the for loop in the VBA code.
7) this loop happens untill all rows that where selected have been passed and report run for each of them.

Is this the correct sequence of what's happening thus far? Would like to make sure I understand what I'm doing.

Is there a line of code I can add to the VBA code that tells Access to quite when done printing.

Do I have to make any changes in the Reports "trans_memo_selected_report"?
On the properties for the report, should I set anything in the 'data tab' as to control source or any other properties so that it knows that only the rec_no is being given to the report and it needs to get the report data from the "Translated_memo" table.

Thanks much

[ljungers]

Here is the info your requested:

Row Source Type = Table/Query (see below for the Query name and info)
Column Widths = 1";1";1";0"
Bound Column = 4
Multi Select = Extended

The following is the Query that loads the ListBox....
Trans_memo_lukup_Query : Select Query

SELECT [last_name], [first_name], [med_rec_no]
FROM Translated_memo
WHERE ((UCase(last_name) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatLastName]),'*')) And (UCase(first_name) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatFirstName]),'*')) And (UCase(med_rec_no) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatMedRecNo]),'*')))
ORDER BY [last_name], [first_name], [med_rec_no];

[NeoPa]

I haven't looked at post #72 properly yet so may need to respond to that.
Importantly though, I know why you have an empty result string. It's because you ignored post #37. You have only 3 fields in your SELECT list. Trying to set the fourth to be 'bound' will fail and accessing the data won't work.
Pick up the latest SQL for your query and it should work for you.

[NeoPa]

Morning, After a nice nights rest I'm ready with a clear head to finish this :)
Sorry about the name changes. I now know the importance of naming in properties. Was trying to make some sence as to functionality with the names.
As for the code, I got that from one of the post # 53, 54 or 56 not sure which one.

This probably explains why you've had so many problems Larry. Without wishing to be insulting (far from it) I hope you're starting to appreciate how important precision and attention to detail are in Access (most coding in fact).

Not sure how to compile, as to what icon I click, and correct steps to follow. I'm sure that is something I need to look into in order to put this into production.

From the VBA window (Alt-F11 from the main Access window) you click on the Debug menu then select the top item which will be Compile {Project} where {Project} is your project name.

If I'm following the logic correctly, is this what we are trying to accomplish:

1) Results from 1st query are made available in list box. "SelectPrintItems"
2) Desired items are selected (click, multi allowed) in the list box.
3) When all desired rows have been selected for report printing the button is clicked. "PrintSelectedItems"
4) Because the properties of the button have OnClick=Event Procedure set, Access will look for and run "PrintSelectedItems_Click" VBA code ( I take it that the naming convention is Object Name, _ and the type action that occured, in this case a Click happened)
5) The "PrintSelectedItems_Click" will loop thru the list box looking for rows that have been selected. When a row has been selected it builds a Where SQL statement.
6) After the Where statement is built a DoCmd is run that opens the report "trans_memo_selected report" for each matching rec_no found that came from the list box and passed by the for loop in the VBA code.
7) this loop happens untill all rows that where selected have been passed and report run for each of them.

Is this the correct sequence of what's happening thus far? Would like to make sure I understand what I'm doing.

If by the last (7) you mean ...and report run which includes each of them. then you are absolutely spot on for every point.

Is there a line of code I can add to the VBA code that tells Access to quite when done printing.

Try it out first.
The report command opens it in Preview mode - you wouldn't want to exit there.

Do I have to make any changes in the Reports "trans_memo_selected_report"?
On the properties for the report, should I set anything in the 'data tab' as to control source or any other properties so that it knows that only the rec_no is being given to the report and it needs to get the report data from the "Translated_memo" table.

Thanks much

Unless you've set this up wrong in the first place then - No.

[ljungers]

I have added the rec_no to the list box col #4 and when the button is clicked the report runs and prints what ever has been selected (1, 2 or more).

Thanks so much. I will compile this project as you described. If at some point in time I want to add a BACK (jump to, link to form), QUIT, CLEAR (clear out text, reset) buttons to any of this, could you tell me what past articles I might check on this site.

Thanks

Larry

[ljungers]

One last question on the compile. I was in the code view and clicked on Debug then compile "the database name" (I take it that the database name is the project name, if so mine is POMG_db1) I then get a compile error, syntax error. THis is because there is some code and items I tried before I found thescripts network. What is the shortest/easiest way to do some house keeping prior to the compile.

[MMcCarthy]

One last question on the compile. I was in the code view and clicked on Debug then compile "the database name" (I take it that the database name is the project name, if so mine is POMG_db1) I then get a compile error, syntax error. THis is because there is some code and items I tried before I found thescripts network. What is the shortest/easiest way to do some house keeping prior to the compile.

Comment out any code that you think is causing a problem. You can always remove it later.

Mary

[NeoPa]

I have added the rec_no to the list box col #4 and when the button is clicked the report runs and prints what ever has been selected (1, 2 or more).

Thanks so much. I will compile this project as you described. If at some point in time I want to add a BACK (jump to, link to form), QUIT, CLEAR (clear out text, reset) buttons to any of this, could you tell me what past articles I might check on this site.

Thanks

Larry

No ideas I'm afraid Larry.
The (Links to useful sites) sticky is a good place to start I suppose.
I would recommend copying/pasting rather than adding the one field at the end. Post #37 had thoroughly checked SQL code. I have no way of knowing if that's true of your latest SQL without doing the extra (unnecessary) work of checking it through again. Your choice but I know what I'd do ;).
Housekeeping - Commenting out the code should work to defer the decision. If it's code you know you don't want then simply delete it.

[ljungers]

Well all is ok as to the functionality of this project. It compiled OK after I commented out the code. When I try to run compile again, the compile is greyed out, not allowing a recompile!

Last but least, do I just copy the forms, query, macro, and report to the actual machine and run it. Do I need to create a menu, I have heard the term switch board? Just want 1st form to flow to the second form (not showing a query code screen for a couple of seconds) between them. I take it that the second form should close after printing in a real production enviorment?

Thanks for the help and on with clean up.

Larry

[NeoPa]

Is this the second form I told you you don't need about 50 posts ago?
The answer should be in this thread already.

[MMcCarthy]

Well all is ok as to the functionality of this project. It compiled OK after I commented out the code. When I try to run compile again, the compile is greyed out, not allowing a recompile!

Last but least, do I just copy the forms, query, macro, and report to the actual machine and run it. Do I need to create a menu, I have heard the term switch board? Just want 1st form to flow to the second form (not showing a query code screen for a couple of seconds) between them. I take it that the second form should close after printing in a real production enviorment?

Thanks for the help and on with clean up.

Larry

If you just want the first form to flow to the second form then you can set the first form as the startup form. Click tools - startup and the startup options window will open. You should also remove and check mark from the Show Database Window.

Mary