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
> Passing Parameters To A Query Launched From Within A Form Field, Access 2013    
 
   
goakenton
post Nov 8 2019, 05:17 PM
Post#1



Posts: 68
Joined: 20-December 12



Hi, I've got the following as Control Source in an unbound field within a form:

CODE
=ConcatRelated("AllEmails","qryMemberEmailsGrouped","Active = " & -1 & " AND [Edmonton] = " & -([chkEdmonton]*3) & " AND [Calgary] = " & -([chkCalgary]*1) & " AND [Central] = " & -([chkCentral]*2) & " AND [Chairs] = " & -([chkChairs]*1) & " AND [Physicians] = " & -([chkPhysicians]*3) & " AND [Psychiatrists] = " & -([chkPsychiatrists]*4) & " AND [PublicMembers] = " & -([chkPublicMembers]*5),"AllEmails","")

The form has a bunch of checkboxes on it. If the user checks a given box, I was hoping it would pass the appropriate parameter through to the query and spit back the appropriate concatenated list of email addresses into the unbound field above.

Here's a screenshot of the design view of query it should be loading:
Attached File  Capture.PNG ( 12.5K )Number of downloads: 5


(I do have the ConcatRelated public function)

When I open the form, it gives me an error 3061 "Too few parameters, expecting 7". I think the code above is passing 7 parameters, but there's something I'm not doing right. Or maybe you can't really even pass a parameter to a query this way.

Can someone please help me out?
Go to the top of the page
 
June7
post Nov 8 2019, 06:09 PM
Post#2



Posts: 1,028
Joined: 25-January 16



The code you show is calling ConcatRelated function with 5 arguments. The expression references a query that is apparently expecting 7 parameters. Need to post that query SQL statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Nov 9 2019, 10:15 AM
Post#3


UA Moderator
Posts: 76,888
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you're using a parameter prompt in your query, you may have to convert them into form references.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
goakenton
post Nov 10 2019, 04:55 PM
Post#4



Posts: 68
Joined: 20-December 12



Hi June7 and theDBguy, THANK YOU both for your thoughts. I slept on it and realized I was really complicating things unnecessarily.

All fixed.

I removed the Conditions from the query itself, and instead of trying to pass through parameters in the form, I changed the ConcatRelated() WHERE clause to give the values of MemberType_ID and Region_ID. I am pretty rusty at this stuff, I don't code enough these days.

Here's the call that works:

CODE
=ConcatRelated("AllEmails","qryMemberEmailsGrouped","Active = " & -1 & " AND Region_ID IN (" & -([chkEdmonton]*3) & ", " & -([chkCalgary]*1) & ", " & -([chkCentral]*2) & ") AND MemberType_ID IN (" & -([chkChairs]*1) & ", " & -([chkPhysicians]*3) & ", " & -([chkPsychiatrists]*4) & ", " & -([chkPublicMembers]*5) & ")","AllEmails","")

Cheers
Go to the top of the page
 
theDBguy
post Nov 11 2019, 12:31 AM
Post#5


UA Moderator
Posts: 76,888
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 04:02 PM