Data Problem!

[Rally]

I keep getting this Error but don't undersand why
Would someone please help me??

Option Explicit
Dim db As Database
Dim rs As Recordset
Dim lstmain1 As ListItem
Dim lstmain2 As ListItem
Dim clmhead1 As ColumnHeader
Dim clmhead2 As ColumnHeader
Private Sub Form_Load()
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\PatsInventory.mdb")
Set clmhead1 = lvwSuppliers.ColumnHeaders.Add(, , "Supplier ID", lvwSuppliers.Width)
SupplierView


End Sub

Private Sub cmdEdit_Click()
cmdCSave.Visible = True
        txtSupplierName.Locked = False
        txtContactName.Locked = False
        txtContactTitle.Locked = False
        txtAddress.Locked = False
        txtCity.Locked = False
        txtState.Locked = False
        txtPostal.Locked = False
        txtCountry.Locked = False
        txtPhone.Locked = False
        txtFax.Locked = False
        txtSupplierName.SetFocus
End Sub

Private Sub cmdSave_Click()
Set rs = db.OpenRecordset("Suppliers")
cmdSave.Visible = False
        txtSupplierName.Locked = True
        txtContactName.Locked = True
        txtContactTitle.Locked = True
        txtAddress.Locked = True
        txtCity.Locked = True
        txtState.Locked = True
        txtPostal.Locked = True
        txtCountry.Locked = True
        txtPhone.Locked = True
        txtFax.Locked = True
        
        While Not rs.EOF
        If lvwSupplier.SelectedItem.Text = rs!ContactID Then
            rs.Edit
            rs!SupplierName = txtSupplierName.Text
            rs!ContactName = txtContactName.Text
            rs!ContactTitle = txtContactTitle.Text
            rs!Address = txtAddress.Text
            rs!City = txtCity.Text
            rs!State = txtState.Text
            rs!Postal = txtPostal.Text
            rs!Country = txtCountry.Text
            rs!Phone = txtPhone.Text
            rs!Fax = txtFax.Text
            rs.Update
            rs.MoveLast
            rs.MoveNext
        Else
            rs.MoveNext
        End If
        Wend
End Sub

Private Sub cmdAdd_Click()
frmAddSuppliers.Show vbModal
End Sub

Private Sub cmdView_Click()
ContactView
rs.Close
End Sub
Private Sub lvwSuppliers_Click()
Set rs = db.OpenRecordset("Suppliers")
While Not rs.EOF
If lvwSuppliers.SelectedItem.Text = rs!SupplierID Then
    txtSupplierName.Text = rs!SupplierName
    txtContactName.Text = rs!ContactName
    txtContactTitle.Text = rs!ContactTitle
    txtAddress.Text = rs!Address
    txtCity.Text = rs!City
    txtState.Text = rs!State
    txtPostal.Text = rs!Postal
    txtCountry.Text = rs!Country
    txtPhone.Text = rs!Phone
    txtFax.Text = rs!Fax
    rs.MoveLast
    rs.MoveNext
Else
    rs.MoveNext
End If
Wend

End Sub
Private Sub txtFax_Change()
Dim dig$, i, digi$, digits$
If txtFax.Text <> "" Then
    dig$ = Mid(txtFax.Text, Len(txtFax.Text), 1)
    If Asc(dig$) < 46 Or Asc(dig$) > 57 Then
        For i = 1 To Len(txtFax.Text) - 1
            digi$ = Mid(txtFax.Text, i, 1)
            digits$ = digits$ & digi$
        Next i
        txtFax.Text = digits$
        txtFax.SelStart = Len(txtFax.Text)
    End If
End If
End Sub

Private Sub txtPhone_Change()
Dim dig$, i, digi$, digits$
If txtPhone.Text <> "" Then
    dig$ = Mid(txtPhone.Text, Len(txtPhone.Text), 1)
    If Asc(dig$) < 46 Or Asc(dig$) > 57 Then
        For i = 1 To Len(txtPhone.Text) - 1
            digi$ = Mid(txtPhone.Text, i, 1)
            digits$ = digits$ & digi$
        Next i
        txtPhone.Text = digits$
        txtPhone.SelStart = Len(txtPhone.Text)
    End If
End If
End Sub



Public Sub cmdDelete_Click()
Set rs = db.OpenRecordset("Suppliers")
        txtSupplierName.Text = ""
        txtContactName.Text = ""
        txtContactTitle.Text = ""
        txtAddress.Text = ""
        txtCity.Text = ""
        txtState.Text = ""
        txtPostal.Text = ""
        txtCountry.Text = ""
        txtPhone.Text = ""
        txtFax.Text = ""
    cmdDelete.Enabled = False
    cmdEdit.Enabled = False
While Not rs.EOF
    If frmMain.lvwSuppliers.SelectedItem.Text = rs!SupplierID Then
    rs.Delete
    ContactView
        If rs.RecordCount = 0 Then
            Exit Sub
        Else
            rs.MoveLast
        End If
    rs.MoveNext
    Else
    rs.MoveNext
    End If
Wend
rs.Close
End Sub
Public Sub SupplierView() '<<<<<<<<-------------- Iam getting my Error here Somone please Help Thanks!!
Set rs = db.OpenRecordset("Suppliers")
frmSuppliers.lvwSuppliers.ListItems.Clear
    While Not rs.EOF
    Set lstmain1 = frmSuppliers.lvwSuppliers.ListItems.Add(, , rs!SupplierID)
    rs.MoveNext
    Wend
    If rs.RecordCount = 0 Then
        MsgBox "There are no records on the database"
    Else
        frmSuppliers.lvwSuppliers.Enabled = True
        frmSuppliers.cmdCDelete.Enabled = True
        frmSuppliers.cmdCEdit.Enabled = True
    End If
End Sub

[SLH]

Can you be more specific as to the line the error occurs on, because i don't thing you can get an error on the actual procedure declaration line (s you've indicated).

[zak2zak]

May be this helps...
U should Select at lease 1 field to loop thru new open recordset

Public Sub SupplierView()
Set rs = db.OpenRecordset("SELECT SupplierID FROM Suppliers")
If rs.RecordCount <=0 then Goto NoRecord
rs.MoveFirst
frmSuppliers.lvwSuppliers.ListItems.Clear
    While Not rs.EOF
    Set lstmain1 = frmSuppliers.lvwSuppliers.ListItems.Add(, , rs!SupplierID)
    rs.MoveNext
    Wend
    frmSuppliers.lvwSuppliers.Enabled = True
    frmSuppliers.cmdCDelete.Enabled = True
    frmSuppliers.cmdCEdit.Enabled = True
Exit Sub
 
NoRecord:
        MsgBox "There are no records on the database"
    Exit Sub
End Sub

[Rally]

I ended up changing the whole thing but I still don't see what I did wrong.
I have been reading in my book but it talks more about SQL than Access
I will post the Util maybe someone will look at it and tell me what I’m doing wrong.
Thanks a lot for the reply !!!!!

[zak2zak]

May be this helps...
I have checked u'r code and u'r database
The Database is empty?
U need to have records in the database to view it in the list...

[Rally]

Even if I go ahead and populate it, there are errors that I don’t understand
However, thanks for the reply!