How to undo delete command in Datasheet view

[radek225]

When I had a subform (continous form) I had a code which check, that record was used in other form, if yes, then code not allowed to delete this record, if not, record was deleted. But now I have a datasheet form, and the question is, What should I do, to have the same results what I had in continous form.
Now it doesn't matter that I past this code in "Before Del Confirm", "On delete", "After Del Confirm", this code doesn't work in this view of form. Always if I click (right button) on record, then i choose "delete record" MS Access delete my record without checking that was used in other form or not.

If Not IsNull(DLookup("Id_Gora_Zlecenia.Value", "TblDolZlecenia", "Id_Gora_Zlecenia.Value=" & ID_gora_zlecenia)) Then
    MsgBox "You can't delete this record because it is used in another form"
    Exit Sub
  End If
  
  DeleteRecord Me
End Sub

DeleteRecord:

Public Function DeleteRecord(ByRef f As Form) As Boolean
DeleteRecord = False
On Error Resume Next
DoCmd.SetWarnings False
  If Not f.NewRecord Then
    DoCmd.RunCommand acCmdDeleteRecord
    
  ElseIf f.NewRecord And Not f.Dirty Then
    DoCmd.Beep
    
  ElseIf f.NewRecord And f.Dirty Then
    DoCmd.RunCommand acCmdUndo
  End If
DoCmd.SetWarnings True
  If Err.number = 0 Then
    DeleteRecord = True
  Else
    MsgBox Err.number & " - " & Err.Description
  End If
End Function

 

[Mihail]

Missinglinq has a very useful technique.
See posts #6 and #7 and #8 in this thread.

 

[radek225]

The problem is. I don't want to hide some records. I need see all of them. But I should have some "support" from code when I try to delete some record. Now I have to "in hand" check that record is use in another form or not.

 

[Pat Hartman]

Your syntax is incorrect.

If Not IsNull(DLookup("Id_Gora_Zlecenia", "TblDolZlecenia", "Id_Gora_Zlecenia=" & Me.ID_gora_zlecenia)) Then

".Value" refers to a property of a control. You were using it incorrectly.

Also, when referring to controls in the class module of a form or report, use "Me." to qualify the names so the interpreter "knows" where the variables are defined. Just using Id_Gora_Zlecenia doesn't give Access any help. It needs to search through all loaded libraries until it comes to wherever the variable is defined. If you use Me.Id_Gora_Zlecenia, Access knows immediately where to find the definition.

And finally, why would you want to write code when you can create a relationship and click the enforce RI box and have the database engine manage this for you?

 

[radek225]

Ok, thanks for an advice. I can't do relations because there's no relation between (Access not allowed to do relations between these two forms). So still it's not working:/

 

[jdraw]

Ok, thanks for an advice. I can't do relations because there's no relation between (Access not allowed to do relations between these two forms). So still it's not working:/

Please explain what you mean by this.
Why can you not set up RI as pat suggested??
Suggest you research referential integrity-- here's a start
http://www.webopedia.com/TERM/R/referential_integrity.html

 

[radek225]

Why can you not set up RI as pat suggested??

It's about two forms "frmGoraZlecenia" and "frmDolZlecenia". These two forms are completely different (except one column). So MS Access not allowed to do referential integrity rules - that's the point.
In frmDolZlecenia I have a column "Id_gora_zlecenia" - multidata, where I can take data from frmGoraZlecenia "ID_gora_zlecenia"
E.G
frmGoraZlecenia in "Id_gora_zlecenia" field, I have a records like this
1)x
2)y
3)z
So in frmDolZlecenia e.g. I can make data like this:
1) x; z
2) x;y;z
3) z

 

[Pat Hartman]

RI is not enforced between forms, it is enforced between tables.

You seem to be using a multi-valued field. That is a completely different problem and my suggestion is to get rid of the multi-valued field and go with a standard 1-many relationship that you manage yourself. You can't use any standard SQL on multi-value fields and I don't know if the domain functions work on them either.

 

[radek225]

RI is not enforced between forms, it is enforced between tables.

