Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2013, 04:40 AM   #1
MG101
Newly Registered User
 
Join Date: May 2013
Posts: 63
Thanks: 11
Thanked 0 Times in 0 Posts
MG101 is on a distinguished road
DLookUp Function Issues

Hi All,

I have been struggling with MS Access for a while now. I am trying to create a form with multiple combo boxes where users can select fields from my main database and click a button to generate a report based on their selections. I think I am supposed to create a generic report with perhaps 4 fields where I would link the four combo boxes to. The issue is I cant seem to get the selection of the combo box to change the field that the report should pull from the database. I think I am supposed to use dlookup in the reports control source for each field, but I cant seem to get it to work.

Any Ideas or Suggestions are very much appreciated!

Thanks!

MG101 is offline   Reply With Quote
Old 06-13-2013, 08:43 AM   #2
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

Are you trying to assign a value to the control on the report or filter the report based on the combobox values?
billmeye is offline   Reply With Quote
Old 06-13-2013, 08:49 AM   #3
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

I guess trying to assign a value to the control.

Essentially what I want is to have a form where the user will select how many fields he wants displayed (For example lets say they click 4). It will then bring them to another form with 4 combo boxes where they can select the fields they want (and possibly also sort options but I can worry about that later). Once they select the four fields they want, it should bring them to a generic "4 field" report where it replaces the control source for each field with those of the ones selected in the combo boxes. A user would only ever want 3-8 fields displayed which means I would have to make a form with 3 boxes, one with 4 boxes, etc... and a generic report with 3 fields, one with 4 fields, etc.

Does this make sense? I know its probably a weird way to do it but I want to already have the generic reports modified so that users dont have to manually go to modify the reports appearence (ex. field spacing etc.)

MG101 is offline   Reply With Quote
Old 06-13-2013, 09:11 AM   #4
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

So, the controls on the report are Unbound and not tied to field in a table or query? If so, you can do this in a couple of ways.

1. You can directly reference the form in each controlsource on your report:

Ctl1 =Forms!frmYourForm!Combobox1
Ctl2 =Forms!frmYourForm!Combobox2

etc.

2. Or, you can assign the report controls using VBA in the On Open event of the report.

Me.Ctl1 = Forms!frmYourForm!Combobox1
Me.Ctl2 = Forms!frmYourForm!Combobox2

Method 1 requires you to keep the form open and at the selected record during the time the report is open and during printing as it only knows the values currently showing on the form.

The second method permanently assigns the values and once the report is open no longer needs the form to be open or at the desired record.
billmeye is offline   Reply With Quote
The Following User Says Thank You to billmeye For This Useful Post:
MG101 (06-14-2013)
Old 06-13-2013, 10:45 AM   #5
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

