Results 1 to 10 of 10
  1. #1
    lukelloyd is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6

    Need help with find record and add data from one form of data doesn’t exist

    Hey all,

    i have a dB with 2 tables, one is for customers called customer view, the other to add additional info about the customer called rider Reg. I want to open the rider Reg form from the customer view form and display information for that customer - defined by the id from the customer record (rider Reg table has a customer if field), if there is not info already there to display, I want it to input the customers if into the customer I’d field in the rider Reg form. I can get one to work without the other but I can’t work out an if null method myself to get the following, search for customer I’d number and if null add it to the customer I’d field.



    hope that makes sense

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why not use form/subform arrangement?
    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
    lukelloyd is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    Why not use form/subform arrangement?
    Will it still pull relevant info for customer? Data is coming from a MySQL database if that’s relevant?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is a common configuration. Main form bound to Customers and subform bound to dependent Reg table.

    Yes, this should work with MySQL backend.
    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
    lukelloyd is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    This is a common configuration. Main form bound to Customers and subform bound to dependent Reg table.

    Yes, this should work with MySQL backend.
    And how will this add customer I’d to the Reg table if data doesn’t exist already for that customer?

    Sorry just trying to picture it

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You input new customer on the main form. Then when you enter record into subform the new customer ID will automatically populate to subform record. That's what form/subform is designed to accomplish. This is basic Access functionality. Have you completed introductory tutorial book?
    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
    lukelloyd is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    You input new customer on the main form. Then when you enter record into subform the new customer ID will automatically populate to subform record. That's what form/subform is designed to accomplish. This is basic Access functionality. Have you completed introductory tutorial book?

    Thank you but but it’s not what I’m looking to achieve. I’ll admit I’ve worked access out myself as and when needing to use it and for the most part am successful with what I need to do, every now and then however I run into a snag like this.

    as I mentioned before I can get the form to open the second and add the data from the original forms I’d field into the new forms customer I’d field and I can get it to open the second form to the record with the customer I’d field matching the original forms I’d but I can’t work out how to get it to open the second form, look for the I’d number and if null add it.

    appreciate all the help :-)

  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,822
    There are several ways to pass data to a form. One uses OpenArgs argument of DoCmd.OpenForm. Then the second form references its OpenArgs property to pull the data.

    Post your code on how you are opening second form to the ID.

    Why would it matter if Reg already has records for the ID? Aren't multiple records for each ID allowed?

    Maybe you just want second form opened to new record row? Several ways to accomplish that.
    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
    lukelloyd is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    There are several ways to pass data to a form. One uses OpenArgs argument of DoCmd.OpenForm. Then the second form references its OpenArgs property to pull the data.

    Post your code on how you are opening second form to the ID.

    Why would it matter if Reg already has records for the ID? Aren't multiple records for each ID allowed?

    Maybe you just want second form opened to new record row? Several ways to accomplish that.

    Its a form that only requires one set of data, as its attached to a MySQL database customers will be able to add these details themselves, if they do or we add it ourselves we need to view the info not add any extra so it’s not a data sheet just a standard form :-)

  10. #10
    lukelloyd is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Quote Originally Posted by lukelloyd View Post
    Its a form that only requires one set of data, as its attached to a MySQL database customers will be able to add these details themselves, if they do or we add it ourselves we need to view the info not add any extra so it’s not a data sheet just a standard form :-)

    I managed to resolve this myself, for those looking at the same problem here is what I did.

    on the first form I created a button to open the second looking for a specific value in the second form to show correct data, in my case customer I’d on the second had to match I’d on the first. Before hand though I entered a temp at to hold onto the I’d.

    on the second form on load I created the following

    if is notnull([tempvar]![name of var]) then
    If isnull([customer id]) then
    setproperty

    name: customer id
    Type: value
    =[tempvar]![name of var]

    or something to that effect, I don’t have it in front of me.

    works perfectly now. Thank you for the previous help that was attempted but maybe you can see what I wanted now from what I achieved :-)

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

Similar Threads

  1. Replies: 2
    Last Post: 11-15-2016, 03:49 PM
  2. Replies: 8
    Last Post: 09-15-2015, 11:34 AM
  3. record doesn't exist
    By slimjen in forum Forms
    Replies: 7
    Last Post: 08-13-2014, 11:07 AM
  4. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  5. Append if record doesn't exist
    By Lorlai in forum Queries
    Replies: 1
    Last Post: 06-14-2011, 06:38 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