Code Problem

[TSSTechie]

Howdy,

Wonder if somebody would be so kind as to have a quick look at this for me :

DoCmd.SetWarnings False
check = MsgBox("This will permanently delete the currently selected member of staff and all of their personal training records. Are you sure you want to do this ?", vbYesNo + vbDefaultButton2 + vbExclamation, "Warning!!!")
If check = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "Test"
Me!ID_Name = Null
Me!ID_Name.Requery
End If
If check = vbNo Then
MsgBox "Delete Operation Cancelled", vbOKOnly + vbInformation, "Information"
End If

It's the bit in bold that i'm having trouble with in that it doesn't appear to run. I put the message box in just to test it but it doesn't show. Equally, the combo box doesn't clear or requery.

What have I done wrong ?

Thanks for any help

TSSTechie

May The Force Be With You

 

[willir]

Do you really have to set ID_Name to null? Does something bad happend if you don't touch the ID_Name after the deletion?

Richard

 

[Spyrios]

It works just fine for me.

If you are hitting "NO" to the first message box it exits out and goes to the next if statement. But your code is right both ways. If "yes" it pops up "Test", then clears out the combo box. I'm not sure about the requery, I just had a value list in the combo box but that looks fine too. If it's a "no" answer then it pops up "operation canceled"


Hope that helps, even if it wasn't much help.


Michael

 

[TSSTechie]

Howdy folks

Thanks very much for the replys.

Willir : Nothing bad happens if I don't set ID_Name to Null, that's just to make the form a bit nicer really however, I really need it to requery since, when it deletes a record it doesn't clear the entry in the combo box, it just changes it to "#Deleted" until I exit the form and go back in. Then it's gone. I was trying to avoid it showing at all by requerying the combo imediately after the delete.

Spyrios : I can't understand it. When I click 'Yes' it deletes the record but nothing further, no 'null'ing of combo box, no requerying of combo box and no message box. Any ideas why this bit would work for you and not me ??

Thanks again

TSSTechie

May The Force Be With You

 

[TheAceMan1]

How are ya TSSTechie . . . . .

First, on clearing the combobox. A combobox has two sections . . . the [purple]TextBox portion[/purple] and the [purple]DropDown list[/purple]. Yor trying to clear the [blue]TextBox portion![/blue] Unfortunately the [blue]TextBox porton has to have the focus[/blue] to do so and you can't use the [blue]Null Value[/blue]. So to clear the Text Portion of the combobox it should be:

[blue]   Me!ID_Name.SetFocus
   Me!ID_Name.Text=""[/blue]

Your [blue]Requery[/blue] line [purple]Me!ID_Name.Requery[/purple] is correct.

Now, in the code you say doesn't work, you have (I've hilighted the actions in [green]green[/green]):

[blue]    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 [green][b]'Delete Record[/b][/green]
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 [green][b]'Paste Append[/b][/green][/blue]

If your using Access 2K or higher, you should be using the [purple]DoCmd.RunCommand Method[/purple] for these items.

Now, for [green]Paste Append[/green] to work, you have to have something in the [blue]Copy Buffer![/blue]. At the time of execution, it doesn't appear so.

However, [green]Delete Record[/green] should work ([purple]there are dependancies[/purple]) and if the recordset of the combobox is based on the same table as the record your deleting, it should windup unlisted in the combobox after requery. So this prompts a question:

Is the recordsource of the combobox based on the same table as the record your deleting?

[blue]Now, for all to see, the proper format of your code is as follows:[/blue]

[blue]   If check = vbYes Then
      DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
      DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
      MsgBox "Test"
      Me!ID_Name = Null
      Me!ID_Name.Requery
   End If
   
   If check = vbNo Then
    MsgBox "Delete Operation Cancelled", vbOKOnly + vbInformation, "Information"
   End If[/blue]

It should as below:

[blue]   If check = vbYes Then
      [purple][b]DoCmd.RunCommand acCmdDeleteRecord
      DoCmd.RunCommand acCmdPasteAppend[/b][/purple]
      MsgBox "Test"
      [purple][b]Me!ID_Name.SetFocus[/b][/purple]
      Me!ID_Name = [purple][b]""[/b][/purple]
      Me!ID_Name.Requery
      End If
   Else
    MsgBox "Delete Operation Cancelled", vbOKOnly + vbInformation, "Information"
   End If
[/blue]

I have to believe, you know you have to select [purple]Yes[/purple] in the first MsgBox for the code you've highlighted to run!

See Ya! . . . . . .

 

[TSSTechie]

Howdy,

Cheers for the help. Unfortunately my problem still exists. I have substituted my

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

for your

DoCmd.RunCommand acCmdDeleteRecord
DoCmd.RunCommand acCmdPasteAppend

(I let the Access wizard make me a delete record button which is where the above code came from. I then just added to it)
I have found that, if I comment out these 2 lines altogether, the rest of the code works perfectly (obviously nothing gets deleted). The combo is requeried, 'null'ed and I get the message "Test" but when the 2 lines are active, the rest stops working.

Any ideas ??

Thanks again

TSSTechie

May The Force Be With You

 

[willir]

TSSTechie

I find the...
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
...usually handles the "#Deleted" message.

8 = SelectRecord
6 = Delete


I have to assume that you have code running elsewhere that is not being refreshed.

But another approach would be to use...
[tt]
Me.Requery[/tt]
or[tt]
Me.Refresh[/tt]
... to requery / refresh the form.

...or[tt]
Me.YourComboBox.Requery[/tt]
...to re-populate the record source of a combo box that now has missing data.

These may be a cleaner solution than setting the value to null.

Richard

 

[TheAceMan1]

[blue]I find the...
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
...usually handles the "#Deleted" message.

8 = SelectRecord
6 = Delete[/blue]

[blue]willir is right![/blue] DoMenuItem refers to prior Access Version listing of the Edit Menu. I checked Access 97 and it is so. So as an eye opener, the numerical index is not the same for Access 2000.

Therefore following the correct commands, try:

[blue]   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdDeleteRecord[/blue]

All were doing here is being explicit instead of using the numeric index.

See Ya! . . . . . .

 

[TSSTechie]

Howdy,

I've narrowed the problem down a bit. I have found that the code for the delete button

Private Sub Delete_Staff_Click()
On Error GoTo Err_Delete_Staff_Click

    DoCmd.SetWarnings False
    check = MsgBox("This will permanently delete the currently selected member of staff and all of their personal training records. Are you sure you want to do this ?", vbYesNo + vbDefaultButton2 + vbExclamation, "Warning!!!")
    If check = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.RunCommand acCmdPasteAppend
    MsgBox "Staff Member Deleted"
    Me!ID_Name = Null
    Me!ID_Name.Requery
    Me!Surname.Visible = False
    Me!Forename.Visible = False
    Me!Employee_Number.Visible = False
    Me!Current_Position.Visible = False
    Me!Employment_Commencement_Date.Visible = False
    End If
    If check = vbNo Then
    MsgBox "Delete Operation Cancelled", vbOKOnly + vbInformation, "Information"
    End If

Exit_Delete_Staff_Click:
  Exit Sub
Err_Delete_Staff_Click:
If Err.Number <> 3021 Then
  MsgBox Err.Description
End If
Resume Exit_Delete_Staff_Click
    Exit Sub
    
End Sub

seems to be conflicting with the following piece of code that runs in the after update of the combo

