Combobox problem

  • Thread starter Thread starter SueM
  • Start date Start date
S

SueM

Hi,

I have a similar problem to Lucy - 1/9/03 8.39am, and have
tried Bruce's suggested solution to her on my problem.

I just want some advice on how to alter this solution to
fit my problem.

Instead of having a combobox and a subform I have 2
comboboxes one depending on the other for it's values.

This part I have entered in the row source of combo2 and
it works fine:
SELECT DISTINCT [Prod_No], [Prod_Desc] FROM ProdQ;

Now I need to narrow this down to only the results for the
product chosen in combo1 and have tried adjusting Bruce's
other suggested solution as follows:

Private Sub Combo1_AfterUpdate()
Dim strFilter As String
'Retrieve the combo's value
If Len(Trim(Me.Combo1.Value) & "") = 0 Then
strFilter = ""
Else
strFilter = "[Prod_No]=""" & Me.Combo1.Value & """"
End If

'Filter the subform
With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
End With

End Sub

But this last part isn't working for me. Any ideas on why
would be greatly appreciated. I'm thinking combos in wrong
places or maybe the Distinct thing shouldn't be used on 2
fields (though this works on its own) or maybe the last
With should be something other than .value.

Also do I need to work a requery into this or will this
automatically change when combo1 changes?

To date I've tried numerous queries with criteria to solve
this problem and have had no luck, so I'm really hoping to
get this one working

Cheers,

Sue
 
Try requerying the combo box after you set the filter.

With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
.Requery
End With
 
Try this first:

If [Prod_No] is a string (text) use this:

strFilter = "[Prod_No]= '" & Me.Combo1.Value & "'"


If [Prod_No] is numeric use:

strFilter = "[Prod_No]= " & Me.Combo1.Value


HTH

Steve
 
Thanks Ken,

I've added in the requery.

At the moment I get an error message when I run the event
procedure whch says "runtime error '424' object requred".

When I go into debug mode the highlighted error is the
line reading:
..Filter = strFilter

Do I need to add in combo2's name in here?

Cheers,

Sue

-----Original Message-----
Try requerying the combo box after you set the filter.

With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
.Requery
End With

--
Ken Snell
<MS ACCESS MVP>

SueM said:
Hi,

I have a similar problem to Lucy - 1/9/03 8.39am, and have
tried Bruce's suggested solution to her on my problem.

I just want some advice on how to alter this solution to
fit my problem.

Instead of having a combobox and a subform I have 2
comboboxes one depending on the other for it's values.

This part I have entered in the row source of combo2 and
it works fine:
SELECT DISTINCT [Prod_No], [Prod_Desc] FROM ProdQ;

Now I need to narrow this down to only the results for the
product chosen in combo1 and have tried adjusting Bruce's
other suggested solution as follows:

Private Sub Combo1_AfterUpdate()
Dim strFilter As String
'Retrieve the combo's value
If Len(Trim(Me.Combo1.Value) & "") = 0 Then
strFilter = ""
Else
strFilter = "[Prod_No]=""" & Me.Combo1.Value & """"
End If

'Filter the subform
With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
End With

End Sub

But this last part isn't working for me. Any ideas on why
would be greatly appreciated. I'm thinking combos in wrong
places or maybe the Distinct thing shouldn't be used on 2
fields (though this works on its own) or maybe the last
With should be something other than .value.

Also do I need to work a requery into this or will this
automatically change when combo1 changes?

To date I've tried numerous queries with criteria to solve
this problem and have had no luck, so I'm really hoping to
get this one working

Cheers,

Sue
Click to expand...


.
Click to expand...
 
Hi Sue,
You need to use the object itself, not the value:

With Me.Combo2
.Filter = strFilter
.FilterOn = True
.Requery
End With

--
HTH
Dan Artuso, Access MVP


SueM said:
Thanks Ken,

I've added in the requery.

At the moment I get an error message when I run the event
procedure whch says "runtime error '424' object requred".

When I go into debug mode the highlighted error is the
line reading:
.Filter = strFilter

Do I need to add in combo2's name in here?

Cheers,

Sue

-----Original Message-----
Try requerying the combo box after you set the filter.

With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
.Requery
End With

--
Ken Snell
<MS ACCESS MVP>

SueM said:
Hi,

I have a similar problem to Lucy - 1/9/03 8.39am, and have
tried Bruce's suggested solution to her on my problem.

I just want some advice on how to alter this solution to
fit my problem.

Instead of having a combobox and a subform I have 2
comboboxes one depending on the other for it's values.

This part I have entered in the row source of combo2 and
it works fine:
SELECT DISTINCT [Prod_No], [Prod_Desc] FROM ProdQ;

Now I need to narrow this down to only the results for the
product chosen in combo1 and have tried adjusting Bruce's
other suggested solution as follows:

Private Sub Combo1_AfterUpdate()
Dim strFilter As String
'Retrieve the combo's value
If Len(Trim(Me.Combo1.Value) & "") = 0 Then
strFilter = ""
Else
strFilter = "[Prod_No]=""" & Me.Combo1.Value & """"
End If

'Filter the subform
With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
End With

End Sub

But this last part isn't working for me. Any ideas on why
would be greatly appreciated. I'm thinking combos in wrong
places or maybe the Distinct thing shouldn't be used on 2
fields (though this works on its own) or maybe the last
With should be something other than .value.

Also do I need to work a requery into this or will this
automatically change when combo1 changes?

To date I've tried numerous queries with criteria to solve
this problem and have had no luck, so I'm really hoping to
get this one working

Cheers,

Sue
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks very much guys.

Now up & running & after a week of fiddling I am on very
happy lady.

Cheers,

Sue
-----Original Message-----
< doh ! > < duh ! >

Dan is absolutely correct. I overlooked that typo!

< as he goes back to writing a database instead of answering questions.....
--
Ken Snell
<MS ACCESS MVP>

Hi Sue,
You need to use the object itself, not the value:

With Me.Combo2
.Filter = strFilter
.FilterOn = True
.Requery
End With

--
HTH
Dan Artuso, Access MVP


Thanks Ken,

I've added in the requery.

At the moment I get an error message when I run the event
procedure whch says "runtime error '424' object requred".

When I go into debug mode the highlighted error is the
line reading:
.Filter = strFilter

Do I need to add in combo2's name in here?

Cheers,

Sue


-----Original Message-----
Try requerying the combo box after you set the filter.

With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
.Requery
End With

--
Ken Snell
<MS ACCESS MVP>

Hi,

I have a similar problem to Lucy - 1/9/03 8.39am, and
have
tried Bruce's suggested solution to her on my problem.

I just want some advice on how to alter this solution to
fit my problem.

Instead of having a combobox and a subform I have 2
comboboxes one depending on the other for it's values.

This part I have entered in the row source of combo2 and
it works fine:
SELECT DISTINCT [Prod_No], [Prod_Desc] FROM ProdQ;

Now I need to narrow this down to only the results for
the
product chosen in combo1 and have tried adjusting
Bruce's
other suggested solution as follows:

Private Sub Combo1_AfterUpdate()
Dim strFilter As String
'Retrieve the combo's value
If Len(Trim(Me.Combo1.Value) & "") = 0 Then
strFilter = ""
Else
strFilter = "[Prod_No]=""" & Me.Combo1.Value
& """"
End If

'Filter the subform
With Me.Combo2.Value
.Filter = strFilter
.FilterOn = True
End With

End Sub

But this last part isn't working for me. Any ideas on
why
would be greatly appreciated. I'm thinking combos in
wrong
places or maybe the Distinct thing shouldn't be used on
2
fields (though this works on its own) or maybe the last
With should be something other than .value.

Also do I need to work a requery into this or will this
automatically change when combo1 changes?

To date I've tried numerous queries with criteria to
solve
this problem and have had no luck, so I'm really hoping
to
get this one working

Cheers,

Sue







.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top