IF statement having multiple conditions

[JQasd]

Private Sub search_Click()
Dim strForm As String

If Me.cbo_mat = "Titanium" Then
If Me.cbo_mt = "Ti-64" Then
If Me.cbo_form = "Sheet Metal" Then
If Me.cbo_data = "Hot Compression" Then
strForm = "FSearch"

Else
If Me.cbo_mat = "Titanium" Then
If Me.cbo_mt = "Ti-6246" Then
If Me.cbo_form = "Sheet Metal" Then
If Me.cbo_data = "Hot Compression" Then
strForm = "TAL7010Compression Data"

Else
strForm = "Master_Table"

End If
End If
End If
End If
End If
End If
End If
End If
DoCmd.OpenForm strForm

End Sub

Hi there, I am trying to run the code but getting the Run Time error 2494.However it executes correctly if I select the Ti-64 as in the first if block. I have to make an If statement based on options selected from 4 combo boxes. I am just trying with two possibilites here so that i could replicate for the rest if it runs. I am new at VBA.Please assit.

thanks

 

[Minty]

You don't need the multiple if's try replacing them with AND

strForm = "Master_Table"  ' This is default set it like this first

If Me.cbo_mat = "Titanium" AND Me.cbo_mt = "Ti-64" AND Me.cbo_form = "Sheet Metal"  AND Me.cbo_data = "Hot Compression" Then 
    strForm = "FSearch"
End if

If Me.cbo_mat = "Titanium" AND Me.cbo_mt = "Ti-6246" AND Me.cbo_form = "Sheet Metal" AND Me.cbo_data = "Hot Compression" Then
    strForm = "TAL7010Compression Data"
End If

DoCmd.OpenForm strForm

Assuming you want your logic to work this way.

 

[Minty]

In fact if you want this to scale out I think you are approaching it from the wrong way. It looks like you are going to end up having a different form for each material type?
This doesn't sound like good design.
Can you post up your table structure and in plain English - not database speak - a brief outline of what you are trying to achieve?

 

[JQasd]

Thanks for your reply

I have 5 combo boxes, the first one is of Material Type for eg Steel, then 2nd combo box is of steel type e.g HS12, third combo box is of material form for eg sheet,billet etc, 4th combo box is of properties mechnaical, physical, thermal and final is data type according to the 3 properties selected above.

according to the material ,its type, form and property selected i want to open the corresponding property report or form,

thanks alot.

 

[Minty]

I think I would be tempted to try and store these different actions in a table, as you could end up with 100 + variations on a theme that are hard coded, or apply some better logic to determine your reporting / form requirements

E.G.

 [B]tblRreporting[/B]
Mat.............. Mt ............... Form ................... Data ................... strForm
Titanium .......Ti-64  ............Sheet Metal  ........... Hot Compression ...........FSearch
Etc

You would then look up the relevant form based on your choices, but you don't need to re-code every time you add a new combination, just add a new record line.

 

[JQasd]

I think I would be tempted to try and store these different actions in a table, as you could end up with 100 + variations on a theme that are hard coded, or apply some better logic to determine your reporting / form requirements

E.G.

 [B]tblRreporting[/B]
Mat.............. Mt ............... Form ................... Data ................... strForm
Titanium .......Ti-64  ............Sheet Metal  ........... Hot Compression ...........FSearch
Etc

You would then look up the relevant form based on your choices, but you don't need to re-code every time you add a new combination, just add a new record line.

Thanks alot for your reply. if you could explain this a bit furthur I am new to VBaccess don't seem to get what you are referring to. Do you recommend me to make a separate table for the combinations possible ?

 

[Minty]

Okay lets assume you have 20 Mat items and 20 Mt items in 5 different Forms with 3 different Data types (I'd really rename those fields names as Form and Data both have specific meanings within Access ! Search for reserved words in Access)

This would give you 20 x 20 x 5 x 3 (6000) possible combinations you might encounter, your If statements will quickly get out of hand trying to accommodate even 1/10 of those.

You may be able to rationalise this down using better logic e.g. If its Titanium then I can only select 3 other options so my form would be x , but this is hard to code and even harder to maintain if you add other criteria or someone moves the goalposts.

Hence make a table that contains all you actual possible combinations and a simple query or Dlookup would give you the strform you need in your code.

 

[JQasd]

You don't need the multiple if's try replacing them with AND

strForm = "Master_Table"  ' This is default set it like this first

If Me.cbo_mat = "Titanium" AND Me.cbo_mt = "Ti-64" AND Me.cbo_form = "Sheet Metal"  AND Me.cbo_data = "Hot Compression" Then 
    strForm = "FSearch"
End if

If Me.cbo_mat = "Titanium" AND Me.cbo_mt = "Ti-6246" AND Me.cbo_form = "Sheet Metal" AND Me.cbo_data = "Hot Compression" Then
    strForm = "TAL7010Compression Data"
End If

DoCmd.OpenForm strForm

Assuming you want your logic to work this way.

Sorry to disturb you again ,can you please tell mw how to open a report if a certain condition is true.

Mine is opening a dialog box showing printing which closes itself. I have defined this in beginning
Dim strReport As String
and ending like this

DoCmd.OpenReport StrReport

thanks in advance

 

[moke123]

DoCmd.OpenReport StrReport, acViewReport

 

[Simon_MT]

The idea of filtering is that you can you fuzzy logic rather than explicit values. The two examples gice "Ti-64" and "Ti-6246" have different properties and the data shown reflect those properties and then you test the record and control the events your want to run.

Simon