Results 1 to 11 of 11
  1. #1
    Jholl is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2023
    Posts
    7

    New Record Button doesn't auto-populate a PK from parent form.

    Please help. I have a subform that I need a button to open a form to enter a new record. I want the new record to automatically populate with the subforms Primary Key. Here is what I have and it opens a new form, but not with the Primary Key from the parent form.

    Private Sub NewBtn_Click()




    DoCmd.OpenForm "FinancialAssistanceF", , , "PersonID=" & PersonID
    DoCmd.GoToRecord acDataForm, "FinancialAssistanceF", acNewRec

    Thanks in advance

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Pass the PersonID in as OpenArgs and set to PK, if NewRecord.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or change the first line to open the form in data add mode (use acFormAdd) then change the second line to:
    Code:
    Forms!FinancialAssistanceF!PersonID=Me.PersonID 'or if you want it from the mainform use Me.Parent.Form.Controls("PersonID")
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Jholl is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2023
    Posts
    7
    Thank you both!

  5. #5
    Jholl is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2023
    Posts
    7

    Works as long as there is already data in Form

    Quote Originally Posted by Gicu View Post
    Or change the first line to open the form in data add mode (use acFormAdd) then change the second line to:
    Code:
    Forms!FinancialAssistanceF!PersonID=Me.PersonID 'or if you want it from the mainform use Me.Parent.Form.Controls("PersonID")
    Cheers,
    The suggestion above worked PERFECTLY as long as the FinancialAssistanceListF had previously listed data. However, if I want to add a new record that hasn't had any data entered before, it autopopulates the PersonID with 0 instead of the PersonID from the parent from. I was thinking that maybe I should use If PersonID = 0 and then populate with main parent form (or something like that, but I'm not sure).

    Here is what I have now that works as long as there is data already entered.

    DoCmd.OpenForm "FinancialAssistanceF", , , acFormAdd
    Forms!FinancialAssistanceF!PersonID = Me.PersonID

    If this is a new record, I need it to populate with the parent form's PersonID.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure I follow you, but I think you just need to save the main form (FinancialAssistanceListF?) before trying to open the new one so you force it to create the new PersonID:

    Just add Me.Dirty=False before opening the other form.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Jholl is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2023
    Posts
    7
    Let me see if I can explain better. First, thank you for your advice.

    I have the main form named "PatientF" (PersonID is the PK). On this form, I have a button that opens the "FinancialAssistanceListF" form (PersonID and FinancialAssistanceID are fields on the form). The FinancialAssistanceListF form is a continuous form that lists a summary of the patient's financial assistance awarded (Date Received, Amount Paid, & Payee). The FinancialAssistanceListF form has a button (New Entry) that allows the user to enter a new financial request by directing them to the FinancialAssistanceF form.

    The FinancialAssistanceListF form and the FinancialAssistanceF form populate the records perfectly when a record exists for the patient and I can enter subsequent records with no issues. However, if there are no records for the patient (new patient being entered), then I receive an error message "Run-time error '2113': The value you entered isn't valid for this field." When I select "Debug", the highlighted line is "Forms!FinancialAssistanceF!PersonID = Me.PersonID"

    My FinancialAssistanceF form has a record source of FinancialAssistanceQ (has a PersonID and a FinancialAssistanceID).
    The FinancialAssistanceList form has a record source of FinancialAssistanceListQ (has PersonID and a FinancialAssistanceID).

    It seems like on new patients, the FinancialAssistanceListF doesn't connect the PersonID unless a record already exists.

    I hope this makes better sense and you can help. Again, thank you for your time.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Quote Originally Posted by Jholl View Post
    It seems like on new patients, the FinancialAssistanceListF doesn't connect the PersonID unless a record already exists.
    That would be reasonable as there aren't any assistance records yet. Why don't you make FinancialAssistanceListF a subform on ParentF? Have button on main form to open FinancialAssistanceF data entry form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Jholl is offline Novice
    Windows 11 Office 365
    Join Date
    Feb 2023
    Posts
    7
    The main reason I didn't put the FinancialAssistanceListF as a subform is because the ParentF form already has two subforms on it. Trying to keep it from being too busy on the main form.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Consider using a Tab control to organize multiple subforms.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I agree that a tab would give you some more room for the list. What you're describing is probably caused by the fact that the new record in PatientF is not saved yet, so you need to save it first. In the FinancialAssistanceListF form add this to the button that opens the FinancialAssistanceF form to enter a new record
    Code:
    Forms!PatientF.Form.Dirty=False
    
    DoCmd.OpenForm "FinancialAssistanceF", , , acFormAdd
    
    Forms!FinancialAssistanceF!PersonID = Forms!PatientF!PersonID
    You haven't showed us the code used on the button on the patient form to open the list; add a Me.Dirty = False there as well.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-28-2021, 01:42 PM
  2. Next Record Button on Parent Form
    By dsajones in forum Forms
    Replies: 3
    Last Post: 11-28-2019, 04:52 AM
  3. Replies: 3
    Last Post: 07-31-2019, 06:27 PM
  4. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  5. Replies: 3
    Last Post: 10-03-2011, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums