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.