Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    np, Bulzie

  2. #17
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25

    Here's a pic. Forgive the terrible photoshop for the cities

    Click image for larger version. 

Name:	RelationalDB.png 
Views:	13 
Size:	48.0 KB 
ID:	27514Here's a pic. Forgive the terrible photoshop for the cities

  3. #18
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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 = false
    else
    'remove 1st And
    sWhere= mid(sWhere,4)
    'just use the filter
    me.filter = sWhere
    me.filterOn = true
    end if

    Aytee111 or anyone, if you see something off, jump in.

  4. #19
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Bulzie View Post
    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 = false
    else
    'remove 1st And
    sWhere= mid(sWhere,4)
    'just use the filter
    me.filter = sWhere
    me.filterOn = true
    end if

    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.,

    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) 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 Sub

  5. #20
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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.

  6. #21
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Bulzie View Post
    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". -

    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.

  7. #22
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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

  8. #23
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Bulzie View Post
    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"

    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

  9. #24
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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 = False
    Else
    'remove 1st And
    sWhere = Mid(sWhere, 4)

    'just use the filter
    Me.Filter = sWhere
    Me.FilterOn = True
    End If
    End Sub

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-21-2016, 09:53 AM
  2. Filtering Using Combo Boxes
    By Beanie_d83 in forum Access
    Replies: 4
    Last Post: 05-19-2016, 06:34 AM
  3. Combo Boxes Filtering
    By gatsby in forum Forms
    Replies: 1
    Last Post: 07-22-2014, 12:46 AM
  4. Replies: 5
    Last Post: 07-26-2012, 02:30 PM
  5. Filtering options using Cascading Combo boxes
    By Lincoln in forum Database Design
    Replies: 3
    Last Post: 07-15-2011, 08:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums