Help with working, but buggy code

L

lovett10

Registered User.
Local time
Today, 04:06
Joined
Dec 1, 2011
Messages
150
Code: 
Private Sub Command55_Click()
Dim strFilter As String
Dim VarItem As Variant
If Me.Check72.Value = True And Me.Check74.Value = True Or Me.Check75.Value = True And Me.Check72.Value Or Me.Check75.Value And Me.Check74.Value Or Me.Check75.Value And Me.Check74.Value And Me.Check72.Value = True Then
MsgBox "You have not entered any search criteria" & vbCrLf & _
        "Please enter some criteria or click cancel to return to the main page" _
        , vbExclamation, "Search Visit"
End If
'If only Quote Number Filled
If Me.Combo41 & "" <> "" And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
strFilter = "[Quote Number] = '" & Me!Combo41 & "'"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If Only Engineer Name
If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
strFilter = "[Engineer 1] = '" & Me!Combo50 & "'Or " & _
"[Engineer 2] = '" & Me!Combo50 & "'Or " & _
"[Engineer 3] = '" & Me!Combo50 & "'"
Debug.Print strFilter
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If only date filled
If IsNull(Me.Combo41) And IsNull(Me.Combo50) And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If Date is blank, rest filled
If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
strFilter = "[Engineer 1] = '" & Me!Combo50 & "'And " & _
"[Quote Number] = '" & Me!Combo41 & "'Or " & _
"[Engineer 2] = '" & Me!Combo50 & "'And " & _
"[Quote Number] = '" & Me!Combo41 & "'Or " & _
"[Engineer 3] = '" & Me!Combo50 & "'And " & _
"[Quote Number] = '" & Me!Combo41 & "'"
Debug.Print strFilter
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If Quote Number is blank, rest filled
If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
"[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
"[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If Engineer is blank, rest filled
If IsNull(Me.Combo50) And Me.Combo41 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If All are filled
If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
"[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
"[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
"[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If
'If all are blank
If IsNull(Me.Combo41) And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
MsgBox "You have not entered any search criteria" & vbCrLf & _
        "Please enter some criteria or click cancel to return to the main page" _
        , vbExclamation, "Search Visit"
End If
End Sub

This is my code, my problem is very occasionally it will not work at all (i press the button but nothing happens) This has happened so far with both the 'Engineer Only and 'If engineer is blank, rest filled

i tested 5 times and these only 1 time didnt work each, please help
 
Your complaint is so unspecific, so you'll be better of debugging it yourself. Follow the Debugging Tips from here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429

Also, read this thread, http://www.access-programmers.co.uk/forums/showthread.php?t=170250 and apply descriptive titles to your post focusing on the issue. ALL posts in the forum are a cry for help, so saying that in the title does not differentiate it from all the other posts.

Make it a habit to rename your controls to something meaningful. Default names like Combo37 and Check15 do not reveal what the thing is about, and one week later you have to check which is which, to understand what is going on.
 
Last edited:
spikepl said:
Your complaint is so unspecific, so you'll be better of debugging it yourself. Follow the Debugging Tips from here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429

Also, read this thread, http://www.access-programmers.co.uk/forums/showthread.php?t=170250 and apply descriptive titles to your post focusing on the issue. ALL posts in the forum are a cry for help, so saying that in the title does not differentiate it from all the other posts.

Make it a habit to rename your controls to something meaningful. Default names like Combo37 and Check15 do not reveal what the thing is about, and one week later you have to check which is which, to understand what is going on.
Click to expand...


The reason my complaint is unspecific is because i have no idea what the problem is. I cant tell you more than what i know myself. I am trying to learn more and more and whilst i thankyou for the tips about renaming i dont think it is helpfull to link me to a time wasters page. I am clearly not trying to waste anyones time. I am only asking people who are nice enough to want to help on this forum with my problem with my code. I do not wish to argue i am not here to do that, i only want to see if i can fix the problem i have with my code. Thankyou
 
Take care to read what is actually written. and then read it again to make sure that you understand.

You did get the advice how to deal with your problem.

As to the titles, the point is that "Help me please code not working" takes up space in all the overviews, and requires opening the post to see what it is about, because it conveys no information. Most people have figured it out - just see the titles. It is a waste of time for all readers if text contains no information.

As to what you problem is: you have code that does what you want 5 out of 6. So the path through the logic differs the 6'th time, or the data in the code are not what you assume. You got the link showing how to check both.

Btw: arguing that you do not wish to argue is logically a contradiction in terms :D
 
spikepl said:
Take care to read what is actually written. and then read it again to make sure that you understand.

You did get the advice how to deal with your problem.

As to the titles, the point is that "Help me please code not working" takes up space in all the overviews, and requires opening the post to see what it is about, because it conveys no information. Most people have figured it out - just see the titles. It is a waste of time for all readers if text contains no information.

As to what you problem is: you have code that does what you want 5 out of 6. So the path through the logic differs the 6'th time, or the data in the code are not what you assume. You got the link showing how to check both.

Btw: arguing that you do not wish to argue is logically a contradiction in terms :D
Click to expand...

Well thankyou for your words of wisdom sir. All i wanted was for someone to proof read my code make sure there are no clear faults as i am no expert when it comes to vba. But apparantly i cant do this without a lesson on how to ask for help? I thought that was the purpose of these forums to ask for help when you need it and to give help when u can.

Btw that was not arguing that was replying, now im arguing
 
If you post code and you need help with it you need to explain what the code actually does. The comments in your code aren't sufficient.

What exactly do you need help with? What did you say doesn't work sometimes? Does it error anywhere?

Also, is that how your code looks in your db? I mean it isn't indented.
 
But apparantly i cant do this without a lesson on how to ask for help?
Click to expand...
Nope, when you obviously do not know how (also see vbaInet's comments above too, that I agree with and should have thought of myself). That's where the forum helps you.

And "proofreading" code that does what is desired, but only sometimes, is pointless without the data, that YOU have. That is why a shortcut is to do the debugging yourself , as described in the first link I gave you, that you apparently still did not bother to check.

You can elect to use the forum as a crutch, each time you run into any problem, or you can use it as source of enlightenment, to learn how to solve problems yourself. Your choice. Bitching will not contribute to either.
 
vbaInet said:
If you post code and you need help with it you need to explain what the code actually does. The comments in your code aren't sufficient.

What exactly do you need help with? What did you say doesn't work sometimes? Does it error anywhere?

Also, is that how your code looks in your db? I mean it isn't indented.
Click to expand...

Hi thanks for the help, my code is for a search function from a form into a report. it opens the report and filters the results. and no it doesnt error anywhere sometimes when i press the button nothing happens at all. and my code does look like that apart from there are gaps between each section but no the majority i dont have indents i dont really understand them are they just to make it more clear to read or do they have another purpose?

Thanks for trying to help even if i havent been very clear.(sorry about that)
 
lovett10 said:
and my code does look like that apart from there are gaps between each section but no the majority i dont have indents i dont really understand them are they just to make it more clear to read or do they have another purpose?
Click to expand...
Absolument! ;) It's a basic skill every programmer must have. I'm struggling to read your code, well I gave up looking at it to be honest. :)

I will attempt to indent your code and you will be able to see how it's structured.
 
vbaInet said:
Absolument! ;) It's a basic skill every programmer must have. I'm struggling to read your code, well I gave up looking at it to be honest. :)

I will attempt to indent your code and you will be able to see how it's structured.
Click to expand...

Thanks for any help you can give
 
Here's your code:
Code: 
Private Sub Command55_Click()
    Dim strFilter As String
    Dim VarItem As Variant
    
    If Me.Check72.Value = True And Me.Check74.Value = True Or Me.Check75.Value = True _
       And Me.Check72.Value Or Me.Check75.Value And Me.Check74.Value Or Me.Check75.Value _
       And Me.Check74.Value And Me.Check72.Value = True Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
    
    'If only Quote Number Filled
    If Me.Combo41 & "" <> "" And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Quote Number] = '" & Me!Combo41 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If Only Engineer Name
    If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!Combo50 & "'Or " & _
                    "[Engineer 2] = '" & Me!Combo50 & "'Or " & _
                    "[Engineer 3] = '" & Me!Combo50 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If only date filled
    If IsNull(Me.Combo41) And IsNull(Me.Combo50) And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If Date is blank, rest filled
    If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'Or " & _
                    "[Engineer 2] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'Or " & _
                    "[Engineer 3] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If Quote Number is blank, rest filled
    If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If Engineer is blank, rest filled
    If IsNull(Me.Combo50) And Me.Combo41 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If All are filled
    If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
    
    'If all are blank
    If IsNull(Me.Combo41) And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
End Sub
I can't really give you any constructive advice because the entire code needs to be rewritten I'm afraid. There's no logic as to what code should run first and what validation should be performed.

So this supposed to be a search function right? Did you get any help from here about this?

Before we proceed, you will need to rename your controls to something meaningful. Check74 and Combo50 don't mean anything to me or anyone else looking at your code. Something like cmbEngineer makes more sense - I can look at conclude that it's a combo box and is bound to the name of an Engineer.

Finally, field names like Engineer1, Engineer2... etc indicate that your tables are not properly normalized. What's the idea behind this?
 
vbaInet said:
Here's your code:
Code: 
Private Sub Command55_Click()
    Dim strFilter As String
    Dim VarItem As Variant
 
    If Me.Check72.Value = True And Me.Check74.Value = True Or Me.Check75.Value = True _
       And Me.Check72.Value Or Me.Check75.Value And Me.Check74.Value Or Me.Check75.Value _
       And Me.Check74.Value And Me.Check72.Value = True Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
 
    'If only Quote Number Filled
    If Me.Combo41 & "" <> "" And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Quote Number] = '" & Me!Combo41 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Only Engineer Name
    If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!Combo50 & "'Or " & _
                    "[Engineer 2] = '" & Me!Combo50 & "'Or " & _
                    "[Engineer 3] = '" & Me!Combo50 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If only date filled
    If IsNull(Me.Combo41) And IsNull(Me.Combo50) And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Date is blank, rest filled
    If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'Or " & _
                    "[Engineer 2] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'Or " & _
                    "[Engineer 3] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Quote Number is blank, rest filled
    If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Engineer is blank, rest filled
    If IsNull(Me.Combo50) And Me.Combo41 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If All are filled
    If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If all are blank
    If IsNull(Me.Combo41) And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
End Sub
I can't really give you any constructive advice because the entire code needs to be rewritten I'm afraid. There's no logic as to what code should run first and what validation should be performed.

So this supposed to be a search function right? Did you get any help from here about this?

Before we proceed, you will need to rename your controls to something meaningful. Check74 and Combo50 don't mean anything to me or anyone else looking at your code. Something like cmbEngineer makes more sense - I can look at conclude that it's a combo box and is bound to the name of an Engineer.

Finally, field names like Engineer1, Engineer2... etc indicate that your tables are not properly normalized. What's the idea behind this?
Click to expand...

Firstly Thanks for indenting my code :). i will rename the fields as you say. and the idea is... there are 3 search criteria "Quote Number" "Engineer Name" and Between "Date Start" and "Date Finish" the user can choose which creteria they search for. thats why i have lots of If statements to cover every possible combination.

Thanks for the help. Really appreciate it
 
vbaInet said:
Here's your code:
Code: 
Private Sub Command55_Click()
    Dim strFilter As String
    Dim VarItem As Variant
 
    If Me.Check72.Value = True And Me.Check74.Value = True Or Me.Check75.Value = True _
       And Me.Check72.Value Or Me.Check75.Value And Me.Check74.Value Or Me.Check75.Value _
       And Me.Check74.Value And Me.Check72.Value = True Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
 
    'If only Quote Number Filled
    If Me.Combo41 & "" <> "" And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Quote Number] = '" & Me!Combo41 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Only Engineer Name
    If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!Combo50 & "'Or " & _
                    "[Engineer 2] = '" & Me!Combo50 & "'Or " & _
                    "[Engineer 3] = '" & Me!Combo50 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If only date filled
    If IsNull(Me.Combo41) And IsNull(Me.Combo50) And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Date is blank, rest filled
    If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'Or " & _
                    "[Engineer 2] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'Or " & _
                    "[Engineer 3] = '" & Me!Combo50 & "'And " & _
                    "[Quote Number] = '" & Me!Combo41 & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Quote Number is blank, rest filled
    If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If Engineer is blank, rest filled
    If IsNull(Me.Combo50) And Me.Combo41 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If All are filled
    If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
    End If
 
    'If all are blank
    If IsNull(Me.Combo41) And IsNull(Me.Combo50) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
End Sub
I can't really give you any constructive advice because the entire code needs to be rewritten I'm afraid. There's no logic as to what code should run first and what validation should be performed.

So this supposed to be a search function right? Did you get any help from here about this?

Before we proceed, you will need to rename your controls to something meaningful. Check74 and Combo50 don't mean anything to me or anyone else looking at your code. Something like cmbEngineer makes more sense - I can look at conclude that it's a combo box and is bound to the name of an Engineer.

Finally, field names like Engineer1, Engineer2... etc indicate that your tables are not properly normalized. What's the idea behind this?
Click to expand...

Firstly Thanks for indenting my code :). i will rename the fields as you say. and the idea is... there are 3 search criteria "Quote Number" "Engineer Name" and Between "Date Start" and "Date Finish" the user can choose which creteria they search for. thats why i have lots of If statements to cover every possible combination.

Thanks for the help. Really appreciate it
 
Just to clarify lovett, I didn't say you should rename the fields, I said you should rename the controls. A field is what you have in tables while a control is a textbox, a combo box... etc.

Ok, so what are the data types of each of those three fields you mentioned?

Still need to know this, why do you have Engineer1 to 3 as field names? What will you do if you have a fourth engineer?
 
vbaInet said:
Just to clarify lovett, I didn't say you should rename the fields, I said you should rename the controls. A field is what you have in tables while a control is a textbox, a combo box... etc.

Ok, so what are the data types of each of those three fields you mentioned?

Still need to know this, why do you have Engineer1 to 3 as field names? What will you do if you have a fourth engineer?
Click to expand...

Sorry i meant control source and have now renamed the comboboxes.

I have Engineer 1 to 3 as field names because within this program there is a section to add visit sheets, there are 3 options for engineers as we very rarely have more than 2, if there was 4 the chances are they would be doing different things and it could be put as seperate visit sheets. there are 3 tables so when you search in the report it will say "Engineer(s)" and show who went with who, to where on what date.


Sorry if it doesnt make much sence and if you think you have a better way of doing this im all ears. and once again thanks for the help.
 
I wonder whether this would help solve the problem as then it has to run something.. what you think? (Ive changed all the Ifs to Elseif)

Code: 
Private Sub Command55_Click()
    Dim strFilter As String
    Dim VarItem As Variant
 
    If Me.Check72.Value = True And Me.Check74.Value = True Or Me.Check75.Value = True _
       And Me.Check72.Value Or Me.Check75.Value And Me.Check74.Value Or Me.Check75.Value _
       And Me.Check74.Value And Me.Check72.Value = True Then
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
 
 
    'If only Quote Number Filled
    ElseIf Me.cbrQuoteNumber & "" <> "" And IsNull(Me.cbrEngineer) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Quote Number] = '" & Me!cbrQuoteNumber & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
 
    'If Only Engineer Name
    ElseIf IsNull(Me.cbrQuoteNumber) And Me.cbrEngineer & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!cbrEngineer & "'Or " & _
                    "[Engineer 2] = '" & Me!cbrEngineer & "'Or " & _
                    "[Engineer 3] = '" & Me!cbrEngineer & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
    'If only date filled
    ElseIf IsNull(Me.cbrQuoteNumber) And IsNull(Me.cbrEngineer) And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
 
    'If Date is blank, rest filled
    ElseIf Me.cbrQuoteNumber & "" <> "" And Me.cbrEngineer & "" <> "" And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[Engineer 1] = '" & Me!cbrEngineer & "'And " & _
                    "[Quote Number] = '" & Me!cbrQuoteNumber & "'Or " & _
                    "[Engineer 2] = '" & Me!cbrEngineer & "'And " & _
                    "[Quote Number] = '" & Me!cbrQuoteNumber & "'Or " & _
                    "[Engineer 3] = '" & Me!cbrEngineer & "'And " & _
                    "[Quote Number] = '" & Me!cbrQuoteNumber & "'"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
 
    'If Quote Number is blank, rest filled
    ElseIf IsNull(Me.cbrQuoteNumber) And Me.cbrEngineer & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!cbrEngineer & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!cbrEngineer & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!cbrEngineer & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
 
    'If Engineer is blank, rest filled
    ElseIf IsNull(Me.cbrEngineer) And Me.cbrQuoteNumber & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Quote Number] = " & Chr(34) & Me!cbrQuoteNumber & Chr(34) & " And " & _
                    "[Date Of Work] between fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
 
    'If All are filled
    ElseIf Me.cbrQuoteNumber & "" <> "" And Me.cbrEngineer & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
        strFilter = "[Engineer 1] = " & Chr(34) & Me!cbrEngineer & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!cbrQuoteNumber & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 2] = " & Chr(34) & Me!cbrEngineer & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!cbrQuoteNumber & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)" & " Or " & _
                    "[Engineer 3] = " & Chr(34) & Me!cbrEngineer & Chr(34) & " And " & _
                    "[Quote Number] = " & Chr(34) & Me!cbrQuoteNumber & Chr(34) & " And " & _
                    "[Date Of Work] BETWEEN fVBDate(#" & Me.DateStart & "#) and fVBDate(#" & Me.DateFinish & "#)"
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
 
    'If all are blank
    Else
        MsgBox "You have not entered any search criteria" & vbCrLf & _
               "Please enter some criteria or click cancel to return to the main page" _
               , vbExclamation, "Search Visit"
    End If
End Sub
 
So they represent names of Engineers 1, 2 and 3 right?

Anyway, let's focus on the subject of the thread. You can get some help about the structure of your tables later.

Is your date field a true Date/Time field? And does it contain a Time part?
 
#16: Nope! Like I mentioned in a previous post, the entire thing needs to be re-written.
 
vbaInet said:
So they represent names of Engineers 1, 2 and 3 right?

Anyway, let's focus on the subject of the thread. You can get some help about the structure of your tables later.

Is your date field a true Date/Time field? And does it contain a Time part?
Click to expand...
Yes they represent names of engineers on the visit.

It is a date field. ive used a text box with the short date format as apose the the DTtime picker. Thanks again
 
Here's some code to get you started:
Code: 
    Dim strWhere As String
    
    ' Quote Number
    If Len(Me.QuoteNumber & vbNullString) <> 0 Then
        strWhere = "[QuoteNumber] = " & Chr(34) & Me.QuoteNumber & Chr(34)
    End If
    
    ' Engineer Name
    If Len(Me.EngineerName & vbNullString) <> 0 Then
        strWhere = "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
    Else
        If Len(strWhere) <> 0 Then
            strWhere = strWhere & " AND " & "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
        Else
            strWhere = "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
        End If
    End If
 
You must log in or register to reply here.

Similar threads

T
Solved It takes too long to download data from server
Replies
16
Views
1,421
GPGeorge
GPGeorge
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
1,032
oxicottin
oxicottin
D
Solved Problem with dates in sql
Replies
7
Views
931
disgracept
D
Mike Krailo
Solved Need help with before update code logic?
Replies
10
Views
1,152
Mike Krailo
Mike Krailo
amorosik
Program1 works fine, program2 doesn't, due to a date problem, but code inside are the same
Replies
17
Views
1,431
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom