Wierd Behaviour in a Combo Box

  • Thread starter Thread starter Don Garry
  • Start date Start date
D

Don Garry

I have a form which has a year and month combo box on the form in Access
2000.

I'm finding that after the form is requeryed with a change to either combo
box and there are no records that the respective combo box suddenly shows
blank ???

Everything works fine if the requery results in having records.

Can anyone shed some light on what is happening here ?? It's driving me a
little crazy !!
 
Hi Don:
Could you elaborate a little: are there two comboboxes? Are they unbound or
bound(do you have anything in the control source property)?

Thanks,
Sam
 
Thanks for the info....after reading Allen Browe's article it looks like
I've got the case of a combo box with a zero-width bound column and the
combo box shows white even though it's providing data.

I'll try to figure out a work around.

In the meantime does anyone have any suggestions on a work around ?
 
One workaround that I use is to set the form's RecordSource equal to an
empty string when the recordset is empty. You'll need to set the
RecordSource back to its original string when you next make a selection in
the combo box. Fairly easy to do, and it's worked for me in similar
situations.

--

Ken Snell
<MS ACCESS MVP>
 
Excellent Ken, what would you consider to be the best way of checking to see
if the recordset is empty ?
 
One way is to check the RecordCount value of the form's RecordsetClone after
you requery the form or reset the RecordSource:

If Me.RecordSource = "" Then
Me.RecordSource = glbstrRecordSourceString
Else
Me.Requery
End If
If Me.RecordsetClone = 0 Then
Me.RecordSource = ""
End If

I usually store the form's RecordSource string as a private global constant
(glbstrRecordSourceString) in the form's module so that I have it available
for setting the RecordSource back to it in other code.

--

Ken Snell
<MS ACCESS MVP>
 
Hi Ken, unfortunately I'm not as well versed as I probably should be with
the Recordsetclone Function. I've tried to put your suggestion in my code
but I'm still struggling.....as I'm sure you'll quickly see.

Here is the code that I've tried so far for one of the combo boxes
(cboMonth) which drives my form:
*********
Private gblstrRecordSourceString As String

Private Sub cboMonth_AfterUpdate()

Dim strSQL As String
strSQL = "SELECT ......... " & _
"FROM ........" & _
"WHERE ....... " & _
"ORDER BY ......;"
Me.RecordSource = strSQL

If Me.RecordSource = "" Then
Me.RecordSource = glbstrRecordSourceString
Else
Me.Requery
End If
If Me.RecordsetClone = 0 Then
Me.RecordSource = ""
End If
Exit Sub
*******
Would it be possible for you to let me know where I've gone wrong in the
above code or provide me a small example ?
 
Further to my last note, I've just read your's again and can already see
that I've got my SQL string in the wrong spot.

I'll carry on trying to solve....
 
If you're building the SQL statement each time you make a selection in the
combo box, then you don't need to use a global constant as I suggested. Try
this generic content:


Private Sub cboMonth_AfterUpdate()

Dim strSQL As String
strSQL = "SELECT ......... " & _
"FROM ........" & _
"WHERE ....... " & _
"ORDER BY ......;"
Me.RecordSource = strSQL
If Me.RecordsetClone = 0 Then
Me.RecordSource = ""
End If

Exit Sub

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top