• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

Adding New Record

Hi Experts,

I have a form call frmdetails which displays all of the people in a table in a list box.  I have a form that adds new people to the table.  It only adds first, middle & surname.  Once the record is added, i then clear the original list on frmdetails then reapply the list with the new recorded, sorted by surname.

How do i get the list box to automatically select and go to the record i have just added?

Regards,

Andrew.
0
andrewl77
Asked:
andrewl77
  • 4
  • 3
1 Solution
 
GrahamSkanRetiredCommented:
Private Sub Command1_Click()
    Dim i As Integer
   
    List1.AddItem "def"
    List1.AddItem "abc"
    i = List1.NewIndex
    List1.ListIndex = i
End Sub
0
 
andrewl77Author Commented:
This is what my code looks like at present:  What change would make this happen.

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
   
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "DSN=1234;database=umas;uid=12343;pwd=1234
    conn.Open
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
   
    rs.Open "select * from umpire where 1 = 0", conn, adOpenDynamic, adLockOptimistic
   
    rs.addnew
   
    rs!firstname = firstname
    If Me.middlename = "" Then
   
    Else
    rs!middlename = middlename
    End If
    rs!surname = surname
   
    rs.update
    Set rs = Nothing
   
    frmumpiredetails.umplist.Clear
   
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
   
    rs1.Open "select * from umpire where active = 0 order by surname", conn, adOpenDynamic, adLockOptimistic
   
    Set frmumpiredetails.umplist.DataSource = rs1
    Do Until rs1.EOF
    frmumpiredetails.umplist.AddItem rs1!surname & " " & rs1!firstname
    frmumpiredetails.umplist.ItemData(frmumpiredetails.umplist.NewIndex) = rs1!umpireno
    rs1.MoveNext
   
    Loop
   
    rs1.Close
         
    conn.Close
    Set conn = Nothing
   
    Unload frmumpnew
0
 
GrahamSkanRetiredCommented:
The simplest way to add the new item without clearing the list.

...
    End If
    rs!surname = surname
    rs.Update
   
   
    frmumpiredetails.umplist.AddItem surname & " " & firstname
    frmumpiredetails.umplist.ItemData(frmumpiredetails.umplist.NewIndex) = rs.umpireno
    Set rs = Nothing

    conn.Close
    Set conn = Nothing
   
    Unload frmumpnew
       
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
andrewl77Author Commented:
How do i then sort the list by surname?
0
 
GrahamSkanRetiredCommented:
If the Sorted property of the ListBox is set to True (at design time), it will sort automatically.
0
 
andrewl77Author Commented:
Okay...now back to the actual question once all that is done, how do i go to the record i just added on the other form?
0
 
GrahamSkanRetiredCommented:
Sorry. I missed that bit off the last lot of code.

   frmumpiredetails.umplist.ListIndex=frmumpiredetails.umplist.NewIndex
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now