Force Immediate Refresh of Combobox on Form

[Phred]

Windows 7, Access 2007, not compiled yet, single user, not split, skill level moderate.



I have a combobox on a form. The Query behind the combobox calls the CustomerNames from the Tbl_Customers

When you select an existing customer it calls up the related information on the form. All works well

When you want to add a new record you click the New Record button and it forces the Form and the Combobox to go to a New Record. All Works well

The user then enters the new Customers Name. In the After Update property of the CustomerName text box I have:

Private Sub CustomerName_AfterUpdate()
Me.Dirty = False
Me.Refresh (my attempt to get the combo box to refresh with the new customer name)
End Sub

It forces the new record to be saved immediately to the Tbl_Customers and I have checked the table and verify that it writes Immediately. All works well.

Even though the record is officially written in the Tbl_Customers... and the combobox queries the Tbl_Customers for Customer Names, when I click on the combo box the new Customer Name does not appear.

I need to refresh the combo box after saveing so the new record is displayed in the combo box immediately.

I have tried several things but I am not hitting on it.

Thanks,

Phred

[June7]

What is purpose of this form - to enter/edit new customers?

If you just added the new customer, why do you need to go back to the combobox?

What things have you tried? Was one:
Me.comboboxname.Requery

[Phred]

Hi:

I am in the process of debugging. The Customer tables are properly normalized.

The purpose of the form is to add a new customer and then enter their data.

My goal is to clean up the whole add-a-new-record process. Here is what I have accomplished so far:

1. The user selects an existing customer from the Combo Box and the related information displays in the form. This all works fine.

2. The user wants to add a new customer so they click Add A New Record Button and the following executes On Click:

(The purpose of this code is to clear the screen. My customer gets really confused by the screen unless it is clear.)

DoCmd.GoToRecord , , acNewRec (Clears Form)
Me.Combo80.RowSource = "" (Clears ComboBox)
Me.Combo80.Requery (I could probably eliminate this.)

The Customer types in the new school name into the CustomerName text box. The following procedure runs from the After Update procedure of the Customer Name text box.

Me.Dirty = False (forces the save in the Tbl_Customers I have confirmed it gets written)
'DoCmd.GoToRecord , , acNewRec (My attempt to get the combo box to refresh but it doesn't work)
'DoCmd.GoToRecord , , acPrevious (My attempt to get the combo box to refresh but it doesn't work)
Me.Combo80.Requery (Your recommendation but it doesn't work)

The customer may not be able to complete the record at the moment and may navigate away and come back to it to finish it.

But when you navigate away and come back one of two things happen:

1. The new school name does not appear in the combobox.
2. No names appear in the combo box.

So my goal is to make the combo box refresh and scoop up the new Customer Name on click. I know the record is in the table, the query for the combo box is based on the CustomerName in the Tbl_Customers. If I close the front end and reopen it the new Customer appears properly in the combo and displays on the screen.

How can I make this happen without closing and reopening the front end? Nothing so far has worked.

The combo box is based on a select query for the CustomerName in the Tbl_Customers. The combo box is not bound to the table.

Thanks,

Phred

[Phred]

Additional info:

I have been trying the following code.

Me.Dirty = False
Me.Refresh
DoCmd.GoToRecord , , acLast
Me.Combo80.Requery

After everything finishes and the record is saved and displays:

If you click on the combo80 box nothing comes up. No customers show up at all.

If I close the form and reopen the combobox is restored and the new customer is there and I can select it.

[June7]

If you requery the combobox after: Me.Combo80.RowSource = ""
then certainly the list will be empty because there is no SQL statement in the RowSource property. And will stay that way until form is reopened or set the RowSource property with code.

[Phred]

Ahhhhh, Now I get it. My me.combo80.rowsource="" deletes the SQL statement from the row source property of the combo box. It doesn't come back until you close and reopen the form. I was wondering why my SQL statement kept disappearing on me. I don't know how to reset the rowsource property with code. but it sounds like a step forward. Can you show me an example or point me in a direction?

Thanks for your time!

Phred

[June7]

Why even change the RowSource?

Setting is simply:
Me.combo80.RowSource = "SELECT ... ;"

[Phred]

My goal is to just clean up the Add-A-New-Record process and make the combo and screen data synchronize better.

Your question is really good. The answer is I don't know any better way.

My client gets skittish when he sees a different name in the combo box then the record he is looking at. This is happening when a record is deleted or added.

I am trying to get the process cleaned up and I don't have a better way to do it.

I wasn't able to get the combo box to clear and this rowsource "" thing was something I spotted in a forum and tried.

I think this answer will help clear up my immediate issue but I think I will end up with the same problems.

Tomorrow is another day. Thanks for your help. This is still open.

Thanks June7

[June7]

I don't really understand the issue 'make combo and screen data synchronize better'.

Don't follow "My client gets skittish when he sees a different name in the combo box then the record he is looking at. This is happening when a record is deleted or added."