Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-28-2013, 12:56 PM   #16
MG101
Newly Registered User
 
Join Date: May 2013
Posts: 63
Thanks: 11
Thanked 0 Times in 0 Posts
MG101 is on a distinguished road
Re: DLookUp Function Issues

Hi Billmeye,

I am just now getting to this custom report function. I got busy with some other stuff to work on haha.

I have been able to get to filter part and was about to attempt it. I created a list box with the multi select function. I am having trouble getting the possible values to filter from my combo box (Combo1) to show up in the list box. I tried adding some code to the row source box but am kind of flustered at the moment. Would you mind giving me some advice. Otherwise I will most likely figure it out next week when I have a fresh mind.

Thanks for all your help!

MG101

MG101 is offline   Reply With Quote
Old 07-01-2013, 11:20 AM   #17
MG101
Newly Registered User
 
Join Date: May 2013
Posts: 63
Thanks: 11
Thanked 0 Times in 0 Posts
MG101 is on a distinguished road
Re: DLookUp Function Issues

Here is the filter code I tried adapting. I had troubles. Any advice?
File is also attached.



Private Sub Report_Open(Cancel As Integer)
Me.Text1.ControlSource = Forms!custom3!Combo1
Me.Text2.ControlSource = Forms!custom3!Combo2
Me.Text3.ControlSource = Forms!custom3!Combo3
Me.Caption1.Caption = Forms!custom3!Combo1
Me.Caption2.Caption = Forms!custom3!Combo2
Me.Caption3.Caption = Forms!custom3!Combo3
Me.OrderBy = Me.Text1.ControlSource & " " & Forms!custom3!Sort1 & ", " & Me.Text2.ControlSource & " " & Forms!custom3!Sort2 & ", " & Me.Text3.ControlSource & " " & Forms!custom3!Sort3
Me.OrderByOn = True

Dim Criteria As String
Dim oItem As Variant
Dim rpt As Report
' Build criteria string from selected items in list box.
Criteria = ""
icount = 0
If Me.Filter1.ItemsSelected.Count <> 0 Then
'create our filter criteria
For Each oItem In Me.Filter1.ItemsSelected
If IsNull(Criteria) Then
Criteria = "me.combo1=" & Me.Filter1.ItemData(oItem) 'this assumes a number field but if text need to add single quotes, i.e, Criteria & "[NameOfFieldToFilterOn]='" & Me.ListBox1.ItemData(oItem) & "'"
Else
Criteria = Criteria & " OR [me.combo1]=" & Me.Filter1.ItemData(oItem)
End If
Next oItem
End If
'now need to do the same for next ListBox2 etc.
If Me.Filter2.ItemsSelected.Count <> 0 Then
'add to filter criteria
For Each oItem In Me.Filter2.ItemsSelected
If IsNull(Criteria) Then
Criteria = "me.Combo2=" & Me.Filter2.ItemData(oItem) 'this assumes a number field but if text need to add single quotes, i.e, Criteria & "[NameOfFieldToFilterOn]='" & Me.ListBox2.ItemData(oItem) & "'"
Else
Criteria = Criteria & " OR me.combo2=" & Me.Filter2.ItemData(oItem)
End If
Next oItem
End If
'Now need to apply filter to report and open report
If Application.CurrentProject.AllReports("rptcustom3" ).IsLoaded = True Then DoCmd.Close acReport, "rptcustom3" 'close it in case it is open
DoCmd.OpenReport "rptcustom3", acViewDesign, , , acHidden 'opens report hidden so we can make edits
Set rpt = Reports("rptcustom3")
rpt.Filter = Criteria
rpt.FilterOnLoad = True
rpt.FilterOn = True
DoCmd.Close acReport, "rptcustom3", acSaveYes
Set rpt = Nothing
DoCmd.OpenReport "custom3", acViewPreview 'This code requires you to be using the full version of Access, does not work in runtime. Also, there is a limit to how long the Criteria can be, I'm not sure but it is a few thousand characters.
End Sub
Attached Files
File Type: zip PurgeValve(6-4-2013)Test2on7-1-13.zip (378.1 KB, 39 views)
MG101 is offline   Reply With Quote
Old 07-02-2013, 03:46 AM   #18
MG101
Newly Registered User
 
Join Date: May 2013
Posts: 63
Thanks: 11
Thanked 0 Times in 0 Posts
MG101 is on a distinguished road
Re: DLookUp Function Issues

Hi accessblaster,

I tried placing the code in the custom3 form page under a click command. But it didnt work. Is it supposed to go somewhere else?

Thanks!

MG101 is offline   Reply With Quote
Old 07-02-2013, 05:40 AM   #19
MG101
Newly Registered User
 
Join Date: May 2013
Posts: 63
Thanks: 11
Thanked 0 Times in 0 Posts
MG101 is on a distinguished road
Re: DLookUp Function Issues

OHHH OK I was confused for a second. I thought the code you posted was one you looked at and fixed to help me... I get what you were saying now. haha sorry. and thanks! Ive been wanting to know how to do that!

I will definitley use in the future!
MG101 is offline   Reply With Quote
Old 07-02-2013, 07:37 AM   #20
MG101
Newly Registered User
 
Join Date: May 2013
Posts: 63
Thanks: 11
Thanked 0 Times in 0 Posts
MG101 is on a distinguished road
Re: DLookUp Function Issues

Reference File for Billmeye:
Attached Files
File Type: zip PurgeValve(6-4-2013)Teston7-2-13.zip (390.2 KB, 42 views)
MG101 is offline   Reply With Quote
Old 07-15-2013, 07:32 AM   #21
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: DLookUp Function Issues

Here is the latest update with added combo boxes for choosing column order.
Attached Files
File Type: zip Cascading Filters Using Multi Select List Boxes.zip (91.4 KB, 46 views)
billmeye is offline   Reply With Quote
Old 07-15-2013, 10:55 AM   #22
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: DLookUp Function Issues

Fixed version.
Attached Files
File Type: zip Cascading Filters Using Multi Select List Boxes.zip (91.1 KB, 53 views)


billmeye is offline   Reply With Quote
Reply

Tags
dlookup , help request , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dlookup return issues amoghjain Forms 2 08-20-2012 05:56 PM
Issues with dlookup and saving a record bloodnfire Modules & VBA 2 05-03-2011 01:03 AM
Dlookup issues! Mikkel Queries 6 10-14-2008 03:45 PM
DLookUp in Query Issues airforceruss Queries 5 02-17-2008 03:41 PM
DLookup Issues wchernock Modules & VBA 3 07-03-2007 02:16 PM




All times are GMT -8. The time now is 06:07 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World