Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form update (2007)

    I must be missing something obvious. I have a subform which is populated by another form which is based on a table. A macro (which resides on the form) is used to set the corresponding subform control using the primary key from the form. It works as long as the form (i.e. the table) contains the entry. If a new entry is added by way of the form it does not work. Error message the microsoft database engine cannot find a record in the table (based on form) with key matching 'primary key'.
    If I requery the form (refresh or F9) after a new entry then it works-it was added to the table. If however I try to requery or sendkeys "[F9}" with the macro I get the same error message.

    I can submit a stripped down db if it would be helpful

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

    Re: Form update (2007)

    The "normal" way to link a form and subform is through the Link Master Fields and Link Child Fields properties of the subform as a control on the main form, you shouldn't need code.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form update (2007)

    I wasn't very clear. There is a form with subform. It is this subform which I would like to update. There is a totally separate additional form with an independent table that is used to populate the subform. As I said it workks fine for existing entries. The macro transfers the primary ID to the subform. The subform is based on a query that has as one its its tables, the table that is the source of that 2nd separate form The problem occurs with new entries to the separate form. I believe it is because the new record has not been added to the table-that is why when the form is refreshed it works

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

    Re: Form update (2007)

    But what's the reason for doing it that way? Isn't it superfluous to have a subform and a separate main form for the same table? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form update (2007)

    I've attached a stripped db Hopefully it will explain things better than I can
    Attached Files Attached Files

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

    Re: Form update (2007)

    It looks pretty, but I don't understand what it does.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form update (2007)

    Cute but too kind. There is a list of doctors with their addresses phone numbers etc. This information is stored in the DoctorsList table. For any given client their physician contact info is stored in the doctors table by patient ID and doctornameID. These two tables should supply all the doctors and their pertinent information for each patientID. On the main form there are in fact a number of tabbed subforms which are all linked by patientID. One of these is the doctors subform which should give a record for each doctor associated with the given patientID

    And it does. But how do I add this information. The doctornew form is opened and searched for a given doctor from the doctorslist table. Once that information is located I want to transfer the doctornameID to the doctors subform as a new entry into the doctors table. This all works fine. I am stuck when I want to add a new entry to the doctorslist table and subsequently to the doctors table.

    For instance I add new doctornameid 4 to the doctorlist table by way of the doctornew form. Now I want to enter it to the doctor subform and therefore to the doctors table. This does not work. I must first insure that the entry is already in the doctorlist table and then add it to the subform and doctors table. I can't seem to update both at the same time unless I refresh the doctorsnew form manually. I can't seem to update the recordset with a macro

    I was hoping that there is a better way to add new patients to both tables

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

    Re: Form update (2007)

    I'd use a combo box on the subform to select a doctor, and use the On Not In List event of this combo box to display the DoctorNew form if the user enters a non-existing name in the combo box. By setting the Response argument to acDataErrAdded, Access will automatically refresh the combo box. See the attached version.
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form update (2007)

    I'm having trouble getting this to work. It seems OK if I select one of the records from the combo box. I can't seem to get the not in list code to engage. If I type in the combo box I get an error-not in list but this doesn't open the doctornew form. I'm I missing a step

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form update (2007)

    The problem may have been that the db was not in a trusted folder. It seems to work but I will need to play with it tomorrow. Thanks for the help

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form update (2007)

    Sorry for the delay. Your solution does do what I would like it to. Thanks as always

Posting Permissions

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