Forms Bang, in a combo box on form on a Tab

[molly]

I am having trouble with a combo box which is on a form that is on a tab. Does the Tab name need to be referenced in the criteria row ?

I have a subform named "2021sfrm Plan Detail" which is on a TAB named PLAN. This subform has a combo field named ContactID. I pick a Contact person's name. I use the criteria of:

[forms]![2008frm Account]![AccountID]

The referenced "2008frm Account" is another form.

When i am on the Plan Tab and pick a Contact name, I want the contact names that only pertain to that AccountID.

I am including an abreviated small Access file. If you can, You can refer to the Plan Tab and then the combo ContactID and pick a fellow. Then go to the next record and you will see the same guys, which is wrong. i tried a Refresh but that did nothing. any suggestions? thanks

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

You don't have to use the Tab name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

HI. I read the reference and tried the following unsuccessfully on the "Plan" tab, the subform "2021sfrm Plan Detail", the "ContactID" field, Row Source line of the combo box:

Parent!2008frm Account!AccountID

Any suggestions? I am trying to select a contactID which pertains to the AccountID. EG pick from say 3 guys who work at the same company. thanks for any help.

 

[molly]

In my next model called Molly24 which is linked below, I made the combo box as:

[Forms]![2008frm Account]![2021sfrm Plan Detail].[Form]![AccountID]

This code is on the subform named "2021sfrm Plan Detail" and on the row source of the ContactID combo box field.

At least i don't get an error message anymore. However, when i pick on the combo box, Nothing shows up. so i am still stuck. Can you advise?

 

[molly]

I am getting closer. Still have a problem though.

On the mainform, at the tippy top, I made a form field
called Text156AccountID with a row source of:
=[AccountID]

In my subform on the combo box field ContactID in the row source criteria line, I put:

[Parent]![Text156AccountID]

Private Sub ContactID_AfterUpdate()
Me.Refresh
End Sub
----------------------------

So the first time that i choose a Contact in my "Plan" subform, i get what i want. i choose from a short list of contacts which pertain to the main forms accountID.

However, when i go to another record, and choose, I get the same fellows. I need different fellows who pertain to a different AccountID. The refresh does not kick in.

I have been reading that FAQ sheet per PHV.
Any ideas? Thanks

 

[molly]

I think I solved it. Maybe there is a better way though.
Here is my mini file to check out my Combo box (cboContactID) that is on the Plan tab.

Beside criteria on the row source, I made an After Update.

For criteria in the cboContactID row source in the AccountID field, I used:

[Forms]![2008frm Account]![Text156AccountID]

In the After Update, I used:

Private Sub cboContactID_AfterUpdate()
Me.cboContactID.Requery
End Sub

I might have a refresh or requery deal going on when i move through my records. I will check that next.

So this works. But I bet someone smarter than me can make it better. thanks

 

[molly]

One thing to add. What made this finally work was an "ON EXIT" event.

Private Sub cboContactID_Exit(Cancel As Integer)
Me.cboContactID.Requery
End Sub

this is in the cboContactID field which is on the Plan subform where i pick a contactID.

Improvements are welcomed.