By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Macro Actions, sequence & passing fields

100+
P: 114
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 #1
Share this Question
Share on Google+
81 Replies


NeoPa
Expert Mod 15k+
P: 31,616
You wouldn't.
What you can do is set the RecordSource of the ComboBox to the query.
Dec 17 '06 #2

100+
P: 114
I do not find the "RecordSource" on the ComboBox properties where I need to reference the query results. I do find "row source type" and "row source". If it helps I'm using Access 2000.
What I do notice is that after I enter query data, click button, I get the second form with an empty combo box. I then change the view to datasheet and see a single column with the name I gave it in the combo box properties, a empty row with a drop down arrow available on right. Clicking the arrow I get a row below with all of field names and a single row that matches the orignal query data.

Am I over looking something.
Dec 17 '06 #3

NeoPa
Expert Mod 15k+
P: 31,616
I do not find the "RecordSource" on the ComboBox properties where I need to reference the query results. I do find "row source type" and "row source".
My bad - yes RowSource. RowSourceType should be Table/Query.
What I do notice is that after I enter query data, ...
What do you mean by Query Data?
... click button, I get the second form with an empty combo box. I then change the view to datasheet and see a single column with the name I gave it in the combo box properties, a empty row with a drop down arrow available on right. Clicking the arrow I get a row below with all of field names and a single row that matches the orignal query data.

Am I over looking something.
I'm afraid I don't really follow the rest as I don't know 'where' you are in the process.
If you could rephrase the last bit a little more carefully perhaps, I'll give what help I can.
Dec 17 '06 #4

100+
P: 114
Thanks for the reply and hope this explanation is better.

Have a LukupForm form that has 3 text boxes and one button. Entering data into the text boxes will narrow down the query, and in my test case I enter 2 fields to narrow down to 3 records/rows that I'm using for testing.

When the button is clicked the OnClick calls a Macro named LukupMacro that runs a OpenQuery using the data from the text boxes in the LukupForm form. At this point all is working.The results can be 1 row or many rows, but this test returns 3 rows.

In the Macro I added another action "OpenForm" to the action list after the "OpenQuery". The OpenForm action uses a form called "SelectForm" that has a combo box and a button. The combo box on this form should contain the results (3 rows) from the OpenQuery, but for now the combo box is blank.

What should happen next is that one or more of the rows in the combo box can be selected, and thoses rows passed when the Button is clicked. Using OnClick will call SelectedReport, passing the selected row(s) in that combo box to SelectReport. In this case I want to select 1 of the 3 rows shown so that only 1 row will be printed on the report.

Just trying to use 2 steps (forms) to narrow down the amount of rows to be printed in a report.
Dec 17 '06 #5

NeoPa
Expert Mod 15k+
P: 31,616
Firstly, if you don't understand something I post - let me know.
I can't see what you can see so I rely on clear communication via your posts.
It seems you didn't understand my first post (Am I right)?

Now let's see if we can sort out what you actually need (rather than what you think you may need).
Let me see if I understand correctly :
You have a table with a (potentially large) number of records in it.
You want to restrict the records shown in your report in two fundamental ways :
1. Enter some values in some text boxes which must match the restricted records shown.
2. When these records are selected you want the option of choosing one or more of these records to be included in your report.

Please confirm that I am on the right track here before we go on (I need your feedback)?
Dec 17 '06 #6

100+
P: 114
Are you sure that you don't read minds, but YES that's what I want to accomplish in a nut shell. Thank you...
Dec 18 '06 #7

NeoPa
Expert Mod 15k+
P: 31,616
I'm assuming the answer to the first question (in post #6) was Yes then.
That will be included in the answer but you need to let me know if any of it doesn't make sense (just as I did with you - ok).
Starting at the end, there are two ways of allowing selections of multiple items that I know of using forms :
1. Updating each record that you want to include in your selection.
This is messy and involves adding a special field for just this purpose. As I say - messy.
2. A ListBox control (with MultiSelect set to either Simple or Extended).

To be honest, this is quite complex either way.
I see no way of executing this logic using macros. VBA code is required to get this to work.

Back to the start :
You don't need to run the query using the OpenQuery action.
The query, or more correctly a SQL derivative of the query, needs to be modified and applied to the RowSource property of the eventual ListBox (We'll call it lstSelRecs).
We may get away with not fiddling the SQL depending on the Query.
Perhaps we should stop here and post the current SQL of the query (and the name). Also the table MetaData would be a very good idea. Can you get that information posted for me please.
Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
When we have a ListBox which shows the restricted results from the three TextBoxes, then we can consider the harder work of running the report showing just the individual items selected within the ListBox.
Dec 18 '06 #8

100+
P: 114
Here is the SQL of the query. Not sure how to get the TableMeta data.

SELECT Translated_memo.last_name, Translated_memo.first_name, Translated_memo.med_rec_no
FROM Translated_memo
WHERE (((Translated_memo.last_name)=[Forms]![Trans_memo_lukup_Form]![WhatLastName]) AND ((Translated_memo.first_name)=[Forms]![Trans_memo_lukup_Form]![WhatFirstName]) AND ((Translated_memo.med_rec_no)=[Forms]![Trans_memo_lukup_Form]![WhatMedRecNo])) OR ((([Forms]![Trans_memo_lukup_Form]![WhatLastName]) Is Null) AND (([Forms]![Trans_memo_lukup_Form]![WhatFirstName]) Is Null) AND (([Forms]![Trans_memo_lukup_Form]![WhatMedRecNo]) Is Null)) OR (((Translated_memo.last_name)=[Forms]![Trans_memo_lukup_Form]![WhatLastName]) AND ((Translated_memo.med_rec_no)=[Forms]![Trans_memo_lukup_Form]![WhatMedRecNo])) OR (((Translated_memo.med_rec_no)=[Forms]![Trans_memo_lukup_Form]![WhatMedRecNo])) OR (((Translated_memo.last_name)=[Forms]![Trans_memo_lukup_Form]![WhatLastName]) AND ((Translated_memo.first_name)=[Forms]![Trans_memo_lukup_Form]![WhatFirstName])) OR (((Translated_memo.first_name)=[Forms]![Trans_memo_lukup_Form]![WhatFirstName])) OR (((Translated_memo.last_name)=[Forms]![Trans_memo_lukup_Form]![WhatLastName]))
ORDER BY Translated_memo.last_name, Translated_memo.first_name, Translated_memo.med_rec_no;

listed above is The SQL for the three fields involved. There is a rec-no field that is the PK of this table.
Dec 18 '06 #9

NeoPa
Expert Mod 15k+
P: 31,616
I may be a while responding to this.
It's party season and I'm not around too much.
I'll look at this when I can.
Dec 18 '06 #10

NeoPa
Expert Mod 15k+
P: 31,616
To get the MetaData open the table in design view and all the information is there.
If it is a very large record layout then simply include the relevant fields - all those filtered on and shown.
Dec 18 '06 #11

NeoPa
Expert Mod 15k+
P: 31,616
Can you try the following SQL code in your query (save/rename the other query first).
At this stage we are just trying to simplify things. The Nz() function returns the first parameter in the parentheses unless it has a value of Null, in which case it returns the second parameter.
Expand|Select|Wrap|Line Numbers
  1. SELECT last_name, first_name, med_rec_no
  2. FROM Translated_memo
  3. WHERE ((last_name Like Nz([Forms]![Trans_memo_lukup_Form].[WhatLastName],'*'))
  4.   AND (first_name Like Nz([Forms]![Trans_memo_lukup_Form].[WhatFirstName],'*'))
  5.   AND (med_rec_no Like Nz([Forms]![Trans_memo_lukup_Form].[WhatMedRecNo],'*')))
  6. ORDER BY last_name, first_name, med_rec_no;
Dec 18 '06 #12

100+
P: 114
Morning,
I did copy/paste, the select worked OK and the results shown in Datasheet view show the filtered rows for columns from the Select (Trans_memo_lukup_Query: Select Query).

I now know what you meant by MetaData. How would a person either print or copy / paste that information?

The Datasheet that the query produces, is that not available for use in a form?
Dec 18 '06 #13

100+
P: 114
Is there a (initupper) inital upper case that I could use for that WhatFirstName and WhatLastName. I realised just now that when I tried to run a test using my first name, no hits where found. But when I type Larry I get like 60 rows showing.
Dec 18 '06 #14

NeoPa
Expert Mod 15k+
P: 31,616
Is there a (initupper) inital upper case that I could use for that WhatFirstName and WhatLastName. I realised just now that when I tried to run a test using my first name, no hits where found. But when I type Larry I get like 60 rows showing.
There is a function somewhere which I will dig up if required.
However, it is more usual to set both sides of the comparison to a set case (Upper=UCase() or Lower=LCase()).
NB It is important to set both sides - assumptions don't serve you well here.
Dec 18 '06 #15

NeoPa
Expert Mod 15k+
P: 31,616
Morning,
I did copy/paste, the select worked OK and the results shown in Datasheet view show the filtered rows for columns from the Select (Trans_memo_lukup_Query: Select Query).

I now know what you meant by MetaData. How would a person either print or copy / paste that information?

The Datasheet that the query produces, is that not available for use in a form?
I'm afraid you'll have to type it out.
If there is too much there then simply include the relevant items. There is a way to produce this in VBA code but more complicated than it's worth to try to explain.
Dec 18 '06 #16

NeoPa
Expert Mod 15k+
P: 31,616
Is there a (initupper) inital upper case that I could use for that WhatFirstName and WhatLastName. I realised just now that when I tried to run a test using my first name, no hits where found. But when I type Larry I get like 60 rows showing.
I wouldn't recommend usage of this for your requirements but the function you were asking about is StrConv().
StrConv Function


Returns a Variant (String) converted as specified.

Syntax

StrConv(string, conversion, LCID)

The StrConv function syntax has these named arguments:

Part Description
string Required. String expression to be converted.
conversion Required. Integer. The sum of values specifying the type of conversion to perform.
LCID Optional. The LocaleID, if different than the system LocaleID. (The system LocaleID is the default.)The conversion argument settings are:

Constant Value Description
vbUpperCase 1 Converts the string to uppercase characters.
vbLowerCase 2 Converts the string to lowercase characters.
vbProperCase 3 Converts the first letter of every word in string to uppercase.
vbWide* 4* Converts narrow (single-byte) characters in string to wide (double-byte) characters.
vbNarrow* 8* Converts wide (double-byte) characters in string to narrow (single-byte) characters.
vbKatakana** 16** Converts Hiragana characters in string to Katakana characters.
vbHiragana** 32** Converts Katakana characters in string to Hiragana characters.
vbUnicode 64 Converts the string to Unicode using the default code page of the system. (Not available on the Macintosh.)
vbFromUnicode 128 Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)
Dec 18 '06 #17

100+
P: 114
There is a function somewhere which I will dig up if required.
However, it is more usual to set both sides of the comparison to a set case (Upper=UCase() or Lower=LCase()).
NB It is important to set both sides - assumptions don't serve you well here.
Thanks, I deffinately could use that, I know I will be asked to do that shortly after I get this working. Never ending ;-)
Dec 18 '06 #18

100+
P: 114
I'm afraid you'll have to type it out.
If there is too much there then simply include the relevant items. There is a way to produce this in VBA code but more complicated than it's worth to try to explain.
here is the metadata that is in the table. Hope I provided enought.

Expand|Select|Wrap|Line Numbers
  1. Field Name --- Data Type -- Description
  2. rec_no - Number - Record Number Primary Key, generated prior to import
  3. seq_no - Number - sequence number of the transcription notes memo
  4. med_rec_no - text - Medical Record Number, can be blank if not assigned yet
  5. first_name - text - Patient First Name
  6. last_name - text - Patient Last Name
  7. full_name - text - Patient Full Name (First middle Last)
  8. trans_memo - memo - Transcription notes Memo data, notes from visit, used to print body of report
rec_no and seq_no have regular General for PK & index with lookup for both just "text"

med_rec_no in general tab show key index information (length & index information), the "Lookup" tab shows display control = Combo Box, Row Source Type = Table/Query, Bound Column = 1, Column Count = 1, Column Heads = No, List Rows = 8, List Width = Auto, Limit to List = No

first_name, last_name, full_name all just show the general as to length and key info, the 'Lookup" tab only shows text box for all three.

trans_memo general = No and "Lookup" tab is blank
Dec 18 '06 #19

100+
P: 114
There is a function somewhere which I will dig up if required.
However, it is more usual to set both sides of the comparison to a set case (Upper=UCase() or Lower=LCase()).
NB It is important to set both sides - assumptions don't serve you well here.
Would I place the StrConv(WhatLastName, 3) in "Event Tab" under properties and if so what event would that be?
Dec 18 '06 #20

NeoPa
Expert Mod 15k+
P: 31,616
No.
You need to update the SQL I gave earlier with UCase() or LCase() on both sides of each 'Like'.
StrConv() is not a good function to use in this context.
Dec 18 '06 #21

NeoPa
Expert Mod 15k+
P: 31,616
When you've done that (You try it first - if you have problems then I'll help) then we are nearly ready to look at using the ListBox data to filter the report.
What data are you showing in your report?
Is it all from the Translated_memo table or is it based on an, as yet unseen, query?
Dec 18 '06 #22

100+
P: 114
When you've done that (You try it first - if you have problems then I'll help) then we are nearly ready to look at using the ListBox data to filter the report.
What data are you showing in your report?
Is it all from the Translated_memo table or is it based on an, as yet unseen, query?
I added the UCase to the query, I get no errors but no data is selected. Did I place the UCase in the wrong place or use the wrong format?

Every Form & Query is based on this one table only for now, no plans to use any other tables yet.
Dec 18 '06 #23

100+
P: 114
I added the UCase to the query, I get no errors but no data is selected. Did I place the UCase in the wrong place or use the wrong format?

Every Form & Query is based on this one table only for now, no plans to use any other tables yet.
Sorry I did not paste the select, here it is.

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

100+
P: 114
NeoPa if I haven't said it yet, Thanks for all of your help. Where I got the idea of what I was trying to accomplish was two MS articles 209645 and either technique at the end for printing, articles 209976 & 211190 from http://support.microsoft.com/kb/209645.

Thanks again for your help and hope we finish this up today. I need some rest <g>
Dec 18 '06 #25

NeoPa
Expert Mod 15k+
P: 31,616
Sorry I did not paste the select, here it is.

SELECT [UCase(last_name)], [UCase(first_name)], [med_rec_no]
FROM Translated_memo
WHERE ((last_name Like Nz([Forms]![Trans_memo_lukup_Form].[UCase(WhatLastName)],'*')) And (first_name Like Nz([Forms]![Trans_memo_lukup_Form].[UCase(WhatFirstName)],'*')) And (med_rec_no Like Nz([Forms]![Trans_memo_lukup_Form].[WhatMedRecNo],'*')))
ORDER BY [last_name], [first_name], [med_rec_no];
Try this instead :
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]),'*'))
  4.   AND (UCase(first_name) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatFirstName]),'*'))
  5.   AND (UCase(med_rec_no) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatMedRecNo]),'*')))
  6. ORDER BY [last_name], [first_name], [med_rec_no]
Look at where the UCase() is used. This may help you to understand.
I know it looks Greek now - but believe me, you will pick it up :).
Dec 18 '06 #26

NeoPa
Expert Mod 15k+
P: 31,616
NeoPa if I haven't said it yet, Thanks for all of your help. Where I got the idea of what I was trying to accomplish was two MS articles 209645 and either technique at the end for printing, articles 209976 & 211190 from http://support.microsoft.com/kb/209645.

Thanks again for your help and hope we finish this up today. I need some rest <g>
I'm out for the evening shortly so that's unlikely :(.
You need some rest !?!
I'll catch you tomorrow ;).
On the bright side - You're keeping my post-count up :D.
Dec 18 '06 #27

100+
P: 114
Thanks, that worked. Feel like a kid with a new toy. It did look a bit strange but it sort of made sence.

The datasheet shows the selection that are desired to be in the combo box now. Guess on to the next step.

Thanks
Dec 18 '06 #28

NeoPa
Expert Mod 15k+
P: 31,616
Not a ComboBox.
Only ListBoxes have the multi-select option I'm afraid.
Dec 18 '06 #29

100+
P: 114
Not a ComboBox.
Only ListBoxes have the multi-select option I'm afraid.
I'll change it to a list box, is there anything that I can play with and possable get part of it working or showing up between now and tomorrow AM.
Dec 18 '06 #30

NeoPa
Expert Mod 15k+
P: 31,616
Signing off now.
No, I don't think so.
Give it a look over and check out what you do understand, but the rest is mainly VBA and a little complex.
We'll see tomorrow.

You can post what you're hoping to display in your report though.
Dec 18 '06 #31

100+
P: 114
Just changed the Combo Box to a List Box and WOW, the query returns all rows that match the filter in the query select. Just need to figure out how to select them. When clicking on a row nothing happens.
Dec 18 '06 #32

MMcCarthy
Expert Mod 10K+
P: 14,534
Just changed the Combo Box to a List Box and WOW, the query returns all rows that match the filter in the query select. Just need to figure out how to select them. When clicking on a row nothing happens.
In the listbox properties under the other tab change the Multi Select property to simple. This will allow you to select more than one value by holding down the control button. You will then need a command button (cmdWhatever) to put the code behind to process the selections.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdWhatever_Click()
  2. Dim valSelect As Variant
  3.  
  4.    For Each valSelect In Me.ListboxName.ItemsSelected
  5.       'your actions here, to refer to each item use Me.ListboxName.ItemData(valSelect)
  6.    Next valSelect
  7.  
  8. End Sub
  9.  
Mary
Dec 19 '06 #33

NeoPa
Expert Mod 15k+
P: 31,616
I suspect the button you need will be the cmdReport (or such like) button.
You will need to access the data from your ListBox (selected items) to restrict the records shown.
This will depend on exactly which data you wish to use and have available in your ListBox.
Can you say what those are?
The SQL of the dataset for the ListBox is the first thing required of course.
Dec 19 '06 #34

100+
P: 114
Morning! I made sure that the properties of the List Box where set. After a couple of changes I am able to see all of the filtered data in the list box, with 3 cols showing (last_name, first_name, med_rec_no) and the 4th is hidden (rec_no Primary Key).

I'm able to click and do the ctrl and shift clicks and select data ranges. Then click the Button called Print that calls OpenQuery to filter the selected data.
Here is the trans_memo_select_Query:

SELECT [Translated_memo].[last_name], [Translated_memo].[first_name], [Translated_memo].[med_rec_no], [Translated_memo].[rec_no]
FROM Translated_memo
WHERE ((([Translated_memo].[last_name])=[Forms]![Trans_memo_Select_Form]!SelectLastName) And (([Translated_memo].[first_name])=[Forms]![Trans_memo_Select_Form]!SelectFirstName) And (([Translated_memo].[med_rec_no])=[Forms]![Trans_memo_Select_Form]!SelectMedRecNo)) Or ((([Forms]![Trans_memo_Select_Form]!SelectLastName) Is Null) And (([Forms]![Trans_memo_Select_Form]!SelectFirstName) Is Null) And (([Forms]![Trans_memo_Select_Form]!SelectMedRecNo) Is Null)) Or ((([Translated_memo].[last_name])=[Forms]![Trans_memo_Select_Form]!SelectLastName) And (([Translated_memo].[med_rec_no])=[Forms]![Trans_memo_Select_Form]!SelectMedRecNo)) Or ((([Translated_memo].[med_rec_no])=[Forms]![Trans_memo_Select_Form]!SelectMedRecNo)) Or ((([Translated_memo].[last_name])=[Forms]![Trans_memo_Select_Form]!SelectLastName) And (([Translated_memo].[first_name])=[Forms]![Trans_memo_Select_Form]!SelectFirstName)) Or ((([Translated_memo].[first_name])=[Forms]![Trans_memo_Select_Form]!SelectFirstName)) Or ((([Translated_memo].[last_name])=[Forms]![Trans_memo_Select_Form]!SelectLastName))
ORDER BY [Translated_memo].[last_name], [Translated_memo].[first_name], [Translated_memo].[med_rec_no], [Translated_memo].[rec_no];

At the moment I get a popup asking for "enter parameter value" titled trans_memo and either way (blank or a valid parameter) I get the next step that is OpenReport "trans_memo_selected_Report" and at the present I get a page for all 29230 records in my table.

I think the reason the selected items in the list box are not being selected by the query (there in an array I think or something like that). Is this where I need a VBA routine prior to the report?
Dec 19 '06 #35

NeoPa
Expert Mod 15k+
P: 31,616
Did we not get past the stage of very messy SQL for this query (see post #26)?
It's a bit of a surprise seeing this again. I'd rather be going forward on a more solid foundation. If there are problems with it we can address those, but this isn't something I can fix again.
Dec 19 '06 #36

NeoPa
Expert Mod 15k+
P: 31,616
Adding [rec_no] is easy enough (That was one of the things I was going to get to later) and should leave you with the following :
Expand|Select|Wrap|Line Numbers
  1. SELECT [last_name],[first_name],[med_rec_no],[rec_no]
  2. FROM Translated_memo
  3. WHERE ((UCase(last_name) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatLastName]),'*'))
  4.   AND (UCase(first_name) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatFirstName]),'*'))
  5.   AND (UCase(med_rec_no) Like Nz(UCase([Forms]![Trans_memo_lukup_Form].[WhatMedRecNo]),'*')))
  6. ORDER BY [last_name],[first_name],[med_rec_no]
Please try this and let me know if you get any problems.
Dec 19 '06 #37

NeoPa
Expert Mod 15k+
P: 31,616
You can update the trans_memo_select_Query with this SQL if you like but I would test it out first.
Let me know if you need instructions on either updating the QueryDef with some replacement SQL or updating the ListBox to use a SQL string instead of a QueryDef object.
Dec 19 '06 #38

100+
P: 114
Hello NeoPa,
Yes the query is working. Sorry about the way I posted that last message. That is a query that is run when the Button on the second form that contains the ListBox is clicked. I was playing around thinking that a query was needed before the Report.

I am at the place where I need to get the selected items from the ListBox and use thoses selected items (first_name, last_name, med_rec_no, rec_id) in the filtered report. The rec_id is the Primary Key of the trans_memo table and could be used to fetch the trans_memo row that needs to be printed.

So I'm at the last phase of this project.
Dec 19 '06 #39

100+
P: 114
What I need to know is what do I do next after the button "Print" is clicked on the form where the ListBox is.

What should happen is the information that was selected on the list box should be used to run a report (1 page per selected row in ListBox) only if 1 or more rows where selected in the list box.
Dec 19 '06 #40

100+
P: 114
All is working Great as to 1st form using what ever data is entered and the OnClick runs the query and OpenForm uses the query results to load the ListBox. All of that works great.

I saw in Post #33 something to do with some code to get the selected items from the ListBox. Where and how is that code placed and can the report be called from that point?
Dec 19 '06 #41

100+
P: 114
With the Trans_memo_select_Form in design view I have the List Box name=SelectItemForPrint and a Button name=PrintSelectedItemsReport with no OnClick assigned.

Is the OnClick where I would place the name of the VBA routine I would call to get the values from the List Box. Would this routine place the values in a temp area for the final step of the report.
Dec 19 '06 #42

100+
P: 114
Down to the last strech of this project. NeoPa was Mary's post # 33 talking about what I saw in Filter Report on multiple selections in a list box post #4. If so could you tell me where I should place the code and where the command to launch that code would be placed.
Dec 19 '06 #43

NeoPa
Expert Mod 15k+
P: 31,616
All is working Great as to 1st form using what ever data is entered and the OnClick runs the query and OpenForm uses the query results to load the ListBox. All of that works great.
That's not what should be happening.
The 'Query' should simply be the RowSource of the ListBox.
In the AfterUpdate event of each of the three TextBoxes there should be some code that issues a SelectItemForPrint.ReQuery. So, whenever any of the selections change, the ListBox is redone to reflect the new criteria. There should be no need for a button to do this - and certainly no point in issuing an instruction to open the query itself. That could have no effect on anything.
I saw in Post #33 something to do with some code to get the selected items from the ListBox. Where and how is that code placed and can the report be called from that point?
That is where the report would be opened from.
I'll go into more detail after I've looked at and responded to your other posts.

