UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Filter By Combobox, Access 2010    
 
   
Delta729
post May 16 2018, 12:10 PM
Post#1



Posts: 138
Joined: 11-January 15



I'm not very good with Access and just trying to help create a database. I'm having a problem on my form, runtime error 94: and all the info I keep finding doesn't seem to help (but it's probably just me).

I have following code in after update event on my form. I know to it's something to do with Null values, but where do I put the Nz, or something else to make this work?

CODE
Private Sub cboFirmFilter_AfterUpdate()
    Dim strFirm As String
    
    strFirm = Forms![TollingFrm]![fFirmID]
    DoCmd.ApplyFilter , "[fFirmID] LIKE '" & cboFirmFilter & "*'"

End Sub


I should also mention that the combobox is "cboFirmFilter" is unbound, but that's probably obvious to you all...
This post has been edited by Delta729: May 16 2018, 12:13 PM
Go to the top of the page
 
theDBguy
post May 16 2018, 12:15 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,502
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You declared a variable called strFirm and then assigned a value to it, but I don't see where you're using it in your code.

As for the Nz() part, try using it this way:

CODE
...
strFirm = Nz(Forms![TollingFrm]![fFirmID])
DoCmd.ApplyFilter , "[fFirmID] Like '" & Nz(cboFirmFilter) & "*'"
...

Hope it helps...
Go to the top of the page
 
Delta729
post May 16 2018, 12:22 PM
Post#3



Posts: 138
Joined: 11-January 15



Yep, that was it... I'm an idiot. I added the Nz part and then just removed the variable I declared. Thanks!
Go to the top of the page
 
theDBguy
post May 16 2018, 12:24 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,502
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You're welcome. Glad to hear you got it sorted out. Good luck with your project.
Go to the top of the page
 
Delta729
post May 16 2018, 12:57 PM
Post#5



Posts: 138
Joined: 11-January 15



For the next step on this database, I also need to filter by the "fReportID". They work dependently of each other, but not together.

I now have this for the combobox:
CODE
Private Sub cboFirmFilter_AfterUpdate()
    DoCmd.ApplyFilter , "[fFirmID] LIKE '" & Nz(cboFirmFilter) & "*'"
End Sub


And this for an option group:
CODE
Private Sub Frame1_AfterUpdate()
    Dim strFilter As String
    
    'Set default record source of form
    Const strSQL = "SELECT TollingListTbl.FirstName, TollingListTbl.MiddleName, TollingListTbl.LastName, TollingListTbl.Sfx, TollingListTbl.fReportID, TollingListTbl.Injury, TollingListTbl.PulledDate, TollingListTbl.fFirmID, TollingListTbl.DateOfBirth, TollingListTbl.SSN, TollingListTbl.AddedDate, TollingListTbl.ExpirationDate, TollingListTbl.RemovedDate, TollingListTbl.Notes FROM TollingListTbl"
    
    Select Case Me.Frame1
        Case 1
            strFilter = strSQL & " Where [fReportID] = '1';"
        Case 2
            strFilter = strSQL & " Where [fReportID] = '2';"
        Case 3
            strFilter = strSQL & " Where [fReportID] = '3';"
        
    'If filter applied with no option selected use default record source
    Case Else
        strFilter = strSQL & ";"
    End Select
    
    ' Set record source with filtered SQL
    Me.RecordSource = strFilter
    Me.Requery

End Sub


Can anyone tell me how I'd make these, I'd guess you'd call cascade or work in combination with each other? I need to select the option group first, and then the combobox. I will then have two more fields that are unbound to the table... a FirstNametxt which should match up to the [FirstName] and then LastNametxt which needs to match up to the [LastName] in the table RollingListTbl, on the form TollingFrm.
This post has been edited by Delta729: May 16 2018, 12:58 PM
Go to the top of the page
 
theDBguy
post May 16 2018, 01:08 PM
Post#6


Access Wiki and Forums Moderator
Posts: 73,502
Joined: 19-June 07
From: SunnySandyEggo


Hi Daniel,

Are you creating a "search form?" If so, you might take a look at some of the search form demos in the Code Archive.

Also, it depends on when you want the filter applied. If the user can select multiple criteria, you might elect to give them an "Apply Filter" button where you can evaluate all the choices before applying the filter.

Otherwise, using the AfterUpdate event might result in redundant code where you check all available options in each control included in the choices.

Just my 2 cents...
Go to the top of the page
 
Delta729
post May 16 2018, 01:58 PM
Post#7



Posts: 138
Joined: 11-January 15



Yes, I am creating a search form. Do you or anyone else know of a sample code I could look at that has an option group and a combobox? I went into several archives and while they do have samples that I could download, I'm currently working a job where I can't actually download anything (at least not without getting in trouble).
This post has been edited by Delta729: May 16 2018, 02:12 PM
Go to the top of the page
 
Delta729
post May 16 2018, 07:13 PM
Post#8



Posts: 138
Joined: 11-January 15



Okay, I probably did this wrong, but what I have really works for the law firm where I'm working. I know that I set it up and it's a really pathetic excuse for a database design.

I have an option group and the combo box I reference earlier, but the combo box is not cascading (meaning it does not rely) on the option group selected item to work. I'm trying to filter the form I have... 1st by the report in the option group. This will weed down 8000 + records to only a few thousand. 2nd or next, I want to select the firm from the combo box, which should further filter down the records. This should be filtering the few thousand records that are left to only a couple of hundred.

After I get this working (if I can with my bad database design), I will need to add an option to filter by two other (or null) values for LastName and then FirstName. I would like to type in the last name first and then after it's updated, it will refresh the query. Then the person can type in the first name, and afterwards it will refresh the query again.

Does that make any sense? Two separate controls that are NOT dependant on the other. I hope so, cause I'd hate to start from scratch.

I should also re-mention that the option group works fine. And then if I use the combo box (and make the code in the option box into 'notations (I believe), then the combo box works fine as well.
This post has been edited by Delta729: May 16 2018, 07:20 PM
Go to the top of the page
 
zaxbat
post May 17 2018, 06:26 AM
Post#9



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Do you have the 8000 records on display in a subform datasheet....then as you update the selectionbox it filters some....and then as you make a choice in the combobox it further filters the datasheet? That seems optimum...but I do not see what you are doing with your filters so far.

in the afterupdate event of both the selection and the combo boxes i would call one routine that will evaluate all of the possible filters maybe call it private sub checkallfilters()


CODE
private sub checkAllFilters()
dim wheremain as string, where1 as string, where2 as string, where3 as string, where4 as string

'if user has made a choice in the selection box then where1 = "[somefield] = selectionbox.value"

'if user has made a choice in the combobox then where2 = '[someotherfield] = combobox.value"

'if user has made a choice in some future other typefilter then where3....etc....    'you get it right?


if len(where1 & where2 & where3 & where4 & "") >0 then
   wheremain = " Where "
   if len(where1 & "") > 0 then wheremain = wheremain & where1 & ", "
   if len(where2 & "") > 7 then wheremain = wheremain & " AND " & where2 & ", "
   if len(where3 & "") > 7 then wheremain = wheremain & " AND "  & where3 & ", "
   if len(where4 & "") > 7 then wheremain = wheremain & " AND "  & where4 & ", "
   wheremain = left(wheremain, len(wheremain) - 1) & ";"
end if

mydatasheetsubform.filter = wheremain
mydatasheetsubform.filteron = true

end sub



this is just aircode but think you can figure it out.
This post has been edited by zaxbat: May 17 2018, 06:27 AM
Go to the top of the page
 
zaxbat
post May 17 2018, 08:21 AM
Post#10



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


sorry, was in a hurry when i originally wrote this....should be more like this....

CODE
private sub checkAllFilters()
dim wheremain as string, where1 as string, where2 as string, where3 as string, where4 as string
dim myand as string
'if user has made a choice in the selection box then where1 = "[somefield] = selectionbox.value"

'if user has made a choice in the combobox then where2 = '[someotherfield] = combobox.value"

'if user has made a choice in some future other typefilter then where3....etc....    'you get it right?

if len(where1 & where2 & where3 & where4 & "") >0 then
   wheremain = " Where "
   if len(where1 & "") > 0 then
     wheremain = wheremain & where1 & ", "
     myand = " AND "
   end if
   if len(where2 & "") > 0 then
      wheremain = wheremain & myand & where2 & ", "
      myand = " AND "
   end if
   if len(where3 & "") > 0 then
      wheremain = wheremain & myand & where3 & ", "
      myand = " AND "
   end if
   if len(where4 & "") > 0 then
      wheremain = wheremain & myand & where4 & ", "
      myand = " AND "
   end if
end if
mydatasheetsubform.filter = wheremain
mydatasheetsubform.filteron = true

end sub
Go to the top of the page
 
Delta729
post May 17 2018, 08:48 AM
Post#11



Posts: 138
Joined: 11-January 15



Thanks for responding, I'm headed into work soon and will mess with this and see if I can get it to work, when I get there. I'm very much a novice and not sure I get the code... I was hoping to see a sample code tidbit that I could emulate, something with a real where clause, I will take some time to decipher.
Go to the top of the page
 
zaxbat
post May 17 2018, 10:48 AM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Okay....try this.....I wouldn't spend any time on the code in those other two posts of mine...it was not completely correct. But this demo works fine.
This post has been edited by zaxbat: May 17 2018, 10:59 AM
Attached File(s)
Attached File  DoFilters.zip ( 39.63K )Number of downloads: 15
 
Go to the top of the page
 
Delta729
post May 17 2018, 11:36 AM
Post#13



Posts: 138
Joined: 11-January 15



Thanks so much, I see where I was headed in the wrong direction now! I was trying to do everything off of one form in one table, without using a subform.

Most of the data will mostly come from being on data entry form. I will actually only need two comboboxes; 1 table (or drop down, after being linked) for the report type, and 2nd table for what firm sent the data to be inserted. The rest needs to be a data entry form for things like [FirstName] and [LastName], and all this needs to be fitered, after the data has been entered.

I'll play with this to see if I can make it work. They are supposed to be having an actual database developer come in, but I've been hearing that same story for several weeks. This will probably be all a waste of time on my part, but thank you all so much for you help!

Go to the top of the page
 
zaxbat
post May 17 2018, 11:39 AM
Post#14



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


thumbup.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2018 - 07:25 AM