Yes I know that.

I must have multi-valued field, because there isn't any possibility. Please see error what I attached. This error showed when i tried to do RI.

 

[Mihail]

I must have multi-valued field, because there isn't any possibility

Hm. There are more than 40 years from the first database.
In all this time the management has been done without multi-valued fields.
Have you take a look at Pat's reputation ? You should. If Pat give me an advice, I think 3 times then I stop to think and I start to do how he said. Of course that he will be in trouble because will should answer to all my questions .

 

[radek225]

I'm not wrote my message in purpose to tell Pat that he's wrong.
I just try to solve my problem.
About multi-valued field:
I have a two subforms (what I mentioned).

frmGoraZlecenia - describe part of order. e.g. If client want to order, folder and business cards. Then I can have 3 part of order (three records, first field is id_gora_zlecenia)

1)"cover" (part of folder), format_job_x, format_job_y, coulors, description, paper_type, volume,
2) "content" (part of folder). format_job_x, format_job_y, coulors, description, paper_type, volume
3) "business cards", format_job_x, format_job_y, coulros, description, paper_type, volume. So that's how my frmGoraZlecenia looks like.

When I have order, then I must print it. But paper size is larger than size of jobs, so sometime I can print 2 or more jobs in one paper. Now I have to choose which one of them could be print together. - frmDolZlecenia (about Details of printing technology). e.g. This order in frmDolZlecenia can looks like this (first field is id_gora_zlecenia):

1) "cover; business cards", format_paper, number_of_boxes, number_of_sheet, printing_technology
2) "content" , format_paper, number_of_boxes, number_of_sheet, printing_technology

So is there any way to do it without multi-valued field (id_gora_zlecenia in frmDolZlecenia)? (I have described this process in a simplified)

 

[JHB]

Of course that he will be in trouble because will should answer to all my questions .

Hmmm - why do you think Pat is a male? Take a look here.
http://www.linkedin.com/pub/patricia-hartman/55/696/647

 

[Mihail]

Sorry. SHE. But still in trouble.

 

[Pat Hartman]

@radek, the error message is very clear. You have data in the many-side table that is orphaned. You need to delete the data or fix the foreign key values to point to a valid parent record.
@JHB, that LinkedIn profile IS me but how did you figure that out? It is for my real estate venture. I do have one for my IT ventures also.

And for the rest of you, bridge is my passion. It is a game you can learn the mechanics of in an evening or two but will take you a lifetime to master. It requires logic and people skills. Math isn't required except for basic counting to 13, but you do need to remember a few key ratios when you are playing a hand such as - in the absence of an actual count, an odd number of outstanding cards will break as evenly as possible and an even number will break oddly. That means if between you and the dummy (the partner of the declarer always faces his hand on the table when play begins), you can see 8 spades, the remaining 5 are most likely divided 3 in one hand and 2 in the other but if you can only see 7 spades the remaining 6 are more likely divided 4/2 than 3/3. Other divisions such as 5/1 and 6/0 are much less likely (although the bidding may indicate otherwise that will impact your assessment of the true odds). It isn't necessary to remember or calculate the exact odds unless you are playing at the expert level and are trying to come up with the best line of play. For the average player, just knowing the "rule" I described will go a long way toward making you a deadly opponent.

 

[JHB]

@JHB, that LinkedIn profile IS me but how did you figure that out? It is for my real estate venture. I do have one for my IT ventures also.

It wasn't so hard to figure it out, with the short description of your self.
I don't think the live more as one person in Stratford, Connecticut, with that name and with a passion of (database) programming.
I just read the profile for the IT, (hmm - when do you sleep? ).
The short description:
Pat Hartman has been designing and developing software applications since before she could vote. Currently she is working on a product that will help investors analyze investment properties. The product will support what if scenarios and allow for multi-property comparisons. It goes beyond the more common spreadsheets which can only analyze a single property at a time.

 

[radek225]

The problem is multi-valued field, if you have, you can't do RI.
So Is there any possibility to achieve what I asked before?