Combobox property problem

[Philo Hamel via AccessMonster.com]

I've got a problem with cascading comboboxes on a form. The form has two
comboboxes, combo1 and combo2. On the AfterUpdate event of combo1 I've got
this code:

Select Case Me.combo1

Case "Day list"
Me.combo2.Format = ""
Me.combo2 = ""
Me.combo2.Enabled = False

Case "Bill third party"
Me.combo2.Format = "00/000"
Me.combo2.RowSourceType = "Table/Query"
Me.combo2.Enabled = True
Me.combo2 = "Pick..."
Me.combo2.RowSource = "SELECT [HBadresDER].[RegNr] FROM [HBadresDER]
WHERE [HBadresDER].[Date] > 0 ORDER BY [RegNr];"
Me.combo2.SetFocus

Case "Products"
Me.combo2.Format = "00/000"
Me.combo2.RowSourceType = "Value List"
Me.combo2.Enabled = True
Me.combo2 = "Pick..."
Me.combo2.RowSource = "Everything; 10000 Technical photography; 20000
Processing; ... 90000 Digital;"
Me.combo2.SetFocus

And now the problem. When selecting "Bill third party" combo2 will show all
the RegNr available (these are integers). After selecting one RegNr and then
changing combo1 to "Products" an error occurs. The popup tells me I'm trying
to insert a text ("Pick...") into a number field (sorry if this doesn't make
any sense, but I'm using a Dutch version and I have to translate a bit). What
property of combo2 do I need to change to make it allow text again?

TIA.
Philo Hamel

 

[Philo Hamel via AccessMonster.com]

Oops, my bad. The Format property within Case "Products" should be Me.combo2.
Format = "" instead of "00/000". But the problem remains the same.

 

[Fred Wilson]

Philo,

I am not sure this will work but here is something to look at.

I believe you want to add the text "Pick..." to your combo box?

If so, as best as I can tell this is normally done with a UNION query
such as.

"SELECT [RegNr] FROM [HBadresDER]
UNION SELECT "Pick..." FROM [HBadresDER]
WHERE [HBadresDER].[Date] > 0 ORDER BY [RegNr];"

That is air code but something like it will put "Pick..." in your combo box.

Now, if your form is an updatable or data entry form it will try to set
the record source of this control to whatever you pick. If at table
level [RegNr] is a number it will not allow "Pick...".

If you are using this form to get a report I would set the controls to
be unbound and pass on the information from the controls to your
respective script.

I know this is not a complete answer but it might help to get you going
again.

Very Respectfully,
Fred

 

[Philo Hamel via AccessMonster.com]

Hey Fred,
Thanks for the reply!

The form is used to filter data for printing (from a report), so its unbound
and will pass the info to the Print button. It looks like the problem lies
within the combobox properties. I tried the whole thing without the Me.combo2
= "Pick..." and the problem was still there. It looks like the combobox keeps
a "number" property after it has been used to show numbers.

So if you have any idea....

Later,
Philo

 

[Fred Wilson]

Hey Fred,
Thanks for the reply!

The form is used to filter data for printing (from a report), so its unbound
and will pass the info to the Print button. It looks like the problem lies
within the combobox properties. I tried the whole thing without the Me.combo2
= "Pick..." and the problem was still there. It looks like the combobox keeps
a "number" property after it has been used to show numbers.

So if you have any idea....

Later,
Philo

I'm sorry I don't think I completely understand what you are trying to
do then or what the problem is.

If you have more details then I might be able to think of something.

 

[Fred Wilson]

Philo,

What happens if for Case "Day List" you set me.combo2.rowsource=Nothing?

Fred

 

[Philo Hamel via AccessMonster.com]

Hi Fred,

Well I think I kind of solved the problem. I decided RegNr doesn't need to be
a number, it can be a text to (but still be made up out of digits). This way
the combo box doesn't give any hassle. I did want to know what I was doing
wrong, but I need to finish the project.
Sorry about not being too clear, though. It's sometimes really hard to
explain to others the proplem you're faced with.

Thanks again for your time.
Later,
Philo