Help with code (MOVED)

I

Ismailmrabet

New member
Local time
Today, 01:16
Joined
Nov 13, 2020
Messages
4
This post was edited by The_Doc_Man
1. Moved to the correct forum.
2. Edited the code by adding code tags and adding indentation to make it more readable.
3. Added occasional line break to help legibility.
NO OTHER CONTENT WAS ALTERED!

Hi,
I hope you are well..
I would filter Data from a table Access "Table_Fuel" below , using six combo boxes based on six criterias (week,Date,shift,project,IDcontroleur and Référence) for extracting it in a subform "sous_formulaire_extraction" (see pictures below) and using vba code.
But, this code isn't correctly wrote , because I can't extract all the rows of Table source !!!!
So, can you help me to found the error ?


Option Compare Database
CODE :

Code: 
Private Sub cbocontroleur_AfterUpdate()
    Call searchcriteria
End Sub

Private Sub cboDate_AfterUpdate()
    Call searchcriteria
End Sub


Private Sub cboprojet_AfterUpdate()
    Call searchcriteria
End Sub

Private Sub cboreference_AfterUpdate()
    Call searchcriteria
End Sub

Private Sub cboshift_AfterUpdate()
    Call searchcriteria
End Sub

Function searchcriteria()
    Dim week, strDate, strshift, strprojet, strIDcontroleur, strreference As String
    Dim task, strcriteria As String

If IsNull(Me.cboweek) Then
    week = "[semaine] like '*'"
Else
    week = "[semaine]= '" & Me.cboweek & "'"
End If
If IsNull(Me.cboDate) Then
    strDate = "[Date] like '*'"
Else
    strDate = "[Date] = '" & Me.cboDate & "'"
End If
If IsNull(Me.cboshift) Then
    strshift = "[shift] like '*'"
Else
    strshift = "[shift] = '" & Me.cboshift & "'"
End If
If IsNull(Me.cboprojet) Then
    strprojet = "[Intituleprojet] like '*'"
Else
    strprojet = "[Intituleprojet] = '" & Me.cboprojet & "'"
End If
If IsNull(Me.cboreference) Then
    strreference = "[Référence] like '*'"
Else
    strreference = "[Référence] = '" & Me.cboreference & "'"
End If
If IsNull(Me.cbocontroleur) Then
    strIDcontroleur = "[ID_controleur] like '*'"
Else
    strIDcontroleur = "[ID_controleur] = '" & Me.cbocontroleur & "'"
End If
strcriteria = week & " And" & strDate & " And " & strshift & " And" & strprojet & " And " & strIDcontroleur & " And " & strreference
task = "select * from Table_Fuel where " & strcriteria
Me.sous_formulaire_extraction.Form.RecordSource = task
Me.sous_formulaire_extraction.Form.Requery

End Function

Private Sub cboweek_AfterUpdate()
    Call searchcriteria
End Sub
 

Attachments

  • IMG-20201108-WA0017.jpg
    IMG-20201108-WA0017.jpg
    28.5 KB · Views: 554
  • IMG-20201113-WA0002.jpg
    IMG-20201113-WA0002.jpg
    198 KB · Views: 404
Last edited:
To start, you are using a function that doesn't return anything (and has no call arguments). You call SEARCHCRITERIA but inside the function you use STRCRITERIA. You never use a RETURN X type of statement and never use SEARCHCRITERIA=X either. OK, that isn't a fatal error, but it leads to confusion in anyone reading that code.

But second, you are calling a function with a CALL statement, which means you would have discarded any returned value anyway. Inside the function, I can see that you ACTUALLY use STRCRITERIA to update a record source, so you obviously weren't expecting to USE a returned value anyway. Which means you might as well have made SEARCHCRITERIA a SUB as opposed to a function.

But the next question is this: You say you cannot extract all the rows of the source table. When you create a recordsource query string that includes valid WHERE clauses, you wouldn't expect to extract everything. So we need more description of WHY you think you are missing something.
 