I tried usuing an unbound text box per your instructions and was not able to get results. I will upload my database for you to look at. On Form 1 (test form for now) I have a combo box with field names from my evap database. I have an evap report with the text box (text5) I left the VB code in so you can see what I did. I received an error "Runtime error 2147... (many numbers) You can not assign a value to this object." I want the report to show all values I have in my databse. For example if the users select VIN, DateCode, and Date Receved, I want the report to show all records in excel style format. (You can refernce the report called "Boss Report"


I appreciate your help!
Attached Files
File Type: zip PurgeValve(6-4-2013) (2).zip (344.1 KB, 45 views)
MG101 is offline   Reply With Quote
Old 06-13-2013, 11:12 AM   #6
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

So, what you are actually doing is assigning a controlsource to your unbound control on the report, not a value. I modified your VBA to do so and also added the line to update your label to match the data.
Attached Files
File Type: zip PurgeValve(6-4-2013).zip (301.7 KB, 51 views)
billmeye is offline   Reply With Quote
The Following User Says Thank You to billmeye For This Useful Post:
MG101 (06-14-2013)
Old 06-13-2013, 11:42 AM   #7
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

Thats exaclty what I wanted! Thank you so much!

Is it possible to also control the sorting of the fields? First by.., second by..., etc.?

Thanks again!

MG101 is offline   Reply With Quote
Old 06-13-2013, 12:04 PM   #8
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

A suggestion would be to order your controls on your report, Text1, Text2, Text3 etc. (Note, Text1 is the Name of the control, not the controlsource) in the exact order you would like them sorted. Have your comboboxes on your form in the same order, Combo1, Combo2 etc. Then take a look at the example VBA and adjust for each report.
Attached Files
File Type: zip PurgeValve(6-4-2013).zip (322.9 KB, 47 views)
billmeye is offline   Reply With Quote
The Following User Says Thank You to billmeye For This Useful Post:
MG101 (06-14-2013)
Old 06-14-2013, 04:28 AM   #9
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

Do you think I could create a additional combo box that goes underneath each combo box on the form so that the user may decide how field is sorted (Asc or Desc or Not at all)?
MG101 is offline   Reply With Quote
Old 06-14-2013, 06:22 AM   #10
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

Yes. You would then need to ensure there is always a default, i.e, put in controls to prevent the user from deleting or having a value that is not valid.
Attached Files
File Type: zip PurgeValve(6-4-2013).zip (322.1 KB, 50 views)
billmeye is offline   Reply With Quote
The Following User Says Thank You to billmeye For This Useful Post:
MG101 (06-14-2013)
Old 06-14-2013, 06:56 AM   #11
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

That makes sense! Thank you again! I wish I knew basic VBA functions, the coding for this isnt very complex, If I only knew some of the main functions that were available. Haha. I spent about 3 hours googling for the VBA you did in a few minutes. Thank you thank you thank you!
MG101 is offline   Reply With Quote
Old 06-14-2013, 07:13 AM   #12
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

Your welcome. We were all the beginning at some point. Now you know a little more and believe me, you'll get to learn a lot more, but you will always be surprised how little you actually know.
billmeye is offline   Reply With Quote
The Following User Says Thank You to billmeye For This Useful Post:
MG101 (06-17-2013)
Old 06-17-2013, 04:53 AM   #13
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,

Sorry to bother you again, but Is it also possible to have an additional combo box as a filter for each field selected. For example, If a user selects part type as a field, maybe they only want the report to display the DCV3's and ELCM's. Could they select which ones to include from a drop down box or maybe a list box?

Also if there is a specific date range they would like the records to be from. I would say the date range could be based off of the DIAM Received Or DIAM Sent field.

I appreciate all of your help!
MG101 is offline   Reply With Quote
Old 06-17-2013, 07:14 AM   #14
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

To add the ability to filter by multiple specific values can get pretty messy. You start with a multi select listbox or combobox and then the code to create the filter is messy enough for 1 listbox (see code) but then to add additional listboxes just adds to the mess. It can be done but you need to work your way up to it.

Code:
    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.ListBox1.ItemsSelected.Count <> 0 Then
'create our filter criteria
For Each oItem In Me.ListBox1.ItemsSelected
            If IsNull(Criteria) Then
            Criteria = "[NameOfFieldToFilterOn]=" & Me.ListBox1.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 [NameOfFieldToFilterOn]=" & Me.ListBox1.ItemData(oItem)
            End If
Next oItem
End If

'now need to do the same for next ListBox2 etc.
If Me.ListBox2.ItemsSelected.Count <> 0 Then
'add to filter criteria
For Each oItem In Me.ListBox2.ItemsSelected
            If isnull(Criteria) Then
            Criteria = "[NameOfFieldToFilterOn]=" & Me.ListBox2.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 [NameOfFieldToFilterOn]=" & Me.ListBox2.ItemData(oItem)
            End If
Next oItem
End If

'Now need to apply filter to report and open report
If Application.CurrentProject.AllReports("rptYourReportName").IsLoaded = True Then DoCmd.Close acReport, "rptYourReportName" 'close it in case it is open
      DoCmd.OpenReport "rptYourReportName", acViewDesign, , , acHidden 'opens report hidden so we can make edits
      Set rpt = Reports("rptYourReportName")
      rpt.Filter = Criteria
      rpt.FilterOnLoad = True
      rpt.FilterOn = True
      DoCmd.Close acReport, "rptYourReportName", acSaveYes
      Set rpt = Nothing
DoCmd.OpenReport "rptYourReportName", 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. If you need to work in runtime than you would either need to create an unbound control on the report or a global variable that the report can reference.

With regards to adding a date criteria, that is straightforward. For the record source (query) your using for your report you would add the criteria:

Code:
SELECT [EVAP Database].*
FROM [EVAP Database]
WHERE ((([EVAP Database].[DIAM Received Date]) Between [forms]![Form1]![DateFrom] And [Forms]![Form1]![DateTo]));
billmeye is offline   Reply With Quote
Old 06-17-2013, 08:15 AM   #15
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,

Thanks for the reply! I do have the full version of Access and will try to adapt this code. I will probably have anywhere from 1 to 4 filters for the report based on how many fields there are.

The date functions feels straight foward and should be quite easy to implement.

Thank you again and I am hoping I can take my time and figure this tough one out!

MG101 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 11:38 AM.


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