Use Dcount on form filtered with parameter query

[vtandi]

In my DB [Referrals] I have a form [FRM_Referrals] that users can filter based a parameter query. I am using a macro with the ApplyFilter command to accomplish this. The problem is that when no records match the user's criteria, a blank form is displayed. I have searched for solutions and most seem to point to the dcount function. This could then produce a message box and run a macro that displays all records. Seems like this should be easy but I keep getting an error.

Macro condition DCount("*","QryFindRefRefFac") = 0
Error "This expression is typed incorrectly or it is too complicated to be evaluated...Try simplifying the expression by assigning parts of the expression to variables"

I suspect the issue may lie in the fact that the filter requires a parameter, but I am not sure. Any suggestions? Thanks in advance. I have been hitting this wall for a day now.

 

[overdraft015]

i am assuming that you have a form with a button that opens another form.

never used the dcount before but i do something similar but use this bit of code on my button

Dim response
Dim strQueryName As String
Dim db As Database
Dim rs As recordset

strQueryName = "[this is your query based on the users filter]"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)

If rs.EOF Then

rs.Close
db.Close

response = MsgBox("There Are No records matching your criteria", vbInformation, "Warning")

Exit Sub

Else

'open the form

im sure there are other ways to do this but this is the one i use. you can then continue to open your form and specify the criteria/filer using the docmd.openform command

It's not what you know. It's who's on Tek-Tip's

 

[dhookom]

IMO, don't use parameter queries faq701-6763.

Duane
Hook'D on Access
MS Access MVP

 

[vtandi]

Thank you both. I have been tasked with creating a database YESTERDAY. Also, I am a VB novice and have not even made an Access application in over 2 years. So,,,,,rusty. I was hoping to avoid code and use macros. Currently, I am using a custom menu item that runs a macro that:

Selects the form object
Removes previous filter property on form
Applies filter based on the parameter query I mentioned

and then I had hoped:

Assesses if there are no records that match the criteria and if that is the case, displays the message box you indicated and then simply shows the form without a filter (all records) after the user responds.

I know this is "baby programming" but I was hoping someone could help me to accomplish my task using macros. I can always go back and make it more powerful as I have the time.

Thanks again for any further suggestions !

 

[overdraft015]

Hi vtandi,

can you put your db up somewhere so i can see what you have done. i could possibly then implement what i have suggested and comment so you know what each bit does so you can copy/use it.

i cant think of any ways to do what you are thinking (if i am understandgin your requirements) using macros.

macros are very limited and some simple vba code may help you.

Michael

It's not what you know. It's who's on Tek-Tip's

 

[vtandi]

Michael and Duane,

So, I have lost the parameter queries per Duane's advice and am now using a small pop-up form where the user can select their parameter. This morning this seems to be working though last night I couldn't get the unbound lookup field to match the values in the lookup field in the referrals table???? I hope this won't suddenly stop working ! The DCount seems to now be working in the macro.

I am including a link to (

http://www.4shared.com/file/136491732/c090e5e2/BareBones.html)the

relevant parts of my DB anyway per your request (database named BareBones.mdb). If you have time to take a look and have any suggestions for improvements, I am all ears :>)

Thanks so much for your time and help!!!!!

Andi

 

[overdraft015]

Hi Andi,

i noticed a couple of errors, not sure if it was my download or because you stripped anything out but these are what i found:

in your table TBL_Referrals you had the field RefFac_ID with a lookup to your referal facilities. however you are trying to store the ID number rather than the name but your lookup had the name twice not the ID as the first field.

also your form FRM_RefByFacility was set to a datasheet view not a simgle form view.

once i fixed these two issues it worked ok for me.

i am going to play about with the db and create a subform view so that you users have 1 screen and can filter and the view will refresh from there. i will also give you the changes i have made to get this.

hope this helps a little bit for you.

It's not what you know. It's who's on Tek-Tip's

 

[vtandi]

Michael,

I can't thank you enough. The version that I sent you did not have the custom menu bar. Basically, the users will work from the Referral form, the menus will be <Referrals>, <Patients> and <Reports> and the options under these will lead to the unbound parameter selection forms (e.g. View by Referring Facility, View by Patient Name). Does that make sense?

Andi

 

[overdraft015]

yeah that makes sense.

if you already have your menu bar great if not and just want a very simple way of switching between forms then you can do something called tab control which is like changing sheets in an excel document. see here for the example.

http://www.techonthenet.com/access/tutorials/tabcontrol/tabcontrol02.php

i am also going to put up my method of subforms/filtering for you to have a look and see if it may be useful for you at some point. should have this up shortly.

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

Sorry Andi i think i have given an idea that wont work in your situation.

Ignore the last post about tabs as you will need seperate data source for each one and i forgot that (or am not aware of) you cant have a seperate data source for each tab.



It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

Hi Andi,

i have done the subform for you to have a look at. i have tried to use as little code as possible and keep as much as you had done. infact while writing this i decided to move the code to macros to make it easier for you.

these are the changes:

1. added the form FRM_Referrals into the form FRM_RefByFacility this will be the one your users view with.

2. changed the form FRM_RefByFacility to have no filter and the data source to have a condition based on the combo4 field in the form FRM_Referrals

3. on your "Go" button this calls the macro "refresh" which then updates the forms data using the newly selected filter.

hope this helps and any questions feel free to come back to me.



It's not what you know. It's who's on Tek-Tip's

 

[vtandi]

Michael,

I can't thank you enough for all of your work on this. I like your form. It is pretty swift. Will probably continue to use the menus because I am going to need them to be able to filter on quite a few other fields. However, I am going to save your version (and the code) for future reference/use. I also like the cosmetic modifications. The blue background is easier on the eyes and the lines between the sections help (I just couldn't make them look correct on the white form).

I may have other posts if more walls pop up along this little journey.

Again, thank you so much!

Andi

 

[overdraft015]

No problems Andi,

Glad to be able to help. feel free to contact me/post on here if there is anything else you need help with.

good luck with the project.

Michael

It's not what you know. It's who's on Tek-Tip's

 

[vtandi]

Michael,

I was so thrilled that the filter was working correctly, I did not notice that the user was unable to edit the form. This even though I have the Allow Edits, deletions, additions and data entry properties of the form all set to yes. It is probably something obvious I have failed to notice.......

Thanks again,
Andi

 

[vtandi]

Please disregard previous post. I played around with relationships and it seems to be working now (even though I'm pretty sure I put everything back the way it was???) :>)

Andi