Filling parameters of query using VB6

oops sorry
it is dim rs as recordset
not dim rs as recrdset

Craig

Actually, I think that steffey may already have the query written in Access, and that query takes two parameters, and you want to know how to pass the two parameters to Access and then run your Access query, and then use the result to move through the third combo and display it?  If so, are you using ADO to connect to the database?  Or DAO?  Or what have you done so far?

Assuming that you have created an ODBC dSN for your database, here is an example for calling a query in Access and passing one parameter, and getting a result set back:

General Declarations
Option Explicit
Dim CN As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()

CN.Open "DSN=MIKETEST;", "", ""

End Sub

Private Sub Command1_Click()
Dim PARM1 As ADODB.Parameter
Dim CMD As ADODB.Command

    Set PARM1 = New ADODB.Parameter
    With PARM1
        .Direction = adParamInput
        .Type = adVarChar
        .Size = 20
    End With
 
 
    Set CMD = New ADODB.Command
    With CMD
        .Prepared = True
        .CommandText = "MIKESQUERY"
        .CommandType = adCmdStoredProc
        .ActiveConnection = CN
        .Parameters.Append PARM1
    End With
   
    CN.CursorLocation = adUseClient
   
    PARM1 = "London"

    Set rs = CMD.Execute
    If rs.EOF = True And rs.BOF = True Then
        MsgBox "No Records found"
    Else
        While Not rs.EOF
            Print rs("FirstName") & vbTab & rs("LastName") & vbCrLf
            rs.MoveNext
        Wend
    End If
    rs.Close

End Sub

O.K. I have done exactly what Craig suggested me to do  ( with the exception of replacing cmb1 & cmb2 with the names of my combo boxes).  I am getting the error of Expected procedure not variable - this error is directed to the combo box name (cmb1) as well as the sqlsearch.  As I said I did exactly what you suggested.  I have been using ADO.

Hi Steffy ok the code above is for DAO
try this for ADO

dim DB As New ADODB.Connection
Dim RecordsetA As New ADODB.Recordset

dim sqlsearch as string
sqlSearch = "Select * from TableName where Combo1 = '" & cmb1 & "' and Combo1 = '" & Cmb2 & "';"

set up the db obeject to your database
    DB.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" _
    & "User ID=" & txtUserName & ";Password=" & txtPassword & ";" _
    & "Initial Catalog=BDBTest;Data Source=SqlServer"

'Set the recordset
RecordsetA.Open Sqlsearch, DB, adOpenStatic, adLockReadOnly, adCmdText

recordsetA.movefirst
cmb3 = recordsetA("Field you want to return to display in cmb3")

recordsetA.close
db.close


this example above is connecting to an sql database


try that :)

Craig

Steffy,

If you are using ADO, then replace the connection object referenced below by CN with whatever your connection object's name is, and replace the cboOne and cboTwo and cboThree with the names of your combo boxes.  Now, this is assuming that you are passing two strings to your Access Query, if you are passing any numbers or dates or anything else, let me know.

Private Sub Command1_Click()
Dim PARM1 As ADODB.Parameter
Dim PARM2 As ADODB.Parameter
Dim CMD As ADODB.Command

  Set PARM1 = New ADODB.Parameter
  With PARM1
      .Direction = adParamInput
      .Type = adVarChar
      .Size = 50
  End With

  Set PARM2 = New ADODB.Parameter
  With PARM2
      .Direction = adParamInput
      .Type = adVarChar
      .Size = 50
  End With

  Set CMD = New ADODB.Command
  With CMD
      .Prepared = True
      .CommandText = "YOURQUERYNAME"
      .CommandType = adCmdStoredProc
      .ActiveConnection = CN
      .Parameters.Append PARM1
      .Parameters.Append PARM2
  End With
   
  CN.CursorLocation = adUseClient
   
  PARM1.Value = cboOne.Text
  PARM2.Value = cboTwo.Text

  Set rs = CMD.Execute
  If rs.State <> adStateClosed Then
     If (Not rs.BOF) And (Not rs.EOF) And (rs.RecordCount <> 0) Then
         For i = 0 To cboThree.ListCount
            If i = cboThree.ListCount Then
                cboThree.ListIndex = -1
                Exit For
            End If
            If StrComp(cboThree.List(i), rs("MYDBFIELD") & "", vbTextCompare) = 0 Then
                cboThree.ListIndex = i
                Exit For
            End If
            rs.MoveNext
         Next i
     Else
         MsgBox "No Records found"
     End If
   
  Else
     MsgBox "No Records found"
  End If
  rs.Close
 
End Sub

Here is the majority of the code that I have been using.  As you will see I was using db to open the database.  This was working just fine for me to be able to fill the 2 combo boxes with the fields from the database. Since I used the code that mdougan suggested it is not reading this code.  It is obvious that I am new to this. I need to know how to call up the database without using db as database and still be able to fill my combo boxes. If my code is completely wrong let me know,like I said I am new to some of this.
I GREATLY appreciate all the help you guys are giving me
Thanks

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strfilename As String
Dim AppExcel As Object
Dim xlsheet1 As Object
Dim xlsheet2 As Object
Dim parm1 As Parameter
Dim parm2 As Parameter
Dim cmd As ADODB.Command
Dim Wb As Object


Private Sub cmdsearch_Click()

Set parm1 = New ADODB.Parameter
With parm1
    .Direction = adParamInput
    .Type = adVarChar
   
      End With
Set parm2 = New ADODB.Parameter
With parm2
    .Direction = adParamInput
    .Type = adVarChar
    End With
   
Set cmd = New ADODB.Command
With cmd
..Prepared = True
..CommandText = "Test query"
..CommandType = adCmdStoredProc
..ActiveConnection = cn
..Parameters.Append parm1
..Parameters.Append parm2
End With

cn.CursorLocation = adUseClient
parm1.Value = cbostart.Text
parm2.Value = cboend.Text

Set rs2 = cmd.Execute

If (Not rs2.BOF) And (Not rs2.EOF) And (rs2.RecordCount <> 0) Then
For i = 0 To Combo1.ListCount
If i = Combo1.ListCount Then
Combo1.ListIndex = -1
Exit For
End If
If StrComp(Combo1.List(i), rs2("miles") & "", vbTextCompare) = 0 Then
Combo1.ListIndex = i
Exit For
End If
rs2.MoveNext
Next i
Else
MsgBox "No Records found"
End If


End Sub strfilename = App.Path & "C:\excel files\travelingexpense.xls"
Set AppExcel = CreateObject("excel.application")
Set Wb = AppExcel.Workbooks.Add("C:\excel files\travelingexpense.xls")
Set xlsheet1 = AppExcel.Workbooks(1).Sheets(1)
Set xlsheet2 = AppExcel.Workbooks(1).Sheets(2)
    AppExcel.Visible = True

Set db = opendatabase("C:\access\eric's.mdb")
Set rs = db.openrecordset("starting point", dbopentable)

    Date.Show

'sets the first recordset for the first list box- these are the names of the fields
With rs
   maxfields = rs.Fields.Count

For i = 0 To maxfields - 1
   List1.AddItem rs.Fields(i).Name
Next
End With
'adds the items from the first recordset to the list box
Do
   cbostart.AddItem rs.Fields("starting point").Value
  rs.MoveNext
Loop While Not rs.EOF

'sets the 2nd recordset for the second list box
Set rs1 = db.openrecordset("ending point", dbopentable)
With rs1
    maxfields = rs1.Fields.Count
For a = o To maxfields - 1
    List2.AddItem rs1.Fields(a).Name
Next
End With
'Fills combo box with the fields in ending point table
Do
    cboend.AddItem rs1.Fields("ending point").Value
    rs1.MoveNext
Loop While Not rs1.EOF
End Sub

Hi,

No, your code is not "wrong" it just looks like you're combining two different types of data access.  You can open the connection to the database using the database object, but I've only seen this in DAO and not ADO as you'd said that you're using (that doesn't mean that you can't use the database object in ADO, just that they more often use the connection object instead).  Since most of your code looks like DAO, lets change the ADO code I gave you above to do the same thing with DAO.  Here are the instructions.

First, go to the Program menu and select the References menu item.  If you have ADO object library selected, de-select it and choose the DAO 3.51 object library.

Now, I'm not sure where you had the following code but I'll assume that the name of your form is called Date, and that the following code is in the form_load procedure, you can keep it just like you had it:

(general declarations section)
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
 
Dim strfilename As String
Dim AppExcel As Object
Dim xlsheet1 As Object
Dim xlsheet2 As Object
Dim Wb As Object

Sub Form_Load()
strfilename = App.Path & "C:\excel files\travelingexpense.xls"
Set AppExcel = CreateObject("excel.application")
Set Wb = AppExcel.Workbooks.Add("C:\excel files\travelingexpense.xls")
Set xlsheet1 = AppExcel.Workbooks(1).Sheets(1)
Set xlsheet2 = AppExcel.Workbooks(1).Sheets(2)
    AppExcel.Visible = True

Set db = opendatabase("C:\access\eric's.mdb")
Set rs = db.openrecordset("starting point", dbopentable)

' is this the same form, or a different one?
    Date.Show

'sets the first recordset for the first list box- these are the names of the fields
' always have to make sure rs has records
if rs.RecordCount > 0 Then
' don't use With, unless you remove
' references to rs
  With rs
'     maxfields = rs.Fields.Count
     maxfields = .Fields.Count

     For i = 0 To maxfields - 1
       List1.AddItem .Fields(i).Name
     Next
'I moved the End With from here to below
'End With

'adds the items from the first recordset to the list box
    Do
      cbostart.AddItem .Fields("starting point").Value
      .MoveNext
    Loop While Not .EOF
' moved to here
  End With
End if

'sets the 2nd recordset for the second list box
Set rs1 = db.openrecordset("ending point", dbopentable)

' if rs1 has no records this will fail
' put this check in first
if rs1.RecordCount > 0 Then
' Don't do a With, if you're still going
' to reference rs1.Fields else change
' rs1.Fields to .Fields
  With rs1
'    maxfields = rs1.Fields.Count
    maxfields = .Fields.Count
    For a = o To maxfields - 1
'      List2.AddItem rs1.Fields(a).Name
      List2.AddItem .Fields(a).Name
    Next
'Fills combo box with the fields in ending point table
  Do
    cboend.AddItem .Fields("ending point").Value
    .MoveNext
    Loop While Not .EOF

  End With

End if

'Unless you have a reason to keep them open
'I'd close the recordsets here
rs.Close
rs1.Close

End Sub

Private Sub cmdsearch_Click()
Dim qd as DAO.QueryDef

Set qd = db.QueryDefs("Test query")

qd.Parameters(0).Value = cbostart.Text
qd.Parameters(1).Value = cboend.Text

Set rs2 = qd.OpenRecordset(dbOpenSnapshot)

if rs2.RecordCount > 0 Then
   For i = 0 To Combo1.ListCount
      If i = Combo1.ListCount Then
         Combo1.ListIndex = -1
         Exit For
      End If
      If StrComp(Combo1.List(i), rs2("miles") & "", vbTextCompare) = 0 Then
         Combo1.ListIndex = i
         Exit For
      End If
      rs2.MoveNext
   Next i
Else
   MsgBox "No Records found"
End If

rs2.Close
qd.Close

End Sub

Now, the only thing I didn't see in your code is where you were loading the Combo1.  My assumption is that you want to load cboStart, cboEnd, and a third combo box, then, when the user selects something from cboStart, and something from cboEnd, and then clicks the search button, you want to pass the values from cboStart and cboEnd to a query that you've already written in Access that will then return one record.  You'll then use the "miles" column returned from the query to search through the third combo, Combo1 to find a matching value?  If so, somewhere you must be loading Combo1 with mile values.

If that is not exactly what you wanted to do, let me know, as that is the assumptions I had when doing the code above.

Now, this also assumes that you've written your Test Query in Access.  You should edit your Test Query in Access, and go to the SQL view, and make sure that you put a line at the top of your query that looks like:

Parameters [pSTART] TEXT, [pEND] TEXT;
SELECT MILES FROM DISTANCES
WHERE DISTANCES.STARTPOINT = [pSTART]
AND DISTANCES.ENDPOINT = [pEND]

If you created your query using the query builder, even if you put parms in your Criteria boxes, when you go to the SQL view, you wont see the line I have above that says Parameters.  You have to manually code that in.  The reason that you do this is to ensure that you know what order the parameters will come into your query as.  That way, in VB, when you set the Parameters(0) and Parameters(1) of the QueryDef object, you'll be sure to set them in the same order.

Good Luck!

After re-reading your question, it actually looks like you don't want to search through a third combo box for a match, if so, then you can forget all of the combo1 code in your cmdSearch routine, and just use the Miles returned from the query any way you want.


Private Sub cmdsearch_Click()
Dim qd as DAO.QueryDef

Set qd = db.QueryDefs("Test query")

qd.Parameters(0).Value = cbostart.Text
qd.Parameters(1).Value = cboend.Text

Set rs2 = qd.OpenRecordset(dbOpenSnapshot)

if rs2.RecordCount > 0 Then
   txtMiles.Text = rs2("Miles")
End if

rs2.Close
qd.Close

End Sub

I wish I could divide the points between both of you guys. You have both helped so much. Thanks a bunch!!!!