Open pop-up form based on selection

[kbrooks]

I've spent the last hour searching for threads I thought were related and patched together some code, which I know very little of.

Here's what I want. I have a form with only 3 fields, one of which is a combobox ("Type") with 3 options ("Charge", "Payment/Adjustment", "Balance Transfer") I have 3 popup forms that correspond to those options. I would like the corresponding popup form to open based on their selection from the Type box.

Here's what I've done:
1. Set the 3 secondary forms to popup and modal
2. Entered the following code in the AfterUpdate event of the Type field (don't laugh):

Private Sub Type_AfterUpdate()
Select Case Me("Type")

Case "Charges":
DoCmd.OpenForm "Charges", , , , , , Me

Case "Payment/Adjustment":
DoCmd.OpenForm "Payments", , , , , , Me

Case "Balance Transfer":
DoCmd.OpenForm "BalanceTransfers", , , , , , Me

End Select
End Sub

I kind of combined the answers on a few threads to come up with this code so it's possible it makes absolutely no sense. When I select a type from the Type field, I get a run-time message with the error "An expression you entered is the wrong data type for one of the arguments". And when I click Debug, it highlights the
DoCmd.OpenForm "Charges", , , , , , Me line. I'm sure it's just the first of many errors it came to.

Can someone please tell me what I did wrong, or if I'm going about it completely wrong?

Another question.....if I have a autonumber (ID) on the main form, and also an autonumber (ID) on each of the secondary forms, will doing this the way I'm attempting link these together? Or do I need to have an ID on the secondary form that corresponds to the one on the main form, and ALSO a autonumber to uniquely identify that record? (Each patient on the main form could have multiple charges, etc on the secondary form.)

 

[mark curtis]

If you only have the three choices then use:

Private Sub Type_AfterUpdate()

if me.combo.value ="Charges" then
DoCmd.OpenForm "Charges"
elseif me.combo.value ="Payment/Adjustment" then
DoCmd.OpenForm "Payments"
else
DoCmd.OpenForm "BalanceTransfers", , , , , , Me
End if
End Sub

 

[kbrooks]

Thanks for your response! I plugged in the above code and got a "Compile error: Method or data member not found" and it highlighted the .combo part of the 1st line.

 

[mark curtis]

When I put me.combo, you need to replace this with the name of your combobox?

if me.combo.value ="Charges" then
DoCmd.OpenForm "Charges"
elseif me.combo.value ="Payment/Adjustment" then
DoCmd.OpenForm "Payments"
else
DoCmd.OpenForm "BalanceTransfers"
End if
End Sub


Zip up the db and post it here and I'll sort it out!

 

[Mile-O]

Did you copy the code verbatim? Is combo a control on your form?

Also, why are you trying to send the current form as an OpenArgs statement? If you are wanting to send the clling form's name then use Me.Name

 

[kbrooks]

No I did not copy the code verbatim, I do have a couple active brain cells. Combo was actually the name of the combo box since I only had one,but I changed it to type and changed it in the code as well.

If I select "Charges" or "Payment/Adjustment" the correct subform opens. But when I select "Balance Transfer" I get the same run-time message with the error "An expression you entered is the wrong data type for one of the arguments" that I got before. It was highlighting the last Else statement so I changed it to read
ElseIf Me.Type.Value = "Balance Transfer" Then
DoCmd.OpenForm "BalanceTransfers", , , , , , Me
instead.

I did check syntax....the combo box value is "Balance Transfer", and the subform is titled "BalanceTransfers" so I'm not sure what else could be wrong.

Mile, I don't quite understand your 2nd question so I'm not sure how to answer.

Mark, I didn't see your request the 1st time to attach the db, but I will now.

Thanks for your help!

 

[Mile-O]

Mile, I don't quite understand your 2nd question so I'm not sure how to answer.

Consider this line:

DoCmd.OpenForm "BalanceTransfers", , , , , , Me


The part at the end: Me

is the OpenArgs property that you are passing to the next form. You are trying to pass the form to the form you are opening. You have no need to do this.

DoCmd.OpenForm "BalanceTransfers", acNormal

is probably better for you.

If you are trying to send the calling form's name to the new form you are opening then this line is what you are after:

DoCmd.OpenForm "BalanceTransfers", acNormal, , , , , Me.Name

 

[kbrooks]

Mile, change your user title to "Genius hand-holder of coding ignorants". I changed the line and it works exactly how I expected it to!! Many thanks!

 

[mark curtis]

ok now

 

[EPD Hater]

moving around in that reworked db file crashed ms access 2000 on my pc! it caused an error in vbe6.dll. It was when i clicked any option in the some combo boxes.