np, Bulzie
np, Bulzie
Sorry those fields are left over code, I think yours would be:
For this code, assuming the actual CityID, ProjectID, RegionID, EngineerID is stored in the main table and not the text names that show on the form and comboboxes are called CityName, ProjectTitle, Region, EngineerNames? Also for EngineerID, if not already included add that to the Query for the form.
sWhere = null
if not isnull(Me.CityName) then sWhere = sWhere & " and [CityID]= " & Me.CityName
if not isnull(Me.ProjectTitle) then sWhere = sWhere & " and [ProjectID]= " & Me.ProjectTitle
if not isnull(Me.Region) then sWhere = sWhere & " and [RegionID]= " & Me.Regionif not isnull(Me.EngineerNames) then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
if IsNull(sWhere) then
me.filterOn = falseelse'remove 1st And
sWhere= mid(sWhere,4)
'just use the filterend if
me.filter = sWhere
me.filterOn = true
Aytee111 or anyone, if you see something off, jump in.
Ok. So would this be under an "after update" under which combo box? I name all my combo boxes "cbo****" for example, cboEngineerNames, or cboRegions, etc.,Sorry those fields are left over code, I think yours would be:
For this code, assuming the actual CityID, ProjectID, RegionID, EngineerID is stored in the main table and not the text names that show on the form and comboboxes are called CityName, ProjectTitle, Region, EngineerNames? Also for EngineerID, if not already included add that to the Query for the form.
sWhere = null
if not isnull(Me.CityName) then sWhere = sWhere & " and [CityID]= " & Me.CityName
if not isnull(Me.ProjectTitle) then sWhere = sWhere & " and [ProjectID]= " & Me.ProjectTitle
if not isnull(Me.Region) then sWhere = sWhere & " and [RegionID]= " & Me.Regionif not isnull(Me.EngineerNames) then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
if IsNull(sWhere) thenme.filterOn = falseelse'remove 1st And
sWhere= mid(sWhere,4)
'just use the filterend if
me.filter = sWhere
me.filterOn = true
Aytee111 or anyone, if you see something off, jump in.
Would it look like this then? and would I repeat this with each combo box?
Private Sub cboCityName_AfterUpdate
sWhere = null
if not isnull(Me.CityName) then sWhere = sWhere & " and [CityID]= " & Me.CityName
if not isnull(Me.ProjectTitle) then sWhere = sWhere & " and [ProjectID]= " & Me.ProjectTitle
if not isnull(Me.Region) then sWhere = sWhere & " and [RegionID]= " & Me.Regionif not isnull(Me.EngineerNames) then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
if IsNull(sWhere) thenme.filterOn = falseelse
'remove 1st And
sWhere= mid(sWhere,4)
'just use the filterend if
me.filter = sWhere
me.filterOn = true
End Sub
If your names start with cbo then it would be like
if not isnull(Me.cboCityName) then sWhere = sWhere & " and [CityID]= " & Me.cboCityName
I would either make it a function then just call that function in the AfterUpdate of each Combo box, or you can create a button on the form maybe called SEARCH and behind that button, put that vba code. So they select all the fields they want, then hit the button to display the data.
So would this be the code? Also, I've highlighted here in red where there's an error message reference that says "Compile Error: Expected: end of statement". -If your names start with cbo then it would be like
if not isnull(Me.cboCityName) then sWhere = sWhere & " and [CityID]= " & Me.cboCityName
I would either make it a function then just call that function in the AfterUpdate of each Combo box, or you can create a button on the form maybe called SEARCH and behind that button, put that vba code. So they select all the fields they want, then hit the button to display the data.
Option Compare Database
Private Function cboProjectType_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.cboRegion) Then sWhere = Not sWhere & " and [RegionID]= " & Me.cboRegionif Then sWhere = sWhere & " and [EngineerID]= " & Me.cboEngrName
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Private Function cboLineName_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.cboRegion) Then sWhere = Not sWhere & " and [RegionID]= " & Me.cboRegionif Then sWhere = sWhere & " and [EngineerID]= " & Me.cboEngrName
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Private Function cboRegion_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.cboRegion) Then sWhere = Not sWhere & " and [RegionID]= " & Me.cboRegionif Then sWhere = sWhere & " and [EngineerID]= " & Me.cboEngrName
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Private Function cboEngrName_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.cboRegion) Then sWhere = Not sWhere & " and [RegionID]= " & Me.cboRegionif Then sWhere = sWhere & " and [EngineerID]= " & Me.cboEngrName
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Thanks, seriously.
Sorry, the line that has the error should be 2 lines, one for the Region code and one for the Engineer code. So remove that line and add the ones below
if not isnull(Me.Region) then sWhere = sWhere & " and [RegionID]= " & Me.Region
if not isnull(Me.EngineerNames) then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
So below is my code now, and when I select a combobox dropdown option, I get this error "The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name"Sorry, the line that has the error should be 2 lines, one for the Region code and one for the Engineer code. So remove that line and add the ones below
if not isnull(Me.Region) then sWhere = sWhere & " and [RegionID]= " & Me.Region
if not isnull(Me.EngineerNames) then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
The names of my combo boxes are things like cboLineName, cboEngrName, etc.,
Any advice? I also found this youtube video(https://www.youtube.com/watch?v=choPri7y_o4) that shows what I want to do, but it is very hard to follow. 5:01-5:13 shows what I want to be able to do, then if you watch the rest of the video, hopefully the reporting piece too, but that reporting piece not a concern right now.
Private Function cboProjectType_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.Region) Then sWhere = sWhere & " and [RegionID]= " & Me.Region
If Not IsNull(Me.EngineerNames) Then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Private Function cboLineName_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.Region) Then sWhere = sWhere & " and [RegionID]= " & Me.Region
If Not IsNull(Me.EngineerNames) Then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Private Function cboRegion_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.Region) Then sWhere = sWhere & " and [RegionID]= " & Me.Region
If Not IsNull(Me.EngineerNames) Then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Private Function cboEngrName_AfterUpdate()
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.Region) Then sWhere = sWhere & " and [RegionID]= " & Me.Region
If Not IsNull(Me.EngineerNames) Then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
If IsNull(sWhere) Then
Me.FilterOn = False
Else
'remove 1st And
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End If
End Function
Think it should be Private Sub not Private Function and End Sub. Also maybe declare sWhere as string. So for first one:
Private Sub cboProjectType_AfterUpdate()
Dim sWhere as string
sWhere = Null
If Not IsNull(Me.cboLineName) Then sWhere = sWhere & " and [LineNameID]= " & Me.cboLineName
If Not IsNull(Me.cboProjectType) Then sWhere = sWhere & " and [ProjectID]= " & Me.cboProjectType
If Not IsNull(Me.Region) Then sWhere = sWhere & " and [RegionID]= " & Me.Region
If Not IsNull(Me.EngineerNames) Then sWhere = sWhere & " and [EngineerID]= " & Me.EngineerNames
If IsNull(sWhere) Then
Me.FilterOn = FalseElse
'remove 1st AndEnd If
sWhere = Mid(sWhere, 4)
'just use the filter
Me.Filter = sWhere
Me.FilterOn = True
End Sub