Unbound forms / Comboboxes not refreshing 1

[TomCarnahan]

Have a form with no recordsource, but several comboboxes that derive their datae from tables in a SQL-Server backend via ADO. I have several buttons that popup forms to edit fields in some of the tables that are sources to the comboboxes. When I return from the edited popup form, without fail, one or more of the comboboxes has one or more fields go blank.

I have tried the following in the form Activate event:

With Me
ctl.requery
ctl.recalc
ctl.refresh
.requery
end with

but inevitablely, one or more comboboxes goes blank after returning to the main from.

Does anyone have an idea how to make sure the comboboxes are consistently refreshed?

Thanks!


--- Tom

 

[RickSpr]

First, when you say "one or more of the comboboxes has one or more fields go blank", are you talking about columns within a combo box being blank, or are you talking about whole rows going blank? I have seen situations (though not all that similar to this as far as I can tell) where it seems as if text were not being painted properly. Yet the cursor will move through it with the arrow keys, and if you copy it and paste it into Notepad, the text is actually there, so it's as if it were being painted as white text on a white background in the control. Can you tell if your problem is like this? Perhaps by clicking a "blank" row and observing the text part of the combo box?

As a workaround, you might try the statement:
ctl.RowSource = ctl.RowSource
Any assignment to a combo or list box's RowSource will force it to be reloaded from scratch.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TomCarnahan]

Rick,
The comboboxes have 3 or 4 columns in them when dropped down. I purposely left the bound autonumber field showing so that I could see what values I was choosing for test purposes. When it blanks out, the bound column still shows, but the first column and any others do not.

In one case, one of the columns was a composite of the following type:

tbl_Employee.LastName & ", " & tbl_Employee.FirstName

I like your idea of: ctl.RowSource = ctl.RowSource

What event do you use to trigger it? I was trying to use the OnActivate event, but Access Help says that event does not fire when returning from a dialog or popup form (which mine is).

When the form first comes up, all comboboxes show their selected values, but when I go to the dialog box to add a record to a table that one of the unbound comboboxes is using. When I return to the main form is when they "go blank" (or partially blank) on me.

Any ideas?

Thanks!



--- Tom

 

[TheAceMan1]

How are ya TomCarnahan . . . . .

If you read carefully the help on the [blue]On Activate[/blue] Event, you'll find its [blue]not reliable[/blue]. Try the following:

In a module in the modules window copy/paste the following code (you substitute proper names in [purple]purple[/purple]):

[blue]Function IsOpenForm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function

Public Sub ReqCBs()
   Dim frm As Form, frmName As String
   
   frmName = "[purple][b]NameOfFormWithComboboxes[/b][/purple]"
   
   If IsOpenForm(frmName) Then
      Set frm = Forms(frmName)
      frm![purple][b]ComobboxName1[/b][/purple].Requery
      frm![purple][b]ComobboxName2[/b][/purple].Requery
                     '
                     '
      Set frm = Nothing
   End If
                  
End Sub
[/blue]

Then in the [blue]Close Event[/blue] of the specific Pop-Up Forms, copy/paste the following code:

[blue]   Call reqCBs[/blue]

Thats it . . . . give it a whirl and let me know . . .

See Ya! . . . . . .

 

[TomCarnahan]

I'll try it as soon as I get to work ... Thanks!

--- Tom