Help with code (MOVED) (1 Viewer)

Ismailmrabet

New member
Local time
Today, 08:57
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: 499
  • IMG-20201113-WA0002.jpg
    IMG-20201113-WA0002.jpg
    198 KB · Views: 345
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:57
Joined
Feb 28, 2001
Messages
27,001
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:57
Joined
Aug 30, 2003
Messages
36,118
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:57
Joined
May 7, 2009
Messages
19,169
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
 

Ismailmrabet

New member
Local time
Today, 08:57
Joined
Nov 13, 2020
Messages
4
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

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.
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.
I'm beginer in vba access, thank you for your suggestions..so can you help me whriting the right code ?
 

Ismailmrabet

New member
Local time
Today, 08:57
Joined
Nov 13, 2020
Messages
4
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]
 

Ismailmrabet

New member
Local time
Today, 08:57
Joined
Nov 13, 2020
Messages
4
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
I think that you should insert an instruction after every Then..
 
Last edited:

Users who are viewing this thread

Top Bottom