codequest
asked on
Refresh ComboBox Query
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!
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!
GRayL
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:<
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")
End With
End Sub
hth
Alan ";0)
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")
End With
End Sub
hth
Alan ";0)
On the after insert event handler on the subform LinkedRecords, refresh the combo e.g.
private sub Form_AfterInsert()
Me.Parent.Form.LinkRecords
'or I think its is if above dont work
Me.Parent.LinkRecordsCodes
end sub
this forces the requery after you insert a new record
private sub Form_AfterInsert()
Me.Parent.Form.LinkRecords
'or I think its is if above dont work
Me.Parent.LinkRecordsCodes
end sub
this forces the requery after you insert a new record
ASKER
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
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
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?
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?
ASKER
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....
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....
ASKER
"built a button to requery the combo as a stopgap" .... in the subform header....
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
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
can u tell me how u call this form? give code example
ASKER
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).RecordsetClon
rst.FindFirst fldID & " LIKE '*" & fldVal & "*'"
If Not rst.NoMatch Then Forms(frmID).Bookmark = rst.Bookmark
Set rst = Nothing
End Sub
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).RecordsetClon
rst.FindFirst fldID & " LIKE '*" & fldVal & "*'"
If Not rst.NoMatch Then Forms(frmID).Bookmark = rst.Bookmark
Set rst = Nothing
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank much for the inputs. After some messing around, I put this construct
Private Sub Form_Activate
me.NameOfSubForm.Form.Name
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!
Private Sub Form_Activate
me.NameOfSubForm.Form.Name
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!