refresh dataset used by combobox

[Zarty]

I have a main form and on that form there is a subform. The subform displays detail records relating to the main form... the Main form gets its record set from TableA. The subform gets its data from TableB and TableA and Table be are linked via a FK relationship. On the subform, there is a combo box that returns data from a lookup, TableC. I also have a button on the form to open TableC to allow new entries. All works find apart from the fact that if I add a new entry to TableC, it doesn't appear in the combobox on the subform. I guess I have to used requery but I can't get it to work. Suggestions please?

Zarty

 

[dkinley]

Try the OnCurrent() event of the form ....

Me.Refresh

-dK

 

[maxmangion]

put Me.Requery on the afterupdate event.

 

[Zarty]

The me.refresh didn't work but had the effect of refreshing the subform but not actually getting the updated data in the combobox on the subform. Also it had the effect of not putting the comboxbox dropdown on the next empty row at the bottom of the list - it just went to the first row.

The me.requery didn't work either although I think this is probably what I should be using.

I found this link..

http://en.allexperts.com/q/Using-MS-Access-1440/Requery-sub-form.htm

Although the info on that page seems promising, I can't get my issue fixed using the examples there...

First example on that page:-

Forms![Nonformulary Drugs]![SubformControlName].Form.Requery

If I replace my subform name and the combobox name above and put that in the 'after update' even of the form used to update the lookup table, I get an error that the form could not be found.

I'm also a bit puzzled that the 'Nonformulary Drugs' form is stated as the main form and yet the 'SubformControlName' implicitly infers that its a control on a subform. I would have thought that you would enter the subform's name, not the main form name? Either way, I can't get it to work.

Zarty

 

[maxmangion]

it seems that you are not properly referencing your control i.e.

Forms![Nonformulary Drugs]![SubformControlName].Form.Requery

check out this link how you should be referencing it.

 

[Zarty]

Thank you, that was a useful link and, yes, that was most of the problem. In the popup form that allows rows to be inserted/updated/deleted from the lookup table referenced by the combobox on the subform, I entered an 'After Update' event and put in the corrected object reference as per the link. I tested by inserting a new row in the lookup and it was immediately viewable in the combox's dropdown list on the subform... Great! However, when I deleted a row in the lookup table, the 'After Update' requery didn't seem to have any effect as the deleted row still appeared in the dropdown list. I added a 'After Delete' event to requery but this didn't work either! Any thoughts?

Regards,
Zarty

 

[maxmangion]

try doing a requery on the OnGotFocus event of your combo.

 

[Zarty]

Yeh, I tried that before. I should explain, on the subform, there is are multiple rows and the dropdown appears on any row you click on. By default, when you make focus on the subform, the dropdown appears on the first empty record in the list. Fine. However, if I put the requery on the OnGotFocus event, after the flickering the dropdown appears on the first record. If I then click again on the first empty record at the bottom of the list, it requeries again, flickers for a while and the dropdown is on the first record.

I'm puzzled why the 'After Update' or 'After Delete' don't seem to work for deletes in this case?

Thanks,
Zarty

 

[maxmangion]

Hi,

could you explain from where and how you are deleting your data from the lookup table? And when you do so, do you still have the form open?

 

[Zarty]

There is the main form, which contains a record by record display from 'tableA'. On the main form is a subform that contains a list of records from a Fact table, TableB. The records in TableB are related by FK relationship to TableA. The combobox allows a lookup to TableC. On the mainform is a button to open up another form (eg. LookupForm) to update TableC.

I changed the 'After Update' event on the LookupForm to requery and it worked, kind of. It works for newly inserted records into TableC - meaning that the dropdown list of the combobox on the subform is successfully requeried. What doesn't work properly is that if I go into the LookupForm and delete a record, this isn't successfully requeried - so the deleted record still appears in the Subform's dropdownlist.

Hope this helps!

Zarty

 

[maxmangion]

so if i understand correctly at one time you may have to forms opened and when you delete a record from a formA and return to formB the combo on formB still shows the record deleted.

If that's the case and all the other events requeries did not work, try to requery the combo on the OnActivate event of the form where the combo lies.

 

[Zarty]

Yes, you understand correctly. I tried entering this...

Me.Combobox.Requery

as well as...

Me.Requery

on the OnActivate event of the subform on which the combobox resides. Didn't work in either case.

Other suggestions?

Thanks,
Zarty