Setting a condition to open one form or another (1 Viewer)

[Laura]

I am having problems with the code below because I would like to add a condition which states

If txtbox = "certain data entry"
then open form1
else open form 2

but I can not seem to get my syntax right, here is my code:
Private Sub open_estate_Click()
On Error GoTo Err_open_estate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ESTATE"

stLinkCriteria = "[DeceasedID]=" & Me![DeceasedList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_open_estate_Click:
Exit Sub

Err_open_estate_Click:
MsgBox Err.Description
Resume Exit_open_estate_Click

End Sub

 

[KevinM]

Dim stDocName As String
Dim stLinkCriteria As String

If MyTexBox = "Whatever" Then
stDocName = "Form1"
stLinkCriteria = "[DeceasedID]=" & Me![DeceasedList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

stDocName = "Form2"
Code here for form2 etc etc......
End If


Exit_open_estate_Click:
Exit Sub

Err_open_estate_Click:
MsgBox Err.Description
Resume Exit_open_estate_Click

End Sub

 

[Laura]

This is great apart from I led you astray saying the condition is in a text box. in fact if I want to do this properly I will have to relate this code to a list box.

So the list box called DeceasedList I have already mentioned has 5 columns and the 5th column is what i want to use for the criteria of my open forms IF statement.

So rather than "MyTextbox = whatever" I need "fifth row of my selected record within DeceasedList = whatever" Does this make sense? Any idea how to define the 5th box in a list of details? maybe I should use a query?

 

[Mitch]

The syntax for refering to list/combo box columns is -

' Columns are indexed from 0
If [DeceasedList].Column(4) = "Whatever" then
'open your form
else
'open your other form
End If

HTH

Mitch.

 

[Laura]

thanks I appreciate that.

Also one last question, what is the syntax for stating lots of criteria?

I have tried "whatever" or "whatever", like in a query but it doesn't work.
I have also tried commas and brackets and cannot find the syntax in my book or on help.

 

[AlaskanDad]

To add on to the last piece of advice, you state multiple criteria like this:

If [DeceasedList].Column(4) = "Whatever" _
Or [DeceasedList].Column(4) = "SomethingElse" Then