Access97-- can I delete record with a macro? 1

[MrsTFB]

I have an Access database that I keep a form called switchboard. The switchboard allows a user to perform certain actions, without knowing very much about Access.

I want to be able to allow them to delete a record from the switchboard. I have the query to select the record, can I then delete it with a macro?

Thanks for any help with this.

Bsimm GO TITANS!!
:-D

 

[kathryn]

Sure. The query you use to select the record just needs to be changed to be a delete query, which will delete the record.

How are your users entering the criteria to decide what record to delete?

Kathryn

 

[MrsTFB]

Thanks for the reply,
It is an employee database. They will enter the employee number. I wouldn't mind a confirmation type messages, showing the name or something to verify that it is the correct record before actually deleting. Does this do that?

Thanks again,

Bsimm

 

[kathryn]

No, you have to program a message box that will give you the information.

I would probably get the Employee Number, run a select query to get the employee name, department, etc.. Then use a message box to tell the user that they are about to delete Joe Smith from Accounting. If the user clicks Yes to confirm, then run the delete query.

I'm not sure what your expertise level is, so yell if you need more detail.

Kathryn

 

[MrsTFB]

Expertise level a little low, I'll try. I've done a message box, but it always only has OK. How do I get the yes on the message box.

I've programmed some VBA in Excel but only used the Macro screen in Access. Do I need to go into the VBA?

Thanks,
Bsimm

 

[kathryn]

Yes, you are going to have to use VBA. Unfortunately,
Access does not have a macro recorder like Excel and Word do, so you have to start from scratch. In the below explanation, the underlined phrases are good help topics to check out.

You will probably want a form with a textbox to have the user enter the employee number and then a command button that the user can click to run the code. The command button would probaly have a caption of "Delete Record".

As far as the message box buttons go, the default is to just show the OK button, but in the code, if you set the buttons parameter of the msgbox function to VbOKCancel, you will see both an OK and Cancel button.

For the two queries, we will use QueryDefs. Design the select query, call it qrySelectRecord, and delete query, call it qryDeleteRecord, as parameter queries; in the criteria line under employee number, type

[EmpNumber].

This is a rough idea of what your code will look like. I am calling the form "frm" and the textbox with employee number "txtbox" and the command button "cmdDelete"

Private Sub cmdDelete_Click()

dim qdfSelect as querydef
dim rstSelect as recordset
dim qdfDelete as querydef
dim strMsg as string
dim intReturn as integer

set qdfSelect = currentdb.querydefs("qryselectrecord&quot
qdfSelect.parameters("EmpNumber&quot = me!txtbox 'this passes the criteria to the query

set rstSelect = qdfSelect.openrecordset

strMsg= "You are about to delete the employee record for " & vbcrlf & rstSelect!EmployeeFirst & " " & rstSelect!EmployeeLast & "." & vbcrlf & "Do you really want to do that?"

intReturn = msgbox strMsg,vbOKCancel

if intReturn = vbOK then

set qdfDelete = currentdb.QueryDefs("qryDeleteRecord&quot
qdfDelete.parameters("EmpNumber&quot=me!txtbox
qdfdelete.Execute

End if


That should get you started!

Kathryn

 

[MrsTFB]

THANK YOU!!
Read all my posts please, LOL

I vote you Tipmaster of the Year. I appreciate you taking so much time on me.

I'll let you know how it turns out.

Bsimm GO TITANS!

 

[kathryn]

Does the GO TITANS refer to the Tennesse Titans? If it does, I hope that you are not also a Tennesse Vols fan, because I have to warn you that I am a Gator fan, so that code will magically turn your computer Gator colors and turn a Gator screensaver on!

P.S. I appreciate the Tipmaster vote, if you really did vote one. I have one mug from Tek-Tips already from being Tipmaster of the week, and I'm going for a full set1

Kathryn

 

[MrsTFB]

I'm getting an expected end of statement error on this line

intReturn = msgbox strMsg,vbokcancel

Any ideas?

I really did vote one.

Tennessee Titans, not much into college football so I hope I don't have gators all over my screen because now I have Titan fireballs, Love my TITANS!
Thanks for your help.

Bsimm
Beth really
B-)

 

[kathryn]

Try

intReturn = msgbox(strMsg, vbokcancel)





Kathryn

 

[MrsTFB]

Hey Kathryn,
No action when I click the button. How should I have put the command button on the form. Went through what I think is the only way, and had to assign it one of the choices through the wizard I guess. I did that, and then changed the type and name and everything to match the cmdDelete. No action at all when I click it.

Any ideas,
Beth

I'm sorry to be trouble!

 

[kathryn]

OK, no problem. Since you are going to be putting your own code behind the button, you shouldn't use the wizard. Create a new button, but click on the "wand" button on the toolbox to turn off the wizard. Name the button by right clicking on the button in design view and choosing properties. Click the All tab and name the button something OTHER than the name you gave the button created with the wizard.

Now click on the event tab and click in the On Click property. A drop down arrow will appear. Choose [event procedure] from the dropdown and then click on the button to the right of the dropdown with the three periods (...) on it.

Now you should be in the cmdDelete_Click code section (or whatever you called your button. Cut and paste the good code from the other button into this procedure. You should be able to find the code by scrolling up and down.

Let me know how it goes.

Kathryn

 

[MrsTFB]

Here's my code: The red line is giving me the error message" Item not found in this collection." Did I type something wrong? I changed the names to match my database.

Thank you so much for helping me!

Private Sub CmdDeleteEmp_Click()


Dim qdfSelect As QueryDef
Dim rstselect As Recordset
Dim qdfdelete As QueryDef
Dim strMsg As String
Dim intReturn As Integer

Set qdfSelect = CurrentDb.QueryDefs("qrySelectRecord&quot
qdfSelect.Parameters("EmpNum&quot = Me!Txtbox

Set rstselect = qdfSelect.OpenRecordset

strMsg = "You are about to delete the employee record for " & vbCrLf & rstselect!Fname & " " & rstselect!Lname & "." & vbCrLf & "Is this correct?"

intReturn = MsgBox(strMsg, vbYesNo)

If intReturn = vbOK Then
Set qdfdelete = CurrentDb.QueryDefs("qryDeleteRecord&quot
qdfdelete.Parameters("EmpNum&quot = Me!Txtbox
qdfdelete.Execute
End If


Exit_cmdDeleteEmp_Click:
Exit Sub
End Sub

 

[kathryn]

My bad. The syntax is

qdfSelect.Parameters![EmpNum] = Me!Txtbox

Make sure to change the syntax in the parameter line for qdfDelete.





Kathryn

 

[MrsTFB]

Kathryn,

Changed the code, but still getting the same error message. I made the Record Source for the frm form the qrySelectRecord. I wasn't sure if that was messing it up, so I took it out, no change.

Any ideas?
Thanks,
Beth

 

[kathryn]

Ok, when you open up qrySelectRecord and look at the Employee Number field, is there a parameter in the criteria row that looks EXACTLY like this:

[EmpNum]


If so, run the query and you should be prompted with a pop-up box with EmpNum as the only text message. Enter a valid Employee Number and let me know what happened.

Kathryn

 

[MrsTFB]

hey,
It looks exactly like that, I even copied and pasted it from your post, but, NO it does not ask me for an employee number.

Beth

 

[MrsTFB]

I changed the brackets to say [Enter EmpNum], and it does ask me for Emp number. but still same error message when I run it from the form.

I'm so confused!

 

[kathryn]

I just learned something that I never knew. Is your field name EmpNum? The parameter criteria cannot be the same as a field name.

OK, did you change the code to read

qdfSelect.Parameters![Enter EmpNum] = Me!Txtbox

Kathryn

 

[MrsTFB]

I did change it in all areas in both queries.
It goes through all the steps, but does not delete the record. I'm thinking it has something to do with the first dim statement. When I hover over every other Dim it gives me a value (You know what I mean)

I'm not really sure. I'm going to also want this form to go away after the delete. Is this difficult.

You have helped me sooo much.

GO GATORS!
Beth