Nested IF statements not working

[flower88]

Hello,
I'm working on a search form. I have 3 combo boxes and I want to find combinations on a query to later show it in a report. Perhaps this is too complicated but I don't know how else to do it. I thought this code worked at first, but then a realized it wasn't really working. I know something is up with the if statements but I don't know how to fix it.

Private Sub Findbtm_Click()
If Me.CompanyCell.Value = "" Then
Beep
    Select Case MsgBox("Cell Company is required", vbOK)
    Case vbOK: 'do nothing
    Case Else: 'do nothing
    End Select
Else
Me.cellbox = Me.CompanyCell
Me.newcell = Me.CompanyCell
Me.firstflux = Me.Companyflux
Me.firstribbon = Me.CompanyRibbon

    If [firstflux] = [company name] And [firstribbon] = [Ribbon Company] Then
            MsgBox ("firstflux<>companyname and firstribbon<>ribbon company")
            DoCmd.OpenQuery "Sales", acViewNormal
            DoCmd.OpenReport "sales", acViewReport
            DoCmd.Close acForm, "sales", acSaveNo
    ElseIf [firstflux] = "" And [firstribbon] = "" Then
     MsgBox ("firstflux="" and firstribbon=""")
            DoCmd.OpenQuery "Sales", acViewNormal
            DoCmd.OpenReport "sales", acViewReport
            DoCmd.Close acForm, "sales", acSaveNo
    ElseIf [firstflux] = [company name] And [firstribbon] = "" Then
     MsgBox ("firstflux=companyname and firstribbon=""")
            DoCmd.OpenQuery "Sales", acViewNormal
            DoCmd.OpenReport "sales", acViewReport
            DoCmd.Close acForm, "sales", acSaveNo
    ElseIf [firstflux] = "" And [firstribbon] = [Ribbon Company] Then
     MsgBox ("firstflux="" and firstribbon=ribbon company")
            DoCmd.OpenQuery "Sales", acViewNormal
            DoCmd.OpenReport "sales", acViewReport
            DoCmd.Close acForm, "sales", acSaveNo
    Else
     Beep
        Select Case MsgBox("Combination not found. Would you like possible combinations?", vbYesNo)
            Case vbYes: 'finds possible combinations
            Me.newcell = Me.CompanyCell
                 Beep
                 Select Case MsgBox("Would you like to make Flux unknown?", vbYesNo)
                      Case vbYes: 'Makes flux company unknown
                          Me.Companyflux.Value = ""
                          Me.newflux = Me.Companyflux
                          
                      Case vbNo: 'leaves flux company as it is
                          Me.newflux = Me.Companyflux
                          
                      Case Else: 'default case to trap any errors
                                 'do nothing
                 End Select
                Beep
                Select Case MsgBox("Would you like to make Ribbon unknown?", vbYesNo)
                      Case vbYes: 'makes ribbon company unknown
                          Me.CompanyRibbon.Value = ""
                          Me.newribbon = Me.CompanyRibbon
                          
                      Case vbNo: 'leaves ribbon company as it is
                          Me.newribbon = Me.CompanyRibbon
                          
                      Case Else: 'defaul case to trab any errors
                                  'do nothing
                End Select
                If [newflux] = "" And [newribbon] = "" Then
       MsgBox ("newflux="" and newribbon=""")
       
                 DoCmd.OpenQuery "Sales", acViewNormal
                 DoCmd.OpenReport "sales", acViewReport
                  DoCmd.Close acForm, "sales", acSaveNo
                    ElseIf [newflux] = [company name] And [newribbon] = [Ribbon Company] Then
              MsgBox ("newflux=companyname and newribbon=ribbon company")
                    
                     DoCmd.OpenQuery "Sales", acViewNormal
                     DoCmd.OpenReport "sales", acViewReport
                     DoCmd.Close acForm, "sales", acSaveNo
                     
                     ElseIf [newribbon] = [Ribbon Company] Then
                     MsgBox ("newribbon=ribbon company")

                     
                     DoCmd.OpenQuery "Sales", acViewNormal
                     DoCmd.OpenReport "sales", acViewReport
                      DoCmd.Close acForm, "sales", acSaveNo
                      
                     ElseIf [newflux] = [company name] Then
                   MsgBox ("newflux=companyname")
                     
                    DoCmd.OpenQuery "Sales", acViewNormal
                     DoCmd.OpenReport "sales", acViewReport
                     DoCmd.Close acForm, "sales", acSaveNo
                Else
                 Beep
                   Select Case MsgBox("Combination not found. Would you like to add a New Product?", vbYesNo)
                
                    Case vbYes: 'opens new product "entry form" and closes "sales" form
                        DoCmd.OpenForm "entry form"
                        DoCmd.Close acForm, "sales", acSaveNo
                        DoCmd.Close acQuery, "sales", acSaveNo
                        DoCmd.Close acReport, "sales", acSaveNo
                    Case vbNo: 'closes "sales" form and goes to "main menu"
                         DoCmd.OpenForm "main menu"
                         DoCmd.Close acForm, "sales", acSaveNo
                         DoCmd.Close acQuery, "sales", acSaveNo
                         DoCmd.Close acReport, "sales", acSaveNo
                    Case Else: 'do nothing
                    
                   End Select
          
                End If
   
     
            Case vbNo:
                'Do nothing
              
            Case Else: 'Default case to trap any errors
                'Do nothing
    
        End Select
          
    
    End If
End If
End Sub

I really appreciate the help!

[patjones]

When you say that it isn't working how it's supposed to, what do you mean by that? Is any of the code executing at all?

In the first test:

If [firstflux] = [company name] And [firstribbon] = [Ribbon Company] Then

where are [company name] and [Ribbon Company] coming from?

Pat

[flower88]

When you say that it isn't working how it's supposed to, what do you mean by that? Is any of the code executing at all?

In the first test:

If [firstflux] = [company name] And [firstribbon] = [Ribbon Company] Then

where are [company name] and [Ribbon Company] coming from?

Pat

The code is suppose to evaluate if the combination chose by the user from 2 drop-down menus exist in the records (I'm using a query to show the records). If it does exits, then it will open up the a query called 'sales'. If it doesn't exist then it will show a message asking if the user wants to find combinations. It'll also ask what variable wants to left unknown. After that, the code finds the combination if there isn't one, it'll ask to add a new entry.

[company name] and [ribbon company] are the field names in the query 'sales'

thanks!

[flower88]

oh, and it doesn't work because it is not finding the record unless I make the two boxes unknown either from the begining or when it asks me to find combinations

[patjones]

The way you wrote it, I don't think the code knows where [company name] and [Ribbon Company] are coming from or even what they are.

If you are looking to find if [firstflux] and [firstribbon] exist in your sales query, you should setup a recordset based upon the query and search the recordset for [firstflux] and [firstribbon].

I think you can probably simplify your If statement structure a little also.

Pat

[flower88]

The way you wrote it, I don't think the code knows where [company name] and [Ribbon Company] are coming from or even what they are.

If you are looking to find if [firstflux] and [firstribbon] exist in your sales query, you should setup a recordset based upon the query and search the recordset for [firstflux] and [firstribbon].

I think you can probably simplify your If statement structure a little also.

Pat

could you tell me a little bit more about how to do the recordset?

Thanks a lot for the help

[patjones]

A quick way to do it, if you are not too concerned about SQL injection attacks, is:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

'Attach recordsets to queries
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM sales WHERE [company name] = '" & [firstflux] & "'", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM sales WHERE [Ribbon Company] = '" & [firstribbon] & "'", dbOpenSnapshot)

I am assuming that [firstflux] and [firstribbon] are strings, thus the reason for enclosing them in ' ' in the comparison. If the selections from the drop-down lists are evaluating to numbers, then you can remove the ' '.

You can then use rs1.EOF to test if [firstflux] is found in [company name], and rs2.EOF to test if [firstribbon] is found in [Ribbon Company]. The EOF flag is set to TRUE if the search returned nothing, and FALSE if the search found at least one match.

Pat

[flower88]

A quick way to do it, if you are not too concerned about SQL injection attacks, is:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

'Attach recordsets to queries
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM sales WHERE [company name] = '" & [firstflux] & "'", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM sales WHERE [Ribbon Company] = '" & [firstribbon] & "'", dbOpenSnapshot)

I am assuming that [firstflux] and [firstribbon] are strings, thus the reason for enclosing them in ' ' in the comparison. If the selections from the drop-down lists are evaluating to numbers, then you can remove the ' '.

You can then use rs1.EOF to test if [firstflux] is found in [company name], and rs2.EOF to test if [firstribbon] is found in [Ribbon Company]. The EOF flag is set to TRUE if the search returned nothing, and FALSE if the search found at least one match.

Pat

Hello again,
I did what you said. I used the recordset code you gave me but now it is giving me this error:
"Run-time error '3061'
Too few parameters. Expected 3"

Any ideas what might be wrong?

Thanks for your time!

[flower88]

Actually, I figured out what was wrong. I had the expression like [forms].[sales].[firstflux] in the query criteria.

Now, it is not doing what I want it to do. How do I make it display only the combination I am looking for?

I'm about to quit this, I don't know what to do. Please help.

[patjones]

Well, what is it doing? Have you set a breakpoint in the code and stepped through it line by line to see how it is executing and whether the recordsets and .EOF tests are evaluating correctly? Also, you need to post the code. We can't work in the dark. Thanks.

Pat

[flower88]

Here it is

Private Sub Findbtm_Click()

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
  
'Attach recordsets to queries
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM [Flux Information] WHERE [Company Name]" & "='" & [Companyflux] & "'", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Ribbon Information] WHERE [Ribbon Company]" & "='" & [CompanyRibbon] & "'", dbOpenSnapshot)

'companycell is required
If Me.CompanyCell.Value = "" Then
Beep
    Select Case MsgBox("all fields are required", vbOK)
    Case vbOK: 'do nothing
    Case Else: 'do nothing
    End Select
Else
Me.cellbox = Me.CompanyCell

'Evaluate values. if either of the combo boxes are empty then it will look up every possible combination and show it on the query 'Sales'
    If [Companyflux] = "" Or [CompanyRibbon] = "" Then
        MsgBox ("empty fields")
        DoCmd.OpenQuery "sales"
 'if they aren't empty then it is going to se if the boxes match a record and open the query 'Sales'
    Else
       If [Companyflux] = rs1.Fields("company name") And [CompanyRibbon] = rs2.Fields("ribbon company") Then
          MsgBox ("finding records")
          DoCmd.OpenQuery "sales"
'if the record is not there, say it wasn't found /////this is not working so far////
        Else
          MsgBox ("not fouund")
                           
       End If
             
           
  End If
End If
End Sub

Now it is finding the pair of records corresponding to the cellbox. When either of the combo boxes companyflux or companyribbon are empty, then it is finding all data that goes along with the cellbox. (this is good). When the boxes are filled and there is a record matching the three boxes, it find that record and opens the query (Sales).
The problem now is that when there is no record, it still opens the query 'Sales' but with no information. For now, I just want it to say there is no record.

I hope this helps...

[patjones]

I think the problem here is that you don't understand how the recordsets work.

For instance, in writing out

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM [Flux Information] WHERE [Company Name]" & "='" & [Companyflux] & "'", dbOpenSnapshot)

you are already finding out if there are any matches in [Company Name] for the value chosen in [Companyflux]. That is what the WHERE part of the SQL statement does. You don't need to do this comparison again in the If statement down below.

What you do want in the If statement is to find out what the results of that SQL were. If there are matches in the field [Company Name] for the value [Companyflux], the recordset rs1 will populate with those records, and rs1.EOF will evaluate to false; otherwise, if there were no matches, rs1 contains zero records and rs1.EOF evaluates to true.

So you really want something like

If Not rs1.EOF And Not rs2.EOF Then
     MsgBox ("finding records")
     DoCmd.OpenQuery "sales"
Else
     ...
     ...
End If

I would suggest doing a search for "DAO recordsets" and reading the help pages to get an understanding for what recordsets do. An understanding of recordsets is crucial for any remotely non-trivial work with databases.

Pat

[flower88]

Thanks for the suggestion.