Results 1 to 8 of 8
  1. #1
    00253's Avatar
    00253 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2017
    Posts
    5

    Smile Add content of text fields with dLookUp() from form into new record; Deleting a record breaks form?

    Hello to whomever is reading this.



    In my form, user inputs some basic information into fields. Name, age, height, weight.
    Based upon this, several other form fields are automatically filled in with dLookUp(). Life expectancy, BMI.
    When using this form to add a new record, I have no trouble assigning the control source of the form fields that the user inputs (= corresponding columns in my desired storage table). I can add those into the new record, no problem.
    But I don't know how to also add to the new record the forms fields whose control source is dLookUp()?

    What would be a correct way to do this?


    * * *

    I also noticed that when I manually delete a record added using the form, the form fields with control source bound to the table refused to cooperate - value in those fields is something like #DELETED. This is not a desired behavior for me, am I doing something wrong? I want to be able to delete old records with the form still working.

    Thank you for your time!

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Why save them at all? They can always be called by a query based on the data you do have.

  3. #3
    00253's Avatar
    00253 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2017
    Posts
    5
    Quote Originally Posted by andy49 View Post
    Why save them at all? They can always be called by a query based on the data you do have.
    That would be true if the data loaded by dLookup remained the same throughout the life of the database, and they don't in my case. The example I posted (age, BMI, etc.) is ilustrative - the real case is more complicated and I'd really like to solve this by recording all the values from the form. But thanks for the input - I'll definetly think about it.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Just off the top of my head, perhaps a button which ran an update query (thereby storing your values)

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The form must be refreshed or requeried to remove the #DELETED.

    VBA can save the lookup values. The real trick is figuring out what event to put the code into. Perhaps the form BeforeUpdate. I normally use a custom button Click event to commit data.
    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.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by 00253 View Post
    In my form, user inputs some basic information into fields. Name, age, height, weight.
    Be aware that "Name" and "Height" are reserved words in Access and shouldn't be used as object names.
    Plus they are not very descriptive.
    "Name" of what?? Better would be "FName" or "F_Name" and "LName" or "L_Name".
    "Height" of what". Maybe ""PersonalHeight" or "SubjectHeight"...


    Problem names and reserved words in Access
    http://allenbrowne.com/AppIssueBadWord.html

  7. #7
    00253's Avatar
    00253 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2017
    Posts
    5
    Quote Originally Posted by June7 View Post
    The form must be refreshed or requeried to remove the #DELETED.

    VBA can save the lookup values. The real trick is figuring out what event to put the code into. Perhaps the form BeforeUpdate. I normally use a custom button Click event to commit data.
    Ok, this is probably what I need, VBA. Do I use a recordset? I've looked it up and most of the documentation seems pretty vague to me, but if that's why I need, I'll stick to it and make it work.





    Quote Originally Posted by ssanfu View Post
    Be aware that "Name" and "Height" are reserved words in Access and shouldn't be used as object names.
    Plus they are not very descriptive.
    "Name" of what?? Better would be "FName" or "F_Name" and "LName" or "L_Name".
    "Height" of what". Maybe ""PersonalHeight" or "SubjectHeight"...


    Problem names and reserved words in Access
    http://allenbrowne.com/AppIssueBadWord.html
    Thanks, these are correct and important suggestions for sure, but I've said it somewhere in the thread: this case is just an illustrative example. I'm using different names for completely different objects.

  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
    Recordset not needed. Code can be macro or VBA - I use only VBA. Can be as simple as:

    Me!fieldname = Me.controlname

    As I said, real trick is figuring out what event to use.
    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. Deleting Record on Form Does Not Work
    By MatthewR in forum Forms
    Replies: 4
    Last Post: 08-07-2017, 01:56 PM
  2. Replies: 2
    Last Post: 02-01-2017, 03:10 PM
  3. problem deleting the LAST record in a single form
    By markjkubicki in forum Forms
    Replies: 5
    Last Post: 04-22-2015, 04:42 PM
  4. Replies: 2
    Last Post: 02-23-2014, 09:48 PM
  5. Deleting The Sub-Form Record From Main Form
    By vdanelia in forum Forms
    Replies: 0
    Last Post: 11-08-2011, 04:33 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