How to display new records?

Topic

New Reply

I’m new at this I have a “Customers” main form that has several subforms. The “Customer’s Applications” subform displays a list of “Applications” associated with the main form’s current customer record. A Customer can have many Applications and an Application can be used by many Customers, so we’re talking about three tables: the Customers table, the Applications table, and the CustomerApplications “junction” table.

The “Customer’s Applications” subform is defined as a continuous form box that gets all records from the CustomerApplications junction table that match the main form’s current Customer. The subform has one field, a combo box that’s populated from the Applications table. When you select an Application in the combo box, that Application gets added to the CustomerApplications table for the current customer. The main form, subform, combo box, and table structure is working correctly, and I’m sure this whole database is very generic.

One of the entries in the Applications table is “(New Application)”. The AfterUpdate event for the combo box tests for that value and opens up an “Applications Table Maintenance” form (not a subform of the main form or the Applications subform) that lets me maintain the Applications table (e.g., add a new Application to the list). That’s working well – I get to the Applications Maintenance form as expected, where I add a new record to the Applications table..

Here’s my problem: when I close the “Applications Maintenance” form and get back to the main form/subforms, the application that I just added to the Applications table doesn’t show up in the subform’s combo boxes until I click in one of them and press F9. What’s driving me crazy is trying to find the correct event to hook the SendKeys F9 action to. What’s the event that fires when I return from the “Applications Maintenance” form to the “Applications” subform? “OnCurrent” fires, but since it also fires when I SendKey the F9 I can’t use it.

I hope you can decipher my ramble and get me on the right path!

Thanks.

Reply | Quote

Replies

Hmmm, have you tried the VBA command Me.Refresh?

Reply | Quote

you could try putting this on the on close event of the “Applications Maintenance” form
forms![whatever form your going to].requery
that should do it for you
HTH
JerryC

Reply | Quote

That’s not a good answer because you can’t guarantee you are going to “whateverform” after you close the maintenance form and also it’s very poor programming (no offense intended). I have the same problem and I have tried a lot of things to fix this. So far no good solution.

Reply | Quote

when you close the maintenance form you should know which form the database is going back to
i do in my databases also they had several comboboxes to refresh requering the entire form will refresh all at once not just one
please excuse the “whateverform” but i didn’t know the name of the form and simply used this as a filler for the code
sorry

Reply | Quote

I feel that it doesn’t matter whether you know which form it is going back to, it is still bad programming to requery one form from another one. It comes down to scope. The form should requery itself. If any of our programmers did that I would take them out and shoot them myself (staff turnover).

The other thing you mentioned is that in requerying the form you requery every combo box on the form but that is not true in the case of unbound combo boxes. I have a form with four unbound combo boxes which are used for filtering subsequent forms opened from the main form. Requerying the form does nothing for the unbound combo boxes and in fact you can’t requery or refresh the form because there is no underlying query, table or control. If the combo boxes are bound then your suggestion is probably cleaner and certainly simpler.

Reply | Quote

David,

Put the following code in the EventProcedure of the OnActivate event of the form:

Forms.FormName.ComboboxName.Requery

I assume this will also work for a subform but you will have to experiment with it to see exactly how it works. You must make sure the combobox name is correct. I usually change the combobox name using the “Other” tab of the properties dialog so that is has a sensible name that I can remember.

I would avoid the SendKey form of this idea if I were you. I feel it is poor programming but it’s up to you.

This is probably not a brilliant solution as it will requery the combo box every time the form is made active (receives the focus) but it will work until someone tells us a better solution.

Reply | Quote