User deletes records

S

Sadie Hewgill

Registered User.
Local time
Yesterday, 20:56
Joined
Oct 18, 2010
Messages
52
How can a user delete a record? I want to restrict user access to the form level, but htey need to be able to edit and delete records I tried putting a command button on my form that could delete the record, but this failed to update my tables. What's worse, it only deleted the record from my forms. Like when I click through the records in form view, the records I deleted don't exist, but when I look in my tables or query the records, then they do. There's some kind of inconsistency here, I just don't know how to fix it.
Thanks,
Sadie
 
You would need to create a Delete Query that uses the current Record ID as it's criteria, and run that on your Button Click event.
 
Ok, thanks. Do you know if there's a way to reset autonumbered fields to start back at one again? I am going to be deleting all the test data I made up while designing, and need the autonumbered ID field to reflect real numbers
 
Also, there is still inconsistencies between the records displayed in the form view and the records in the tables. For example, I have a duplicate record that appears when I scroll through the form (it is both record 3/10 and 4/10), but when I change any of the data in the fields both "records" are updated, and it only affects one record in the table since there is only one instance of it in table form. I only have fake data for now, but I need to figure out how to fix this before I start putting real stuff in. Any ideas?
 
Sadie Hewgill said:
Ok, thanks. Do you know if there's a way to reset autonumbered fields to start back at one again? I am going to be deleting all the test data I made up while designing, and need the autonumbered ID field to reflect real numbers
Click to expand...

If you are using the Autonumber to do anything other than provide a unique primary key, and it sound's as if you may be, Don't. Search this forum on the subject and consider using Dmax() + 1 to generate your own Auto incrementing number.

The attached sample uses this principal to create a sequential number that starts from one each day for each of any number of offices (two in the sample).
 

Attachments

Sadie Hewgill said:
Also, there is still inconsistencies between the records displayed in the form view and the records in the tables. For example, I have a duplicate record that appears when I scroll through the form (it is both record 3/10 and 4/10), but when I change any of the data in the fields both "records" are updated, and it only affects one record in the table since there is only one instance of it in table form. I only have fake data for now, but I need to figure out how to fix this before I start putting real stuff in. Any ideas?
Click to expand...
What is the Record Source for your form? is it a query or a table?
 
I am having some technical difficulties uploading my database. Can I send it to you in email form?
 
I looked into using Dmax like you suggested earlier. The compiler doesn't like the line: LMax = DMax("Part Number", "Part Numbers") + 1. I think it's just the syntax, but I am new at VBA. "Part Number" is the field in my table "Part Numbers" is the table.
Here's the code:

Private Sub Part_Number_Enter()
Dim LMax As Integer
If IsNull(Me.Part_Number) Then

LMax = DMax("Part Number", "Part Numbers") + 1
Me![Part Number] = LMax

End If
End Sub

Thank you!
 
VBA isen't too fond of spaces i objectnames, enclose these in []

Code: 
Private Sub Part_Number_Enter()
Dim LMax As Integer
If IsNull(Me.Part_Number) Then
 
LMax = DMax("[Part Number]", "[Part Numbers]") + 1
Me![Part Number] = LMax
 
End If
End Sub

Also Lmax do you expect it to be bigger than 32000, if so change it to Long instead.

JR
 
Hey thanks a lot, that works much better now!
The form that I have enters the part number into the child table, and my autonumbering used to come from the parent, so there was always a record existing in the parent table. Now I need some way to create the number that I get from the Dmax +1 function in the parent table first. Some line like : Store LMax in [Part Number].[Part Numbers] Except I don't know how to code that.
 
Code: 
Private Sub Part_Number_Enter()
Dim LMax As Integer
If IsNull(Me.Part_Number) Then
 
LMax = DMax("[Part Number]", "[Part Numbers]") + 1
Me![Part Number] = LMax
 
End If
' Update Part Numbers with new PartNumber
CurrentDb.Execute " UPDATE [Part Numbers] SET [Part Number]=" & LMax, dbFailOnError

End Sub

JR
 
hmm, on second thought I think autonumbers will suit me better here, but thank you for the info on Dmax
 
You must log in or register to reply here.

Similar threads

T
Cancel,Save and Delete buttons for Order form
Replies
11
Views
467
Pat Hartman
P
C
Solved No Current Record on Access Close
Replies
11
Views
610
Pat Hartman
P
J
Unable to delete record due to record lock
Replies
6
Views
321
The_Doc_Man
The_Doc_Man
J
Record is deleted
Replies
7
Views
401
Mike Krailo
Mike Krailo
R
Delete record in dependent cascading combo box
Replies
4
Views
177
RFreund
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom