Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Refresh ComboBox Query

Posted on 2006-07-22
13
Medium Priority
?
5,280 Views
Last Modified: 2011-08-18
This is a general scenario that I use in several different places in my DB, with multiple types of linked records.

I've got a Form "Main" with subform "LinkedRecords" with a combobox "LinkedRecordsCodes".  The source for the combobox is a query on a table "LinkedRecords".   I've got another form where I can add records to "LinkedRecords".  (The subform just provides a brief listing of what the linked records are...it's source is the many to many relationship table between Main and LinkedRecords).

When I add a record to "LinkedRecords", and then go back to "Main" and attempt to add the "LinkedRecord" in the "LinkedRecord" subform via the combobox, the newly added record doesn't show up in the combobox.

Clearly, I've got to refresh or requery the combo box, but I don't know which, and I don't know which event to use.

I don't want to requery Main because I want it to stay pointed to the record I'm working on.

Any suggestions would be appreciated.

Thanks!

0
Comment
Question by:codequest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17161649
When you say: 'I've got a Form "Main" with subform "LinkedRecords" with a combobox LinkedRecordsCodes".', and then have a table with the same name as the form - trouble!  I expected you to say: "I've got a Form "fmMain" with subform "sfLinkedRecords" with a combobox "cboLinkedRecordsCodes".  You can chase your tail a thousand miles if you do not have a proper naming convention:<  
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 17162215
hi codequest...

Write an after update event proc for the form you use to add the records

Private Sub YourForm_After_Update()

With Form_Main
  .Controls("YourComboName").Requery
End With

End Sub

hth

Alan ";0)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17162427
On the after insert event handler on the subform LinkedRecords, refresh the combo e.g.

private sub Form_AfterInsert()

    Me.Parent.Form.LinkRecordsCodes.Requery

'or I think its is if above dont work

    Me.Parent.LinkRecordsCodes.Requery

end sub



this forces the requery after you insert a new record

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:codequest
ID: 17163554
Maybe a better explanation (aliases for component names)

frmMain                                              frmLinkedRecordAdder (adds to tblLinkedRecord)           tblLinkedRecord
     subfrmLinkedRecord                                                                                                              fldLinkedRecordID
           cmboLinkedRecordLookup                                                                                                 fldLinkedRecordName
                 source = select fldLinkedRecordID, fldLinkedRecordName from tblLinkedRecord


> add tblLinkedRecord row in frmLinkedRecordAdder
> want to see that new row in cmboLinkedRecordLookup

Both frmMain and frmLinkedRecord will be open at the same time, so OnOpen for frmMain is not a good place to do requery


         
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17163607
When I add a record to "LinkedRecords",

this is the subform right?

ok, can u not check out form_afterinsert

try this

private sub form_afterinsert()
    msgbox "Just Added"
end if

now run your code, add a rec. Do u get a msg?
good

the combo, its on the main form right?
so if u know afterinsert a message comes up, can u at that point, not requery the combo?
0
 
LVL 2

Author Comment

by:codequest
ID: 17163834
Thanks for response.    "Add record to LinkedRecords" happens -not- in the "subform"   (happens in frmLinkedRecordsAdder in the model above...how long until we can add graphics?)

So I'm adding the record in the OTHER form, completely separate, not a subform, and then I come back to the Main form/subform/combobox, which is where I want to see the result of the add.

I built a button to requery the combo as a stopgap, but a similar relationship occurs in six instances, so I was hoping I could use the "Activate" or some other event to automagically trigger the requery on the combo.   I tried that, but I got "anamolous" results....either I couldn't keep track of my test cases, or it didn't work...or it worked sporadically....
0
 
LVL 2

Author Comment

by:codequest
ID: 17163839
"built a button to requery the combo as a stopgap"  .... in the subform header....
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17163923
ok, I knew there was this other form, I thought it was a subform.

Ok, how do u call this other form? do u call it from the main form?
If so then thats not a problem

ensure you open the form as acDialog, then requery straight after

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17164070
can u tell me how u call this form? give code example

0
 
LVL 2

Author Comment

by:codequest
ID: 17164269
I open the "Add to LinkedRecords" form from a menu bar, usually.  Sometimes I open it from a button on the Main form, by passing it parameters from this.  

I'm calling it "LinkedRecords" in this example, but they aren't proper "child" records...they've got a standalone play in the DB independent of Main.

The combo box in question, of course, is back on the sub-form of the Main form.

Public Sub Find_Field_Value(frmID As Variant, fldID As Variant, fldVal As Variant)
'If ModuleSortSeq = "Class" Then Exit Sub
Dim rst As DAO.Recordset
DoCmd.OpenForm frmID
If Nz(fldVal, "") = "" Then Exit Sub
Set rst = Forms(frmID).RecordsetClone
rst.FindFirst fldID & " LIKE '*" & fldVal & "*'"
If Not rst.NoMatch Then Forms(frmID).Bookmark = rst.Bookmark
Set rst = Nothing
End Sub
0
 
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 700 total points
ID: 17164757


Hi codequest,

built the forms and tested this, give it a try, if you like...



' this code is for the the after_update event
' of your form called frmLinkedRecordAdder
Private Sub Form_AfterUpdate()
 
  ' Expose the frmMain form properties n members
  With Form_frmMain
    ' Expose the subform properties n members
    With .subfrmLinkedRecord
      ' Explicitly address the combobox control on the subform
      .Controls("cmboLinkedRecordLookup").Requery
    End With
  End With
End Sub



hth
Alan ";0)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1300 total points
ID: 17165726
firstly, your code. If u open form as dialog, it ensures that has focus so remaining code is run after form is closed.

DoCmd.OpenForm frmIDm, , , , , acDialog




ok, so this form can be opened in multiple places

Now this is what we can try, add the following code in a new module


Public Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            fIsLoaded = True
        End If
    End If
End Function


Then save your module as something

Now in the form LinkedRecords, on the Form_AfterInsert, we can add code to check if a certain form is open and if so, update fields on it

private sub Form_AfterInsert()
    if fIsOpen("nameofmainform") then
        forms!mainform!subform.form!comboboxname.requery
        'or perhaps like this, cant remember syntax exactly
        forms!mainform!subform.form.comboboxname.requery
    end if
end sub

0
 
LVL 2

Author Comment

by:codequest
ID: 17169611
Thank much for the inputs.   After some messing around, I put this construct

Private Sub Form_Activate
me.NameOfSubForm.Form.NameOfComboBox.Requery
End Sub

in the "Main" forms where the type of combo box I'm focused on is used, and it works.

I recognize this is incredibly inefficient, since it bangs the requery every time the form is touched, but this is a one-user DB (me), and always will be, and there's no noticible response delay at the volumes I'm using.

alanwarren, I attempted to get your construct to work, and wasn't able to...though I may not have executed it propertly.  I appreciate your taking the time to build the test.

rockiroads, I see what you're doing with the dialog and "test for open", and that looks right...

If anyone wants an efficient implementation, I suggest looking at those methods.

As for me, I've got to get back to populating the DB, and so I'll live with my fix for the moment.

I wouldn't have found my solution without the prompts to keep at it, so points all around.

Grazie!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

730 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