MS ACCSS HELP - COMPLEX FILTER

[madetoheal13]

Hi all! Ok here is my dilemma...(I have another but will write a more taylored thread....

Ok, I have a database that has 7 tables all with relationship. The main table serves as the source of a form in which the other 6 tables are subforms in that form. As I move from record to record in the main form...all the info in the subforms change correspondingly, so I assume the relationships are all ok. The main table/form has information regarding store names, numbers, and addresses for all stores in our Retail database. One of the subforms(alternate tables), houses information on the sales representatives that service each store(record). All subforms are housed in the main form to make it easy for data entry. And there is one table that is an actual store visit table..which has information that changes by each visit, but not to overwrite, so someone can see progression from visit to visit. I would like to filter the main from by the sales rep id field on one of the subforms so that each sales rep is only dealing with accounts in their respective geographic area. Is it possible to filter a main form by a field in a subform within that main form so that the main form information changes as well? Sorry for being so longwinded.

Thanks,
Tired of access here

 

[Trevil]

Assuming the user clicks (or selects) some value in the subform, you could trigger an event that would requery the main form. There are a few items to consider.

1. Since the original form would present ALL stores, once you filter to select only by sales rep, you may need a mechanism (i.e. button) to reset the filter to see all?

2. When you select the rep from the subform, you could filter by:
a) setting the forms 'filter / filter on' property and requerying (easiest)
b) changing the row source to some sql that has a 'where' clause that you build.
c) having some unbound field on the main form that you set a value in, then you rowsource filters using that value (you would need to set to '*' at form load / reset).

"Have a great day today and a better day tomorrow!

 

[madetoheal13]

Hey thanks, but you are speaking greek to me here...I am not that Access Savy...everything I know...I've taught myself over the last 5 months. I don't understand comment a), b) (I know SQL exists, but don't know what it is or does), c)(I think I've tried something like this...but it failed.)

I'm ready to just scrap this whole thing...
but I've put too much time into it.

Thanks!
Madetoheal13

 

[Trevil]

Here's what you can try: (Since I don't know the names of ANY objects in your database, you MUST CHANGE THE NAMES I USED to whatever names you have!!)

1. Open the subform where the REPID field is in Design View
2. I assume you can see the 'properties'. If not, click on menu View | Properties
3. Click on REPID field, then in properties scroll down to find the 'On Dbl Click' property.
4. Double-click inside that property area (you will see 'Event Procedure'), then click on the Build (...) on the right. This will take you to the code window. You will see you already have something like the following:
Private Sub REPID_DblClick(Cancel As Integer)

End Sub

Insert the following in that sub (if REPID is numeric):
Me.Parent.Filter = "REPID=" & ME.REPID
Me.Parent.FilterOn = True

Insert the following in that sub (if REPID is NOT numeric):
Me.Parent.Filter = "REPID='" & ME.REPID & "'"
Me.Parent.FilterOn = True

Now when you double-click on a REPID in the subform, the main form will be filtered by thet REPID.

You will need a command button on the main form that when you click it, it simply does:
Me.Filter = ""
Me.FilterOn = false



"Have a great day today and a better day tomorrow!

 

[madetoheal13]

Hey Trevil!

I did what you said to do...but in the separate subform when I double click on the field it comes back as runtime error 2452: The expression you entered has an invalid reference to the Parent property.

I do the same thing on the field in the Main from and a parameter question pops up asking for my Sales_ID....I enter one in and it filters to nothing...says filtering (1) record....I click the button to restore the filter...then type in the initials of the first record and it runs a filter but brings back all records. Is it possible the tables are not related correctly...I ran the analyze performance tool and it told me to relate all tables so i optimized them...and now my relationships diagram looks like a spiderweb. WHat do I do?

Thanks
Madetoheal13

 

[Trevil]

Easy things first. You can drag the tables in your "spiderweb" around to make it easier to read.

1. Did you rename the code to use your names? (appears you call it 'Sales_ID'??)

2. Is the subform where you placed the code a 'first-level' subform on the main form? Or is it actually a subform that is on another subform that is on the main form (i.e. 'second-level subform')?? If the latter, then the code would need to be modified since 'Parent' would NOT refer to the main form.

"Have a great day today and a better day tomorrow!

 

[madetoheal13]

Jebry,

I think it is 1st level...it there is one main form that has a source of the primary table. The primary table is related to 7 other mini tables. The form then has these seven mini tables embedded on the main form...so I have one form with all the information from all of the tables in one place...when I inserted the subforms...I believe it was with the wizard...it made the subform their own forms in the form object area. I went into the individual subform/form to do the coding, but created the button on the main form...was this correct? Please Advise.

THanks
Madetoheal13

 

[Trevil]

Yes, the button should be on the Main form.

Here's what I tested:
1. I have a main form (named 'Department') that has a table name as the record source (not a query). There are four (4) records in that table.
2. That form has two subforms. One named 'Hourly_Wages Subform'. In that subform is a field named 'Employee' and the following is the code I used:
Private Sub Employee_DblClick(Cancel As Integer)
Me.Parent.Filter = "DepartmentNo='" & Me.Employee & "'"
Me.Parent.FilterOn = True
End Sub

3. When I double-click on the field, the main form is immediately filtered to show only ONE (1) record.
4. My main form has the subform linked (link child / link master is set to proper field names).

Let's focus on "...2452: The expression you entered has an invalid reference to the Parent property" -- I assume you still have that problem? Please show me the code you have and provide the names of your forms and subform.





"Have a great day today and a better day tomorrow!

 

[madetoheal13]

Ok here goes....
Main Form is called Face2FaceReports...main source CUstomer Table (with fields: Name, address, store #, state, zip, etc.) Subform is called Sales Reps...main source is Sales Rep Table (with field: Sales ID)...

In my Sales Reps Form...I went to the Sales_ID properties and put in an event procedure on DblClick as follows.

Private Sub Sales_ID_DblClick(Cancel As Integer)
Me.Parent.Filter = "Sales_ID='" & Me.Sales_ID & "'"
Me.Parent.FilterOn = True
End Sub

I also made a control button on the Face2FaceReports form with code written as event procedure on Click

Private Sub Command33_Click()
Me.Filter = ""
Me.FilterOn = false
End Sub

Please advise.
I am doing this for my company although not in the job description...are there places I can take the Database to...to have a professional look at it on an hourly wage? I am located in Chicago. I thought it was almost done but now in addition to this I am having trouble setting the securities...as everything I know from Access I've taught myself.

Thank you soooo much for your time.
Madetoheal13

 

[Trevil]

One LARGE problem: You describe the fields in the 'CUstomer' table used in form 'Face2FaceReports', yet I see NO REFERENCE to 'Sales ID'. Was this an oversight on your part?

1. Does fiels 'Sales ID' (or is it 'Sales_ID') exist in the 'Customer' table?
2. If yes, are you sure you are spelling it correctly (does it have a space or underscore in the name?)

I'm sure there is someplace you could get someone to look at your database, but I have no idea where in Chicago.

This is not complicated (although sometimes elusive) and can work through this.

"Have a great day today and a better day tomorrow!

 

[madetoheal13]

Hey Trevil!

The 'Sales ID' Field is in the SalesRepsSubform which lies in the MainForm 'Face2FaceReports' (which has underlying record source of 'CustomerTable')

There is no _ in the title...but when I am writing the code...there builder suggests as you type and it puts the _ in the name...if I shouldn't do that...should I bracket or put parenthesis around this field name?

What types of business should I investigate to (like in the yellow pages...where should i start?)bring it to..for someone to look at?

Thank you again!!!
Madetoheal13

 

[Trevil]

I think we're having a slight failure to communicate... I have yet to hear you confirm that "Sales ID" is a field that exists in the "Customer" table. Let me try and ask a different way.

Let's say your main form is based on a table that contains information on every "State" in the U.S. It includes state name, State Abbreviation, Population, etc.

Now you have another form that shows information on every county in the U.S. and has State Abbreviation, County Name, and Population. That form is included as a subform on your main form.

Now when you double-click on the "State Abbreviation", you want the main form to filter by that "State Abbreviation".

The above requires "State Abbreviation" to be a common field that is available to BOTH forms.

Please let me know what the deal is with "Sales ID".

"Have a great day today and a better day tomorrow!

 

[madetoheal13]

Trevil...

The first question I posted on this forum was....Is it possible to filter a main form by a field in a subform within that main form...

So to answer your question...NO...Sales ID does not exist in the Main (F2F) nor the Customer Table (the source for Main form)....it exists in a seperate table Sales Reps...to which SubformSalesReps uses the Sales Reps Table as its source. This SubformSalesReps is placed within the Main (F2F) form. I want to be able to choose a "sales rep" from the subform...and have the main form only display those customers linked to that Sales Rep ID.

Thanks,
Madetoheal13

 

[Trevil]

Well, that certainly clears up the reason! Now you need to tell me EXACTLY how the table 'Customer' is RELATED to the table used in the subform. I would assume your database has something like tables for Customers, Sales Reps, Orders (or territory), etc. You mentioned earlier that you had table relationships defined, so that's a start.

My guess is that you will now need to generate a query that uses that relationship and set that as the record source for the main form. I can't suggest SQL until I know if it's networked or member/detail.


"Have a great day today and a better day tomorrow!

 

[keun]

I would take this a different direction. I would copy your Main form and on the copy delete the subforms then make it look nice and neat. Call it frmMainCopy.

Then, go back to the original Main form with the subforms. On your sales-rep subform put a command button using the wizard. USing the wizard: Form Operation - Open Form. Tell it to open frmMainCopy. Next. Choose the first option: OPen the form and find specific data to display. You will get two lists. Pick RepID in both tables and hit next. then you are done.

When you hit the button, frmMainCopy should open and show you only those records which correspond to the Rep record which you were on in the subform.

This does not do exaclty what you want. You will have a form open over your other form but it should be filtered the way you like and when you are done you just close it.