Cant assign value (easy problem)

[Adam]

Not a terribly hard problem, just cant get it right.

Im trying to assign a combo box the value "Reset Form" after a different
combo is updated, so in my "after update" of a combo i have:

Me.cboOwner = "RESET FORM"
Me.cboRigName = "RESET FORM"
Me.cboMooringLines = "RESET FORM"

cboOwner and cboRigName change to "RESET FORM" after the update.

cboMooringLines is a NUMBER; the values in the dropdown of the combo box are
all number values. I think this where i keep getting hung up, so i think i a
"#" somewhere? I have the identical problem on the same form with a box
cboDateRevised which is a combo with dates in it (in the form of XX/XX/XXXX).


In other code on the form i had to do something like this for the
cboDateRevised to work:

If Not IsNull(Me.cboDateRevised) Then
strWhere = strWhere & "([Date Last Revised] = #" & Me.cboDateRevised & "#) "
End If


I may of made it sound complicated, sorry, but simply i just need ot
reassign the value of my combo box to "RESET FORM", and the combo happens to
contain number values currently.

Thanks in advance, i always get great help on this forum.
Adam.

 

[Graham Mandeno]

Hi Adam

I assume that these are unbound combo boxes that are used to apply a
progressive filter to your form.

The easy solution would be to set their values to Null when the filter is
reset.

It actually looks like you are using a test for Null anyway:

If Not IsNull(Me.cboDateRevised) Then

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Not a terribly hard problem, just cant get it right.

Im trying to assign a combo box the value "Reset Form" after a different
combo is updated, so in my "after update" of a combo i have:

Me.cboOwner = "RESET FORM"
Me.cboRigName = "RESET FORM"
Me.cboMooringLines = "RESET FORM"

cboOwner and cboRigName change to "RESET FORM" after the update.

cboMooringLines is a NUMBER; the values in the dropdown of the combo box
are
all number values. I think this where i keep getting hung up, so i think i
a
"#" somewhere? I have the identical problem on the same form with a box
cboDateRevised which is a combo with dates in it (in the form of
XX/XX/XXXX).


In other code on the form i had to do something like this for the
cboDateRevised to work:

If Not IsNull(Me.cboDateRevised) Then
strWhere = strWhere & "([Date Last Revised] = #" & Me.cboDateRevised & "#)
"
End If


I may of made it sound complicated, sorry, but simply i just need ot
reassign the value of my combo box to "RESET FORM", and the combo happens
to
contain number values currently.

Thanks in advance, i always get great help on this forum.
Adam.

 

[Adam]

Thanks for the easy solution, but i would like to still have the value "reset
form".

They are all unbound. I have multiple combo, and a table on my form.
Selecting a value in a combo filters the table, then i have a reset button to
remove the filter. It only works using one combo as a filter criteria, so i
have the remaining combo boxes display "reset form" when a combo box has a
value selected in it to let the user know to reset the form before using a
different combo box.

Hi Adam

I assume that these are unbound combo boxes that are used to apply a
progressive filter to your form.

The easy solution would be to set their values to Null when the filter is
reset.

It actually looks like you are using a test for Null anyway:

If Not IsNull(Me.cboDateRevised) Then

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Not a terribly hard problem, just cant get it right.

Im trying to assign a combo box the value "Reset Form" after a different
combo is updated, so in my "after update" of a combo i have:

Me.cboOwner = "RESET FORM"
Me.cboRigName = "RESET FORM"
Me.cboMooringLines = "RESET FORM"

cboOwner and cboRigName change to "RESET FORM" after the update.

cboMooringLines is a NUMBER; the values in the dropdown of the combo box
are
all number values. I think this where i keep getting hung up, so i think i
a
"#" somewhere? I have the identical problem on the same form with a box
cboDateRevised which is a combo with dates in it (in the form of
XX/XX/XXXX).


In other code on the form i had to do something like this for the
cboDateRevised to work:

If Not IsNull(Me.cboDateRevised) Then
strWhere = strWhere & "([Date Last Revised] = #" & Me.cboDateRevised & "#)
"
End If


I may of made it sound complicated, sorry, but simply i just need ot
reassign the value of my combo box to "RESET FORM", and the combo happens
to
contain number values currently.

Thanks in advance, i always get great help on this forum.
Adam.

 

[Graham Mandeno]

Hi Adam

Depending on the way your combo box properties are set up, this might work:

Set the combo box's Format property to:
@;"Reset Form"

Then, if the value is Null, it will display "Reset Form".

But... I'm a little confused... From your previous posts I assumed that you
wanted to build a "progressive" filter - i.e. you select something in one
combo and it filters the form and also narrows down the choices in the other
combos. For example, choose a number of mooring lines and then the combo to
choose the owner shows only those who own rigs with that number of lines.
Is this not what you are trying to do?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the easy solution, but i would like to still have the value
"reset
form".

They are all unbound. I have multiple combo, and a table on my form.
Selecting a value in a combo filters the table, then i have a reset button
to
remove the filter. It only works using one combo as a filter criteria, so
i
have the remaining combo boxes display "reset form" when a combo box has a
value selected in it to let the user know to reset the form before using a
different combo box.

Hi Adam

I assume that these are unbound combo boxes that are used to apply a
progressive filter to your form.

The easy solution would be to set their values to Null when the filter is
reset.

It actually looks like you are using a test for Null anyway:

If Not IsNull(Me.cboDateRevised) Then

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Not a terribly hard problem, just cant get it right.

Im trying to assign a combo box the value "Reset Form" after a
different
combo is updated, so in my "after update" of a combo i have:

Me.cboOwner = "RESET FORM"
Me.cboRigName = "RESET FORM"
Me.cboMooringLines = "RESET FORM"

cboOwner and cboRigName change to "RESET FORM" after the update.

cboMooringLines is a NUMBER; the values in the dropdown of the combo
box
are
all number values. I think this where i keep getting hung up, so i
think i
a
"#" somewhere? I have the identical problem on the same form with a box
cboDateRevised which is a combo with dates in it (in the form of
XX/XX/XXXX).


In other code on the form i had to do something like this for the
cboDateRevised to work:

If Not IsNull(Me.cboDateRevised) Then
strWhere = strWhere & "([Date Last Revised] = #" & Me.cboDateRevised &
"#)
"
End If


I may of made it sound complicated, sorry, but simply i just need ot
reassign the value of my combo box to "RESET FORM", and the combo
happens
to
contain number values currently.

Thanks in advance, i always get great help on this forum.
Adam.

 

[Adam]

Well ive actually tried, with help of the forums, before to create a form
that has all my combo boxes to progressively filter my form, but i wanted to
go in any order through the combos and it would never work, i could only
figure a code out to go in a certain order through them all to filter.

For now, i think filtering from just one combo narrows results enough, it
only gives a few results after each filter. If you are well educated about
doing this, possibly you could help make my current form progressively filter
my data, allowing the user to pick and choose any combo they would like, no
particular order.

Thanks.

Hi Adam

Depending on the way your combo box properties are set up, this might work:

Set the combo box's Format property to:
@;"Reset Form"

Then, if the value is Null, it will display "Reset Form".

But... I'm a little confused... From your previous posts I assumed that you
wanted to build a "progressive" filter - i.e. you select something in one
combo and it filters the form and also narrows down the choices in the other
combos. For example, choose a number of mooring lines and then the combo to
choose the owner shows only those who own rigs with that number of lines.
Is this not what you are trying to do?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the easy solution, but i would like to still have the value
"reset
form".

They are all unbound. I have multiple combo, and a table on my form.
Selecting a value in a combo filters the table, then i have a reset button
to
remove the filter. It only works using one combo as a filter criteria, so
i
have the remaining combo boxes display "reset form" when a combo box has a
value selected in it to let the user know to reset the form before using a
different combo box.

Hi Adam

I assume that these are unbound combo boxes that are used to apply a
progressive filter to your form.

The easy solution would be to set their values to Null when the filter is
reset.

It actually looks like you are using a test for Null anyway:
If Not IsNull(Me.cboDateRevised) Then

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Not a terribly hard problem, just cant get it right.

Im trying to assign a combo box the value "Reset Form" after a
different
combo is updated, so in my "after update" of a combo i have:

Me.cboOwner = "RESET FORM"
Me.cboRigName = "RESET FORM"
Me.cboMooringLines = "RESET FORM"

cboOwner and cboRigName change to "RESET FORM" after the update.

cboMooringLines is a NUMBER; the values in the dropdown of the combo
box
are
all number values. I think this where i keep getting hung up, so i
think i
a
"#" somewhere? I have the identical problem on the same form with a box
cboDateRevised which is a combo with dates in it (in the form of
XX/XX/XXXX).


In other code on the form i had to do something like this for the
cboDateRevised to work:

If Not IsNull(Me.cboDateRevised) Then
strWhere = strWhere & "([Date Last Revised] = #" & Me.cboDateRevised &
"#)
"
End If


I may of made it sound complicated, sorry, but simply i just need ot
reassign the value of my combo box to "RESET FORM", and the combo
happens
to
contain number values currently.

Thanks in advance, i always get great help on this forum.
Adam.

 

[Linq Adams via AccessMonster.com]

cboMooringLines is a NUMBER; the values in the dropdown of the combo box >areall number values

Quite simply, if the values in cboMooringLines are numeric values, you can't
have it display "RESET FORM," a text message. The answer would be to make the
value held a Text datatype instead of Numeric. Are the values in
cboMooringLines ever used in math? If not, they don't need to Numeric to
begin with. "Numbers" fields only need to be Numeric datatype if they're
going to be used in mathematical equations. If the values *are* going to be
used in math, you can still change them to Text datatype, allowing "RESET
FORM" to be displayed, and when using the values in math, reference the value
as Val(cboMooringLines).

 

[Adam]

Thanks Linq Adams, changing the data type did the trick!

 

[Graham Mandeno]

Hi Adam

The Format trick should work whether the field in your RowSource is number
or text. You just set the combo box's value to Null and it will *display*
"Reset Form" instead of a blank box.

I would be wary about changing a field such as NumberOfMooringLines from
numeric to text. One day you might want to query for "all rigs with more
than 4 mooring lines" and you will get unexpected results because "10" is
less than "4".

If you really want to get the progressive filter going then I'm willing to
help you with that. I think the best approach is to create a single
function which returns a filter string based on all the non-null combo box
selections. Your function would take an optional argument which is the name
of a combo box to leave out of the string.

After updating any one of the combo boxes, you call your function once to
generate a filter string for the whole form and for any combo boxes that are
still null, then you call it once again for every non-null combo, omitting
that combo from the filter.

For example, using three combos, let's say:
cboRegion = "Gulf of Mexico"
cboOwner = "Shell"
cboMooringLines is Null

CreateFilter() returns:
([Region]='Gulf of Mexico') AND ([Owner]='Shell')
Call this one strBaseFilter

CreateFilter("cboRegion") ignores cboRegion:
([Owner]='Shell')

CreateFilter("cboOwner") ignores cboOwner:
([Region]='Gulf of Mexico')

Now, strBaseFilter gets applied to the form:
Me.Filter = strBaseFilter
Me.FilterOn = True

And also to the RowSource of any combo boxes that are Null:
cboMooringLines.RowSource = _
"Select distinct MooringLines from YourTable where " & strBaseFilter

The other non-null combos use the modified filter:

cboRegion.RowSource = _
"Select distinct Region from YourTable where " &
CreateFilter("cboRegion")
This string is constructed thus:
Select distinct Region from YourTable where ([Owner]='Shell')
so it ONLY lists those regions where there are rigs owned by Shell.

Similarly:
cboOwner.RowSource = _
"Select distinct Owner from YourTable where " &
CreateFilter("cboOwner")
This string is constructed thus:
Select distinct Owner from YourTable where ([Region]='Gulf of Mexico')
so it ONLY lists those owners who have rigs in the Gulf of Mexico.

Do you get the idea?

Post back if you need more help and tips.