Listview and Autonumber Problem!

[paoloMF]

my system is only showing 191 items. but in my database it has 240 items and my autonumber is not working as well

[Doogle]

Without seeing any code it's difficult to guess what's going on.

[paoloMF]

Without seeing any code it's difficult to guess what's going on.

This is the code for my Listview to display the items in MS ACCESS

Sub display_list()
Set rs = New ADODB.Recordset
With rs
.Open "Select * from tblProduct", cn, 2, 3
LvList.ListItems.clear
Do While Not .EOF
LvList.ListItems.Add(1).Text = !ProductID
LvList.ListItems.Item(1).ListSubItems.Add.Text = !ProductName
LvList.ListItems.Item(1).ListSubItems.Add.Text = !Description
LvList.ListItems.Item(1).ListSubItems.Add.Text = !category
LvList.ListItems.Item(1).ListSubItems.Add.Text = !Supplier
LvList.ListItems.Item(1).ListSubItems.Add.Text = !Date
LvList.ListItems.Item(1).ListSubItems.Add.Text = !SupplierPrice
LvList.ListItems.Item(1).ListSubItems.Add.Text = !WholesalePrice
LvList.ListItems.Item(1).ListSubItems.Add.Text = !RetailPrice
LvList.ListItems.Item(1).ListSubItems.Add.Text = !qty
LvList.ListItems.Item(1).ListSubItems.Add.Text = !CritLevel
.MoveNext
Loop
End With
End Sub

and this is the code for my autonumber

Private Sub Auto_num()
Set rss = New ADODB.Recordset
rss.Open "select * from tblProduct", cn, 3, 2
With rss
If .RecordCount = 0 Then
txtID.Text = "PR-0001"
Else
rss.MoveLast
txtID.Text = "PR-" & Format(Right(rss!ProductID, 4) + 1, "0000")
End If

rss.Close
Set rss = Nothing
End With
txtID.Locked = False
End Sub

[Doogle]

In your first post there's a message "Security Warning Certain content in the database has been disabled" in the screenshot of the Table, what's that all about ?

Also, what do you mean by "my auto number is not working"? You can't rely on the SELECT statement bringing the data back in any particular order, so in Auto_num you should specify the order you require.

rss.Open "SELECT * from tblProduct ORDER BY ProductID", cn, 3, 2

EDIT: Since the ProductID is fixed length with leading zeros you can simplify the calculation of the next one to use by selecting the Maximum value in the Table, rather than selecting them all.

Private Sub Auto_num()
Set rss = New ADODB.Recordset
rss.Open "SELECT MAX(ProductID) AS MaxID FROM tblProduct", cn, 3, 2
With rss
    If (.EOF And .BOF) Then
        txtID.Text = "PR-0001"
    Else
        txtID.Text = "PR-" & Format(Right(rss!MaxID, 4) + 1, "0000")
    End If
    rss.Close
    Set rss = Nothing
End With
txtID.Locked = False
End Sub

