473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Access 2003 Filtering subform problems!

Scott Price
1,384 Expert 1GB
Hello,

I'm running Access 2003 trying to filter a subform with approximately 15 records per year per the main record contained on the parent form. The filter works fine if the subform is opened independently, and right-clicking on the subform after opening the main form, and choosing filter by selection also works. The problem is getting the filter to apply when the parent form opens.

I've checked for spelling errors a dozen times, what I currently have for code is:

Private Sub CboConventionName_AfterUpdate()
Dim lngHistYear As Long
Dim strFilterYear As String

lngHistYear = 1990
strFilterYear = "Year = " & lngHistYear
DoCmd.OpenForm "frmConvInformation", acNormal, , "ConvId =" & Me!CboConventionName.Value, , acHidden
Forms!frmConvInformation!sfrmHistory.Form!Filter = strFilterYear
Forms!frmConvInformation!sfrmHistory.Form!FilterOn = True
Forms!frmConvInformation.Visible = True

End Sub.

This produces an error message claiming that Access can't find the sfrmHistory referred to in lines 8 & 9 of the code above.

I have also tried substituting the subform control name (PagHistory) instead of the subform name. This produces a different error message: Object doesn't support this property or method.

Anyone have ideas?

Thanks...
Scott
Jul 3 '07 #1
5 2758
nico5038
3,080 Expert 2GB
Hmm, I guess Access hasn't yet loaded the form, thus your two lines of code aren't yet finding the form.
Better to use the OpenArgs of the Docmd.Openform (the last parm) to pass the information and apply the filter from the OnOpen event of the frmConvInformation.

Getting the idea ?

Nic;o)
Jul 3 '07 #2
Scott Price
1,384 Expert 1GB
Thanks for the reply!

This is what I've changed the code to now:

Dim lngHistYear As Long
Dim strFilterYear As String

lngHistYear = 1990
strFilterYear = "Year = " & lngHistYear
DoCmd.OpenForm "frmConvInformation", acNormal, , "ConvId =" & Me!CboConventionName.Value, , , "Year" = strFilterYear

In the frmConvInformation on open sub I've put in this code:

DoCmd.ApplyFilter , "Year = Me.OpenArgs"

This now gives me two prompt boxes, one asking for the Year value (am I dumb or should it know that Year = 1990?) the other asking for Me.Openargs value (a syntax problem without doubt, but I'm not clicking into what the syntax should be!!)

Upon navigating through these prompt boxes, it still hasn't applied the filter to the subform! Instead of seeing the records that only contain 1990 in the field Year, I see all 187 or so from all years. Help!
Jul 3 '07 #3
nico5038
3,080 Expert 2GB
Try:

DoCmd.OpenForm "frmConvInformation", acNormal, , "ConvId =" & Me!CboConventionName.Value, , , strFilterYear

As you did build the string already.
In the OnOpen use:

me.sfrmHistory.form.filter = me.openargs
me.sfrmHistory.form.filteron = true

This will pass the filter and activate it.

Nic;o)
Jul 3 '07 #4
Scott Price
1,384 Expert 1GB
Yeesh... VBA gives plenty of opportunity to make a guy humble :-)

Many Thanks! Those changes fixed the problem... all working fine now.
Jul 3 '07 #5
nico5038
3,080 Expert 2GB
Glad I could help, success with the application !

Nic;o)
Jul 3 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
19
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main...
0
by: Sabine Oebbecke | last post by:
Hi Experts! I have several forms and reports where controls get their values with 'Forms!MainForm!Control' or 'Forms!MainForm!Subform!Control' resp 'Forms!MainForm!Subform.Form!Control' which...
2
by: Lenin Torres | last post by:
Hi everybody I have an Union Query that works fine. I used this query as the RecordSource for a Form. That Form is used as a subform in another form. Everything works fine, except for the "Filter...
3
by: Malcolm Webb | last post by:
My database was developed and is currently running in Access 2000. I am contemplating upgrading to the latest version of Access (2003?) running on new computers. Will there be any major...
12
by: sparks | last post by:
My boss and I both have access 97 and access 2003 installed on our systems. Lately he has been having a lot of problems. Databases that won't close. The database looks like it closes but access...
11
by: BobBanks | last post by:
Hi, Im making a db for my company in Access 2003 usin xp sp2. Ive created an orders form that has a subform in it. The Order form (parent(is linked to a Orders Qry)) and the Orders Subform...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
17
by: Neil | last post by:
A client of mine likes some of the new bells and whistles in Access 2007, and is thinking about converting our A03 format MDB to an A07 format file. However, while some of the users have A07, many...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.