It's hard to keep track of where you are if you go off at tangents. I know you're learning and having new, different ideas, which is good, but spare a thought for poor old NeoPa trying to keep up at a distance, with only your disparate posts for clues.
Dec 19 '06 #44

NeoPa
Expert Mod 15k+
P: 31,616
With the Trans_memo_select_Form in design view I have the List Box name=SelectItemForPrint and a Button name=PrintSelectedItemsReport with no OnClick assigned.

Is the OnClick where I would place the name of the VBA routine I would call to get the values from the List Box.
Sort of.
You could put the VBA code itself in here if required.
Would this routine place the values in a temp area for the final step of the report.
Sort of.
In fact, I would consider simply setting the WhereCondition for the report.
One of the parameters for the DoCmd.OpenReport function is WhereCondition.

OpenReport Method
See AlsoApplies ToExampleSpecificsThe OpenReport method carries out the OpenReport action in Visual Basic.

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
expression Required. An expression that returns a DoCmd object.

ReportName Required Variant. A string expression that's the valid name of a report in the current database. If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name, first in the library database, then in the current database.

View Optional AcView. The view to apply to the specified report.

AcView can be one of these AcView constants.
acViewDesign
acViewNormal default Prints the report immediately.
acViewPivotChart Not supported.
acViewPivotTable Not supported.
acViewPreview

FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

WindowMode Optional AcWindowMode. The mode in which the form opens.

AcWindowMode can be one of these AcWindowMode constants.
acDialog The form's Modal and PopUp properties are set to Yes.
acHidden The form is hidden.
acIcon The form opens minimized in the Windows taskbar.
acWindowNormal default The form is in the mode set by its properties.

OpenArgs Optional Variant. Sets the OpenArgs property.
Dec 19 '06 #45

NeoPa
Expert Mod 15k+
P: 31,616
Down to the last strech of this project. NeoPa was Mary's post # 33 talking about what I saw in Filter Report on multiple selections in a list box post #4. If so could you tell me where I should place the code and where the command to launch that code would be placed.
Yes.

However, with a flexible and variable list like that which you propose, I would use the In() structure instead. I looked for a link to explain this but I found none :(. I include an example to illustrate instead.
Expand|Select|Wrap|Line Numbers
  1. strWhere = "([rec_no] In(1,57,2394)"
  2. or, if [rec_no] is a string field :
  3. strWhere = "([rec_no] In('1','57','2394')"
Essentially you enter in the parentheses a list of items to match against - any one of which being true will trigger a TRUE result. FALSE is only returned if none of the values matches.
Dec 19 '06 #46

NeoPa
Expert Mod 15k+
P: 31,616
Right, the final leg is upon us.
As in Mary's post, you need to process through the .ItemsSelected items.
These ar Variant objects containing indexes into the .Column or .ItemData collections.
The .Column can refer to Rows and Columns though, so unless you have the [rec_no] column as the BoundColumn, you'll need this.
You need to get to a stage where the strWhere string has been built up to resemble the example I had in post #46.
This looks complicated by take it one step at a time and I think you'll make it.
Let me know how you get on - remember I only know what you tell me.
Dec 19 '06 #47

100+
P: 114
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
Dec 19 '06 #48

100+
P: 114
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. Here is what I added but I think I may of not correctly done this step.

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. The report will be called for each selected row found in the list box and pass the rec_no in the built where statement.
Dec 19 '06 #49

100+
P: 114
to add the code in last post:

Private Sub PrintSelectedItemsReport()
Dim valSelect As Variant

For Each valSelect In Me.PrintSelectedItemsReport.ItemsSelected
strWhere = "([rec_no] In(1,57,2394)"
Next valSelect

End Sub
Dec 19 '06 #50

81 Replies

Post your reply

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