[RESOLVED] Help Autogenerate Number
Hello guys! i have a problem with my code...
http://img831.imageshack.us/img831/5217/capturemv.png
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
Re: Help Autogenerate Number
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
Code:
'after the AddNew
rs.Open "Select MAX(Index) As Idx From tblCustomer") 'Index is the AutoNumber field
Debug.Print rs.Fields("Idx").Value
Re: Help Autogenerate Number
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.
Re: Help Autogenerate Number
Quote:
Originally Posted by
dbasenoob
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:
Code:
MsgBox rs.Fields("id").Value
(change "id" to the name of your field).
Quote:
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.
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.
Re: Help Autogenerate Number
VBClassicRocks, Thanks problem 1 solved!
vb Code:
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
vb Code:
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
Quote:
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
Re: Help Autogenerate Number
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.
Re: Help Autogenerate Number
i change the private declaration into public both forms and it works. but when i adding data it makes duplicates on the listview.
vb Code:
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
vb Code:
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
