[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to use VB string to populate a report

Posted on 2011-02-18
15
Medium Priority
?
444 Views
Last Modified: 2012-05-11
Hi,

I am down to my last command button function and my form will be complete :)

I have a command button that filters a sub_form based on the value from a combo box.  The value in VB for that "Private Sub" is "str_FULL_SQLString_Single".

I have also created a report from the sub form.

I am trying to program a button reading:
---docmd.openreport "rpt_Print_Single",acviewpreview, wherecondition:=str_full_string_single

But when the report comes up, it is not using the filter setup in the string.  

Private Sub cmd_Preview_Report_Click()

output:
        DoCmd.OpenReport "rpt_test_print", acViewPreview, , wherecondition:=str_FULL_SQLString_Single

End Sub


My thinking is that it's not using the string because the string is created under a different sub...  
Thoughts?
Brian
0
Comment
Question by:KollBrian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34928866
post the content of  the string "str_FULL_SQLString_Single"


you can get it by adding this line before opening the report

Private Sub cmd_Preview_Report_Click()

output:

debug.print  str_FULL_SQLString_Single



        DoCmd.OpenReport "rpt_test_print", acViewPreview, , wherecondition:=str_FULL_SQLString_Single

End Sub
0
 

Author Comment

by:KollBrian
ID: 34929095
Hi Capricorn,

Same result, the report displays but has all the data.

When I run the "single search" command button, the SQL verbage is:
SELECT tbl_Box_Detail.[Box_ID], tbl_Box_Detail.[Pallet_ID], tbl_Box_Detail.[Part_ID], tbl_Box_Detail.[Quantity], IIf(([tbl_Box_Detail]![Pallet_ID]='' Or [tbl_Box_Detail]![Pallet_ID] Is Null),[tbl_Box_Detail]![Venue_Ref],[tbl_Pallet_Detail]![Venue_Ref]) AS Venue_Ref, tbl_box_detail.[box_req], tbl_pallet_detail.[pallet_req] FROM tbl_Pallet_Detail RIGHT JOIN tbl_Box_Detail ON tbl_Pallet_Detail.Pallet_ID = tbl_Box_Detail.Pallet_ID WHERE part_id = 'Part #1'

Well the very last part after the Where is variable but you get the picture.  But when I refer to this string in VB as the string name (str_full_sqlstring_single) in the print command like you have in your sample all i get is the report, not a filtered report...

I also cleared the Immediate window on the VB screen when I ran your sample and nothing populated... not sure if thats related to the report filter or not.
Brian
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34929224
what is the RecordSource of the report..
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:KollBrian
ID: 34929394
Hi Capricorn,

The record source for the report is the same as the source for the sub-form:

qry_box_to_pallet_print

So, the main "search" form has the sub-form imbedded into it.  When you use one of the 4 combo boxes to specify the value you want, the command buttons then goto work and create the string you saw above to alter the view of the sub-form to only show the requested info.

Brian
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34929414
are you going to use all the 4 combo boxes to filter the report?
0
 

Author Comment

by:KollBrian
ID: 34929718
Yes and no, I have 2 command buttons, one is a single filter/search and one does a specific double (only 2 of the 4 combos).

I figured once I got the printing to work for the single, duplicating that for the double wouldnt be toooooooooo hard :)

Brian
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34929729
upload a copy of the db
0
 

Author Comment

by:KollBrian
ID: 34929854
ok :)

there is no real data yet, as I continue to work out the little bugs brian-db.accdb
0
 

Author Comment

by:KollBrian
ID: 34929924
The button for single search is where i am currently tinkering, thats why it wont work at the moment, it is erroring due to the sql string being too long for the rpt where.  

So I am tinkering to figure that out at the moment.  The "preview report" button actually does work, just not with the filtered view...  That was covered a little while ago when I figured out the string was empty...  

My working theory is if I can get the single search to work with printing (and double down the way) I will duplicate single search and make it "single search print"  and just have one that filters and one that prints.  But I have to get the string to work first so... thats where I am at the moment. how to get a really long string to work in a report.
0
 

Author Comment

by:KollBrian
ID: 34930108
I am now tinkering (i.e. researching) how to use the string to update the query and then run the report.  I am thinking I was going down the wrong path...

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34930350
test this

codes added in between '***

select an item from cmb_boxid, then click
cmd_Search
brian-db.accdb
0
 

Author Comment

by:KollBrian
ID: 34942013
Hi Capricorn,

Same behavior, it still doesnt bring up the print preview.  

Brian
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34943017
brian,

did you use/test the db i uploaded?

select an item from cmb_boxid, then click
cmd_Search
0
 

Author Comment

by:KollBrian
ID: 34944157
:( oops apologies, I didnt realize the test fix was specific to the BoxID :)

Yes that works perfectly.  
what I am seeing is you created a strfilter string.  and that string replaces my "full single" string.  

what I am having a hard time figuring is the IF clause in the combo box section of the VB.  It reads like a sub if, so I did a test and copied it to all four combo boxes and it works for all but one, the venue ref combo box.

The VB doesnt really give an error it just goes to debug when trying to filter only venue ref...  My guess is that I am still running into the "too long" problem with the report but when I try to copy/paste a string that does work into a test report macro button it still says too long so I am unsure, why would venue_ref fail but the others work.?

Brian
0
 

Author Comment

by:KollBrian
ID: 34944977
It looks like I was guilty of "over thinking" this again.  I just ran "[tbl_Pallet_Detail]![Pallet_Req]=True And [tbl_Pallet_Detail]![Req_Printed]=False" in a generic macro command button and viola it does the job I need it to do. :)

Capricorn Many thanks for you help, I would still like to know why the sub-if doesnt work on that one combo box though...?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question