Results 1 to 12 of 12
  1. #1
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9

    Open form and pass parameters whether or not record already exists in new form

    I have two forms - frmPersonDetails and frmHouseholds (Head of Household). I have a button set on frmPeople to open frmHouseholds. On frmPeople, I have PersonID, first name, last name, cell phone. If that person is also the HOH, I want to open frmHouseholds to add or edit address, phone #, and family information. In my button on frmPersonDetails, I have the code:



    Code:
         
    
    DoCmd.OpenForm "frmHouseholds", acNormal, "", "[HeadHouseholdID]=" & Me.intPersonID, , acDialog, intPersonID.Value
    In my frmHouseholds form load event I have

    Code:
     
    
    If Me.OpenArgs <> vbNullString Then
        
        intArgs = Me.OpenArgs
        Me.cmbPersonID = intArgs
    This works beautifully if the record already exists in the frmHouseholds. I want to be able to create a new record using the PersonID from the first form, if the record doesn't exist in frmHouseholds. What code do I need to add?

    Please be patient with me. I'm just learning VBA.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If record already exists and form opens to that record, why are you setting cmbPersonID to OpenArgs? Shouldn't that field already have value in existing record?

    If you are attempting to open form to one record and that record does not exist, the form should then be on New Record row. So:

    If Me.NewRecord Then Me.cmbPersonID = Me.OpenArgs

    However, it seems odd that a person record already exists without a household record.
    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.

  3. #3
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Thank you for you response. My problem is that it isn't a new record in the people form, but it is in the HOH form. When I pass the OpenArgs to the second form, it doesn't go to a new record. It opens to the first record.

    Here's my reasoning. The database is for maintaining person records for our our church. It will hold every person, including family members who are not heads of household. We will also maintain outside contacts for whom we don't need address information. The household table is to associate spouses and children with the HOH (in a church directory, for example) and for people for whom we will need address information. So the people table will be populated first. Only a few of them will be added to the HOH table.

    The user may not have household (address) information when they are adding the person. Later they may not remember whether or not they have added them to the household or they may need to edit the household information. I didn't want them to have to search on the household table every time before they add the information. If the person already exists in the people table and in the HOH table, I want to open the HOH form with the existing record. (This part works fine.) If they don't exist in the HOH table, I still need to use the same person ID, but passed to a new record.

    I'm open for other suggestions. Thank you!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you provide filter criteria for opening Household form and no records match that criteria, the form should open on new record. So exactly what is not working?
    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.

  5. #5
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Quote Originally Posted by June7 View Post
    If you provide filter criteria for opening Household form and no records match that criteria, the form should open on new record. So exactly what is not working?
    That's what I expected. But it isn't opening on a new record. It's opening the first HOH record.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And there are other records available? The form is not filtered? If it is filtered, have you applied correct filter criteria?
    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.

  7. #7
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Quote Originally Posted by June7 View Post
    And there are other records available? The form is not filtered? If it is filtered, have you applied correct filter criteria?
    I'm not sure I understand. Yes there are other records in each table. The form is filtered, but since I'm on the current record in table 1 and it's nonexistent in table 2, I'm not sure how a filter would affect that.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I thought you want form to open to a single household record and if doesn't exist should be on new record?
    If the form is opening to an existing record then are there other records that can be navigated to? If yes then your filter does not seem to be appropriate.

    I am not understanding your data structure and form design. I will have to review db to help further.
    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
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Thanks for your help. Yes, you understand what I'm trying to do. My only filter that should be affecting it is the
    Code:
     DoCmd.OpenForm "frmHouseholds", acNormal, "", "[HeadHouseholdID]=" & Me.intPersonID, , acDialog, intPersonID.Value 


    The other filters are only used if OpenArgs is null. I put your code into my OnLoad event on my second form and did a "Step into" in my VBA. My OpenArgs are obtaining the right values, but
    Me.NewRecord is never true. I just need to force it to create a new record when it doesn't find a match.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And we are going in circles.

    I tested this. When there is no match to the OpenForm command filter criteria, form has no records and opens on new record row.
    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
    SandyB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    9
    Ok, thanks so much for your help! I’ll just have to figure out another way to do this.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You don't want to provide db for analysis?
    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.

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

Similar Threads

  1. Replies: 7
    Last Post: 11-05-2019, 12:04 PM
  2. Replies: 5
    Last Post: 09-16-2016, 03:16 PM
  3. Form Parameters and Pass Through Queries
    By Paul H in forum Queries
    Replies: 3
    Last Post: 02-21-2014, 01:46 PM
  4. Replies: 1
    Last Post: 10-04-2013, 02:52 PM
  5. Pass parameters to a query from a form
    By zipmaster07 in forum Queries
    Replies: 1
    Last Post: 02-21-2012, 02:19 PM

Tags for this Thread

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