469,267 Members | 909 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Macro Actions, sequence & passing fields

114 100+
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.
Dec 17 '06
81 7942
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strWhere As String
  3.  
  4.    strWhere = "[FieldName] IN ("
  5.    For Each valSelect In Me.ListboxName.ItemsSelected
  6.       strWhere = strWhere & Me.ListboxName.ItemData(ValSelect) & ", "
  7.       'or for strings
  8.       'strWhere = strWhere & "'" &  Me.ListboxName.ItemData(ValSelect) & "', "
  9.    Next valSelect
  10.  
  11.    strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
  12.  
  13.  
Mary
Dec 19 '06 #51
MMcCarthy
14,534 Expert Mod 8TB
Sorry slight amendment after seeing the last post

Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strWhere As String
  3.  
  4.    strWhere = "[rec_no] In ("
  5.    For Each valSelect In Me.ListboxName.ItemsSelected
  6.        strWhere = strWhere & Me.ListboxName.ItemData(ValSelect) & ", "
  7.    Next valSelect
  8.  
  9.    strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
  10.  
  11.    strWhere = strWhere & ")"
  12.  
  13.  
Mary
Dec 19 '06 #52
NeoPa
32,171 Expert Mod 16PB
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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSelectedItemsReport_OnClick()
  2. Dim valSelect As Variant
  3. Dim strWhere As String
  4.  
  5.    strWhere = "[rec_no] In ("
  6.    For Each valSelect In Me.SelectItemForPrint.ItemsSelected
  7.        strWhere = strWhere & Me.SelectItemForPrint.ItemData(ValSelect) & ", "
  8.    Next valSelect
  9.  
  10.    strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
  11.  
  12.    strWhere = strWhere & ")"
  13. End Sub
Let us know how you get on.
Dec 20 '06 #53
MMcCarthy
14,534 Expert Mod 8TB
Just one thing if the bound column is column 4 then you will need to alter as follows:


Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSelectedItemsReport_OnClick()
  2. Dim valSelect As Variant
  3. Dim strWhere As String
  4.  
  5.    strWhere = "[rec_no] In ("
  6.    For Each valSelect In Me.SelectItemForPrint.ItemsSelected
  7.        strWhere = strWhere & Me.SelectItemForPrint.ItemData(ValSelect, 3) & ", "
  8.    Next valSelect
  9.  
  10.    strWhere = Left(strWhere, Len(strWhere)-2) 'removes last comma and space
  11.  
  12.    strWhere = strWhere & ")"
  13. End Sub
I think this is the correct syntax. If it doesn't work let me know and I'll check.

Mary
Dec 20 '06 #54
NeoPa
32,171 Expert Mod 16PB
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 ...
Dec 20 '06 #55
ljungers
114 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSelectedItems()
  2. Dim valSelect As Variant
  3. Dim strWhere As String
  4.  
  5.    strWhere = "[rec_no] In ("
  6.    For Each valSelect In Me.SelectPrintItems.ItemsSelected
  7.        strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
  8.    Next valSelect
  9.  
  10.    strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  11.  
  12.    strWhere = strWhere & ")"
  13.  
  14. 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"
Dec 20 '06 #56
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 20 '06 #57
MMcCarthy
14,534 Expert Mod 8TB

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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "Trans_memo_selected_Report", , , strWhere
to then end of the code

Mary
Dec 20 '06 #58
ljungers
114 100+
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.
Dec 20 '06 #59
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 20 '06 #60
NeoPa
32,171 Expert Mod 16PB
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.
Dec 20 '06 #61
NeoPa
32,171 Expert Mod 16PB
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.
Dec 20 '06 #62
ljungers
114 100+
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSelectedItems_Click()
  2.  
  3. Dim valSelect As Variant
  4. Dim strWhere As String
  5.  
  6.    strWhere = "[rec_no] In ("
  7.    For Each valSelect In Me.SelectedPrintItems.ItemsSelected
  8.        strWhere = strWhere & Me.SelectedPrintItems.ItemData(valSelect) & ", "
  9.    Next valSelect
  10.  
  11.    strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  12.  
  13.    strWhere = strWhere & ")"
  14.  
  15. DoCmd.OpenReport "Trans_memo_selected_Report", , , strWhere
  16.  
  17. End Sub
I also change the OnClick to Event Procedure on the button
Dec 20 '06 #63
NeoPa
32,171 Expert Mod 16PB
Which line is highlighted in yellow when the error message comes up?
Dec 20 '06 #64
ljungers
114 100+
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
Dec 20 '06 #65
ljungers
114 100+
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?
Dec 20 '06 #66
ljungers
114 100+
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSelectedItems_Click()
  2.  
  3. Dim valSelect As Variant
  4. Dim strWhere As String
  5.  
  6.    strWhere = "[rec_no] In ("
  7.    For Each valSelect In Me.SelectPrintItems.ItemsSelected
  8.        strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
  9.    Next valSelect
  10.  
  11.    strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  12.  
  13.    strWhere = strWhere & ")"
  14.  
  15. DoCmd.OpenReport "trans_memo_selected_report", , , strWhere
  16. End Sub
The line that is highlighted in yellow is the last line, the DoCmd

Will check first thing in AM. Thanks
Dec 20 '06 #67
NeoPa
32,171 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSelectedItems_Click()
  2.    Dim valSelect As Variant
  3.    Dim strWhere As String
  4.  
  5.    strWhere = "[rec_no] In ("
  6.    For Each valSelect In Me.SelectPrintItems.ItemsSelected
  7.        strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
  8.    Next valSelect
  9.  
  10.    strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  11.    strWhere = strWhere & ")"
  12.  
  13.    DoCmd.OpenReport "Trans_memo_selected_Report", , , strWhere
  14. 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.
Dec 20 '06 #68
NeoPa
32,171 Expert Mod 16PB
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.
Dec 20 '06 #69
NeoPa
32,171 Expert Mod 16PB
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?
Dec 20 '06 #70
NeoPa
32,171 Expert Mod 16PB
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
Dec 20 '06 #71
ljungers
114 100+
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
Dec 20 '06 #72
ljungers
114 100+
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [last_name], [first_name], [med_rec_no]
  2. FROM Translated_memo
  3. 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]),'*')))
  4. ORDER BY [last_name], [first_name], [med_rec_no];
Dec 20 '06 #73
NeoPa
32,171 Expert Mod 16PB
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.
Dec 20 '06 #74
NeoPa
32,171 Expert Mod 16PB
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.
Dec 20 '06 #75
ljungers
114 100+
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
Dec 20 '06 #76
ljungers
114 100+
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.
Dec 20 '06 #77
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 20 '06 #78
NeoPa
32,171 Expert Mod 16PB
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.
Dec 20 '06 #79
ljungers
114 100+
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
Dec 20 '06 #80
NeoPa
32,171 Expert Mod 16PB
Is this the second form I told you you don't need about 50 posts ago?
The answer should be in this thread already.
Dec 20 '06 #81
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 20 '06 #82

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

25 posts views Thread by Andrew Dalke | last post: by
1 post views Thread by meganrobertson22 | last post: by
13 posts views Thread by uma676 | last post: by
44 posts views Thread by Simon Morgan | last post: by
12 posts views Thread by Prabu Subroto | last post: by
6 posts views Thread by jason | last post: by
10 posts views Thread by gouqizi.lvcha | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.