delete problem (1 Viewer)

[viperstingray]

well, new york would appear since it is now the 'last' facility for that client. I don't really care about which one would appear (whether it would be the first one, california; or the NEW last one, NY). As long as it shows ANY facility for that client, then I am happy.

I really appreciate your trouble

 

[RuralGuy]

"Last" is a relative term and based on some type of sort order. What need to be done to get back to New York? What if there are no more facilities for this client?

 

[viperstingray]

Well, last really means the last audit that was before the one that got deleted. I don't really care which one shows, as long as I get any audit that belongs to that client (in this example MICROSOFT).

If there was only ONE facility for microsoft and that was deleted (making the database free of any microsoft facilities) then return to the main menu.

 

[RuralGuy]

You really need to answer all of the questions asked.

What need to be done to get back to New York?

...then return to the main menu.

How would you accomplish that?

 

[viperstingray]

I will.

If you are looking through entires for a client, who has five different entries in my database (1 client, 5 facilities) and you realize that you need to get rid of one of those facilities then you would use the delete button. The delete button DOES work but when it gets rid of the facility in question, it will load up the next one in the tables (still belonging to the same client). This is not a problem until you try deleting the last facility for a specific client. If you are on record 5 of 5 for a client and you want to delte the fifth record, it will delete it but will also load a new 'audit info' form that will accept new entries. I DO NOT WANT THIS TO HAPPEN. What I want is that when the fifth record for a client is deleted, the fourth record should be displayed...not a blank form

IF there is only ONE facility for a client (microsoft) then I would want to program the delete button in such a way that it would delete the facility in question and then open the main form...not a blank form that would accept entires.

 

[RuralGuy]

I'm sorry but without some close proximation of your mdb I can be of no further assistance.

 

[viperstingray]

well, I am off work now so if I do not reply do not take it personally. I will try to check the forums from home, otherwise, I will be back tomorrow morning.

THANKS

 

[KeithIT]

Okay... sorry I didn't reply for a few days. My computer needed a new motherboard and Dell was nice enough to send one out, but it didn't get here soon enough.

It sounds like you want to count the number of records returned in your recordset, determine the count number of the record you are going to delete, and then do one of two things depending on the answer to the above two questions.

If the record you are deleting is the last record, than after you requery the form you want to move to the new last record (if you deleted record 5 then you should move to record 4). If the record you are deleting is the only record then you want to close the form and move to the main form.

I know you are going to hate me for saying this, but search this forum for the way to do the above logic steps, they are all explained in various places here.

Post back if you have more questions about the logic or if you can't find the solution to one of the steps on the forum

 

[viperstingray]

Hey BUDDY,

Don't worry...no need to be apologetic. You have done enough for me to last a lifetime. Anyways, any ideas on what the search should contain. I have been on this forum for days now just solving this one SIMPLE problem.

Did you look at my code, did you find anything in it that needs change. PLEASE HELP

 

[KeithIT]

Wow, give me a little bit more time to look through it, but preliminarily:

You seem to have the basic logic down, it appears to be your implementation that is causing you problems.

For starters, you should really try to use delete queries and select queries rather than cmdRecordDelete. Search for "delete record query" and see what comes up, some of these posts should definately be helpful in learning how to set up the queries properly and call them from VBA.

Next, you never requery your form. If you dont requery your form after you update the information in a drastic way (i.e.: delete a record or move records around) your form will not reflect those changes. The only time a form dynamically updates information is if you change the information in the record, not change the recordset itself.

Moving to the last record is a simple task. Set up a recordsetclone of the requeried form, use the MoveLast command, then set your bookmarks equal. This ensures that your bookmarked record is the last record in the set.

In terms of opening the main form if you delete the only record in the recordset, you can either choose to do this when the RecordCount = 1, or when your BOF and EOF tests are both True, whichever you feel most comfortable with.

I think that addresses most of the questions you asked here, let me know if you need more information on anything, or if you need more help with your code or implementation.

 

[viperstingray]

well, there is a little problem with your last post. My database is query free!!! I know, I know, its not right but I just saw no need to use one (i got hired part-way after the database was under construction so I didn't want to change and start over again).

I did manage to get it to delete and not show an empty form but there is a problem. If i select record # 40 (assume that client is represented by records 35,36,37,38,39,40) then it will delete record 39 and display record 40. It just needs some refinements
here is the code, see if you notice anything

---code---
Private Sub command271_Click()
On Error GoTo Err_cmdDelete_Click
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[client name]=" & "'" & Me![CLIENT NAME] & "'"
'stDocName = "Audit Info"
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to delete this Audit and its associated measures?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
If Me.CurrentRecord = lngCount Then
DoCmd.OpenForm "audit info", , , stLinkCriteria
'stDocName = "Audit Info"
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Else
If Me.CurrentRecord <> lngCount Then
DoCmd.OpenForm "audit info", , , stLinkCriteria
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If

CLIENT_NAME.SetFocus

End If
Exit_command271_Click:
DoCmd.SetWarnings True
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_command271_Click
End If
End Sub
---code---

 

[The_Doc_Man]

Whenever you are working on records underlying a form and you delete a record, you might need to do a .requery of the underlying records. (In form-based code, use the Me.Requery method.) There is also the issue of using the .recordsetclone property to find the "new" last record and then selecting it for the form.