Private Sub Name_AfterUpdate()
    Me.RecordsetClone.FindFirst "[ID Name] = """ & Me![ID_Name] & """"
    Me.Bookmark = Me.RecordsetClone.Bookmark
Me!Surname.Visible = True
Me!Forename.Visible = True
Me!Employee_Number.Visible = True
Me!Current_Position.Visible = True
Me!Employment_Commencement_Date.Visible = True
End Sub

If I take away the 'Event Procedure' from the After Update of the combo, the delete code works a treat but I need the code in the after update for the combo so when an employee is chosen, their details are shown.

Can anyone offer a solution ??

Thanks again

TSSTechie

May The Force Be With You

 

[willir]

TSSTechie

Your code looks like it should run. But there is one thing that is a cause of concern. You use Check but have not defined the variable locally. This suggests that you either defined the variable globally, or you have not set Option Explicit.

I suspect most programmers would want to use Option Explicit. This option means that you have to explicitly define each variable used in your code. Otherwise, Access will create the variable for you ... as data type varient. If Access will automatically define variables for you, why use Option Explicit? Because many of us make typing mistakess when we code -- Access would define a new variable every time we make a mistake -- creates chaos when trying to debug / figure out what is wrong with one's code.

Scroll to the very top of your module for the form...

Option Compare Database[COLOR=blue]
Option Explicit[/color]

Add Option Explicit to your code.

A minor tweak to your code...

Private Sub Delete_Staff_Click()
On Error GoTo Err_Delete_Staff_Click
[COLOR=green]
'Dim Check as Interger  'this is how to define a variable[/color]
Dim strMsg as String    'example of variable in use
[COLOR=green]
'Turn warning off [/color][COLOR=blue]
DoCmd.SetWarnings False[/color]
[COLOR=green]
'Assigning message to text string variable[/color][COLOR=blue]
strMsg[/color] = "This will permanently delete the currently selected member of staff and all of their personal training records. Are you sure you want to do this ?"
[COLOR=green]
'Instead of using Check, you can test for the result of
'the message box within If statement [/green]
If MsgBox(strMsg, vbYesNo + vbDefaultButton2 + vbExclamation, "Warning!!!") = vbOk Then
    [COLOR=red]'If check = vbYes Then  'delete[/color]
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.RunCommand acCmdPasteAppend
    MsgBox "Staff Member Deleted"
    Me!ID_Name = Null
    Me!ID_Name.Requery
    Me!Surname.Visible = False
    Me!Forename.Visible = False
    Me!Employee_Number.Visible = False
    Me!Current_Position.Visible = False
    Me!Employment_Commencement_Date.Visible = False
[COLOR=red]    'End If                'delete [/color][COLOR=green]
'You can use Else instead of two If statements[/color] [COLOR=blue]
Else[/color]
[COLOR=red]    'If check = vbNo Then  'delete [/color]
    MsgBox "Delete Operation Cancelled", vbOKOnly + vbInformation, "Information"
End If
[COLOR=green][b]
'Need to Turn warning back ON [/b][/color] [COLOR=blue]
DoCmd.SetWarnings True[/color]

Exit_Delete_Staff_Click:
  Exit Sub
Err_Delete_Staff_Click:
If Err.Number <> 3021 Then
  MsgBox Err.Description
End If
Resume Exit_Delete_Staff_Click
    Exit Sub
    
End Sub

...Moving on

seems to be conflicting with the following piece of code that runs in the after update of the combo

What is the conflict?

I do have one concern...[tt]
Me!ID_Name = Null
Me!ID_Name.Requery[/tt]

Here you are assigning Null (no value) as the name of them member. And then requerying the combo box. You might want to try the code without Me!ID_Name = Null

Richard

 

[TheAceMan1]

TSSTechie . . . . .

In your latest post you have:

[blue]    If check = vbYes Then
    [purple][b]DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.RunCommand acCmdPasteAppend[/b][/purple]
    MsgBox "Staff Member Deleted"[/blue]

I corrected this a few posts prior to:

[blue]   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdDeleteRecord[/blue]

This is the corect functionality of the Delete Button.

[blue]DoCmd.RunCommand acCmdPasteAppend[/blue] has nothing to do with deletion and may be helping the problem. I can't believe any results unless this is corrected . . . . .

I know your anxious to get this resolved, but a little [blue]Post Hypnosis[/blue] could have us all traveling down the wrong path . . . . Take Your Time!

See Ya! . . . . . .

 

[TSSTechie]

Howdy folks

Thanks ever so much for all this help. Sorry 'TheAceMan1' the commands you suggested I change look so similar, I must have missed that bit. I have changed them now.

Ok, so here's the code i'm now working with :

Private Sub Delete_Staff_Click()
On Error GoTo Err_Delete_Staff_Click
    
    DoCmd.SetWarnings False
    If MsgBox("This will permanently delete the currently selected member of staff and all of their personal training records. Are you sure you want to do this ?", vbYesNo + vbDefaultButton2 + vbExclamation, "Warning!!!") = vbYes Then
    Stop
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    MsgBox "Staff Member Deleted"
    Me!ID_Name = Null
    Me!ID_Name.Requery
    Me!Surname.Visible = False
    Me!Forename.Visible = False
    Me!Employee_Number.Visible = False
    Me!Current_Position.Visible = False
    Me!Employment_Commencement_Date.Visible = False
    Else
    MsgBox "Delete Operation Cancelled", vbOKOnly + vbInformation, "Information"
    End If
    
    DoCmd.SetWarnings True
    
Exit_Delete_Staff_Click:
Exit Sub
    
Err_Delete_Staff_Click:
If Err.Number <> 3021 Then
  MsgBox Err.Description
End If
Resume Exit_Delete_Staff_Click
    Exit Sub

End Sub

with the code in the 'AfterUpdate' event of the ID_Name combo being :

Private Sub Name_AfterUpdate()
    Me.RecordsetClone.FindFirst "[ID Name] = """ & Me![ID_Name] & """"
    Me.Bookmark = Me.RecordsetClone.Bookmark
Me!Surname.Visible = True
Me!Forename.Visible = True
Me!Employee_Number.Visible = True
Me!Current_Position.Visible = True
Me!Employment_Commencement_Date.Visible = True
End Sub

If I remove 'Event Procedure' from the 'AfterUpdate' property of the combo, the delete code works perfectly hence why I assume there is a conflict. I assume this is with the actual delete code itself (i.e. DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord) since, if I comment out those two lines the rest of the code runs like a dream, even with 'Event Procedure' in the 'AfterUpdate' of the combo.

I also picked up another trick from the web whereby one can use the 'Stop' command then press F8 to step through the code so I gave that a try (you may have noticed the stop in the above code).
When doing this it highlights the line

DoCmd.RunCommand acCmdSelectRecord

then it highlights the lines

If Err.Number <> 3021 Then
MsgBox Err.Description

I then get the following pop up message :
"The Command or action 'SelectRecord' isn't available now"
Then, as the code suggests, exits the sub.

Does anybody understand what is going on here because it's got me stumped

Thanks again for any advise

TSSTechie


May The Force Be With You

 

[dRahme]

Hi, don't know if it will help your problem or not, but you might try the following:


Private Sub Name_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID Name]) = '" & Me![ID_Name] & "'"
Me.Bookmark = rs.Bookmark

End Sub

 

[TSSTechie]

Howdy

Thanks for that. Unfortunately the problem still exists.

Thanks again

TSSTechie

May The Force Be With You

 

[KenReay]

Hi

Have you got this sorted yet?

I note the line:

"Private Sub Name_AfterUpdate()"

This implies to me that you have a control called Name, this is not a good idea, since .Name is a property and thus a reserved word in Access. Using it as a control or column name will cause problems

It may not be the cause of your problem, but personnaly I would chaneg it just to eliminate the possibility

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TSSTechie]

Howdy,

Thanks. I have changed that. It hasn't solved the problem but I see where you are coming from with that

Thanks again

TSSTechie

May The Force Be With You

 

[RoyVidar]

The last question is probably the easyest to answer, lot of the .RunCommand actions available thru the DoCmd object are not available when stepping thru the code, but only when running it (err 2046?).

From what you're saying, it seems the delete code is invoking the after update of the combo, which seems a bit strange...

I'd take it a step further than KenReay's suggestion, rename all controls having the same name as the field it's bound to - most naming conventions use some means of identifying which controltype or datatype one are dealing with, txt for text controls, cbo for combos, lst for listboxes or similar - which tends to help.

Next, I've never been a fan of neither the menuitem thingies nor the runcommand actions for dealing with data, I'd go for some straight query stuff:

[tt]dim strSql as string
strSql="delete * from mytable where id=" & me("txtID").value
currentdb.execute strSql
me.requery[/tt]

Just wondering, your combo, is it by any chance bound? It shouldn't be if you're also using it as means of navigation.

Another thing, from your message, it seems you're not only deleting the master record (member of staff), but also child records ("...all of their personal training records"), not sure how this equate in this scenario, but this would imply having the cascade delete thingie checked. My approach to such, to have some bit of control, is deleting the child records first, then the master record thru sql, and not rely on menuitem/docmd thingies.

If none of the suggestions in this thread does solve your challenge, I'd start from scratch with this, rethinking the approach, building it incremental... - cause to my eyes, there's no "obvious error" to point out that's causing the challenge.

But to go back to the initial question of why the message box and setting the combo to Null didn't work, is probably related to err.number 3021 (You cannot add or change a record because a related record is required to the table) - I see you trap for that error, not showing any message, so I think that the menuitem/docmd thiniges performs something altering the record in a way that breaks integrity rule settings, then just skips the assigning of null/msgbox etc jumping directly to the error handler and exit. So the code is probably not working because of manually entered changes (breaking integrity) or altered thru some other code (navigation combo which is also bound?...) - ie not because of errors within the posted code.

To get an accurate account of where the error occurs, comment the line starting with "on error..."...

Roy-Vidar

 

[TSSTechie]

Howdy

I replaced the delete code with the SQL you provided (changing the appropriate field names) however I get the following message when it executes :
"Syntax Error (missing operator) in query expression 'Name=Test'."
Test is the name of the member of staff I was trying to delete. Any ideas why this happens ?

The combo is Unbound but it does get it's data from the table from which the record is being deleted. Is this a problem ?

I don't think it's anything to do with child records because I tried it with no training records assosiated with Test and also with Enforce Referencial Integrity turned off. This made no difference.

I tried commenting out the "On error ..." line like you suggested and got the follwing message :
"Run-time error '3021':
No current record."

Is there anything else I can try before re-thinking the methods i'm using ?

Thanks again for all the help.

TSSTechie

May The Force Be With You

 

[RoyVidar]

Query stuff, 2 things - "Name" see KenReays reply, you might get around it using [brackets] around it, and text need single quotes...

[tt]... id='" & me("txtID").value & "'"[/tt]

No current record, means there isn't a current record either to delete, or perform anything else with - without knowing which line it's hard/impossible to say what the problem is...

Roy-Vidar