How to code command buttons to go to form based on users choices..

[Mary A Perez]

I have a form for which the user will choose a particular plan (cmbPlan) then
based on that plan choice the user will choose a product choice (cmbProduct).
Once the the user makes these choices, the next choice will be either to
choose a command button for Current Rates, Admendments, or Previous Effective
Rates.
What I need is code that will go to the correct form (based on Plan and
Product choices) when the command button is clicked.
Previously I had asked for help, & the code suggested sent the user to the
form upon immediately choosing the product. I want the user to be able to
choose what type of form he/she wants. So, I believe it will be the "on
click" event, but not sure.
Thanks for your help.

 

['69 Camaro]

Hi, Mary.

Previously I had asked for help, & the code suggested sent the user to the
form upon immediately choosing the product. I want the user to be able to
choose what type of form he/she wants. So, I believe it will be the "on
click" event, but not sure.

The following is from your previous request:

The OnClick( ) event can execute the code. However, from your description,
it's not clear whether you have a single button which changes captions based
upon the combo box choices or three buttons for each of the three choices.
I'll assume the latter, because that's simpler for beginners. (But tell me
if I'm wrong.) It's also not clear whether there are multiple rate forms
(based upon the plan and product chosen) or a single rate form that displays
diffferent things (based upon the plan and product chosen). Again, I'll
assume the latter. (But tell me if I'm wrong.)

In the following examples, I've named the CurrRatesBtn the button for
Current Rates, AmendBtn for Amendments, and PrevEffRatesBtn for Previous
Effective Rates. The name of the rate form is frmRates, the primary key for
the Record Source of the frmRates form is ProdID (a numeric value), the combo
box for products is named cmbProduct, and the value used to determine the
primary key is in the first column of that combo box (Column(0)). This
example assumes that the rate form will have the necessary fields and produce
the values for the plan based upon the product selected in the cmbProduct
combo box in the current form. (But tell me if I'm wrong.)

Private Sub CurrRatesBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm "frmRates", acNormal, , "ProdID = " &
Me!cmbProduct.Column(0)

Exit Sub

ErrHandler:

MsgBox "Error in CurrRatesBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

Private Sub AmendBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm "frmRates", acNormal, , "ProdID = " &
Me!cmbProduct.Column(0)

Exit Sub

ErrHandler:

MsgBox "Error in AmendBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

Private Sub PrevEffRatesBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm "frmRates", acNormal, , "ProdID = " &
Me!cmbProduct.Column(0)

Exit Sub

ErrHandler:

MsgBox "Error in PrevEffRatesBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Mary A Perez]

Thanks so much for your response.

The code given may not work because the primary key in my
"tblCurrentContractRatesandSpecifications" is the "ProductName" which joins
to the "tblPlan" and the "tblProduct" which each of those tables have the
"autonumber" "PlanId" and "ProductID" as their primary keys. Those two
tables have referenital integrity.
That may not even be the reason, but the "frmPlanProductChoices" use the
"tblPlan" and "tblProduct" for the "cmbPlan" and the "cmbProduct" combo boxes
in this form.
The "cmbCurrentContractRatesandSpecifications" at this time has "onclick"
tied to the "frmCurrentContractRatesandSpecifications", but what I want is to
have "onclick" the form open specific to the Plan and Product chosen.
The "frmCurrentContractRatesandSpecifications" has all the plans and
products with each of their rate information.
Besides the "cmdCurrentContractRatesandSpecifications" there will be 2 other
command buttons for the user to choose from which are
"cmdContractAmendmentRatesandSpecifications" and
"cmdPreviouslyEffectiveContractRatesandSpecifications".
So, my goal is for the user to be able to choose any of those 3 command
buttons after choosing the plan & procut and upon the "click" go directly to
the form that contains only the information for the plan and product chosen.
I hope this helps explain what I need.
I really appreciate your time and help.
Thank you