Display values in subform based on multiple combo boxes

[yasmeen]

HI,

I am new to MS Access and stuck in a problem from few days.

My Question: I have 3 combo box (Submitter, date from and date to) on my form and i want that when i select values from each of these combo boxes, a subform will display all the matching rows based on it, display rows based on selected submitter and the between the dates chosen in 2nd and 3rd combo boxes. All the values are coming from same table. 'date to' and 'date from' is also from same column.

Right now, my subform is displaying rows but not based on all combo box's selection, when i select submitter, it display only for that submitter,not for the chosen dates and vise verse.

2nd, my form is not displaying values between the chosen dates.

Please help me solve my problem. Thanks

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

Welcome to the forum!

Copy and paste the Row Source property of one of the first combo box.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

there is not much code, its a simple query on my combo boxes. here it is

SELECT DISTINCT [1st dec].Submitter FROM [1st dec];

Welcome to the forum!

Copy and paste the Row Source property of one of the first combo box.

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

I was just checking that it has only one column. Do the other two combo boxes have just one column too?

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

Yes...they also have only one column..

I was just checking that it has only one column. Do the other two combo boxes have just one column too?

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

Few more questions:

1. Do you have a button that will perform this action?
2. What are the names of the combo boxes?
3. What are the names of the fields in the subform that you will like to filter by?
4. What is the name of the subform control. I don't mean the name of the subform, I mean the subform control. The one that has the Source Object property.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

1. I don't have any button, when i change the value of combo box, the values of subform is automatically changing accordingly.

2.My Combo boxes are called(combo62, combo53, combo55)
combo62 - submitter
combo53 - date from
combo55 - date to

3. I want to filter 2 fields: 'Submitter' and 'date'

4. I don't have any control on subform. I have only 6 fields there which i have taken from same table.

Thanks

Few more questions:

1. Do you have a button that will perform this action?
2. What are the names of the combo boxes?
3. What are the names of the fields in the subform that you will like to filter by?
4. What is the name of the subform control. I don't mean the name of the subform, I mean the subform control. The one that has the Source Object property.

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

1. I don't have any button, when i change the value of combo box, the values of subform is automatically changing accordingly.

Now, because you have From and To dates, it would be better to use a button.

4. I don't have any control on subform. I have only 6 fields there which i have taken from same table.

Ok, people get this confused but your subform is a control too, also the textboxes displaying those fields on your subform are also called controls. But what I want is the name of the subform (control). In design view, click on the subform ONCE and look at the Name property under the Other tab of the Property Sheet. Tell me what it reads. Also, under the Data tab tell me if you see Source Object.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

Ok, My subform control name is '1st dec subform2' and under data tab, source object is '1st dec subform2'

Now, because you have From and To dates, it would be better to use a button.

Ok, people get this confused but your subform is a control too, also the textboxes displaying those fields on your subform are also called controls. But what I want is the name of the subform (control). In design view, click on the subform ONCE and look at the Name property under the Other tab of the Property Sheet. Tell me what it reads. Also, under the Data tab tell me if you see Source Object.

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

Right, change the name of your subform control to subf1stDec. If you have other vba code that calls that subform you will need to change those too. Once you've done that, put the following code in the CLICK event of the Button (aircode):

Dim strFilter As String

If Len(Me.Combo62 & "") = 0 And (IsDate(Me.Combo53) = False Or IsDate(Me.Combo55) = False) Then
     Exit Sub
End If

strFilter = "[Submitter] = '"  & Nz(Me.Combo62, vbNullString) & "'"

If strFilter = "[Submitter] = ''" Then
     strFilter = "[Date] BETWEEN " & Me.Combo53 & " AND " & Me.Combo55
Else
     If (IsDate(Combo53) = False Or IsDate(Combo55) = False) = False Then
          strFilter = strFilter & " AND ([Date] BETWEEN " & Me.Combo53 & " AND " & Me.Combo55 & ")"
     End If
End If

Me.Filter = strFilter
Me.FilterOn = True

It will be good to also have a Remove Filter or Remove/Clear Criteria button. If you do, here's the code:

Me.Filter = vbNullString
Me.FilterOn = False

A few other points to note - give your controls meaningful names. Here's a link to guide you:

http://www.acc-technology.com/namconv.htm

The other thing is don't use names like Date. That is a reserved keyword for Access and it will cause you problems.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

Thanks for you help, I have placed that code under button click, but still there is same problem. Data is not displaying according to selected two dates.

I have these properties set:
Link master feild: submitter
link child field : submitter

I think it is displaying data according to these properties only. If i am removing it, then also it is not working.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

And one thing more, My dates are not in date format, they are just digits like 20101025

As my client got excel sheets daily from somewhere And it comes in this format (numeric) and he has to embed these sheets daily into this access database, So for the simplycity i leave it in numeric format only.

Thanks

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

I have these properties set:
Link master feild: submitter
link child field : submitter

I think it is displaying data according to these properties only. If i am removing it, then also it is not working.

If you have those properties set then it will filter based on the parent form's submitter value. Remove it.

And one thing more, My dates are not in date format, they are just digits like 20101025

As my client got excel sheets daily from somewhere And it comes in this format (numeric) and he has to embed these sheets daily into this access database, So for the simplycity i leave it in numeric format only.

Well, this is vital information that you should have mentioned long before now. Some parts of the code will now need to be rewritten. Always give important information at the start of your post.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

Oh, i am sorry, i did not notice this thing ealier. but what i need to change in code now

If you have those properties set then it will filter based on the parent form's submitter value. Remove it.

Well, this is vital information that you should have mentioned long before now. Some parts of the code will now need to be rewritten. Always give important information at the start of your post.

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

No problemo!

Here's the amended code:

Dim strFilter As String

If Len(Me.Combo62 & "") = 0 And (Len(Me.Combo53 & "") = 0 Or Len(Me.Combo55 & "") = 0) Then
     Exit Sub
End If

strFilter = "[Submitter] = '"  & Nz(Me.Combo62, vbNullString) & "'"

If strFilter = "[Submitter] = ''" Then
     strFilter = "[Date] >= " & Me.Combo53 & " AND [Date] <= " & Me.Combo55
Else
     If Len(Me.Combo53 & "") <> 0 And Len(Me.Combo55 & "") <> 0 Then
          strFilter = strFilter & " AND ([Date] >= " & Me.Combo53 & " AND [Date] <= " & Me.Combo55 & ")"
     End If
End If

Me.Filter = strFilter
Me.FilterOn = True

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

I am sorry that i am not able to get it.

If I remove following filters
Link master feild: submitter
link child field : submitter

then button is not filtering at all.

I am uploading the database here..i would be very helpful if you look over it. My form name is '1st dec'

Thanks ton

No problemo!

Here's the amended code:

Dim strFilter As String

If Len(Me.Combo62 & "") = 0 And (Len(Me.Combo53 & "") = 0 Or Len(Me.Combo55 & "") = 0) Then
     Exit Sub
End If

strFilter = "[Submitter] = '"  & Nz(Me.Combo62, vbNullString) & "'"

If strFilter = "[Submitter] = ''" Then
     strFilter = "[Date] >= " & Me.Combo53 & " AND [Date] <= " & Me.Combo55
Else
     If Len(Me.Combo53 & "") <> 0 And Len(Me.Combo55 & "") <> 0 Then
          strFilter = strFilter & " AND ([Date] >= " & Me.Combo53 & " AND [Date] <= " & Me.Combo55 & ")"
     End If
End If

Me.Filter = strFilter
Me.FilterOn = True

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

Actually, since we're filtering the subform, we should be referencing the subform. So take out the Link Master/Child Fields properties and replace these two lines:

Me.Filter = strFilter
Me.FilterOn = True

with these:

Me.subf1stDec.Form.Filter = strFilter
Me.subf1stDec.Form.FilterOn = True

Remember to add those references for the Clear button too.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

Thank You very much, It is working perfect now. I appriciate your efforts. Today was my deadline for submitting this. You helped me alot.

Actually, since we're filtering the subform, we should be referencing the subform. So take out the Link Master/Child Fields properties and replace these two lines:

Me.Filter = strFilter
Me.FilterOn = True

with these:

Me.subf1stDec.Form.Filter = strFilter
Me.subf1stDec.Form.FilterOn = True

Remember to add those references for the Clear button too.

 

[vbaInet]

Re: Dispaly values in subform based on multiple combo boxes

You're welcome!!

But ...

... I just looked at your db and noticed that in your table there are cases where Submitter is Null and some cases where the Date field is Null too. For example, you can't find records that have Null Submitter values.

 

[yasmeen]

Re: Dispaly values in subform based on multiple combo boxes

Yeah, but my client don't want that records who has no submitter or date. So its not a problem for now.

You're welcome!!

But ...

... I just looked at your db and noticed that in your table there are cases where Submitter is Null and some cases where the Date field is Null too. For example, you can't find records that have Null Submitter values.