Problem with adding deleting records in bounded

[tony007]

Hi guys i a have bounded form that supposed to update/add and delete records from customers table. But it is acting very weried when i try to use it. For example in the pic below it shows my form trying to
add a new record number 8 .

http://i5.photobucket.com/albums/y18...oaddrecord.jpg

After i added new record and if i press the button to bring me to first record i see my new record added to begining of list and if i press button to take me to last record which is number 8 it shows its customername value as blank! I be happy if some one help me fixt this strange problem. Also the first record in the db get delete with new record!

http://i5.photobucket.com/albums/y18...ngrecords1.jpg ( value in table customer before adding new record)


http://i5.photobucket.com/albums/y18...howsblank2.jpg


http://i5.photobucket.com/albums/y18...newrecord3.jpg ( vlaus in table customer after adding new record .first record get deleted with out my intention!)

Now if i try to delete a record. should i number all the records or not ? I be happy if some one show me how to solve this problem as well.Thanks

http://i5.photobucket.com/albums/y18...edeleting4.jpg ( values of customer table before deleting record)


http://i5.photobucket.com/albums/y18...rdeleting5.jpg

(values of customer table after deleting records)

my code

Option Compare Database



Private Sub cmdSearch_Click()
    Dim strStudentRef As String
    Dim strSearch As String
    
'Check txtSearch for Null value or Nill Entry first.

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
        
'Performs the search using value entered into txtSearch
'and evaluates this against values in customerno
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("customerno")
    DoCmd.FindRecord Me!txtSearch
        
    customerno.SetFocus
    strStudentRef = customerno.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found sets focus in customerno and shows msgbox
'and clears search control

    If strStudentRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        customerno.SetFocus
        txtSearch = ""
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtSearch.SetFocus
    End If
End Sub

Private Sub Command14_Click()
'''On Error GoTo Err_CmdAdd_Click
    '''Me.DataEntry = True
    '''Me.CmdFilter.Visible = False
    '''Me.CmdShowAll.Visible = True
   '''DoCmd.GoToRecord , , acNewRec
   If DCount("*", "Customer") = 0 Then
        Me.customerno = 1
   Else
    
        Me.customerno = DMax("Customerno", "Customer") + 1
        Me.customerName.Value = " "
   End If
   
'''Exit_CmdAdd_Click:
    '''Exit Sub

'''Err_CmdAdd_Click:
    '''MsgBox Err.Description
    '''Resume Exit_CmdAdd_Click
End Sub

Private Sub cmdDelete_Click()

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub

'''Private Sub cmdDelete_Click()

'''Dim x As Variant

'''x = MsgBox(" You are abut to delete " & Me.customerName & " from this table - proceed ? ", vbOKCancel)

'''If x = 1 Then

'''With myRS

'''.Delete
'''.MoveFirst


'''End With

'''End If

'''End Sub

[RobDog888]

How come your deleting by code instead of the delete button that you can add to the forms record navigator. It usually is next to
the new record button.

Also if your numbering your field you should use an Autonumber field if your not already.

[tony007]

How come your deleting by code instead of the delete button that you can add to the forms record navigator. It usually is next to
the new record button.

Also if your numbering your field you should use an Autonumber field if your not already.

thank u for u reply. Well i do not know how to add that delet button next to record locator. Could u tell me how i can add it. Furthermore is there a way to use add button in right side of record locater so that once it is clicked it auto incrment value of customerno ? I do not want to change the designe of my db to make a field autonumbe. if i delete a record should i re number all the records ?I be happy if u help me fix this problem.


note: more thing my tables are linked to sql server tables.

[RobDog888]

Its in the Form View toolbar when the form is displayed. There are buttons for new/delete/sort up/sort donw/etc.
Since your tables are linked to a SQL server you can not add a autonumber filed in access. You need to add it in SQL. The field of
concern could be changed to a Identity field data type an enter the seed value and increment value.

[tony007]

Its in the Form View toolbar when the form is displayed. There are buttons for new/delete/sort up/sort donw/etc.
Since your tables are linked to a SQL server you can not add a autonumber filed in access. You need to add it in SQL. The field of
concern could be changed to a Identity field data type an enter the seed value and increment value.

Thanks for u reply. is there a way to drag that button and place it inside the form so that user see it all the time and not looing for it.i be happy to get your help here.Furthermore, do not u think it is possible to auto incrment in code ? one more thing what will happend if i am using auto increment and i delete a record? does the system will auto number all the records or should i do some thing about it ?thank u and looking forward to your reply.Thanks