ssswmmcihm
asked on
VB6 ADO, MS Access 2000. Dropdown list, select record, this becomes current record
Using VB6, MS Access 2000, ADO. Used VB Data Form Wizard, Access, linked to the database NE.mdb, used "Single Record" option, ADO Data Control, selected a table called "Students", all records in table. I am using an ADO Data Control because I need to update the fields from this form, so I want them to be bound to the database table.
PROBLEM: I have no way of searching for a certain Student, except to click through each record one at a time. I would like to have a dropdown menu on the Name field, be able to select the correct name, then have this record become the current record.
There are many fields so would like to avoid using many lines of code to re-populate each field line by line. I just need it to jump to the record (random access) rather than having to click through each sequentially.
I tried "Creating a Simple Datacombo Application" but it would not allow me to enter a Listfield in the properties of the DataCombo.
PROBLEM: I have no way of searching for a certain Student, except to click through each record one at a time. I would like to have a dropdown menu on the Name field, be able to select the correct name, then have this record become the current record.
There are many fields so would like to avoid using many lines of code to re-populate each field line by line. I just need it to jump to the record (random access) rather than having to click through each sequentially.
I tried "Creating a Simple Datacombo Application" but it would not allow me to enter a Listfield in the properties of the DataCombo.
'Please add 1 ComboBox and 1 CommandButton to text...
Private sub form_load()
call loaddatatocombo
end sub
Private sub CmdSearch_click()
adodc1.recordset.find combo1.text
end sub
Private sub LoadDataToCombo()
On error goto ErrFound
Dim Cnn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim DbPath as string
Set Cnn = New ADODB.Connection
Cnn.Open "Provider=Microsoft.Jet.OL
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open "Select StudentName from Students order by students asc", Cnn, adOpenStatic, adLockReadOnly
if Rs.recordcount then
msgbox "Empty Recordset,Connection will be Close"
rs.close
set rs= nothing
Cnn.close
set Cnn = nothing
exit sub
end if
rs.movefirst
do while not rs.eof
combo1.additem rs.fields(0).value
rs.movenext
loop
rs.close
set rs= nothing
Cnn.close
set Cnn = nothing
exit sub
ErrFound:
msgbox "Err description : " & Err.Description
if rs.state=adstateOpen Then
rs.close
set rs = nothing
end if
if Cnn.state = adstateOpen then
Cnn.close
set Cnn = nothing
end if
End sub
Private sub form_load()
call loaddatatocombo
end sub
Private sub CmdSearch_click()
adodc1.recordset.find combo1.text
end sub
Private sub LoadDataToCombo()
On error goto ErrFound
Dim Cnn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim DbPath as string
Set Cnn = New ADODB.Connection
Cnn.Open "Provider=Microsoft.Jet.OL
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open "Select StudentName from Students order by students asc", Cnn, adOpenStatic, adLockReadOnly
if Rs.recordcount then
msgbox "Empty Recordset,Connection will be Close"
rs.close
set rs= nothing
Cnn.close
set Cnn = nothing
exit sub
end if
rs.movefirst
do while not rs.eof
combo1.additem rs.fields(0).value
rs.movenext
loop
rs.close
set rs= nothing
Cnn.close
set Cnn = nothing
exit sub
ErrFound:
msgbox "Err description : " & Err.Description
if rs.state=adstateOpen Then
rs.close
set rs = nothing
end if
if Cnn.state = adstateOpen then
Cnn.close
set Cnn = nothing
end if
End sub
ASKER
I tried this suggestion. It appears to load all the NAME values into a combo box.
However, picking a name in the combo box does change the current record to the selected NAME's record, which is what I am looking for. In other words, I am trying to give the user a drop-down list of names to choose from on the form, so that when they highlight and click on that name, the current record becomes the record they chose.
I do not want to use a search button. The search button that you provided does not work, it gives a runtime error 3001.
However, picking a name in the combo box does change the current record to the selected NAME's record, which is what I am looking for. In other words, I am trying to give the user a drop-down list of names to choose from on the form, so that when they highlight and click on that name, the current record becomes the record they chose.
I do not want to use a search button. The search button that you provided does not work, it gives a runtime error 3001.
ok,wait a minute...
'it seem combo box have no event after you have select item from list.
'Maybe other control will help you.all i can do will like below...
'Press Enter after you have select item from the list.
Private Sub Combo1_KeyDown(KeyCode As Integer, Shift As Integer)
Dim St As String
St = Adodc1.Recordset.Fields(0)
Debug.Print St
If KeyCode = vbKeyReturn Then
Adodc1.Recordset.Find St
End If
End Sub
'Why use Enter key to search ?
'if user using mouse scroll button to search data,this will slow down database process.
'anyway ,up to you.
'Maybe other control will help you.all i can do will like below...
'Press Enter after you have select item from the list.
Private Sub Combo1_KeyDown(KeyCode As Integer, Shift As Integer)
Dim St As String
St = Adodc1.Recordset.Fields(0)
Debug.Print St
If KeyCode = vbKeyReturn Then
Adodc1.Recordset.Find St
End If
End Sub
'Why use Enter key to search ?
'if user using mouse scroll button to search data,this will slow down database process.
'anyway ,up to you.
Hi ssswmmcihm. Supposing you have other controls bound to the result set of the ado data control and your populating your combo box by looping through the same result set (recordset) and adding the items to the combo box list. Put the following code on the click event of your combo box:
If Not myADOControl.Recordset.BOF
myADOControl.Recordset.Mov
myADOControl.Recordset.Mov
End If
Hope that helps :0)
If Not myADOControl.Recordset.BOF
myADOControl.Recordset.Mov
myADOControl.Recordset.Mov
End If
Hope that helps :0)
fiend,are you there :)
'I have find out what you want...
'just go to where your Vb6 install folder then search for PRJDE.VBP.Just like below:
C:\Program Files\Microsoft Visual Studio\MSDN\2000JAN\1033\S
Run the program and click button combo...
'I have find out what you want...
'just go to where your Vb6 install folder then search for PRJDE.VBP.Just like below:
C:\Program Files\Microsoft Visual Studio\MSDN\2000JAN\1033\S
Run the program and click button combo...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wasn't able to make it work but I was able to figure out what I needed to do and am posting it here for the benefit of others who may have a similar question. I do not know if this is the best way to do this, but it works.
The field I am looking to match on is field 5, that is why I changed it from field 0.
Private Sub Combo1_Click()
'go to the beginning of the recordset
datPrimaryRS.Recordset.Mov
'scan through each record until you find the record that matches the combo box value
Do While Not datPrimaryRS.Recordset.EOF
datPrimaryRS.Recordset.Mov
If datPrimaryRS.Recordset.Fie
Loop
End Sub
The field I am looking to match on is field 5, that is why I changed it from field 0.
Private Sub Combo1_Click()
'go to the beginning of the recordset
datPrimaryRS.Recordset.Mov
'scan through each record until you find the record that matches the combo box value
Do While Not datPrimaryRS.Recordset.EOF
datPrimaryRS.Recordset.Mov
If datPrimaryRS.Recordset.Fie
Loop
End Sub
Dear ssswmmcihm:)
I will subject you modify and use my code (Recordset.Find method) because your code will have problem when open large amount of records.
You can also try the Recordset.filters method.
anyway,up to you.
I will subject you modify and use my code (Recordset.Find method) because your code will have problem when open large amount of records.
You can also try the Recordset.filters method.
anyway,up to you.
how to use two criteria in rs.find
say to find field1=text1.text and field2=text2.text
say to find field1=text1.text and field2=text2.text
can someone help
No way....the find method is not your best answer here. You should simply use a Do Until EOF loop and exit the loop if you find the record. For example
Rs.MoveFirst
Do Until Rs.EOF
If Rs.Fields!Field1 = Text1.Text and Rs.Fields!Field2 = Text1.Text then
'might want to assign the data to variables here or do whatever code you need
Exit Do
Else
Rs.MoveNext
End If
Loop
If Rs.EOF then
Msgbox "File not found"
End If
If you find the record, you can code what you need to at that part of the loop. If there is no match, the computer will inform the user. This will only happen if it loops thru all of the records and doesn't find anything. Good luck!
Rs.MoveFirst
Do Until Rs.EOF
If Rs.Fields!Field1 = Text1.Text and Rs.Fields!Field2 = Text1.Text then
'might want to assign the data to variables here or do whatever code you need
Exit Do
Else
Rs.MoveNext
End If
Loop
If Rs.EOF then
Msgbox "File not found"
End If
If you find the record, you can code what you need to at that part of the loop. If there is no match, the computer will inform the user. This will only happen if it loops thru all of the records and doesn't find anything. Good luck!
then adodc1.find combobox.text