A couple of additional thoughts. The correct delimiter for date/time data types is # not ', so that needs to be changed (unless the inappropriately named "Date" field is text). A "Like *" criteria on a text field will fail to return Null values. For those criteria that aren't input, either leave them out of the criteria completely or add "(OR FieldName Is Not Null) to those. In concatenating your strings, you've sometimes missed the space after "AND":

week & " And" & strDate

which will end up with

AndWhatever

instead of

And Whatever
 
you should familiar yourself with different Delimiters to use on
each datatype.
Code: 
Function searchcriteria()
    Dim week, strDate, strshift, strprojet, strIDcontroleur, strreference As String
    Dim task, strcriteria As String

If IsNull(Me.cboweek) Then
Else
    week = "[semaine]= '" & Me.cboweek & "' And "
End If
If IsNull(Me.cboDate) Then
Else
    strDate = "[Date] = #" & Format(Me.cboDate, "mm\/dd\/yyyy") & "# And "
End If
If IsNull(Me.cboshift) Then
Else
    strshift = "[shift] = '" & Me.cboshift & "' And "
End If
If IsNull(Me.cboprojet) Then
Else
    strprojet = "[Intituleprojet] = " & Me.cboprojet & " And "
End If
If IsNull(Me.cboreference) Then
Else
    strreference = "[Référence] = '" & Me.cboreference & "' And "
End If
If IsNull(Me.cbocontroleur) Then
Else
    strIDcontroleur = "[ID_controleur] = '" & Me.cbocontroleur & "' And "
End If
task = "select * from Table_Fuel"
strcriteria = week & strDate & strshift & strprojet & strIDcontroleur & strreference
If Len(strcriteria) > 0 Then
    If Right$(strcriteria, 4) = "And " Then
        strcriteria = Trim$(Left$(strcriteria, Len(strcriteria) - 4))
    End If
    task = task & " where " & strcriteria
End If
Me.sous_formulaire_extraction.Form.RecordSource = task
Me.sous_formulaire_extraction.Form.Requery

End Sub
 
pbaldy said:
A couple of additional thoughts. The correct delimiter for date/time data types is # not ', so that needs to be changed (unless the inappropriately named "Date" field is text). A "Like *" criteria on a text field will fail to return Null values. For those criteria that aren't input, either leave them out of the criteria completely or add "(OR FieldName Is Not Null) to those. In concatenating your strings, you've sometimes missed the space after "AND":

week & " And" & strDate

which will end up with

AndWhatever

instead of

And Whatever
Click to expand...

The_Doc_Man said:
To start, you are using a function that doesn't return anything (and has no call arguments). You call SEARCHCRITERIA but inside the function you use STRCRITERIA. You never use a RETURN X type of statement and never use SEARCHCRITERIA=X either. OK, that isn't a fatal error, but it leads to confusion in anyone reading that code.

But second, you are calling a function with a CALL statement, which means you would have discarded any returned value anyway. Inside the function, I can see that you ACTUALLY use STRCRITERIA to update a record source, so you obviously weren't expecting to USE a returned value anyway. Which means you might as well have made SEARCHCRITERIA a SUB as opposed to a function.

But the next question is this: You say you cannot extract all the rows of the source table. When you create a recordsource query string that includes valid WHERE clauses, you wouldn't expect to extract everything. So we need more description of WHY you think you are missing something.
The_Doc_Man said:
To start, you are using a function that doesn't return anything (and has no call arguments). You call SEARCHCRITERIA but inside the function you use STRCRITERIA. You never use a RETURN X type of statement and never use SEARCHCRITERIA=X either. OK, that isn't a fatal error, but it leads to confusion in anyone reading that code.

But second, you are calling a function with a CALL statement, which means you would have discarded any returned value anyway. Inside the function, I can see that you ACTUALLY use STRCRITERIA to update a record source, so you obviously weren't expecting to USE a returned value anyway. Which means you might as well have made SEARCHCRITERIA a SUB as opposed to a function.

But the next question is this: You say you cannot extract all the rows of the source table. When you create a recordsource query string that includes valid WHERE clauses, you wouldn't expect to extract everything. So we need more description of WHY you think you are missing something.
Click to expand...
I'm beginer in vba access, thank you for your suggestions..so can you help me whriting the right code ?
Click to expand...
 
arnelgp said:
you should familiar yourself with different Delimiters to use on
each datatype.
Code: 
Function searchcriteria()
    Dim week, strDate, strshift, strprojet, strIDcontroleur, strreference As String
    Dim task, strcriteria As String

If IsNull(Me.cboweek) Then
Else
    week = "[semaine]= '" & Me.cboweek & "' And "
End If
If IsNull(Me.cboDate) Then
Else
    strDate = "[Date] = #" & Format(Me.cboDate, "mm\/dd\/yyyy") & "# And "
End If
If IsNull(Me.cboshift) Then
Else
    strshift = "[shift] = '" & Me.cboshift & "' And "
End If
If IsNull(Me.cboprojet) Then
Else
    strprojet = "[Intituleprojet] = " & Me.cboprojet & " And "
End If
If IsNull(Me.cboreference) Then
Else
    strreference = "[Référence] = '" & Me.cboreference & "' And "
End If
If IsNull(Me.cbocontroleur) Then
Else
    strIDcontroleur = "[ID_controleur] = '" & Me.cbocontroleur & "' And "
End If
task = "select * from Table_Fuel"
strcriteria = week & strDate & strshift & strprojet & strIDcontroleur & strreference
If Len(strcriteria) > 0 Then
    If Right$(strcriteria, 4) = "And " Then
        strcriteria = Trim$(Left$(strcriteria, Len(strcriteria) - 4))
    End If
    task = task & " where " & strcriteria
End If
Me.sous_formulaire_extraction.Form.RecordSource = task
Me.sous_formulaire_extraction.Form.Requery

End Sub[/CODE
[/QUOTE]
Click to expand...
 
arnelgp said:
you should familiar yourself with different Delimiters to use on
each datatype.
Code: 
Function searchcriteria()
    Dim week, strDate, strshift, strprojet, strIDcontroleur, strreference As String
    Dim task, strcriteria As String

If IsNull(Me.cboweek) Then
Else
    week = "[semaine]= '" & Me.cboweek & "' And "
End If
If IsNull(Me.cboDate) Then
Else
    strDate = "[Date] = #" & Format(Me.cboDate, "mm\/dd\/yyyy") & "# And "
End If
If IsNull(Me.cboshift) Then
Else
    strshift = "[shift] = '" & Me.cboshift & "' And "
End If
If IsNull(Me.cboprojet) Then
Else
    strprojet = "[Intituleprojet] = " & Me.cboprojet & " And "
End If
If IsNull(Me.cboreference) Then
Else
    strreference = "[Référence] = '" & Me.cboreference & "' And "
End If
If IsNull(Me.cbocontroleur) Then
Else
    strIDcontroleur = "[ID_controleur] = '" & Me.cbocontroleur & "' And "
End If
task = "select * from Table_Fuel"
strcriteria = week & strDate & strshift & strprojet & strIDcontroleur & strreference
If Len(strcriteria) > 0 Then
    If Right$(strcriteria, 4) = "And " Then
        strcriteria = Trim$(Left$(strcriteria, Len(strcriteria) - 4))
    End If
    task = task & " where " & strcriteria
End If
Me.sous_formulaire_extraction.Form.RecordSource = task
Me.sous_formulaire_extraction.Form.Requery

End Sub
Click to expand...
I think that you should insert an instruction after every Then..
 
Last edited:
You must log in or register to reply here.

Similar threads

Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
390
Mike Krailo
Mike Krailo
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
780
CJ_London
C
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
853
Uncle Gizmo
Uncle Gizmo
adhoustonj
combobox reverting to original value with Not In List event
Replies
15
Views
724
MajP
MajP
C
Next record code moves to the next record in two sets of records in two textboxes when it should be only be moving down on one
Replies
3
Views
629
ChazRab
C

Users who are viewing this thread

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