Combo Box blank when no records on Form

[ejstefl]

Hello,

I have an unbound combo box in the header of a continuous form. The box is set to value list, and when you select a value, the recordsource of the form is changed. This works fine.

However, if I there are no records in the underlying form, the combo box is blank. If I check the value in the immediate window or the after update, I can see that it is set properly and contains a value, it just doesn't display it. I am using Access 2000. I have tested on Access 2003 and cannot replicate the problem.

I have found one post here on the topic.

I found this article on Microsoft which has my symptoms, but I am not opening a recordset.

Anyone know how to fix this??

 

[llkhoutx]

If your combo box bound to the form table?

I filter extensivly with a header combo box and have never encountered you problem with any version of Access.

 

[ejstefl]

Its an unbound combo box...

 

[Rich]

Use an unbound form and display the continuous form as a sub form

 

[Meltdown]

I know this is an old thread, I was just wondering if there is any other solution to this problem apart from making a sub-form, I'm to far down the line on a complex form to go for that.

To recap, on a continuous form with a set of unbound combos in the header to filter the data, all the combos go blank when no data is returned, when they should retain the values they were set to.

If anyone has anything else to suggest I'd appreciate it.

Thanks
Melt

 

[Meltdown]

I just wanted to update thread with a fix to this problem.

On an unbound form, I'm creating dynamic sql/query through code and then setting the recordsource of the form, when no records were returned all the combos go blank.

The fix:
Add a textbox to the form and set it's controlsource to =Count(*)

Then at the end of my code that has created my query, I check the value of Count textbox and if it's zero, I requery the form, like this:

If Me.txtCountTextbox.Value = 0 Then
Me.Requery
End If

That solves the problem, albeit with the overhead of a requery.

A second method is to use a recordsetclone:

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast

Dim C As Integer
C = rst.RecordCount
If C = 0 Then
Me.Requery
End If

Regards
Melt