select similar data as one

cuber_killua
11-02-2004, 07:29 PM
hi guys! i just want to know if it is possible to select similar datas in one field and populate it in combo box as one. example in a field course: bscs, beed, ab, bscs, beed, bse, bscs. thats it select all bscs or beed as one and populate it at combo box...

Dennis DVR
11-02-2004, 07:57 PM
hi guys! i just want to know if it is possible to select similar datas in one field and populate it in combo box as one. example in a field course: bscs, beed, ab, bscs, beed, bse, bscs. thats it select all bscs or beed as one and populate it at combo box...

SELECT DISTINCT Field1 FROM myTable

the select statement above will remove any duplicate record in Field1.


Private Sub LoadItemToCombo()
Dim rs As ADODB.Recordset
Dim cnn As String 'Holds the connection string
Set rs = New ADODB.Recordset
cnn = "Your Connection String Here"
'Retrive the data, but remove any duplicates
rs.Open "SELECT DISTINCT Field1 FROM myTable", cnn, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF 'keep looping until eof
combo1.AddItem rs.Fields("Field1").Value 'Populate the combo
rs.MoveNext 'move to the next record
Loop
rs.Close
Set rs = Nothing
End Sub

cuber_killua
11-03-2004, 07:10 PM
i have tried your code but it doesnt work, and even if i add an ado control and set the provider at design time... have you already tried that code?
this is the code and i add an ado control and set the provider but it doesnt work

Private Sub Form_Load()
LoadItemToCombo

End Sub

Private Sub LoadItemToCombo()
Dim rs As ADODB.Recordset
'Dim cnn As String 'Holds the connection string
Set rs = New ADODB.Recordset
'cnn = "provider = microsoft.jet.4.0.oledb;persist security info = false;datasource = " & App.Path & "/db1.mdb"
'Retrive the data, but remove any duplicates
rs.Open "SELECT DISTINCT course FROM tab1", cnn, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF 'keep looping until eof
Combo1.AddItem rs.Fields("course").Value 'Populate the combo
rs.MoveNext 'move to the next record
Loop
rs.Close
Set rs = Nothing
End Sub

tboltfrank
11-03-2004, 08:01 PM
Hi cuber_killua,

Is this Query perhaps any closer to what you're after?

Dim StrSQL As String
StrSQL = "SELECT DISTINCT * FROM tab1 WHERE course = 'bscs' Or course = 'beed'"

rs.Open StrSQL, Cnt, adOpenForwardOnly, adLockReadOnly

Dennis DVR
11-03-2004, 08:29 PM
i have tried your code but it doesnt work, and even if i add an ado control and set the provider at design time... have you already tried that code?

No I didn't test that code, but giving the correct connection string, I'm positive that it would work.


Private Sub Form_Load()
LoadItemToCombo
End Sub

Private Sub LoadItemToCombo()
Dim rs As ADODB.Recordset
Dim cnn As String 'Holds the connection string
Set rs = New ADODB.Recordset
'This is the correct connection string
Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\db1.mdb"
'Retrive the data, but remove any duplicates
rs.Open "SELECT DISTINCT course FROM tab1", cnn, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF 'keep looping until eof
Combo1.AddItem rs.Fields("course").Value 'Populate the combo
rs.MoveNext 'move to the next record
Loop
rs.Close
Set rs = Nothing
End Sub


and you don't need a data control, you just need a reference to Microsoft ActiveX Data Object 2.x

cuber_killua
11-05-2004, 07:30 PM
i tried again your code and i found it that my code is wrong and your code is correct... my problem is, i once i click the combo1 that is populated w/ course i combo 2 will then be populated w/ names under the course i chose in combo1... this is my code can u please check it out:

Private Sub search()
Dim rs1 As New ADODB.Recordset
End Sub
Dim recset As New ADODB.Recordset
Dim co As String
Set recset = New ADODB.Recordset
co = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\sample.mdb"
recset.Open "SELECT DISTINCT name FROM rec", co

Do While Not recset.EOF
rs1.Open "SELECT * FROM rec WHERE year= '" & Text1.Text & "' and course= '" & cbocors.Text & "'", co
If rs1.RecordCount <> 0 Then
cboname.AddItem recset("name")
End If
recset.MoveNext
Loop
recset.Close
rs1.Close
Set rs1 = Nothing
Set recset = Nothing

