Displaying Records on a MS Access 2000 form (frontend) using MySQL (backend)

[John Kirkpatrick]

Hi all,

I am having difficulty displaying records on a frontend MS Access 2000 form using a MySQL backend. The following code works well with the Jet database engine but doesn't work properly using the MySQL backend database. All records are displayed on the form using the Jet, but only one record appears using MySQL. Perhaps it just needs some tweaking to work with MySQL. Any advice would be great. Thanks

Function HelpDesc(HelpFrm As Integer)
i = 0
j = 0
StrHelp = "Help not found"
strHelpSection = "Help not found"
strAccessSQL = "SELECT * from tbl_ADMIN_FormHelp Where (CtlFlag = Yes) and ID = " & HelpFrm
GetCurrentDatabase
Set rsAccessControl = db.OpenRecordset(strAccessSQL)
If rsAccessControl.RecordCount = 1 Then
strSQLUpdate = rsAccessControl("ID")
strHelpSection = "Waiting................"
strHelpSection = "Section Name : " & rsAccessControl.Fields("CtlCaption") & vbCrLf & vbCrLf
StrHelp = vbCrLf
StrHelp = StrHelp & Trim(rsAccessControl.Fields("CtlDesc")) & vbCrLf
StrHelp = StrHelp & Trim(rsAccessControl.Fields("CltBusinessRule")) & vbCrLf
strAccessSQL = "Select * from qry_ControlTab where tbl_admin_FormHelp.id = " & HelpFrm & " or tbl_admin_FormHelp.Ctltype = " & HelpFrm
GetCurrentDatabase
Set rsAccessControl = db.OpenRecordset(strAccessSQL)
i = 0
For j = 0 To rsAccessControl.RecordCount - 1
strSelected = rsAccessControl.Fields("tbl_Admin_ControlHelp.CtlT ype").Value

If IsNull(rsAccessControl.Fields("CtlDesc")) = False Or IsNull(rsAccessControl.Fields("CltBusinessRule")) = False Then
i = i + 1
If strSelected = "104" Then
'StrHelp = StrHelp & vbCrLf & i & ". Button Name : " & rsAccessControl.Fields("CtlCaption") & " " & vbCrLf
StrHelp = StrHelp & vbCrLf & i & " . " & rsAccessControl.Fields("CtlCaption") & " : " & vbCrLf
Else
'StrHelp = StrHelp & i & ". Label Name : " & rsAccessControl.Fields("CtlCaption") & " " & vbCrLf
StrHelp = StrHelp & i & " . " & rsAccessControl.Fields("CtlCaption") & " : " & vbCrLf
End If
If IsNull(rsAccessControl.Fields("CtlDesc")) = False Then
StrHelp = StrHelp & " " & rsAccessControl.Fields("CtlDesc") & vbCrLf
Else
StrHelp = StrHelp & vbCrLf
End If
If IsNull(rsAccessControl.Fields("CltBusinessRule")) = False Then
StrHelp = StrHelp & " " & rsAccessControl.Fields("CltBusinessRule") & vbCrLf & vbCrLf
Else
StrHelp = StrHelp & vbCrLf
End If
End If


rsAccessControl.MoveNext
Next
rsAccessControl.Close
DoCmd.OpenForm "frm_Gen_sfrm_Help", acNormal, , , acFormReadOnly, acDialog
Else
DoCmd.OpenForm "frm_Gen_sfrm_Help", acNormal, , , acFormReadOnly, acDialog
End If

End Function