By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Filtering a record on a subform

P: 6
Dear Gentlemen,

I,am a newbie for access and still learning on progress. I develop a small database called a Purchase Orders Log and I wanted to filter some certain records in a subform which is related to a certain field. Let say i.e. to filter a posting date.

I want to use the combox box but it seems like difficult to handle. However I get some vba code in the net and apply to my database. Since I don't know how to use the vab code in the mainform to filter a records in a subform, I apply it to the subform but since if the subform is locked the combo box is being freezed. So I could not able to use it. But if I will open directly to the subform the filter method is working perfectly.

Any help is much appreciated.

Best regards,

Adrian.
Aug 14 '12 #1
Share this Question
Share on Google+
11 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
What you can do is base the subform on a query. The query can be setup to filter based on the selection in the combo box. Then all you have to do is create an After_Update event for the combo box to requery the subform, like this:

Expand|Select|Wrap|Line Numbers
  1. Me.subform name.Requery
Just curious, why is the subform locked?
Aug 14 '12 #2

P: 6
Hi Dear,

Thanks to your response the subform is locked as it is to avoid anybody can edit the records. But I have a button to unlock the subform then inputs data, it's called add or edit button so the subform property will unlock.

If I would post the vba code I develop can you make a review?

Regards,
Aug 14 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,931
I can try. Please remember to use code tags.
Aug 14 '12 #4

zmbd
Expert Mod 5K+
P: 5,287
Adrian:

Welcome to Bytes.

In addition to the method Seth offered:
http://bytes.com/topic/access/insigh...filtering-form

When you post your code for the lock/unlock button you should more than likely post using a new thread. Also, you might do a search on this site as there are several examples that might serve as a template.

-Z


--Z's BOILER PLATE -- just a few links I tend to post quite often:

This is a must read: Posting Guidelines
How to ask good questions
FAQ
A Tutorial for Access
Database Normalization and Table Structures.
Aug 14 '12 #5

P: 6
Hi Seth,

here is the vba code that used to the subform which is working perfectly but if I put the combo box to the mainform and use the same vba code it will no longer works and gets me error;

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo21_AfterUpdate()
  2. Dim strSQL As String
  3.      Dim strSQLSF As String
  4.  
  5.      strSQL = "SELECT DISTINCT qrySlave.PostingYear FROM qrySlave "
  6.      strSQL = strSQL & " WHERE qrySlave.PostingYear = '" & Combo21 & "'"
  7.      strSQL = strSQL & " ORDER BY qrySlave.PostingYear;"
  8.  
  9.      strSQLSF = "SELECT * FROM qrySlave "
  10.      strSQLSF = strSQLSF & " WHERE qrySlave.PostingYear = '" & Combo21 & "'"
  11.  
  12.      Me.RecordSource = strSQLSF
  13.      Me.Requery
  14.  
  15. End Sub
Hopes that you can help me out as I put myself hard just to get the proper syntax of coding.

Best Regards,
Aug 15 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
Adrian,

Just a quick peek before I'm on the road for work so I haven't taken a good look at your code; however, from first blush:

Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & " WHERE qrySlave.PostingYear = '" & Combo21 & "'"
Your error is more than likely with the "Combo21" you'll need a dot-value at the end and more than likely drop the quotes depending on the record source and binding of the control.


Pleas confirm:
What is the name of the combobox control you are using?
What is the record source of the combobox you are using?
Which line of the posted code is causing the error, and what is the error message?

-Z
Aug 15 '12 #7

P: 6
Hi Dear,

The name of the combobox control is Combo21. The recordsource of Combo21 is qrySlave. There is no line which was highlighted but the error says "The expression you entered as field, control, or property name that Microsoft Access can't find."

Best Regards,
Aug 15 '12 #8

zmbd
Expert Mod 5K+
P: 5,287
Please double check the following:

Every VBA module (forms too) make sure that you have both:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
As the very first lines in the module.

Next do a debug/compile on the code... fix any errors that occur. I do beleave that you will get an error on the line indicated in my post #7...

If you haven't done so, then please read the tutorial I posted the link to in my most #5 you might also desire to follow the links given at the bottom as they expand upon the article and discuss how to use comboboxes.

-
z

Once your done with these steps post back.

Your
Aug 15 '12 #9

Seth Schrock
Expert 2.5K+
P: 2,931
Something that I noticed is that you have single quotation marks around the combo box name as part of the SQL. What type of data does the combo box hold? If you look at the Bound To column in the properties for the combo box, there should be a number (probably 1). Then open the qrySlave and tell me what the data type is for the column referenced in the Bound To field.
Aug 17 '12 #10

P: 6
Dear Gents,

Apologize for my tardy reply due some urgent priorities, and I'am not in the office due to Eid Holidays here in the KSA until Wednesday. I will get you back once resume to the office.

Regards,
Aug 20 '12 #11

P: 6
Hi Seth,

At last after several attempts I figured it out and I remove some syntax which are not used in the assigned statement. And using of "Me." it keeps things clarified.

Many thanks to your tireless support.

Best Regards,
Aug 25 '12 #12

Post your reply

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