DLookUp Function Issues (1 Viewer)

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!

Are you trying to assign a value to the control on the report or filter the report based on the combobox values?

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.)

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.

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!

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.

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!

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.

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)?

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.

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!

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.

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!

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.


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:

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!

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

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

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!

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!

Reference File for Billmeye: