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?
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.
0
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.
0
Helen FeddemaCommented:
If you are running Access 2010, you could try the new data macro feature for tables.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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?
0
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
0
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.
0
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,
0
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)"
0
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
CurrentProject.Connection.Execute "Your Insert String Here"
0
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,
0
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];"
0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.