Solved

Access 2007: Main form with subforms - after adding item to Combo box on Main Form, subforms do not refresh (?)

Posted on 2008-10-26
21
2,939 Views
Last Modified: 2013-11-28
I have a main form with 4 subforms.  The main form has a combo box that you choose a value and the subforms work fine.  To add to the combo box, I have a command button that opens a popup form to add an item to.  I close the form and the new item is in the combo box.
However, when I choose the NEW item from the combo box, the subforms do not update - or go to new blank records.  If I choose one of the previous items from the combo box and hit [F5] to refresh it then works when I choose the new item from the combo.  How and where do i put code to make the form/subforms refresh for the new item in the combo box.  
0
Comment
Question by:mlaurin
  • 11
  • 9
21 Comments
 
LVL 13

Expert Comment

by:wiswalld
ID: 22809349
After Update of the combobox. Then refresh the subforms.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22810471
...the code would look something like this:

Sub YourComboBox_AfterUpdate()
  Me.Subform1.Form.Requery
  Me.Subform2.Form.Requery
  Me.Subform3.Form.Requery
  Me.Subform4.Form.Requery
End sub

Obviously you will have to substitute your own subform names

JeffCoachman
0
 

Author Comment

by:mlaurin
ID: 22812424
Thanks, I will try this tonight and get back you.
0
 

Author Comment

by:mlaurin
ID: 22818412
It did not work.  However, I already have some code in the ComboxBox_AfterUpdate().

I tried putting it a few different places and got different error messages.  The current one says-
Compile error:
method or data member not found.

Here is what I have in the AfterUpdate:

Private Sub Combo7_AfterUpdate()
 
    Me.sfJobReqQtyPlanks.Form.Requery
    Me.sfJobReqQtyFrames.Form.Requery
    Me.sfJobReqQtyBraces.Form.Requery
    Me.sfJobReqQtyComponents.Form.Requery

   ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LocID] = " & Str(Nz(Me![Combo7], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    End Sub

Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22819389
mlaurin,

Then please describe, in detail, the system by which you select "New" from the combobox.

JeffCoachman
0
 

Author Comment

by:mlaurin
ID: 22827647
Hi,
The main form has an unbound combo box to choose a value.  The subforms are linked to the main form so the correct records come up based on the main form combo box value.  

This is for inventory locations.  The main form combo box is to choose a location.  Then the main form shows the warehouse details, the subforms show different categories of inventory at the location (warehouse).

If a new location is needed - it is not in the combo box, therefore it needs a new record added.  There is a command button to "Add a Location" that opens a popup form to enter the details. After the location details are added, the popup form gets closed.  The "On Close" even of the popup form requeries the combo box on the main form.
So, the new location is available in the combo box but when you choose it - it does not pull up the record on the main form or give new records for the subforms.
However - if you hit [F5] to refresh it then works.

Hope this makes sense and I appreciate your help!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22828853
Swap your code on the combobox

You are re-querying the subforms, then promptly moving to a new record, thus probaly requiring another requery.

So try your code like this:


JeffCoachman
Private Sub Combo7_AfterUpdate()
 

  ' Find the record that matches the control.

    Dim rs As Object
 

    Set rs = Me.Recordset.Clone

    rs.FindFirst "[LocID] = " & Str(Nz(Me![Combo7], 0))

    If Not rs.EOF Then Me.Bookmark = rs.Bookmark 

    

    Me.sfJobReqQtyPlanks.Form.Requery

    Me.sfJobReqQtyFrames.Form.Requery

    Me.sfJobReqQtyBraces.Form.Requery

    Me.sfJobReqQtyComponents.Form.Requery
 

End Sub

Open in new window

0
 

Author Comment

by:mlaurin
ID: 22836850
Thanks but that does not work either.  Possibly the main form has to first be refreshed?  I tried the same format as you gave me to requery subforms and put it before the code to requery the subforms but get an error.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22837524
Then we may be at the point where you need to post a sample of your database that illustrates this issue.
0
 

Author Comment

by:mlaurin
ID: 22842660
Db is attached.
From the switchboard, choose "Add Inventory Items" or at the Forms window the Main form is "2mfInventoryLocation" and the subforms all start with "2".
1. Click the button that says "Add/Edit Location"
2. Enter a new Location into the popup form, then close the form.
3. From the combo box  "Choose Location" - choose the location you entered in the popup form.
4. Here is the part that does not work.  The main form should now show the new location and the subforms should move to new (blank) records.

Thank you for looking at this!

VanWellMasonryInventory1030.mdb
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22843476
"From the switchboard, choose "Add Inventory Items"
Done, Great.

  "Or...

I don't need an "OR", just give me one way to do it.
Because when I click "Add Inventory Items" I don't see a "Add/Edit Location" button (Which is listed as step 2.)

I only see "Add/Edit Location" when I open the "2mfInventoryLocation" form.

Even then, when the "Add Location" pop-up appears, there is already has an existing record in it.
and there is no button on the form to create a new record?

Please provide a direct set of steps that you have walked through yourself, to test.
;-)

JeffCoachman
0
 

Author Comment

by:mlaurin
ID: 22853491
----From the switchboard, choose "Add Inventory Items"
**SORRY - should have been "Add Inventory LOCATIONS"  Which does open the "2mfInventoryLocation" form.
Other than than, the rest of the instructions are accurate.

1. Click the button that says "Add/Edit Location"
2. Enter a new Location into the popup form, then close the form.
** YES, there are several items in here - it is a continuous form that you can enter new locations into.  Just go to the first blank row and enter a test one.
3. From the combo box  "Choose Location" - choose the location you entered in the popup form.
**In the previous step you added a new location to the list.  It is now in the list of the combo box for "Choose Location" at the top of the form.
4. Click on the dropdown combobox, you choose the new item in the combo box. Here is the part that does not work:
The main form should now show the new location and the subforms should move to new (blank) records.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22854109
OK,
;-)

I'll try again tonight
;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22865745
mlaurin,

Question:
Was this form ever working?
The reason I ask is that if you had a single main form and a single subform that worked, you would/could have simply added 3 more subforms.
If, on the other hand, you created the entire Main form and subforms without checking, then something like this could be the result.

The first issue is that you have more relationships established that were originally viewable in the relationship window.

Some of which were "Circular".
Whether this is the source of the problem, I could not determine.
Even when I added the Location table to the Recordsource of one of the subforms and set the relationship to show "All" the locations, it still displayed only Locations 1 and 2.

It is also not clear what the One too many relationship between the main form and the subforms is.
One Location many _______?

Perhaps the solution is simple, but it escapes me because I do not know the details of your database or it's design.

I have posted a sample showing a simple Mainform with two subforms.

If this is the basic setup you are after, you will have to examine the sample carefully and see if you can adapt your database to work in the same way.

JeffCoachman
Access-EEQ23848913OneMainFormTwo.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22865765
Here is a revised sample with a combobox to select the customer, to more closely approximate your setup
Access-EEQ23848913OneMainFormTwo.mdb
0
 

Author Comment

by:mlaurin
ID: 22867470
My Form and subforms work perfect. The  combo box works perfect.  If I choose to add a new record and enter the new location there it works perfect.
The problem is after adding a new location (by clicking the button to open a form, entering  and closing) and then choosing that new location from the combo box.  The Main form and subforms do not refresh.  If I close and reopen, it does work.  

Problem is not with the design of the database or the combo box or the forms that you enter a new location into.

The problem is getting the main form and subforms to refresh/requery after choosing the new item from the combo box.
The main form should refresh and show that new location that is now in the combo box at the top of the main form - above the subforms.  The subforms should have blank records, ready for  data entry.

How about forgetting about the subforms for now.  
How would I get the main form to refresh/requery to show the item that was Entered into the form from the command button and added to the combo box.  

The  Main form is info from the Location table.  I could simply add a record and all would be fine.  Except for multiple other reasons I do not want it to work that way.
The pop up form that opens is also from the Location table.
Basically both forms are from the Location table.  When I enter into the pop up form, then close it, it needs to requery or refresh the Main form to show that new record.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22873896
Ummmm... OK let's be clear...

    "My Form and subforms work perfect."
1. If they did, then you would not have posted this question?
Or am I missing something?
;-)

2. You can have a system that "Works" but still be inefficient (Not normalized), in the same way you can have a car that "Works" but get's horrible gas milage, has no seatbelts, has no spate tire, is not inspected ...ect
;-)

"How would I get the main form to refresh/requery to show the item that was Entered into the form from the command button and added to the combo box. "
Your form already *does* list the newly entered value in the combobox.
Do you mean you want the new value to be "Selected" in the combobox after it is entered?

JeffCoachman
0
 

Author Comment

by:mlaurin
ID: 22877043
When you select the item in the combo box, the main form should then show that.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22892296
mlaurin,

On the main form add:
    Me.Requery
...to the top of the combobox code.

Like so:
Private Sub Combo7_AfterUpdate()
   
    'Requery this form
    Me.Requery
   
    'Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LocID] = " & Str(Nz(Me![Combo7], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
   
End Sub

You also will need a Requery on the OnCurrentEvent of the main form as well, in case the end user uses the navigation buttons to move to the new location.


Notes:
You will have to research making the form requery only when a new location is added (Perhaps when the pop-up form closes after adding a new record.)
But this another separate issue altogether.

JeffCoachman
0
 

Author Comment

by:mlaurin
ID: 22897215
Perfect -  thank you!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22900132
Whew!
;-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now