Setting Parameter in Recordsource

[Guest]

I've been staring at this problem for hours - it's time to call in the
reinforcements!

My form is continuous and is based on a query of Supply Items. I want to
include an indicator for each record that shows with or not this same Supply
Item exists in another table.

I put the indicator in the rowsource query and wrote a function to value it.
The function requires three input values, one of which is not available in
the query - so I made that one a Parameter.

I have this code to value the parameter:

dim db as DAO.Database, qdfSource as DAO.QueryDef

set db = CurrentDb
set qdfSource = db.QueryDefs(me.RecordSource)

qdfSource.Parameters(0) = 4 ' the 4 will be a control value
later

and I put the code in the Current event for the form.

The code sort of works but mostly doesn't - my parameter of 4 is valued but
then it seems that the query is executing multiple times and the parameter
isn't always there (it keeps getting requested of the user).

I know I don't have the event right - and I'm confused about whether or not
the querydef and db objects should be closed. There is definitely something
fundamental that I am not getting.

Any thoughts?

 

[WAstarita]

The best way to do this is to set the source of the form programically
throught a hand written sql statement. Encapsulate the statement in a
sub so you can call it from multiple places.

Sub ResetRS()

Me.Recordsource = "Select * From Table Where X = " & Me.cbo.value
Me.Requery

The statement then becomes persistent with that value until you close
the form. Call the new subroutine in the Forms Open event and the
AfterUpdate event of the control (or controls) that is contributing
parameters to the query.

 

[tina]

suggest you just add tblOther to the form's underlying query. in query
design view create a link *from* tblSupplyItems *to* tblOther, on whatever
field unique identifies a supply item in each table - i'll call it ItemID.
set the link parameters to "Show all field in tblSupplyItems and only
matching records in tblOther". create a calculated control in the query, as

InOther: IIf(tblOther.ItemID Is Null, False, True)

in the form, you should be able to bind the InOther field to either a
textbox control or a checkbox control.

hth

 

[Guest]

Thanks, WAstarita - this is what I resorted to - I was resistent to the idea
because the SQL is quite complex and I don't really like the idea of having
to manage the code - I like to be able to just make changes in the design
view of the query.

 

[Guest]

I hadn't thought of this option! - hmmm, yes, that just might work. Thanks

 

[tina]

you're welcome
let us know how it turns out; if problems, perhaps we can tweak it to get
the required result.