pass-through query - creating prompt

[Jimmy2128]

I have the following pass-through query
select *
from name n, product p, meet_master m where n.chapter=p.product_minor and p.prod_type='chapt' and ((m.meeting_type ='CHAP' and substring(m.meeting,3,2)=n.chapter)or (m.meeting_type='AFF') and substring(m.meeting,1,charindex('_',m.meeting)-1)=n.chapter )and m.begin_date>=getdate() and n.chapter='BO'


n.chapter='BO' I want to be able to create a prompt where the user can enter BO or ST or DU. How can I modify the part that creates that prompt when running the currey. Thanks for helping.

Jimmy

 

[PCLewis]

You can't do it with just a query. If your query is bound to a report or a form then one approach is to create another form to open the object you are after (i.e. the report or form to open). In the popup form you can allow the parameters to be entered, and then in code, change the contents of the pass-through query using ADOX. You might be able to change the pass-through query SQL statement with DAO as well - sorry I don't know for certain on that one (should be possible). In DAO, pull out the Query from the CurrentDB.QueryDefs collection. In ADOX, use the ADOX.Catalog.Procedures or ADOX.Catalog.Views collection to find the query.

 

[Darrylles]

Hi,

Yep, you need to get the user response BEFORE you build your query string in VBA, thus you have the correct 'chapter' to use in your pass-through.

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk