Opening forms with coding (1 Viewer)

LHolden · Aug 13, 2012

Hi all,

I'm really new to access, and even newer to coding. my basic goal is to have a button open 1 of 3 forms based on some criteria on the current form. I have 2 combo boxes on the original form which can be either a yes or a no. I would like the button to open 1 form when 1 is yes, 1 when the other is yes, and the final one when both are yes.
I have been messing around with If Then Else statements, but I'm all thumbs with coding.

Any help is greatly appreciated!

pr2-eugin · Aug 13, 2012

Show us the code you have so far..

LHolden · Aug 13, 2012

well like i said, I've just barely started using VBA and Access, but what I tried was along the lines of

If [cboA]="Yes" And [cboB]="No" Then
DoCmd.OpenForm "A", acNormal, "", "", , acNormal
If [cboA]="No" And [cboB]="Yes" Then
DoCmd.OpenForm "B", acNormal, "", "", , acNormal
If [cboA]="Yes" And [cboB]="Yes" Then
DoCmd.OpenForm "C", acNormal, "", "", , acNormal
End If
End If
End If

pr2-eugin · Aug 13, 2012

The problem is because your If condition's logical flow is wrong, you are checking three conditions instead of checking them one after another using ElseIf you have used If inside If, which means if the First condition is False it will exit the Statement. So reconstruct your statement as

Code:

If [cboA]="Yes" And [cboB]="No" Then 
    DoCmd.OpenForm "A", acNormal, "", "", , acNormal
ElseIf [cboA]="No" And [cboB]="Yes" Then 
    DoCmd.OpenForm "B", acNormal, "", "", , acNormal
ElseIf [cboA]="Yes" And [cboB]="Yes" Then 
    DoCmd.OpenForm "C", acNormal, "", "", , acNormal
Else
    Msgbox("Enter the correct options")
End If

The last Else I have added just in case someone selects 'No' and 'No' or if no selection was made.

Hope this is clear..

LHolden · Aug 13, 2012

Thats really helpful! Thanks for the thorough explanation too! I love these forums because I always learn instead of just being given the answer

Thanks so much!

pr2-eugin · Aug 13, 2012

No problem.. We all learn here everyday..

Good luck !

bob fitz · Aug 13, 2012

If you use the Nz() function, the correct form can be opened even if the user dosen't select "No" in the other combo box.

Code:

If [cboA]="Yes" And[COLOR=black] Nz([[/COLOR]cboB],"No[COLOR=black]")[B]="[/B][/COLOR]No" Then 
    DoCmd.OpenForm "A", acNormal, "", "", , acNormal
ElseIf Nz([cboA],"No")="No" And [cboB]="Yes" Then 
    DoCmd.OpenForm "B", acNormal, "", "", , acNormal
ElseIf [cboA]="Yes" And [cboB]="Yes" Then 
    DoCmd.OpenForm "C", acNormal, "", "", , acNormal
Else
    Msgbox("Enter the correct options")
End If

boblarson · Aug 13, 2012

You could also not use ElseIF's and just use IF's (I usually do it that way if there is always exclusive criteria):

Code:

If Nz([cboA],"No")="Yes" And Nz([cboB],"No")="No" Then 
    DoCmd.OpenForm "A"
End If

If Nz([cboA],"No")="No" And Nz([cboB],"No")="Yes" Then 
    DoCmd.OpenForm "B"
End If

If Nz([cboA],"No")="Yes" And Nz([cboB],"No")="Yes" Then 
    DoCmd.OpenForm "C"
End If

Oh, and you should NOT be putting "" in the Filter and Where clause parts and the default is acWindowNormal so you don't need that part.

So, the shortened

DoCmd.OpenForm "FormNameHere"

would be sufficient. No need to include the rest.

Opening forms with coding (1 Viewer)

LHolden

Registered User.

pr2-eugin

Super Moderator

LHolden

Registered User.

pr2-eugin

Super Moderator

LHolden

Registered User.

pr2-eugin

Super Moderator

bob fitz

AWF VIP

boblarson

Smeghead

Similar threads

Users who are viewing this thread