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
:
: 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 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.
: :
: : 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
: : :
: : : 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]