Help with If Then Else Statement 1

[MMichaels]

I have a button for a search form I made and I am very VERY new to writing in VB. In the “On Click” event procedure, I want to do an If Then Else statement, like this:

If [FormType] = “Back Office” then OPEN frm Main
Else
If [FormType] = “Accounts Payable” then OPEN frmAccounts Payable
Else
If [FormType] = “Assets” then OPEN frmAssets

FormType is a field in my table and frmMain, frmAccounts Payable and frmAssets are all different forms that update that table upon data entry.

I am not sure how to write this in VB… Can someone please help! Thanks in advance!

Melissa

 

[PHV]

Have a look at the DoCmd.OpenForm method and the Select Case instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Melissa,

You're very close - the use of ElseIf here, I believe, would be appropriate.

If [FormType] = "Back Office" Then
 DoCmd.OpenForm "frmMain", acNormal
    ElseIf [FormType] = "Accounts Payable" Then
     DoCmd.OpenForm "frmAccountsPayable"
        ElseIf [FormType] = "Assets" Then
         DoCmd.OpenForm "frmAssets"
End If

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Bah..forgot arguments after Accounts Payable and Assets cases, but you get the idea. PHV's suggestion with the Select Case instruction would work wonders as well...both ElseIf or Select Case approaches would be easy to maintain.

~Melagan
______
"It's never too late to become what you might have been.

 

[MMichaels]

Yay! That worked.. except I have one more question because I'm a dork and forgot to mention, that I am doing a search on a "Member Name" also, so the form looks a little like this:

"member name" "formtype"
Brad Pitt Back Office
Tom Cruise Assets
Paris Hilton Accounts Payable

So, how do I get it to open that form for that member name?

Sorry to be so confusing!!! You are very helpful!

Melissa

 

[PHV]

Have a look at the 4th argument of the DoCmd.OpenForm method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MMichaels]

Great! Thank you! I think that worked... one more question, how do I get that search form to close after I click on the record I want?

 

[PHV]

As the very last line (just above End Sub):
DoCmd.Close acForm, Me.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MMichaels]

Ok, so everything is working great except one little thing. Its not pulling up the EXACT record I want, I know you wanted me to put a "wherecondition" in there but I am not sure how to state it correctly, as everything I have tried doesn't work.

So... lets say that Brad Pitt, Tom Cruise and Paris Hilton all have records coming from the Accounts Payable form. When I run the search, it shows each, but when I click to open that form, it pulls up the first record only. For example: I am searching for Tom Cruise and it pulls up Brad Pitt.

In the "wherecondition", I was typing "[mbr_Name] = ???"
??? = not sure how to make it pull whatever name I want it to pull, one I find the record I want to open?

Do I make any sense? Sorry I am such a VB newbie... but I do appreciate your help immensely!!!!!!!!! I couldn't have gotten this far without you!!!!

Melissa

 

[PHV]

The basic idea:[tt]
"[name of field] = '" & Forms![name of search form]![name of control] & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886