Where to put a Refresh or Requery

Rhino999

Registered User.
Local time
Today, 12:16
Joined
Feb 8, 2010
Messages
62
I hope someone can help and has solved my exact problem with a simple solution and very little coding.
You should know that I consider myself a beginner in VB and MS Access.

I have 3 forms:
Main form FrmCustMainAboveSubJob
Caption Customer
Record Source Customer

Inside FrmCustMainAboveSubJob above is:
Subform FrmJobTblSubfrm
Caption FrmJobTbl
Record Source TblJobCarpet
Name FrmJobTbl
Source Object FrmTblJobSubfrm
With: Link Master Field: CustomerID
Link Child Field: CustomerID

Inside the subform, FrmJobTblSubfrm above is: Subform FrmQuantityXUnitRateTbl in datasheet view, (like a spreadsheet)

Caption FrmQuantityXUnitRateTbl
Record Source QuantityXUnitRateTbl
Name FrmQuantityXUnitRateTbl
Source Object FrmQuantityXUnitRateTbl
With: Link Master Field: JobID
Link Child Field: JobID

On the FrmQuantityXUnitRateTbl subform is a ComboBox
Name ItemID
Control source Item
Row Source SELECT [UnitRateLookup].[Item], [UnitRateLookup].[ItemDesc], [UnitRateLookup].[UnitRate], [UnitRateLookup].[Taxable] FROM UnitRateLookup;

The ComboBox lists Items that are associated with Jobs that belong to Customers.

What I hope I have described to you is that I have a Main Form which has a SubForm inside of it and inside of that SubForm, I have another SubForm, which has a CombBox.

Upon a Dbl Click (Double Click) of the Item Combobox, a new Form Opens using a DoCmd.OpenForm "ItemDescUnitRate". That Form, updates a table of Items that can be added to any Job for a Customer .

The problem that I have is upon returning from the Double Click of the Item ComboBox Form, ItemDescUnitRateSubform , the lasts Item that was added to the Table is not in the ComboBox dropdown, yet it is in the table. I can hit the F9 Function Key and the Form FrmQuantityXUnitRateTbl SubForm updates properly with any recent changes to the table.

Since the F9 key works, then I think I only need a Refresh, but I'm not sure if I should have a Requery?
A Requery of the Main Form takes me complete out of the 3 forms, back to theSwitchBoard, so that doesn’t work.

Anyway, my real problem is that I have Not be able to figure out where and when I can put the Refresh or Requery to make it happen automatically! Additionally, do I Refresh or Requery the Form or the ComboBox?

I have tried AfterUpdate on the Item ComboBox Form FrmQuantityXUnitRateTbl SubForm, but that does not work, because the update is not happening on that Form.

I have tried putting a Requery in the Close of the Table update Form ItemDescUnitRate, but I am unable to Address the ComboBox Form, because it is not loaded. I ran a routine to show all Form that are loaded in the Project, but only the Main Form, FrmCustMainAboveSubJob is loaded, none of the 2 subform are loaded.

If someone can help me with this problem I would really appreciate it. I have been working on it for a long time. It seems so simple, but I cannot resolve it. I hope you can follow the description of my Forms.

Thanks in advance!
 
Last edited:
Upon a Dbl Click (Double Click) of the Item Combobox, a new Form Opens using a DoCmd.OpenForm "ItemDescUnitRate". That Form, updates a table of Items that can be added to any Job for a Customer .



On the Get Focus event of Item Combobox add the following code
DoCmd.Requery "[Item Combobox] "

Assuming Item Combobox is the name of your dropdown. Note, you will need to put the name in square brackets [ ] as you have a space in the name.
 
This is some code that runs from the On Not On List Event. It is a different approach to what you are using but I have used it for years.

You will need to change the names of forms etc to suit your situation.

Code:
Private Sub WorkOrderNumber_NotInList(NewData As String, Response As Integer)
Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer
    ' Display message box asking if user wants to add a new category.
    strTitle = "This is a New Work Order. Must Create,"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewCategory = MsgBox("You Must add this New Work Order. Continue?", intMsgDialog, strTitle)
    If intNewCategory = vbYes Then
        ' Remove new SubSection from SubSection combo box so
        ' control can be requeried when user returns to form.
        DoCmd.RunCommand acCmdUndo
        
        DoCmd.OpenForm "FrmWorkOrderDetails", acNormal, , , acAdd, acDialog, NewData
        ' Continue without displaying default error message.
        Response = acDataErrAdded
    End If
End Sub
 
Thanks for responding.
The name of the ComboBox is ItemID.
The Control of the ComboBox is Item.

I assume you mean Got Focus becaus e you wrote Get Focus.

" On the Get Focus event of Item Combobox add the following code
DoCmd.Requery "[Item Combobox] " "

When I go to the comboBox and Code in DoCmd.Requery it ask for a Control name ([ContiolName]) so I used the Control Name Item and coded DoCmd.Requery ([Item]) then for good measure I tried DoCmd.Requery ([ItemID]) in both instances I get an error.

It picks up the data in the Combobox and gives me an error.

see attached screen prints.

Got Focus picks up the Data in Field.jpg
Got Focus Scerrn Print 1.jpg

Thanks
 
This works!
I have been trying to find a fix for this for 5 days.
Thanks to everyone who helped me!
This was simple solution, which was exactly what I wanted.

I used a DoCmd.OpenForm with acDialog and a Requery of the ComboBox name. On the ComboBox double click Event, I did the following, See below!

DoCmd.OpenForm "ItemDescUnitRate", acNormal, , , , acDialog
Me!ItemID.Requery
End Sub



Additionally info below in case anyone wants more detailed explanation:
http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm
Opening a form as a Dialog form

When a form opens in Dialog mode, its Popup and Modal properties are set to Yes. This means that:
  • The form will stay on top until you dismiss it
  • The user is forced to interact with the form -- no other forms or windows are active while the Dialog form is displayed
  • If your window is Maximized, the dialog will not affect the window state of the application, even though the popup form will not open Maximized. This means that you won't have your forms resizing as you open and close them
DoCmd.OpenForm "frmSales", acNormal, , , , acDialog


Then I found:
http://www.techonthenet.com/access/comboboxes/refresh_values.php
Question: In Microsoft Access 2003/XP/2000/97, I've created a combo box that is populated by a code table. After I add a new value to the code table it doesn't appear in the combo box list. How do I refresh the values in a combo box?
Answer: Sometimes it is necessary for the user to update code tables while they are using the system. For example, if a user clicks on a combo box and the value that they are looking for is not present, you can set up the combo box to launch the corresponding code table on the combo box double-click event. The only problem with this is that you need to refresh the combo box values after the user has left the code table screen.
To do this, you will need to call the "Requery" method for the combo box.
For example:
Category.Requery

In this example, you have a combo box called Category. The Requery method forces the combo box to refresh the values that it lists.
In our example, you would place this code in the double-click event after the "docmd.openform" code. For example,
Private Sub Category_DblClick(Cancel As Integer)

'Open code table
DoCmd.OpenForm "CategoryCodes", acNormal, , , , acDialog
Form_Suppliers!Category.Requery

End Sub

So when the user double-clicks on the category combo box, the form called "CategoryCodes" would open. After the user has closed the CategoryCodes form, the category combo box would requery its values.
 
Rhino

What did you not try the solution I posted.

I feel you went to a lot of trouble when you had the solution in front of you.

Or did you try it and it didn't work.
 
Last edited:
To RainLover:

Thank you so much for supplying me with a solution.
Unfortunately, I needed the fasted and simplest solution to implement ASAP.
I’m sure that your solution would work fine, but I just do not have enough experience to modify it quickly and diagnose and issues that would arise because of my inexperience.

Again, thank you very much for helping me.
In the future when I have more time I will make an effort to try to use it and see how things work out.

Regards,
Rhino999
 

Users who are viewing this thread

Back
Top Bottom