Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Form goes to new record (2002)

    Hi,

    I'm attaching a stripped down database that I'm working on. It's saved to 97 version since I couldn't get it small enough to upload even after deleting everything that I didn't need.

    frmNameLookup opens frmFaceSheet. This can either be done by double clicking the name in the list or or the new record icon. Everything works fine when a name is clicked. My problem is that when a new record is opened, I need to have a status of "inactive" with the date now() assigned. I've tried every which way I know and have worked at this for hours and can't get it. When the forms opens to a new record (as it should) if I assign a status (by clicking the add/edit status button) it works fine, but once I save or exit sfrmStatusTrending, frmFaceSheet goes to a new record. I want it to stay on the record if was on, not cycle to a new record.

    I'm finally conceeding defeat. I've worked on this till I'm blind. Everything else is working fine but this has stumped me.

    Also, for whatever reason, I'm not being prompted to save forms or queries when changed and I'm not sure why. I don't recall changing any settings.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form goes to new record (2002)

    Can you explain what the purpose of the table tblStatusTrending and the corresponding subform sfrmStatusTrending is? The table tblFaceSheet contains fields ID and StatusID and the table tblStatusTrending contains fields StatusID and ResidenLink. I don't understand what you want with this and what the relationship between the tables is.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form goes to new record (2002)

    Hi Hans,

    A resident [ID] can have more than one status [statusID0 during an admission and a resident can have more than one admission to the agency. [ResidentLink] links the status's that correspond to that resident. A resident may also have more than one admission, thus more than one [ID] and there needs to be a way to correspond the status to that id. When frmNameLookup opens it opens to the most recent status for a resident (thanks to your help with a query the other day). When the user double clicks the name, the face sheet with the corrsponding [id] opens. If the user needs to change the status, they do so by clicking the add/edit status button. When the status is changed, frmNameLookup and is requeried to update to the change and frmFaceSheet is requeried as well. This works fine. My problem is that when a new resident is being entered, there is no [ID] established yet. When the user enters the status and the forms requery, I keep ending up with a new record in frmFaceSheet. The new new ID has been saved to tblFacesheet, but it is not the current record showing on the form.

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form goes to new record (2002)

    The reason that you move to a new record is that you requery frmFaceSheet when leaving the subform.
    The reason that you don't get prompted to save forms etc. is that you have DoCmd.SetWarnings False in the On Click procedure for cmdClose on frmFaceSheet without a corresponding DoCmd.SetWarnings True.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form goes to new record (2002)

    To come back to your original question, you may be able to solve this by creating an unbound form in which the user can enter the minimum information necessary for a new person. When the user clicks OK, create a new record in tblFaceSheet with this info, and also create a new record in tblStatusTrending for the ID just created, with status set to Inactive and status date set to Now.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form goes to new record (2002)

    I tried this approach and after a great deal of trial and error got it to work.

    Thanks!
    Leesha

Posting Permissions

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