(I also suspect you want to 'Unlock' txtID, put the new value in and then Lock it so the user can't change it)

I can't see why you're not getting 240 records in the Listview unless 49 of them are blank (or you haven't posted the actual code that's being executed).

Try putting:

MsgBox LvList.ListItems.Count & " Records Added to LvList"

after the 'End With' statement in subroutine display_list; that will confirm the actual number of records added to the ListView.

[paoloMF]

MY CODE FOR PUTTING ITEMS IN LISTVIEW TO TEXTBOXES BY USING LVLIST_ITEMCLICK
Private Sub LvList_ItemClick(ByVal Item As MSComctlLib.ListItem)
Set rs = New ADODB.Recordset
With rs
.Open "Select * from tblProduct where ProductID ='" & LvList.SelectedItem.Text & "'", cn, 2, 3
Do While Not .EOF

If !ProductID = LvList.SelectedItem.Text Then
txtID.Text = !ProductID
txtname.Text = !ProductName
txtDescription.Text = !Description
cboCategory.Text = !category
cboSupplier.Text = !Supplier
DTPicker1.Value = !Date
txtSPrice.Text = !SupplierPrice
txtWPrice.Text = !WholesalePrice
txtRPrice.Text = !RetailPrice
txtqty.Text = !qty
txtCritlevel.Text = !CritLevel

Exit Do
Else
.MoveNext
End If
Loop
End With

End Sub



THIS IS MY CODE FOR SAVING ITEMS TO MS ACCESS
Set rs = New ADODB.Recordset
With rs
.Open "Select * from tblProduct", cn, 2, 3

If lblAdd_Edit.Caption = "ADD" Then
If MsgBox("Do you want to save this new record?", vbQuestion + vbYesNo, "Message") = vbNo Then: Exit Sub
.AddNew
!ProductID = txtID.Text
!ProductName = txtname.Text
!Description = txtDescription.Text
!category = cboCategory.Text
!Supplier = cboSupplier.Text
!Date = DTPicker1.Value
!SupplierPrice = txtSPrice.Text
!WholesalePrice = txtWPrice.Text
!RetailPrice = txtRPrice.Text
!qty = txtqty.Text
!CritLevel = txtCritlevel.Text
.Update
Else
If MsgBox("Do you want to save this changes?", vbQuestion + vbYesNo, "Message") = vbNo Then: Exit Sub
Do While Not .EOF

If LvList.SelectedItem.Text = !ProductID Then
!ProductID = txtID.Text
!ProductName = txtname.Text
!Description = txtDescription.Text
!category = cboCategory.Text
!Supplier = cboSupplier.Text
!Date = DTPicker1.Value
!SupplierPrice = txtSPrice.Text
!WholesalePrice = txtWPrice.Text
!RetailPrice = txtRPrice.Text
!qty = txtqty.Text
!CritLevel = txtCritlevel.Text
.Update

Exit Do
Else
.MoveNext
End If
Loop
End If


End With

Complete_Cancel

End Sub

AND MY CODE FOR DISPLAYING RECORDS IN ACCESS TO LISTVIEW
Sub display_list()
Set rs = New ADODB.Recordset
With rs
.Open "Select * from tblProduct", cn, 2, 3
LvList.ListItems.clear
Do While Not .EOF
LvList.ListItems.Add(1).Text = !ProductID
LvList.ListItems.Item(1).ListSubItems.Add.Text = !ProductName
LvList.ListItems.Item(1).ListSubItems.Add.Text = !Description
LvList.ListItems.Item(1).ListSubItems.Add.Text = !category
LvList.ListItems.Item(1).ListSubItems.Add.Text = !Supplier
LvList.ListItems.Item(1).ListSubItems.Add.Text = !Date
LvList.ListItems.Item(1).ListSubItems.Add.Text = !SupplierPrice
LvList.ListItems.Item(1).ListSubItems.Add.Text = !WholesalePrice
LvList.ListItems.Item(1).ListSubItems.Add.Text = !RetailPrice
LvList.ListItems.Item(1).ListSubItems.Add.Text = !qty
LvList.ListItems.Item(1).ListSubItems.Add.Text = !CritLevel
.MoveNext
Loop
End With
End Sub


THATS ALL THE MAIN CODE THAT I HAVE!

[Doogle]

Did you read Post #4 ?

[paoloMF]

Thanks sir! i already did it! but my only problem is the quantity deduction during sales i dont have any idea on how to do it in MSHFlexgrid.
for example when I click the save button, thats the time the quantity of the product i Inserted in Flexgrid will be deducted in the stocks please? can you give me
some set of codes? help me SIR!

[paoloMF]

HERE IS MY CODE FOR SAVE!
Private Sub save()
If Combo1.Text = "" Then: MsgBox "Please fill the blanks!", vbInformation, "Message": Exit Sub
If txtAddress.Text = "" Then: MsgBox "Please fill the blanks!", vbInformation, "Message": Exit Sub
Call Myps
Dim counter As Integer
With MSHFlexGrid1
For counter = 1 To MSHFlexGrid1.Rows - 2
MSHFlexGrid1.Row = counter
ps.AddNew
ps!SalesID = .TextMatrix(.Row, 0)
ps!ProductName = .TextMatrix(.Row, 1)
ps!Description = .TextMatrix(.Row, 2)
ps!qty = .TextMatrix(.Row, 3)
ps!Price = .TextMatrix(.Row, 4)
ps!DiscountedPrice = .TextMatrix(.Row, 5)
ps!LineTotal = .TextMatrix(.Row, 7)
ps!TotalAmount = txttotal.Text
ps!Date = DTPicker1.Value
ps!CustomerName = Combo1.Text
ps!Address = txtAddress.Text
ps.Update

Next
Set rs = New ADODB.Recordset
Dim i As Integer
Dim prodname As String
Dim qty As Integer
prodname = MSHFlexGrid1.TextMatrix(i, 1)
rs.Open "Select qty from tblProduct where ProductName='" & prodname & "'", cn, 3, 3
qty = MSHFlexGrid1.TextMatrix(i, 3)
If rs.RecordCount > 0 Then
rs!qty = rs!qty - qty
rs.MoveNext
End If


.clear
End With
End Sub

I GET A DEBUG IN THIS LINE: qty = MSHFlexGrid1.TextMatrix(i, 3)