Topic: ADO question

[Jim Yadon]

I have been noodling over this one for days now. Can anyone help me out with this one?

I keep getting a 3265 error but the data is there. I can remove ITM# or DESC (along with the relevant SQL bulding syntax) and pull the data for one field but not both together. In the past it's been my SQL. I've tested it within the MDB though so I'm reasonably sure that's not it.

Here's the code -

Code: [Select]

Private Sub UserForm_Initialize()
    Call Populate2ComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "ITM#", "DESC", "JOB DETAIL LIST", "JOB", "00-0000")
End Sub

Public Sub Populate2ComboWhere(objCombo As ComboBox, strDBase As String, strField1 As String, strField2 As String, strTable As String, strControlField As String, strControlData As String)
  Dim strSQL As String
  Dim objConn As ADODB.Connection
  Dim objRSet As ADODB.Recordset
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDBase & ";" & _
        "Persist Security Info=False"
  With objConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .mode = adModeReadWrite
  End With
  objConn.Open
  strSQL = "SELECT [" & strTable & "].[" & strField1 & _
                "],[" & strTable & "].[" & strField2 & _
                "] FROM [" & strTable & _
                "] WHERE [" & strTable & "].[" & strControlField & "]=""" & strControlData & _
                """ ORDER BY [" & strTable & "].[" & strField1 & "]"
  Set objRSet = objConn.Execute(strSQL, , adCmdText)
  Debug.Print strSQL
  Do Until objRSet.EOF
    objCombo.AddItem objRSet.Fields(CStr(strField1) & "~" & strField2)
    objRSet.MoveNext
  Loop
  objRSet.Close
  objConn.Close
End Sub


[MP]

Use single quotes instead of double quotes for string tests and terminate with a semicolon.

i.e. Select * from tablename where fieldname = 'some value';

/guess

[Jim Yadon]

Same result. Hmmm...

Here's the revised version what I'm using. It's a basic Access 2000 db BTW.

Code: [Select]

Private Sub UserForm_Initialize()
'    Call PopulateComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "ITM#", "JOB DETAIL LIST", "JOB", "00-0000")
'    Call PopulateComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "DESC", "JOB DETAIL LIST", "JOB", "00-0000")
    Call Populate2ComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "ITM#", "DESC", "JOB DETAIL LIST", "JOB", "00-0000")
End Sub

Public Sub Populate2ComboWhere(objCombo As ComboBox, strDBase As String, strField1 As String, strField2 As String, strTable As String, strControlField As String, strControlData As String)
  Dim strSQL As String
  Dim objConn As ADODB.Connection
  Dim objRSet As ADODB.Recordset
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDBase & ";" & _
        "Persist Security Info=False"
  With objConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .mode = adModeReadWrite
  End With
  objConn.Open
  strSQL = "SELECT [" & strTable & "].[" & strField1 & _
                "],[" & strTable & "].[" & strField2 & _
                "] FROM [" & strTable & _
                "] WHERE [" & strTable & "].[" & strControlField & "]='" & strControlData & _
                "' ORDER BY [" & strTable & "].[" & strField1 & "]"
  Set objRSet = objConn.Execute(strSQL, , adCmdText)
  Debug.Print strSQL
  Do Until objRSet.EOF
    objCombo.AddItem objRSet.Fields(CStr(strField1) & "~" & strField2)
    objRSet.MoveNext
  Loop
  objRSet.Close
  objConn.Close
End Sub

Public Sub PopulateComboWhere(objCombo As ComboBox, strDBase As String, strField1 As String, strTable As String, strControlField As String, strControlData As String)
  Dim strSQL As String
  Dim objConn As ADODB.Connection
  Dim objRSet As ADODB.Recordset
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDBase & ";" & _
        "Persist Security Info=False"
  With objConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .mode = adModeReadWrite
  End With
  objConn.Open
  strSQL = "SELECT [" & strTable & "].[" & strField1 & "] FROM [" & _
            strTable & "] WHERE [" & strTable & "].[" & strControlField & "]='" & _
            strControlData & "' ORDER BY [" & strTable & "].[" & strField1 & "]"
  Set objRSet = objConn.Execute(strSQL, , adCmdText)
  Debug.Print strSQL
  Do Until objRSet.EOF
    objCombo.AddItem objRSet.Fields(strField1)
    objRSet.MoveNext
  Loop
  objRSet.Close
  objConn.Close
End Sub

[Jürg Menzi]

How about:

Code: [Select]

objCombo.AddItem CStr(objRSet.Fields(strField1)) & "~" & CStr(objRSet.Fields(strField2))

[Jim Yadon]

Simple answer to my own question. Make sure the references are correct for the project you are working in. :doh: :ugly:

[Kerry]

Hi Jim

Just to see what the future holds ...

http://msdn.microsoft.com/netframework/future/linq/

C# 3.0 LINQ in action
C# 3.0 XLinq in action
C# 3.0 DLinq in action

the thought of the possibilities blew me away ..
The VB Versions are also available < I assume >

[MP]

Hi Jim

Just to see what the future holds ...

http://msdn.microsoft.com/netframework/future/linq/

C# 3.0 LINQ in action
C# 3.0 XLinq in action
C# 3.0 DLinq in action

the thought of the possibilities blew me away ..
The VB Versions are also available < I assume >

Very interesting and lol: "... Anders Hejlsberg, Chief randomizer on the C# development team ..."

[Jim Yadon]

Cool stuff!!!    Thanks Kerry!