Execute MySQL Select passthrough from a subform and send output recordset to a parent form

diotzhou 注册会员
2023-01-25 11:52

It's a little confusing to me what you're trying to do here but as I understand it you have a pure MySQL query (PTQName) that is the recordsource of a form (MainForm) when it is first opened. Right now this is a passthrough query defined in Access but not in MySQL. Then you want to be able to open a pop up form to filter the results you get from PTQName in MainForm. What I would do first is take the code for PTQName and create an actual view in MySQL Workbench from this (for example, call it "MyView"). Then link that view into your Access database. Access will treat this MySQL view as just another linked table. Set the recordsource of your MainForm to the name of this view so when it opens it displays records from your view. When you want to filter the records shown in your MainForm using your popup form, either use MainForm's Filter and FilterOn properties, or set MainForm's recordsource = "select * from MyView where ...". I am not seeing a need to even use a pass-through query here, let alone modify its SQL at runtime. But if I am misunderstanding please let me know!

dududu55 注册会员
2023-01-25 11:52

Really doesn't make sense to modify query object and then set form RecordSource to that same SQL statement.

In design view, set form RecordSource to pass-through query object name or an SQL statement that uses pass-through query as source: SELECT * FROM PTQname;. Use code to modify pass-through query object to change parameters but don't change form RecordSource.

dsjack1412 注册会员
2023-01-25 11:52

Finally, I solved the problem; @deluxeinformation gave me the right track, but I also wish to thank tho other users who gave me useful hints.

I defined a view on MySQL database named View_list, then set View_list as Mainform record source and, on Mainform load, the filtered PT query SELECT * FROM View_list WHERE F1 is executed, where F1 is the default filter where Mainform is loaded.

Similar PT query but with a different filter is being called clicking on the OK button in the Subform; the VBA code bound to this event is the following (kwd is the value entered in the input text keywordDescr of the subform Subform):

qryPT = "SELECT * FROM View_list WHERE description LIKE '" & kwd & "'"

With qdfc
     .Connect = ODBC_STRING
     .SQL = qryPT
     .ReturnsRecords = True
     Forms!Mainform.RecordSource = qryPT   
End With

About the Author

Question Info

Publish Time
2023-01-25 11:52
Update Time
2023-01-25 11:52

Related Question

attributeerror: 'mysqlpipeline' object has no attribute 'cousor



启动并登陆mysql shell

select 1 = '1 1' 查出来 1


php7+mysql8中使用MATCH AGAINST进行搜索时的怪问题。

mySQL - Web应用程序只与私人用户