Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Combo / database / Search ... Help Please

Hiya everyone,

I am having trouble coding a search on a database in my VB project. I am using VB6 Enterprise edition, and windows XP and Access 2000.

The basic synopsis is;

I want to load records into a combo box on a form, then let the user chose a name and search the database and have the results displayed in non changeable text boxes. I have two problems at the moment. Each time I try to code the "Fill combo" section I get various errors. And that then leaves the search impossible. The second problem is every time I use Option Explicit in the code it doesn't run at all.
*********************************
CODE:-
' Option Explicit

Private Sub Form_Load()
'** This is the connection to the database
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OleDb.4.0"
conn.ConnectionString = (App.Path & "wtfc.mdb")
conn.Open
' ** This setsthe record set ie conection to the Players table
Set rs = New ADODB.Recordset

rs.ActiveConnection = conn
rs.Open "Select * from players"
' ** END of the connection section.

' ** Fill the Combo box with the names.
'Name of table = Players
'Name of field in DB to fill the combo with = Name.
Dim rs2 As Recordset

rs2.OpenRecordset "SELECT Name FROM Players"

cboPlayers.AddItem
'dbcboPlayers.AddItem rs2, 1

End Sub

Private Sub cmdsearch_Click()
' Using the Content of the cbo to search the database and put results into the textboxes
Dim playersql As String
Dim playersx As String
playersx = cboPlayers.Text
playersql = "select * from players Where As =' " & playersx & "'"
Set rs = conn.OpenRecordset(playersql)
MsgBox "You choose " & playersql & " And here it is !"
End Sub
******************************************

Any help and advice is really welcomed. I want to code the displaying the results in text boxes is that possible, and not use the properties boxes in VB.


Comments

  • GideonOmegaGideonOmega Member Posts: 617
    : Hiya everyone,
    :
    : I am having trouble coding a search on a database in my VB project. I am using VB6 Enterprise edition, and windows XP and Access 2000.
    :
    : The basic synopsis is;
    :
    : I want to load records into a combo box on a form, then let the user chose a name and search the database and have the results displayed in non changeable text boxes. I have two problems at the moment. Each time I try to code the "Fill combo" section I get various errors. And that then leaves the search impossible. The second problem is every time I use Option Explicit in the code it doesn't run at all.
    : *********************************
    : CODE:-
    : ' Option Explicit
    :
    : Private Sub Form_Load()
    : '** This is the connection to the database
    : Set conn = New ADODB.Connection
    : conn.Provider = "Microsoft.Jet.OleDb.4.0"
    : conn.ConnectionString = (App.Path & "wtfc.mdb")
    : conn.Open
    : ' ** This setsthe record set ie conection to the Players table
    : Set rs = New ADODB.Recordset
    :
    : rs.ActiveConnection = conn
    : rs.Open "Select * from players"
    : ' ** END of the connection section.
    :
    : ' ** Fill the Combo box with the names.
    : 'Name of table = Players
    : 'Name of field in DB to fill the combo with = Name.
    : Dim rs2 As Recordset
    :
    : rs2.OpenRecordset "SELECT Name FROM Players"
    :
    : cboPlayers.AddItem
    : 'dbcboPlayers.AddItem rs2, 1
    :
    : End Sub
    :
    : Private Sub cmdsearch_Click()
    : ' Using the Content of the cbo to search the database and put results into the textboxes
    : Dim playersql As String
    : Dim playersx As String
    : playersx = cboPlayers.Text
    : playersql = "select * from players Where As =' " & playersx & "'"
    : Set rs = conn.OpenRecordset(playersql)
    : MsgBox "You choose " & playersql & " And here it is !"
    : End Sub
    : ******************************************
    :
    : Any help and advice is really welcomed. I want to code the displaying the results in text boxes is that possible, and not use the properties boxes in VB.
    :
    :
    :


    you can use the following code to fill a combo box:
    [code]
    set objRS = new adodb.recordset
    with objRS
    .ActiveConnection = objConn
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Source = "Select NAME from players"
    .Open
    do while not .EOF
    combobox.additem !NAME
    .movenext
    loop
    .close
    end with
    [/code]

    that should take care of the first part of your problem, I'm a bit busy right now so I'll get back to you for the rest of your question later.
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • LensmeisterLensmeister Member Posts: 48
    : : Hiya everyone,
    : :
    : : I am having trouble coding a search on a database in my VB project. I am using VB6 Enterprise edition, and windows XP and Access 2000.
    : :
    : : The basic synopsis is;
    : :
    : : I want to load records into a combo box on a form, then let the user chose a name and search the database and have the results displayed in non changeable text boxes. I have two problems at the moment. Each time I try to code the "Fill combo" section I get various errors. And that then leaves the search impossible. The second problem is every time I use Option Explicit in the code it doesn't run at all.
    : : *********************************
    : : CODE:-
    : : ' Option Explicit
    : :
    : : Private Sub Form_Load()
    : : '** This is the connection to the database
    : : Set conn = New ADODB.Connection
    : : conn.Provider = "Microsoft.Jet.OleDb.4.0"
    : : conn.ConnectionString = (App.Path & "wtfc.mdb")
    : : conn.Open
    : : ' ** This setsthe record set ie conection to the Players table
    : : Set rs = New ADODB.Recordset
    : :
    : : rs.ActiveConnection = conn
    : : rs.Open "Select * from players"
    : : ' ** END of the connection section.
    : :
    : : ' ** Fill the Combo box with the names.
    : : 'Name of table = Players
    : : 'Name of field in DB to fill the combo with = Name.
    : : Dim rs2 As Recordset
    : :
    : : rs2.OpenRecordset "SELECT Name FROM Players"
    : :
    : : cboPlayers.AddItem
    : : 'dbcboPlayers.AddItem rs2, 1
    : :
    : : End Sub
    : :
    : : Private Sub cmdsearch_Click()
    : : ' Using the Content of the cbo to search the database and put results into the textboxes
    : : Dim playersql As String
    : : Dim playersx As String
    : : playersx = cboPlayers.Text
    : : playersql = "select * from players Where As =' " & playersx & "'"
    : : Set rs = conn.OpenRecordset(playersql)
    : : MsgBox "You choose " & playersql & " And here it is !"
    : : End Sub
    : : ******************************************
    : :
    : : Any help and advice is really welcomed. I want to code the displaying the results in text boxes is that possible, and not use the properties boxes in VB.
    : :
    : :
    : :
    :
    :
    : you can use the following code to fill a combo box:
    : [code]
    : set objRS = new adodb.recordset
    : with objRS
    : .ActiveConnection = objConn
    : .CursorLocation = adUseClient
    : .CursorType = adOpenForwardOnly
    : .Source = "Select NAME from players"
    : .Open
    : do while not .EOF
    : combobox.additem !NAME
    : .movenext
    : loop
    : .close
    : end with
    : [/code]
    :
    : that should take care of the first part of your problem, I'm a bit busy right now so I'll get back to you for the rest of your question later.
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :
    :
    Thanks a lot ... I've been trying to rack my brain over this code for ages .. all help Very much appreciated.
  • LensmeisterLensmeister Member Posts: 48
    : : Hiya everyone,
    : :
    : : I am having trouble coding a search on a database in my VB project. I am using VB6 Enterprise edition, and windows XP and Access 2000.
    : :
    : : The basic synopsis is;
    : :
    : : I want to load records into a combo box on a form, then let the user chose a name and search the database and have the results displayed in non changeable text boxes. I have two problems at the moment. Each time I try to code the "Fill combo" section I get various errors. And that then leaves the search impossible. The second problem is every time I use Option Explicit in the code it doesn't run at all.
    : : *********************************
    : : CODE:-
    : : ' Option Explicit
    : :
    : : Private Sub Form_Load()
    : : '** This is the connection to the database
    : : Set conn = New ADODB.Connection
    : : conn.Provider = "Microsoft.Jet.OleDb.4.0"
    : : conn.ConnectionString = (App.Path & "wtfc.mdb")
    : : conn.Open
    : : ' ** This setsthe record set ie conection to the Players table
    : : Set rs = New ADODB.Recordset
    : :
    : : rs.ActiveConnection = conn
    : : rs.Open "Select * from players"
    : : ' ** END of the connection section.
    : :
    : : ' ** Fill the Combo box with the names.
    : : 'Name of table = Players
    : : 'Name of field in DB to fill the combo with = Name.
    : : Dim rs2 As Recordset
    : :
    : : rs2.OpenRecordset "SELECT Name FROM Players"
    : :
    : : cboPlayers.AddItem
    : : 'dbcboPlayers.AddItem rs2, 1
    : :
    : : End Sub
    : :
    : : Private Sub cmdsearch_Click()
    : : ' Using the Content of the cbo to search the database and put results into the textboxes
    : : Dim playersql As String
    : : Dim playersx As String
    : : playersx = cboPlayers.Text
    : : playersql = "select * from players Where As =' " & playersx & "'"
    : : Set rs = conn.OpenRecordset(playersql)
    : : MsgBox "You choose " & playersql & " And here it is !"
    : : End Sub
    : : ******************************************
    : :
    : : Any help and advice is really welcomed. I want to code the displaying the results in text boxes is that possible, and not use the properties boxes in VB.
    : :
    : :
    : :
    :
    :
    : you can use the following code to fill a combo box:
    : [code]
    : set objRS = new adodb.recordset
    : with objRS
    : .ActiveConnection = objConn
    : .CursorLocation = adUseClient
    : .CursorType = adOpenForwardOnly
    : .Source = "Select NAME from players"
    : .Open
    : do while not .EOF
    : combobox.additem !NAME
    : .movenext
    : loop
    : .close
    : end with
    : [/code]
    :
    : that should take care of the first part of your problem, I'm a bit busy right now so I'll get back to you for the rest of your question later.
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :
    :
    I have changed the code to fill the Combobox now and I have tried to do two things with the code since:
    1. I have tried to get the recordset to show the fields intextboxes.
    2. show the number of records in the table.

    neither seem to work. Maybe I am just going over the old mistakes as I have been out of programming for a few years. Anyhelp to kickstart my brain is welcomed heartily.

    ***********************************************************************
    'Option Explicit
    Private Sub Form_Load()
    ' Setup sizes etc.
    Me.Width = 10665
    Me.Height = 9270
    Me.WindowState = 0
    Me.ScaleHeight = 8580
    Me.ScaleMode = 1
    Me.ScaleWidth = 10545

    '** This is the connection to the database
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OleDb.4.0"
    conn.ConnectionString = (App.Path & "wtfc.mdb")
    conn.Open
    ' ** This setsthe record set ie conection to the Players table
    Set rs = New ADODB.Recordset

    rs.ActiveConnection = conn
    rs.Open "Select * from players"
    ' ** END of the connection section.

    ' This section to count the number of records in the table.
    'Dim rs2 As Integer
    '
    'rs.CursorLocation = adUseClient
    'rs.Open "SELECT * FROM players", cn, adOpenDynamic, adLockOptimistic
    ' Label15.Caption = "Whitby have fielded " & RecordCount & " players."
    '** End of the record count section.


    ' ** Fill the Combo box with the names.
    'Name of table = Players
    'Name of field in DB to fill the combo with = Name.
    Set objrs = New ADODB.Recordset
    With objrs
    .ActiveConnection = conn
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Source = "Select Name From players"
    .Open
    Do While Not .EOF

    cboPlayers.AddItem !Name
    .MoveNext
    Loop
    .Close
    End With
    End Sub

    Private Sub cmdsearch_Click()
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OleDb.4.0"
    conn.ConnectionString = (App.Path & "wtfc.mdb")
    conn.Open
    Set playerRS = New ADODB.Recordset

    Dim PlayerSX As String
    PlayerSX = cboPlayers.Text
    'MsgBox "You choose " & PlayerSX & " And here he is !"
    playerRS = "SELECT * FROM players WHERE '" & PlayerSX & "'"

    If PlayerSX = "Choose a player." Then
    MsgBox "Please use the arrow and scroll down until you find the player you want"
    Else
    MsgBox "You choose " & PlayerSX & " And here he is !"
    Text1.Text = PlayerSX
    'text2.text =
    ' text3.text = signed date column on database table players
    ' text4.text = debut column on database table players
    ' text5.text = debut date column on database table players
    ' text6.text = 1st goal column on database table players
    ' text7.text = 1st goal date column on database table players
    ' text8.text = left to date column on database table players
    ' text9.text = left to date column on database table players
    ' text10.text = app column on database table players
    ' text11.text = sub column on database table players
    ' text12.text = total column on database table players
    ' text13.text = goals column on database table players
    Text14.Text = PlayerSX
    Text12.Text = Val("0" & Text10.Text) + Val("0" & Text11.Text) ' This will add up the Appearences and the subs to give the total.
    End If
    End Sub
    ************************************************************************

    Thanks.
    lensmeister
  • GideonOmegaGideonOmega Member Posts: 617
    : : : Hiya everyone,
    : : :
    : : : I am having trouble coding a search on a database in my VB project. I am using VB6 Enterprise edition, and windows XP and Access 2000.
    : : :
    : : : The basic synopsis is;
    : : :
    : : : I want to load records into a combo box on a form, then let the user chose a name and search the database and have the results displayed in non changeable text boxes. I have two problems at the moment. Each time I try to code the "Fill combo" section I get various errors. And that then leaves the search impossible. The second problem is every time I use Option Explicit in the code it doesn't run at all.
    : : : *********************************
    : : : CODE:-
    : : : ' Option Explicit
    : : :
    : : : Private Sub Form_Load()
    : : : '** This is the connection to the database
    : : : Set conn = New ADODB.Connection
    : : : conn.Provider = "Microsoft.Jet.OleDb.4.0"
    : : : conn.ConnectionString = (App.Path & "wtfc.mdb")
    : : : conn.Open
    : : : ' ** This setsthe record set ie conection to the Players table
    : : : Set rs = New ADODB.Recordset
    : : :
    : : : rs.ActiveConnection = conn
    : : : rs.Open "Select * from players"
    : : : ' ** END of the connection section.
    : : :
    : : : ' ** Fill the Combo box with the names.
    : : : 'Name of table = Players
    : : : 'Name of field in DB to fill the combo with = Name.
    : : : Dim rs2 As Recordset
    : : :
    : : : rs2.OpenRecordset "SELECT Name FROM Players"
    : : :
    : : : cboPlayers.AddItem
    : : : 'dbcboPlayers.AddItem rs2, 1
    : : :
    : : : End Sub
    : : :
    : : : Private Sub cmdsearch_Click()
    : : : ' Using the Content of the cbo to search the database and put results into the textboxes
    : : : Dim playersql As String
    : : : Dim playersx As String
    : : : playersx = cboPlayers.Text
    : : : playersql = "select * from players Where As =' " & playersx & "'"
    : : : Set rs = conn.OpenRecordset(playersql)
    : : : MsgBox "You choose " & playersql & " And here it is !"
    : : : End Sub
    : : : ******************************************
    : : :
    : : : Any help and advice is really welcomed. I want to code the displaying the results in text boxes is that possible, and not use the properties boxes in VB.
    : : :
    : : :
    : : :
    : :
    : :
    : : you can use the following code to fill a combo box:
    : : [code]
    : : set objRS = new adodb.recordset
    : : with objRS
    : : .ActiveConnection = objConn
    : : .CursorLocation = adUseClient
    : : .CursorType = adOpenForwardOnly
    : : .Source = "Select NAME from players"
    : : .Open
    : : do while not .EOF
    : : combobox.additem !NAME
    : : .movenext
    : : loop
    : : .close
    : : end with
    : : [/code]
    : :
    : : that should take care of the first part of your problem, I'm a bit busy right now so I'll get back to you for the rest of your question later.
    : : [blue]
    : : C:Dos
    : : C:Dos Run
    : : Run Dos Run
    : : [/blue]
    : :
    : :
    : I have changed the code to fill the Combobox now and I have tried to do two things with the code since:
    : 1. I have tried to get the recordset to show the fields intextboxes.
    : 2. show the number of records in the table.
    :
    : neither seem to work. Maybe I am just going over the old mistakes as I have been out of programming for a few years. Anyhelp to kickstart my brain is welcomed heartily.
    :
    : ***********************************************************************
    : 'Option Explicit
    : Private Sub Form_Load()
    : ' Setup sizes etc.
    : Me.Width = 10665
    : Me.Height = 9270
    : Me.WindowState = 0
    : Me.ScaleHeight = 8580
    : Me.ScaleMode = 1
    : Me.ScaleWidth = 10545
    :
    : '** This is the connection to the database
    : Set conn = New ADODB.Connection
    : conn.Provider = "Microsoft.Jet.OleDb.4.0"
    : conn.ConnectionString = (App.Path & "wtfc.mdb")
    : conn.Open
    : ' ** This setsthe record set ie conection to the Players table
    : Set rs = New ADODB.Recordset
    :
    : rs.ActiveConnection = conn
    : rs.Open "Select * from players"
    : ' ** END of the connection section.
    :
    : ' This section to count the number of records in the table.
    : 'Dim rs2 As Integer
    : '
    : 'rs.CursorLocation = adUseClient
    : 'rs.Open "SELECT * FROM players", cn, adOpenDynamic, adLockOptimistic
    : ' Label15.Caption = "Whitby have fielded " & RecordCount & " players."
    : '** End of the record count section.
    :
    :
    : ' ** Fill the Combo box with the names.
    : 'Name of table = Players
    : 'Name of field in DB to fill the combo with = Name.
    : Set objrs = New ADODB.Recordset
    : With objrs
    : .ActiveConnection = conn
    : .CursorLocation = adUseClient
    : .CursorType = adOpenForwardOnly
    : .Source = "Select Name From players"
    : .Open
    : Do While Not .EOF
    :
    : cboPlayers.AddItem !Name
    : .MoveNext
    : Loop
    : .Close
    : End With
    : End Sub
    :
    : Private Sub cmdsearch_Click()
    : Set conn = New ADODB.Connection
    : conn.Provider = "Microsoft.Jet.OleDb.4.0"
    : conn.ConnectionString = (App.Path & "wtfc.mdb")
    : conn.Open
    : Set playerRS = New ADODB.Recordset
    :
    : Dim PlayerSX As String
    : PlayerSX = cboPlayers.Text
    : 'MsgBox "You choose " & PlayerSX & " And here he is !"
    : playerRS = "SELECT * FROM players WHERE '" & PlayerSX & "'"
    :
    : If PlayerSX = "Choose a player." Then
    : MsgBox "Please use the arrow and scroll down until you find the player you want"
    : Else
    : MsgBox "You choose " & PlayerSX & " And here he is !"
    : Text1.Text = PlayerSX
    : 'text2.text =
    : ' text3.text = signed date column on database table players
    : ' text4.text = debut column on database table players
    : ' text5.text = debut date column on database table players
    : ' text6.text = 1st goal column on database table players
    : ' text7.text = 1st goal date column on database table players
    : ' text8.text = left to date column on database table players
    : ' text9.text = left to date column on database table players
    : ' text10.text = app column on database table players
    : ' text11.text = sub column on database table players
    : ' text12.text = total column on database table players
    : ' text13.text = goals column on database table players
    : Text14.Text = PlayerSX
    : Text12.Text = Val("0" & Text10.Text) + Val("0" & Text11.Text) ' This will add up the Appearences and the subs to give the total.
    : End If
    : End Sub
    : ************************************************************************
    :
    : Thanks.
    : lensmeister
    :


    Well from a brief look You will need to change this:
    [code]
    playerRS = "SELECT * FROM players WHERE '" & PlayerSX & "'"
    [/code]
    to
    [code]
    playerRS = "SELECT * from players where name = '" & PlayerSX & "'"
    [/code]

    otherwise you have no condition for it to search for - although I'm suprised it doesn't raise an error.

    I believe that the ado recordset object also has a rowcount property.

    something like objRS.RowCount
    so
    intRows = objRS.RowCount
    should give you the number of records if executed on a open recordset

    ... it may be something like objRS.RecordsCount.... not sure though

    Hope it helps.
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

Sign In or Register to comment.