Results 1 to 4 of 4
  1. #1
    Randy.Wehnes is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    3

    Unhappy Auto Fill Date field on one table based on a Date Field on another table.

    I have a two tables: Census and Notes. The tables are set to a one to one relationship with the Census_ID being the foreign key in the Notes table and it is set to Enforce Referential Integrity.

    I have created a query that joins the needed fields from each table and a form based on that query, the Form is named Enrollment.

    I am trying to figure out how to set the Enrollment_Date field to create a new record in the Notes table and auto fill the two date fields (683 and Reassessment) in the Notes table.

    I am not using a sub form, I have simply created control boxes for the table fields and pointed the data to the respective fields on the respective tables.

    One method I have tried is to create a Macro in the "On Exit" Event (This is when the user tabs out of the "Enrollment_Date" Field).

    Steps in the Macro:

    SelectObject: Object Type "Table" Object Name "Notes" In Database Window "No" (I want this to be transparent to the user)
    GoToRecord: Oject Type "Table" Object Name "Notes" Record "New" Offest left blank (This is suppose to create a new record in the Notes Table)
    SetLocalVar: Name "Census_ID" (This is required for some reason) Expression "[Notes]![Census_ID] = [Census]![Census_ID]"
    SetLocalVar: Name "683" Expression "[Notes]![683] = [Census]![Enrollment Date]"
    SetLocalVar: Name "Reassessment" Expression "[Notes]![Reassessment] = [Census]![Enrollment Date]"

    When run I get this error:



    The object "Notes" is not open.

    I have looked at adding the OpenTable command just after the SelectObject but there is no way I can see to keep it from becoming visable. As I mentioned I want this process to be invisible to the user.

    I am a n00b to access and really do not know anything of VBA. I have tried to search the forums for solutions but am unable to make what I want to happen based on what I have read.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,445
    A form can enter/edit data to only one table. That macro method is only to add data to the one table. Add/edit data of another table requires SQL insert and update actions.

    Why not use subform?

    User must enter the Enrollment_Date? User must enter note info?

    Why even bother with two tables if the relationship is 1-to-1?
    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
    Randy.Wehnes is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    3
    The original census table is pretty big as it is, bigger than I would like. As I work with it I am trying to break it appart into related tables which is why I set the notes to a seperate table.

    Enrollment date is a required field, although not set up (Not sure how, have not had time to look into it.) as a required entry.

    As to sub forms: I would need to figure out how to make the sub form invisible to the user. I guess I could go that route.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,445
    Big as in a lot of fields? Why is that an issue? Use queries to 'break apart'.

    It's a balancing act between normalization and ease of data entry/edit.

    So the user enters nothing into the Notes table?

    One way to do what you want without VBA and without subform is to create Access query object for the INSERT action. The query would refer to controls on form for the values to insert.

    Then the macro would run the query object.

    How to prevent duplicate records is another issue.
    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. Auto-fill one field from another table
    By tasoper in forum Access
    Replies: 3
    Last Post: 06-13-2014, 07:17 AM
  2. Lock field after auto date fill
    By DCV0204 in forum Forms
    Replies: 4
    Last Post: 11-25-2013, 08:53 AM
  3. Auto populate date field in payment table
    By jeffrey.ccs in forum Access
    Replies: 4
    Last Post: 11-08-2012, 04:44 PM
  4. Replies: 1
    Last Post: 02-06-2011, 06:36 PM
  5. Replies: 5
    Last Post: 01-20-2011, 11:36 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