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

I manage a Microsoft Access 2019 Database (owned by a customer) with a form (named Mainform) whose recordset source is a MySQL passthrough query (SELECT * FROM table_on_mysql_db); each recordset (shown directly on opening Mainform) is only readable and it has three fields: one of them, description, contains text.

On double clicking on description field, a small sized subform (name Subform, containing one textvalue field named keywordDescr, plus an OK button and a Cancel button) pops up.

If I enter some words in keywordDescr (i.e. anyword) and press OK, the following passthrough query

is being called and the resultset ouput must be displayed in Mainform (Subform still remains opened); unfortunately, the Mainform content is not updated accordingly to the above MySQL filtered query.

The following is the VBA code called on clicking the OK button in Subform (OK is the label and the button name is button_search_description):

Private sub button_search_description_Click()
  on Error goto ErrDescr

  Dim qdfc as DAO.QueryDef
  Dim qryPT as String
  Dim ODBC_STRING as String
  Dim kwd as String

  kwd = Me.keywordDescr
  kwd = Replace(kwd, "*", "%") '(the customer is still used to entering Access wildcard rather than MySQL wildcard!)
  kwd = Replace(kwd, "'", "\'")

  ODBC_STRING = "ODBC;DSN=MY_DSN_NAME"  ' it works!  

  qryPT = "SELECT * FROM table_on_mysql_db WHERE description LIKE '" & kwd & "'"
  
  DoCmd.setWarnings = false
  Set qdfc = DBEngine(0)(0).CreateQueryDef("")

  With qdfc
     .Connect = ODBC_STRING
     .SQL = qryPT
     .ReturnsRecords = True
     Me.Parent.RecordSource = qryPT   
  End With

  Set qdfc = nothing
  DoCmd.setWarnings = true
  
ErrDescr:
  Resume Next
End Sub

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

Good Question 0Comment Collection Report

0 Answer

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!

Agree 0 0Comment

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.

Agree 0 0Comment

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

Agree 0 0Comment

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

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

0 Comment

0 Comment

0 Comment

0 Comment

About the Author

Question Info

Related Question

attributeerror: 'mysqlpipeline' object has no attribute 'cousor

MYSQL:如果数据有相同的ID[重复]

mysql起别名时传入一个参数没有生效

启动并登陆mysql shell

select 1 = '1 1' 查出来 1

AUTO_INCREMENT在MySQL项目中不工作

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

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

mysql创建存储过程DECLARE标红

请教MySQL8.0.32安装问题