binding combobox controlsource in an unbound form

[dabruins]

I've been looking unsuccessfully for an answer to a problem I'm having in binding a controlsource to a combobox in an unbound form.

My problem is that when I set up an unbound form to view records from my MySQL database table, I bind the controls of the form to the fields in the table of the db for viewing and record navigation purposes. Some of the controls are comboboxes that I have set up with a recordset from different tables/queries in the mysql db (Me.combobox1.recordset = adodb.recordset). I have no problem setting the recordset for the combobox however once I bind the combobox controlsource to a column in the table of interest the information does not always appear in the combobox for the current record being viewed. It's also not possible to change the value in the combobox (the value of cureent record for the bound field) to another value from its recordset. Binding controls from access works just fine for textboxes. The following is some code I have added for an AppyFilter command that looks for recordss in a table based upon the user input in unbound controls of ther form. Once I find the records I set the rcordset of the form to that of the return records of the query and then bind the fields to the controls of the now bound form.

Maybe I'm missing a step in setting this up properly? I'd appreciate any insight you might be able to share on this.

Thanks.

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_Handler

Dim strsql As String
Dim ctl As Control
Dim cnt As Integer
strsql = "SELECT * FROM `usijobs` "

cnt = 1
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not ctl.Value = "" Or Not IsNull(ctl.Value) Then
Select Case ctl.Name
Case "txtjobno", "txtdept", "txtAFEnumber", "txtOrderNumber", "txtfilenumber"
If cnt = 1 Then
strsql = strsql & "WHERE " & Right(ctl.Name, Len(ctl.Name) - 3) & " like '%" & ctl.Value & "%'"
Else
strsql = strsql & " AND " & Right(ctl.Name, Len(ctl.Name) - 3) & " like '%" & ctl.Value & "%'"
End If
cnt = cnt + 1
Case "txtCreatedate"
If cnt = 1 Then
strsql = strsql & "WHERE " & Right(ctl.Name, Len(ctl.Name) - 3) & " = '" & Format(ctl.Value, "YYYY-MM-DD") & "'"
Else
strsql = strsql & " AND " & Right(ctl.Name, Len(ctl.Name) - 3) & " = '" & Format(ctl.Value, "YYYY-MM-DD") & "'"
End If
cnt = cnt + 1
Case "cmbCompanyid", "cmbOrderedby", "cmbhowreceived", "cmbinvoiceto", "cmblandagent", "cmbengcons", "cmbfieldrep"
If cnt = 1 Then
strsql = strsql & "WHERE " & Right(ctl.Name, Len(ctl.Name) - 3) & " = " & ctl.Value & ""
Else
strsql = strsql & " AND " & Right(ctl.Name, Len(ctl.Name) - 3) & " = " & ctl.Value & ""
End If
cnt = cnt + 1
End Select
End If
End If
Next ctl

strsql = strsql & ";"

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Mode = adModeReadWrite
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "PORT=3306;" _
& "STMT=;" _
& "OPTION=16384;" _
& "DATABASE=usidbmysql;" _
& "UID=user;" _
& "PWD=password"

conn.Provider = "MSDASQL"
conn.Open

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open strsql, conn, adOpenStatic, adLockPessimistic, acCmdText
If Not rs.EOF Or Not rs.BOF Then
rs.MoveFirst
End If

Set Me.Recordset = rs
Dim actl As Control
For Each actl In Me.Controls
Select Case actl.ControlType
Case acTextBox, acComboBox
actl.ControlSource = Right(actl.Name, Len(actl.Name) - 3)
End Select
Next actl


rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

Exit_cmdapplyfilter_Click:
Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.description
Resume Exit_cmdapplyfilter_Click
End Sub

 

[dabruins]

Hi All.

Just wanted to let you know that it turns out that there is a problem with using UNSIGNED integers in MySQL when using Access as a frontend. Once I removed UNSIGNED from my column definitions that store an interger value the column values show up in the form controls as expected and in place updates are permitted. Still not sure why this is so but at least I've gotten by that problem. It only took two days to solve it!

Hope this might help someone else? I thought this might be a problem with access. Maybe this should have been posted in the MySQL forum?