[RESOLVED] Help Autogenerate Number

[dbasenoob]

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

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

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

[VBClassicRocks]

Your post isn't exactly clear, but if I understand, you want to get the autonumber value
which was applied when you add records? Perhaps this will help

 'after the AddNew
 rs.Open "Select MAX(Index) As Idx From tblCustomer") 'Index is the AutoNumber field
 Debug.Print rs.Fields("Idx").Value

[dbasenoob]

yes, my post wasn't clear because im not good at english.

well,as you can see on photo above, i want to happen in my program is when i want to click the new button will show the generated number at the customer no. then after that i will put some values. then click the update button to add the customer. after adding the customer will be shown on listview. after that i want modify the details of the customer by selecting at listview when its highlighted. then that's the time you could click the modify button. it's look like a adding form of customer that will show his customer id, name, and so on.

[si_the_geek]

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.

Don't.

While the kind of thing that VBClassicRocks showed will sometimes be correct, there will be times when it gives the wrong number.

What you should do instead is add the record to the database first, and then show the number that was used. As an example, you could use this at the end of Command1_Click:

MsgBox rs.Fields("id").Value

(change "id" to the name of your field).

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.

Move all of the code in first form that uses rs inside the List sub (you could also move all of the cn code there too).

By doing that, you don't need the If's and End If's that are in Form_Unload.

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.

Detect the ID for the item that has been selected (using ListView1.SelectedItem I think), then pass that value to the other form.

In the other form, use that value in the Where clause of a Select statement, and use that to open the recordset.

[dbasenoob]

VBClassicRocks, Thanks problem 1 solved!

Private Sub Command1_Click()
Dim rs2 as New ADODB.Recordset
With rs
.AddNew
.Fields("cusname").Value = Text2.Text
.Fields("address").Value = Text3.Text
.Fields("contact").Value = Text4.Text
.Update
End With
rs2.Open " SELECT Max(cusID) As ID FROM tblCustomer ",cn
Text1.Text = rs2.Fields("ID") + 1
rs2.Close
set rs2 = Nothing
End Sub

Private Sub Form_Load()
Dim rs1 as New ADODB.Recordset
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
rs1.Open "SELECT Max(cusID) As ID FROM tblCustomer ",cn
Text1.Text = rs1.Fields("ID")
rs1.Close
set rs1 = Nothing
End Sub

problem number 2 and 3 not yet solved,

well, at problem number 2 after i click the update button the the information that i inputted was not showed at the listview. and also the old details is cleared after i click the update button. what's wrong with my code?

problem number 3, i want to select from listview via highlight the selected item. then i will click the modify button. how to code it?


@si_the_geek

Move all of the code in first form that uses rs inside the List sub (you could also move all of the cn code there too).

By doing that, you don't need the If's and End If's that are in Form_Unload.

Quote:
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.
Detect the ID for the item that has been selected (using ListView1.SelectedItem I think), then pass that value to the other form.

In the other form, use that value in the Where clause of a Select statement, and use that to open the recordset.

how could i make that one? i have no idea... im not so good at coding

[si_the_geek]

You can do 2... just change the first form so the code is inside one routine (like rs1 in Form_Load in your last post), rather than being spread around different parts of the code.

[dbasenoob]

i change the private declaration into public both forms and it works. but when i adding data it makes duplicates on the listview.

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

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

there is something wrong with my code