Hello guys! i have a problem with my code...



1. how could i make the auto generate number when i click the new button?, since the data type of the customer ID at ms access is autonumber? and also will show the last number.

2.how could i show the added customer in the listview after inputting the details? because after i clicked the update button the listview will be clear, there is something wrong with my code.

3. how could i make when i want to modify the customer i want to happen is when i want to click the customer on the listview then i will click the modify will show the details.

this is the code for the first form
Code:
Option Explicit
Private rs As ADODB.Recordset
Private cn As ADODB.Connection

Private Sub Command1_Click()
frmNewCustomer.Show 1
End Sub

Private Sub Command2_Click()
frmModCustomer.Show 1
End Sub

Private Sub Command4_Click()
Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
    If Not rs Is Nothing Then
        'first, check if the state is open, if yes then close it
        If (rs.State And adStateOpen) = adStateOpen Then
            rs.Close
        End If
        'set them to nothing
        Set rs = Nothing
    End If
    'same comment with rs
    If Not cn Is Nothing Then
        If (cn.State And adStateOpen) = adStateOpen Then
            cn.Close
        End If
        Set cn = Nothing
    End If
End Sub
Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider = Microsoft.Jet.Oledb.4.0;" & _
                     "Data Source =" & App.Path & "\motolite.mdb"
cn.Open
Set rs = New ADODB.Recordset
    With rs
        .Open "tblCustomer", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
    End With
Call list
End Sub

Public Sub list()
Dim x As Integer
ListView1.ListItems.Clear
While Not rs.EOF
Set lst = ListView1.ListItems.Add(, , rs(0))
     For x = 1 To 3
     lst.SubItems(x) = rs(x)
     Next x
rs.MoveNext
Wend
End Sub
this is the code for second form when i click the button new
Code:
Option Explicit
Private rs As ADODB.Recordset
Private cn As ADODB.Connection

Private Sub Command1_Click()
With rs
.AddNew
.Fields("cusname").Value = Text2.Text
.Fields("address").Value = Text3.Text
.Fields("contact").Value = Text4.Text
.Update
End With
frmCustomer.list
End Sub

Private Sub Command2_Click()
Unload Me
End Sub

Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider = Microsoft.Jet.Oledb.4.0;" & _
                     "Data Source =" & App.Path & "\motolite.mdb"
cn.Open
Set rs = New ADODB.Recordset
    With rs
        .Open "tblCustomer", cn, adOpenDynamic, adLockOptimistic, adCmdTable
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not rs Is Nothing Then
        'first, check if the state is open, if yes then close it
        If (rs.State And adStateOpen) = adStateOpen Then
            rs.Close
        End If
        'set them to nothing
        Set rs = Nothing
    End If
    'same comment with rs
    If Not cn Is Nothing Then
        If (cn.State And adStateOpen) = adStateOpen Then
            cn.Close
        End If
        Set cn = Nothing
    End If
End Sub