In MS Access 2007, how do I save/add new record from a form to a specific table?

I have a form bound to a table where I use a command button to run a macro, then assign a date to a specific text box on the form.  Everytime I open and close the form, the button works fine where the date is recorded to the table.  However, whenever I use the command button again without closing and opening the form it is on, the new date overwrites the old one.
dragonheartsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's the way bound forms work - any code you run impacts the current record, unless you specifically tell Access to work with other records.

Can you explain more about your macro? I have worked with macros in a long time, but if your macro does nothing more than write a date to the Form's textbox, then clicking that button will overwrite it each time it's clicked.

If you want to create a NEW record each time the button is clicked, you can do that as well.

Please clarify what you want to do, and we'll see if we can assist.
Helen FeddemaCommented:
Could it be that you are writing the date to a new record?  If so, that might better be done by using code on the BeforeInsert event of the form.  That would ensure that the date is only written to a new record.  Or (if it is something simple, like today's date), just put Date() in the table as the Date field's default value.
Helen FeddemaCommented:
If you are running Access 2010, you could try the new data macro feature for tables.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Richard DanekeTrainerCommented:
Could you add a test in the Macro to see if a date is already in the field and to not update the value if IsDate([field]) is true?
dragonheartsAuthor Commented:
Sorry for the delay.  The client has forced me to change my form/subform since then.

I have a main form and 3 subforms (all are bounded to 4 specific tables that have the hierarchical relationships.  the subform I want to append data has 3 fields: Task, Analyst and Date.
Everytime I change a specific field on the main form [Main_Task], I want to take the current values of Main_Task, Main_Date and sfrm_Analyst and append it to the subform's 3 fields: Task, Analyst and Date.  I am assuming I need to use a button to trigger a macro to append the 3 specific values?

Thank you in advance
Richard DanekeTrainerCommented:
You do not have to use a button.  This wouuld provide a pro-active method to run the process.   When the main form does an update, the BeforeUpdate Event is triggered.  Your code can be placed in this routine to add the subform record and, then, refresh the display.
dragonheartsAuthor Commented:
so on the main form, under the event BeforeUpdate, what is the code and what do I use to refresh both form and subforms?

the actual names of the form is "frm 100 Working F" and the subform is "sfrm 300 Working TL"

Thanks,
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just use an INSERT query:

CurrentProject.Connection.Execute "INSERT INTO YourTable(Col1, Col2, Col3) VALUES(Val1, Val2, Val3)"
dragonheartsAuthor Commented:
Below in the Append Query (Insert Into query).  What would be the VB syntax in placing in the "AfterUpdate()" Sub?

INSERT INTO [300 Working T] ( superkey, [Task Analyst], Task, [Task Date] )
SELECT [100 Working F].superkey, [000 Task List].Analyst, [100 Working F].[Current Task], [100 Working F].[Status Date]
FROM [100 Working F] INNER JOIN [000 Task List] ON [100 Working F].superkey = [000 Task List].superkey
WHERE ((([100 Working F].superkey)=[Forms]![frm 100 Working F]![superkey]))
ORDER BY [100 Working F].[Status Date];


Thanks in advance
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
CurrentProject.Connection.Execute "Your Insert String Here"
dragonheartsAuthor Commented:

I just entered as prescribed but it errored out.
What am I missing?

CurrentProject.Connection.Execute "INSERT INTO [300 Working T] ( superkey, [Task Analyst], Task, [Task Date] )SELECT [100 Working F].superkey, [000 Task List].Analyst, [100 Working F].[Current Task], [100 Working F].[Status Date] FROM [100 Working F] INNER JOIN [000 Task List] ON [100 Working F].superkey = [000 Task List].superkey WHERE ((([100 Working F].superkey) = [Forms]![frm 100 Working F]![superkey]))ORDER BY [100 Working F].[Status Date];"

Thanks,
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What was the error?

Try this:

CurrentProject.Connection.Execute "INSERT INTO [300 Working T] ( superkey, [Task Analyst], Task, [Task Date] )SELECT [100 Working F].superkey, [000 Task List].Analyst, [100 Working F].[Current Task], [100 Working F].[Status Date] FROM [100 Working F] INNER JOIN [000 Task List] ON [100 Working F].superkey = [000 Task List].superkey WHERE ((([100 Working F].superkey) = " &  [Forms]![frm 100 Working F]![superkey] & " )) ORDER BY [100 Working F].[Status Date];"

Is "Superkey" aa String value, or a Numeric value? If it's a string:

CurrentProject.Connection.Execute "INSERT INTO [300 Working T] ( superkey, [Task Analyst], Task, [Task Date] )SELECT [100 Working F].superkey, [000 Task List].Analyst, [100 Working F].[Current Task], [100 Working F].[Status Date] FROM [100 Working F] INNER JOIN [000 Task List] ON [100 Working F].superkey = [000 Task List].superkey WHERE ((([100 Working F].superkey) ='" &  [Forms]![frm 100 Working F]![superkey] & "' )) ORDER BY [100 Working F].[Status Date];"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.