Subform Comobox Requery

[txfink]

I think this is pretty simple and my lack of knowledge with VBA is holding me back, but I've been trying for a few days and still can't get to work so figured I'd post the question. Let me know if anyone would like any other information. Thanks in advance for taking the time to read / help.

So I have an input form, bound to a table called tblTransaction (form name 'frmAddNewTransaction'). On this input form is a subform called 'subfrmTransactionOwner'. The subform has a combobox on it, called 'cmboSelectOwner'. I also have a button on the main form, called 'cmdAddOwner', that launches a form to add an owner to the system - 'frmAddOwner'.

The main form, subform, combobox and add add Owner form work great, except under this one scenario.

The scenario is if I need to add a new transaction, that has an owner not currently in the system.

The process would be to open 'frmAddNewTransaction' and add the relevant data. Then when the user gets to the subform & combobox, ('subfrmTransactionOwner' & 'cmboSelectOwner'), the owner is not in the list (which it shouldn't be at this point). So the user clicks the button, 'cmdAddOwner', and launches 'frmAddOwner'. The owner is added, 'frmAddOwner' is closed and we're back on 'frmAddNewTransaction' at 'cmboSelectOwner' again. But 'cmboSelectOwner' needs to reload / requery to display the owner I just added.

The problem is, I can't figure out how to get this combobox to requery.

Hope my explanation isn't confusing - let me know if anyone would like more info. Thanks

-smf

 

[postermmxvicom]

I fixed the same scenario this way:

In your combobox's OnGotFocus event add

Me.cmboSelectOwner.requery

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[lameid]

Depending on where you need to do the requery, one of the following.

Forms!frmAddNewTransaction!subfrmTransactionOwner.Form!
cmboSelectOwner.requery

Me!subfrmTransactionOwner.Form!cmboSelectOwner.requery

The former is good from anywhere where the latter of course only works if it is on the calling form. Also in this syntax, subfrmTransactionOwner is the name of the subform control which by default is the name of the subform.

A good way to get the long version is to open up a query in design view, build (right click and build) and navigate the panes and double click the control. That would give you everything before the .requery. <Rant> Through Access 97 you could right click in the VBA screen and do the same but not anymore since Microsoft 'unified' the VBA interface accross Office. <End Rant>

 

[lameid]

postermmxvicom and I cross posted. His solution would work but unesessarily requeries (slows things down).

Also, my 'first' line of code should be on one line as it wrapped.

 

[postermmxvicom]

lameid is correct my code requeries when it is not neeeded (in addition to when it is)..I was being both green and lazy at the time. I should prolly go back and update my old code that is this way

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[txfink]

Perfect - thank you both. It works great now. I added the first line of code from lameid in the 'On Active' event for the main form.

Forms![frmAddNewTransaction]![subfrmTransactionOwner].Form![cmboSelectOwner].Requery

I think the logic would be when the owner is added, 'frmAddOwner' closes and then the main form is active again so the below code has the combo box requeried. Anyways thanks again guys - def appreciate it.

-smf

 

[lameid]

How about doing it on the close event of 'frmAddOwner'?

That would eliminate extra requerying.

 

[txfink]

I thought about doing it that way, and it would certainly work if 'frmAddNewTransaction' was the only form accessing 'frmAddOwner', but I have three other input forms with essentially the same scenario (selecting the owner from a combobox or adding an owner via 'frmAddOwner' if the owner isn't listed). So if I put the code on the close event of 'frmAddOwner' then I would have to do the same for each of the four input forms and I would get an error when the code tries to run for the forms not open (or at least I think I would?). Not sure if that makes sense or not? Let me know if you think I should try to set them all up that way - no doubt you are right it would eliminate extra querying.

 

[postermmxvicom]

I thought about doing it that way, and it would certainly work if 'frmAddNewTransaction' was the only form accessing 'frmAddOwner'...and I would get an error when the code tries to run for the forms not open

I was thinking the same thing after lameid pointed out my extra requerying.

But we can check to see if a form is loaded see faq181-320 and thread702-1481292

So, you could just write a piece of code that checks to see if a given form is open, then requeries it. Then the only thing you'd have to worry about then is if the user could possibly get back to the other form without closing the form for adding a new owner.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[lameid]

Global strFormCallingAddOwner as string

Private Sub cmdOpenAddOpen_click
     strFormCallingAddOwner = Me.Name
     Doccmd.OpenForm "frmAddOwner"
end Sub

Private Sub Form_Close()

'unwrap this line
Forms(strFormCallingAddOwner)!subfrmTransactionOwner.Form!
cmboSelectOwner.requery

End Sub

Or you if the subforms are different you could do something more specific...

Private Sub Form_Close()

 Select Case strFormCallingAddOwner
 Case "frmAddNewTransaction"
   'unwrap this line
    Forms!frmAddNewTransaction!subfrmTransactionOwner.Form!
cmboSelectOwner.requery

 Case "frm2"
   'unwrap this line
   Forms!frm2!subfrm2.Form!
cmboSelectOwner.requery
 Case Else
'Nothing to requery
 End Select
 
End Sub