Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB6 ADO, MS Access 2000.  Dropdown list, select record, this becomes current record

Posted on 2003-12-02
13
Medium Priority
?
74,424 Views
Last Modified: 2013-12-25
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.

   
0
Comment
Question by:ssswmmcihm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +2
13 Comments
 
LVL 8

Expert Comment

by:MYLim
ID: 9863206
You have to add 1 combobox and insert all student name into combobox item.
then adodc1.find combobox.text
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9863302
'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.OLEDB.4.0;Data Source=" & DbPath & ";User Id=admin;Password=;"

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
0
 

Author Comment

by:ssswmmcihm
ID: 9870726
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.







0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:MYLim
ID: 9871466
ok,wait a minute...
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9871800
'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).Name & "='" & Combo1.Text & "'"
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.
0
 
LVL 8

Expert Comment

by:spongie
ID: 9874798
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 And Not myADOControl.Recordset.EOF Then
      myADOControl.Recordset.MoveFirst
      myADOControl.Recordset.Move (myComboBox.ListIndex)
    End If

Hope that helps :0)
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9895192
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\SAMPLES\VB98\DEGuide\PRJDE.VBP

Run the program and click button combo...
0
 
LVL 8

Accepted Solution

by:
MYLim earned 750 total points
ID: 9895286
'i am such an idiot,your requirement can be simply fullfil by using click event ...Pls try again

Private Sub Combo1_Click()
On Error GoTo ErrFound
Debug.Print Adodc1.Recordset.Fields(0).Value & "='" & Combo1.Text & "'"
    Adodc1.Recordset.Find Adodc1.Recordset.Fields(0).Name & "='" & Combo1.Text & "'"
Exit Sub
ErrFound:

If Adodc1.Recordset.EOF = True Then
    Adodc1.Recordset.MoveFirst
else
    MsgBox "Error Number : " & Err.Number & Chr(10) + Chr(13) & "Error Description : " & Err.Description, vbInformation, Err.Source
End If
Resume
End Sub
0
 

Author Comment

by:ssswmmcihm
ID: 9917097
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.MoveFirst
   'scan through each record until you find the record that matches the combo box value
   Do While Not datPrimaryRS.Recordset.EOF
      datPrimaryRS.Recordset.MoveNext
      If datPrimaryRS.Recordset.Fields(5).Value = Combo1.Text Then Exit Do
   Loop
   
End Sub
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9917394
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.
0
 

Expert Comment

by:leenapedenekar
ID: 13750307
how to use two criteria in rs.find
say to find field1=text1.text and field2=text2.text
0
 

Expert Comment

by:leenapedenekar
ID: 13750857
can someone help
0
 

Expert Comment

by:russbergen
ID: 14425732
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!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question