End Sub

i try to put that code in combo1 that is in click procedure but in doesnt work...

MKoslof
11-06-2004, 09:43 AM
Why are you opening two recordsets here? What are you trying to do. You should put together one query that returns all the information that you need. And you should be putting []'s around Name [name] this is a reserved word when using Access. And you should be passing in all parameters to your recordset open method:



rs1.Open myQuery, myConnection, adOpenKeyset, adLockOptimistic, adCmdText

cuber_killua
11-07-2004, 10:31 PM
i was trying to open a field (name) in a table... but that is after i click the combo box. i open to recordset becoz the first recordset is to avoid similarities of items and one is to open a filed after i click bombo box. can u help me doing my code.

Dennis DVR
11-07-2004, 11:32 PM
Private Sub search()
Dim recset As New ADODB.Recordset
Dim co As String
Set recset = New ADODB.Recordset
co = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\sample.mdb"
recset.Open "SELECT DISTINCT [name] FROM rec WHERE [year]= '" & Text1.Text & "' and course= '" & cbocors.Text & "'", co, adOpenForwardOnly, adLockReadOnly,adCmdText
Do While Not recset.EOF
cboname.AddItem recset("name").Value
recset.MoveNext
Loop
recset.Close
Set recset = Nothing
End Sub


If the year is a numeric datatype the query should look like this:

'Remove the single quote around the text1.text
recset.Open "SELECT DISTINCT [name] FROM rec WHERE [year]= " & Text1.Text & " and course= '" & cbocors.Text & "'", co, adOpenForwardOnly, adLockReadOnly,adCmdText


Avoid using a reserve word as your field name :)

cuber_killua
11-08-2004, 06:20 PM
i try to put your code at form_load and in click procedure at combobox, but it doesnt work... and the combo box w/ course item at first there is no similarities but at second click all course apear w/ similar to others.

Dennis DVR
11-08-2004, 06:37 PM
i try to put your code at form_load and in click procedure at combobox, but it doesnt work... and the combo box w/ course item at first there is no similarities but at second click all course apear w/ similar to others.

Just post the code for these 2 combos and other related code.

cuber_killua
11-08-2004, 07:40 PM
there is an error occurs, "data type mismatch in criteria".
recset.Open "SELECT DISTINCT name FROM rec WHERE year= '" & Text1.Text & "' and course= '" & cbocors.Text & "'", co, adOpenForwardOnly, adLockReadOnly, adCmdText
an error occurs in that line.

cuber_killua
11-08-2004, 07:40 PM
there is an error occurs, "data type mismatch in criteria".
recset.Open "SELECT DISTINCT name FROM rec WHERE year= '" & Text1.Text & "' and course= '" & cbocors.Text & "'", co, adOpenForwardOnly, adLockReadOnly, adCmdText
an error occurs in that line.

cuber_killua
11-08-2004, 07:40 PM
there is an error occurs, "data type mismatch in criteria".
recset.Open "SELECT DISTINCT name FROM rec WHERE year= '" & Text1.Text & "' and course= '" & cbocors.Text & "'", co, adOpenForwardOnly, adLockReadOnly, adCmdText
an error occurs in that line.

tboltfrank
11-08-2004, 08:16 PM
Try changing: ... WHERE year= '" & Text1.Text & "' and course= ...

To: ... WHERE year= " & Text1.Text & " and course= ...

because I would think that your year Value is probably not a string
and therefore shouldn’t need to be surrounded by single quotes.

Edit: The above is the way that Duanes numeric code example is,
plus try adding the brackets around [year] and [name] as Duane also did,
being that those may be reserved words.

cuber_killua
11-09-2004, 07:38 PM
year, course and name are not only the fields inside my table it consist of course, name, year, date, login and logout field and therefore year and date are not string...

cuber_killua
11-09-2004, 07:50 PM
i think single quote is not the reason why error occurs, coz when you delete those single quotes surely an error occurs. this is the code:

Private Sub search()
Dim recset As New ADODB.Recordset
Dim co As String
Set recset = New ADODB.Recordset
co = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\sample.mdb"
recset.Open "SELECT DISTINCT [name] FROM rec WHERE [year]= '" & Text1.Text & "' and course= '" & cbocors.Text & "'", co, adOpenForwardOnly, adLockReadOnly, adCmdText
Do While Not recset.EOF
cboname.AddItem recset(Name).Value
recset.MoveNext
Loop
recset.Close
Set recset = Nothing
End Sub

Private Sub add()
Dim rs As New ADODB.Recordset
Dim conn As String
Set rs = New ADODB.Recordset
conn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\sample.mdb"
rs.Open "SELECT DISTINCT course FROM rec", conn, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF
'rs1.Open "select distinct rec.name, rec.year from rec where name= '" & cboname.Text & "' And Year = '" & Text1.Text & "'", conn
'If rs1.RecordCount > 0 Then
cbocors.AddItem rs.Fields("course").Value
rs.MoveNext
'End If
Loop
rs.Close
Set rs = Nothing
End Sub

those two codes are the codes that i put inside the form load.

tboltfrank
11-09-2004, 07:51 PM
The brackets are around year and name, only because those might be
reserved words, the other field's don't need them, but will work either way.

And the fact that year is not a string, is why I was saying that you should not
surround it with single quotes. The single quotes will only work with strings
, that's why Duane gave you two examples, the first to use if your year is a string and the secound, to use if it's numeric. ;)

Edit: Oh, I see that you were posting at the same time, as I was. I'll look at your code now, and see if I can spot anuthing else.

cuber_killua
11-09-2004, 08:07 PM
but when i delete the single quote missing operator occurs, and in that code is it ok to disregard the other three fields? :confused:

cuber_killua
11-09-2004, 08:22 PM
i have already copied and pased your code but still missing operator occurs, in my ms acces table the year field is in number category.

tboltfrank
11-09-2004, 11:48 PM
What do you mean by Quote: " those two codes are the codes that i put inside the form load "

If the TexBox that you use to enter the year, is in the same Form as the two ComboBox's, the FormLoadEvent wouldn't be appropriate for executing your DISTINCT Name query, as you first need to have a year entered. If the year is supplied by a Textbox from another Form, you would need to explicitly reference it... ie: MyOtherForm.Text1.Text

I succesfully tested very similar code to yours, with the TextBox in the same Form as the two ComboBox's, only using the Form Load Event to execute the DISTINCT Course Query and a CommandButton, to fire the DISTINCT Name Query.

Here's an outline of what I did:

Based on the fact that you stated that your year field, is numeric,
(1) I did not enclose the Text1 TextBox, with single quotes, in the SQL for the Name Query.
(2) I used the FormLoadEvent, to run the SQL that adds Course's to the < cbocors ComboBox >
(3) I entered a year, (ie: 1996), in the Text1 TextBox
(4) and selected a course in the < cbocors ComboBox >,
(5) then I clicked the CommandButton, to run the DISTINCT Name query,
which succesfully added Names, (based on the two criteria's), to the < cboname combobox >

Private Sub Form_Load()
Call ComboBoxLoad_cbocors
End Sub
' //
Private Sub ComboBoxLoad_cbocors()
'bla, bla, bla
rs.Open "SELECT DISTINCT [course] FROM rec", conn, _
adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF
cbocors.AddItem rs.Fields("course").Value
rs.MoveNext
Loop
'bla, bla, bla
End Sub
' //
Private Sub CommandButton_cboname_Click()
'bla, bla, bla
recset.Open "SELECT DISTINCT [name] FROM rec " & _
"WHERE [year]= " & Text1.Text & " and course= '" & cbocors.Text & "'", _
co, adOpenForwardOnly, adLockReadOnly, adCmdText
Do While Not recset.EOF
cboname.AddItem recset.Fields("name").Value
recset.MoveNext
Loop
'bla, bla, bla
End Sub

I was able to replicate the error you spoke of, but only by not entering a year value,
into the Text1 TextBox.

-

Edit: Later, you can add validation methods, that will prevent that error from being possible, but I think that first,
you would probably be well served, to modify your code, as I outlined, & test run it, so that you can see it works.

-

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum