Passing a query a parameter [Access 2016]

Question

To make a longer story shorter:

I'm an Access noob, doing a quick-and-dirty conversion of a massive Excel spreadsheet into an Access database. Part of the requirements are to mimic some of the functionality of Excel, specifically, pulling data from a certain table and doing some basic calculations on it (sums, averages, etc.).

I've written a chain of queries to pull the data, count/sum it, etc., and have been testing them by using a manually-entered Parameter (i.e., the kind where the input box pops up and asks you to type a response). Now that I'm ready to drop these queries into a (sub)form, though, I have no idea how to automatically pass that parameter from a box in the form into the subform into the query.

Every query I've written uses a manually-entered Parameter named "MATCHNAME," which holds the name of an individual. In manual testing, if I enter this parameter on one query, all the queries it calls also get that value. So, I think I just need to figure out how to tell the top query what MATCHNAME actually is, and that'll take care of it.

Problem is, I don't know how to do that in Access. If it was any other programming language, I'd do something like "queryXYZ(MATCHNAME);", but I don't think I can do that in Access. Plus, since the values queryXYZ returns are all calculated, I'm not sure how to add an extra MATCHNAME field, nor how to actually make sure that gets read by the queries, nor how to make sure it gets passed down the chain. I've even tried creating a Parameter in design view, then trying to set up Link Master Fields, but the Parameter doesn't appear in that window.

I'd also like to re-run these queries whenever a new record is pulled up, but I'm not sure how to do that either--i.e., the numbers should be current for whatever record I'm looking at.

And, before we go there--I feel like a Relationship is out of the question, as the data itself is auto-generated, and is in rough enough shape to where I can't guarantee that any given key is wholly unique, and large enough (20k+) that, outside of writing a magical script, I can't assign a numerical key. However, I don't know much about Relationships in Access, so please prove me wrong.

(Is this all making sense?)

Do you have any suggestions for me--for how to make a subform read a field on the main form to run its queries on? Alternately, is there an easier way to do this, i.e., to bed SQL calls inside a form?

Thanks very much for your help...

Answer 1

You can use SQL as the recordsource of the subform in the property tab and use the afterupdate event of your matchname field to change yourform.recordsource = "Select * from table where filteredfieldname = & me.matchname & ";" . You can also use sql as the control source of form fields. To pass criteria to filter the subform using the whole table as the recordsource, add an event procedure to your field's after update event like this

`In the declarataions at the top
Global mtchnmfltr as string 

Private Sub MATCHNAME_AfterUpdate() 
'use the same procedure for Private Sub yourmainform_Current() 
mtchnmfltr  = "[yourfilterfield] = " & Chr(34) & me.matchname & Chr(34)  
'if matchname is not text then just = "[yourfilterfield] = " & me.matchname  
with me.subformname.form  
.filter = mtchnmfltr  
.filteron = true  
end with
'Build your sql as a string for your sum avg fields etc. using mtchnmfltr in the where clause  
 me.yoursumfield.controlsource = "Select...where " & mtchnmfltr & ";"
 'etc.
 end sub  

Or you could throw Matchname into a sql recordsource of the subform and add the function fields to the subform on the same on current and after update events

if me.newrecord = true then    
me.dirty = false  
end if  
me.subform.form.recordsource = "Select Table.Matchname, sum(yourfield) as sumalias, _  
(etc.) from yourtable where table.matchname = " & chr(34) & me.matchname & _  
chr(34) & Group By table.matchname" 

If you are storing your sums etc in a table you need to do it a bit different, since your controls controlsource are bound to fields.

dim strsqlsumfld as string 
dim rs as dao.recordset 
strsqlsumfld= "Select SUM.....AS sumfldalias where " & mtchnmfltr & ";"  
set rs = currentdb.openrecordset(strsqlsumfld) 
me.yoursumfield = rs("sumfldalias")
rs.close