Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Cambridgeshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Deletion from Form (97 SR-2)

    Hi

    I have 2 sets of linked databases, one for live data and one I use as a test system. Both have been split such that the tables reside in one db and the forms, reports etc. in another (effectively giving me a total of 4 dbs).

    The problem that I have is only apparent in the 'live' db rather than the 'test' db (typical!). Basically, when I delete a record from the test system, I get the '#deleted' string appearing on the form itself (as I would expect) and the system will let me navigate to a new record as normal. However, when I do the same in the live system, the underlying table shows the record as deleted, but I do not get the '#deleted' string in the fields, nor will the form let me navigate to a new record - if I take off the record locking. If I try to take off the record lock, it will not delete it at all! The (correctly functioning) test system has the edited record locked and will delete as above.

    As far as I can see, the live and test dbs appear to be the same - have used the documenter function and there are no apparent differences.

    Can anyone help??

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Deletion from Form (97 SR-2)

    There's no reason for the form to display that in either database. *How* are you deleting a record?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Cambridgeshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Deletion from Form (97 SR-2)

    Hello Charlotte !

    I use VB coding...

    MyQry = "DELETE * FROM tblTPI_Uncollected_Archive "
    MyQry = MyQry & "WHERE invoice_no = '" & InvoiceNum & "'
    Set MyQdf = CurrentDb.CreateQueryDef("", MyQuery)
    MyQdf.Execute
    If MyQdf.RecordsAffected = 0 Then
    Msgbox " failed for invoice " & MyInvoice
    End If

    If the record lock on the form is set to 'no lock', the deletion in fact takes place, but I cannot refresh nor requery the form. Access keeps telling me that the record is deleted. Also, I cannot navigate through the various records and get the same error message. I cannot close the form either.

    if the record lock on the form is set to 'edited record', the delete fails as the record is locked by the form.

    This does not happen in my 'test' system which is a mirror image as far as queries, forms, reports... are concerned. The record is successfully deleted, and the data is requeried without any problems nor error messages. That the form record lock is set to either 'no lock' or 'edited record' does not make any difference. The deletion succeeds in both cases.

    I compared the tables in both 'data' databases and I could not see any obvious difference. There must be differences, somewhere, but where ?? I have been struggling with this problem since yesterday morning and am getting quite frustrated with Access. Several colleagues had a go too, but to no avail...

    Thanks for your help

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Record Deletion from Form (97 SR-2)

    Have you tried a repair and compact of both databases (frontend-queries, forms,etc and backend-data).

    Failing that try setting a new database for each of them and import the objects and see if that works.

    HTH
    Pat

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Cambridgeshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Deletion from Form (97 SR-2)

    Patt,

    Thanks for your suggestions. I have tried both but, unfortunately, the problem is still there.

    I am getting round the problem by closing the form, then deleting the record and reopening the form. It is untidy, but at least it works.

    If you have any other suggestions, I would be interested as I want to get to the bottom of this mystery.

    Many thanks to you and Charlotte for your replies.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Record Deletion from Form (97 SR-2)

    If your databases don't have sensitive data, post them and let people have a go at them.
    Tell us which form is the culprit and where the delete code is.
    Is your live db on a network? If yes, could this be a problem?
    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Deletion from Form (97 SR-2)

    Are you requerying the form from your code? If not, try inserting a Me.Requery after the code that deletes the record.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Apr 2002
    Location
    Cambridgeshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Deletion from Form (97 SR-2)

    If the record lock on the form is set to 'no lock', the deletion in fact takes place, but I cannot refresh nor requery the form.

    if the record lock on the form is set to 'edited record', the delete fails as the record is locked by the form and there is nothing to requery.<font face="Comic Sans MS">text</font face=comic>

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Deletion from Form (97 SR-2)

    What do you mean you can't refresh or requery the form? Post the code you're trying to use and tell us exactly what happens when you try.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •