Code needed to delete and update records

[coxmd410]

I have an Access database that has one table containing employee information. I have created Forms for easy user-interface. I am having problems with one of my forms.

When looking at my main menu (a form) you see employee names. You can select a name and click an "edit/delete" button that opens a pop-up form showing all the information for that employee.

I have a button on that pop-up form to delete the employee you are viewing. The problem is my code is deleting all records in the table, not just the record being viewed.

Here is my code:

Private Sub deleteRecords_Click()

Dim db as Database
Set db = CurrentDb
db.Execute "Delete * From employees;"

End Sub

Also, could someone please explain what Dim db and Set db do? I just copied them from online? Thanks!

 

[AD_Taylor]

As far as I can tell this what you are doing:

Dim db As Database - creates a variable to hold an Access Database
Set db = CurrentDb - assigns the current database to that variable. Basically you now have a reference to the database you're working on

db.Execute - execute a SQL statement on that database

The last line is where you're going wrong in my opinion. It needs to have a where clause something like:

db.Execute "Delete * From employees Where employeeId = " & txtBoxId.Value & ";"

employeeId = the field name in your database holding the employee id
txtboxId = a text box holding the currently selected employee id number

Not sure about this as I don't use Access a lot but thats what it look like
HTH!

 

[coxmd410]

I ran this and it gave me error 424. No Object. Any idea why?

As far as I can tell this what you are doing:

Dim db As Database - creates a variable to hold an Access Database
Set db = CurrentDb - assigns the current database to that variable. Basically you now have a reference to the database you're working on

db.Execute - execute a SQL statement on that database

The last line is where you're going wrong in my opinion. It needs to have a where clause something like:

db.Execute "Delete * From employees Where employeeId = " & txtBoxId.Value & ";"

employeeId = the field name in your database holding the employee id
txtboxId = a text box holding the currently selected employee id number

Not sure about this as I don't use Access a lot but thats what it look like
HTH!

 

[AD_Taylor]

So you ran this?

[COLOR=#333333]Private Sub deleteRecords_Click()[/COLOR]

[COLOR=#333333]Dim db as Database[/COLOR]
[COLOR=#333333]Set db = CurrentDb[/COLOR]
[COLOR=#333333]db.Execute "Delete * From employees [/COLOR]Where employeeId = " & txtBoxId.Value & ";"

[COLOR=#333333]End Sub[/COLOR]

Try running the one below instead. What line does it error on?

[COLOR=#333333]Private Sub deleteRecords_Click()[/COLOR]

[COLOR=#333333]Dim db as Database[/COLOR]
[COLOR=#333333]Set db = CurrentDb
[/COLOR]
Debug.Print txtBoxId.Value
[COLOR=#333333]db.Execute "Delete * From employees [/COLOR]Where employeeId = " & txtBoxId.Value & ";"

[COLOR=#333333]End Sub[/COLOR]

Hopefully this should narrow down the problem...

 

[coxmd410]

That worked, thanks!

I only have one last question. When looking at the form, the user sees all the employee information listed. I want to give the user the ability to type over what is currently showing and click a "save" button that will update the employee table with all the information they typed in.

 

[xenou]

Not sure if you're thinking of other strategies, but if you bind your form to the table, then edits are all handled automatically by Access. It is always possible to use the button wizard to add a save button (which is unnecessary but seems to make users feel good).

 

[coxmd410]

I will try that out and let you know how it works. Thanks!