Pop Up form based on criteria in combo box

[Sandra]

I have a form with a combo box that contains a list
of "Campaigns" with the field name Campaign. This form is
based on a master Donations Table, the PK is TransID. I
need to store additional data for some, but not all of the
campaigns. The "In Memory" and "In Honor" campaigns need
to store additional data in a table called HonorMemory.
The fields in this table are:
HMID - PK for each campaign name
TransID - Link to the master Donation table.
BenefitOf - Name of honoree
By - Person making the donation

The "In-Kind" campaign needs to store data in another
table called InKind. The fields for this table are:
IKID - PK for each description
TransID - Link to the master Donation table
Description - description of the item donated

How do I create a proc that will pop up the appropriate
form based on the criteria selected in the combo box?

Something like: On_Exit (from combo box)
IF Campaign = In Honor OR In Memory
THEN Pop up HonorMemory form
ELSE Do nothing
IF Campaign = In-Kind
THEN Pop up InKind form
ELSE Do nothing

I'm just beginning to understand Access procedures and I
could certainly use some help!

Thanks,
Sandra

 

[Ken Snell]

I would use the AfterUpdate event of the combo box, not the OnExit event.
Sample code is this:

Private Sub Campaign_AfterUpdate()
Dim strFormName As String
Select Me.Campaign.Value
Case "In Honor", "In Memory"
strFormName = "HonorMemory"
Case "In-Kind"
strFormName = "InKind"
End Select
DoCmd.OpenForm strFormName, , , , , acDialog
End Sub

 

[Sandra]

Ken, thanks so much for the help!
Sandra

-----Original Message-----
I would use the AfterUpdate event of the combo box, not the OnExit event.
Sample code is this:

Private Sub Campaign_AfterUpdate()
Dim strFormName As String
Select Me.Campaign.Value
Case "In Honor", "In Memory"
strFormName = "HonorMemory"
Case "In-Kind"
strFormName = "InKind"
End Select
DoCmd.OpenForm strFormName, , , , , acDialog
End Sub

--
Ken Snell
<MS ACCESS MVP>




.

 

[Sandra]

Hi Ken,
This works perfect and brings up the pop up forms. Now my
dilemma is that I cannot seem to link the pop up forms to
the subform from which this is called. Apparently there
is no way to do LinkChildFields and LinkMasterFields with
a pop up form. If I go to the pop up form's table and add
the value manually then tables are correctly linked. So I
need to pass the value of TransID in the subform to the
TransID field on the pop up form. Can I add some sort of
statement to this code that would do that?

 

[Ken Snell]

You can do this a couple of ways: (A) run code in the popup form to get the
value from the subform (works easiest if the popup form will be called only
from the one subform and not from any other form/subform), or (B) run code
in your subform to set the value in the popup form (works so long as the
code in the subform doesn't need to run more steps after the you open the
popup form).

Here are two ways you can do (A):

(A) - 1 (recommended):
Use the OpenArgs argument of the OpenForm action to carry the value of
TransID to the popup form, and then have the popup form use that value.

In the code that I'd given to you, change the DoCmd.OpenForm step to
this:
DoCmd.OpenForm strFormName, , , , , acDialog, Me.TransID.Value

Then, put this code in the popup forms' OnLoad events:
Private Sub Form_Load()
Me.TransID.Value = Me.OpenArgs
End Sub

(A) - 2 (alternative so long as the popup forms will be opened only by the
one subform):
Put this code in the popup forms' OnLoad events:
Private Sub Form_Load()
Me.TransID.Value = Forms!FormName!SubFormName!TransID
End Sub

substituting FormName with the name of the main form, and substituting
SubFormName with the name of the subform control on the main form
(the control that holds the subform).



Here is the way you can do (B):

(B) (so long as the subform's code does not need to do additional steps
after the popup formis opened):

Change the code that I'd given to you to this:
Private Sub Campaign_AfterUpdate()
Dim strFormName As String
Select Me.Campaign.Value
Case "In Honor", "In Memory"
strFormName = "HonorMemory"
Case "In-Kind"
strFormName = "InKind"
End Select
DoCmd.OpenForm strFormName
Forms(strFormName).Controls("TransID").Value = Me.TransID.Value
End Sub

 

[Sandra]

Thanks Ken, you've been a big help again!
Sandra

-----Original Message-----
You can do this a couple of ways: (A) run code in the popup form to get the
value from the subform (works easiest if the popup form will be called only
from the one subform and not from any other form/subform), or (B) run code
in your subform to set the value in the popup form (works so long as the
code in the subform doesn't need to run more steps after the you open the
popup form).

Here are two ways you can do (A):

(A) - 1 (recommended):
Use the OpenArgs argument of the OpenForm action to carry the value of
TransID to the popup form, and then have the popup form use that value.

In the code that I'd given to you, change the DoCmd.OpenForm step to
this:
DoCmd.OpenForm strFormName, , , , , acDialog, Me.TransID.Value

Then, put this code in the popup forms' OnLoad events:
Private Sub Form_Load()
Me.TransID.Value = Me.OpenArgs
End Sub

(A) - 2 (alternative so long as the popup forms will be opened only by the
one subform):
Put this code in the popup forms' OnLoad events:
Private Sub Form_Load()
Me.TransID.Value = Forms!FormName! SubFormName!TransID
End Sub

substituting FormName with the name of the main form, and substituting
SubFormName with the name of the subform control on the main form
(the control that holds the subform).



Here is the way you can do (B):

(B) (so long as the subform's code does not need to do additional steps
after the popup formis opened):

Change the code that I'd given to you to this:
Private Sub Campaign_AfterUpdate()
Dim strFormName As String
Select Me.Campaign.Value
Case "In Honor", "In Memory"
strFormName = "HonorMemory"
Case "In-Kind"
strFormName = "InKind"
End Select
DoCmd.OpenForm strFormName
Forms(strFormName).Controls("TransID").Value = Me.TransID.Value
End Sub

--
Ken Snell
<MS ACCESS MVP>




.