INSERT INTO

Experts,
I am trying to INSERT INTO tblHistoryChangeDesignatedEmply the following:
Me.StoreID.Column(0) (FK) (number)
Me.cboDistrictManager.Column(0) (FK) (number)
Me.cboDistrictManage.Column(2) (FK) (number)
DateModified = Now()

Data from my form and cannot get there
Frank FreeseAsked:
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.

lluddenCommented:
docmd.Runsql("INSERT INTO tblHistoryChangeDesignated (StoreID, DistrictManagerID, DistrictManageID, DateModified) VALUES (" & Me.StoreID.Column(0) & "," & Me.cboDistrictManager.Column(0) & "," & Me.cboDistrictManage.Column(2) & ",#" & now() & "#)"

If the values are strings, you need to surround the fields with single quotes.
0
omgangIT ManagerCommented:
What is the schema for tblHistoryChangeDesignatedEmply?  Are any of the fields required?  When you say you 'cannot get there' what, specifically, problems are you running into?

OM Gang
0
Rey Obrero (Capricorn1)Commented:

two ways you can do this

dim sql as string
sql="INSERT INTO tblHistoryChangeDesignatedEmply([fieldName1],[fieldname2],[fieldname3],[fieldname4]) values("& Me.StoreID.Column(0) & "," & Me.cboDistrictManager.Column(0) & "," & Me.cboDistrictManage.Column(2) & ",#" & Now & "#")
currentdb.execute sql,dbfailonerror

or use recordsets

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.

mbizupCommented:

strSQL = "INSERT INTO tblHistoryChangeDesignatedEmply (StoreID, Manger1, Manager2, DateModified) " _
& "VALUES(" &  Me.StoreID.Column(0) & "," _
& Me.cboDistrictManager.Column(0) & "," _
& Me.cboDistrictManage.Column(2) & ",#" _
& Now() & "#)"
CurrentDB.Execute strSQL, dbfailonerror



You'll have to verify that (StoreID, Manger1, Manager2, DateModified) match the field names in your table.
0
mbizupCommented:
Me.cboDistrictManager.Column(0)
Me.cboDistrictManage.Column(2)

Also, is one of these a typo, or are these really two distinct combo boxes?  If it is a typo, you'll have to adjust your SQL accordingly.
0
Frank FreeseAuthor Commented:
thanks folks - great solutions - lludden came from a different angle. hope this distributuion is OK
0
mbizupCommented:
The point distribution is okay with me, but can you let us know how each of the solutions worked in your database?

Thanks!
0
Frank FreeseAuthor Commented:
great point....and to add to that why a solution wass choosen over another.
0
mbizupCommented:
It helps out not only to let us know where our posts stand, but also to clarify how the issue was resolved - which is good for the participants, and for anyone looking for  a similar solution.

In this thread, the sql string in the accepted solution is actually syntactically incorrect, so its not clear whether you corrected it (or how), or whether you opted to go with the recordset method suggested in that comment.

Using recordset code is a great way to handle data inserts and updates from forms, which can make for convoluted SQL statements.  The recordset code tends to be easier to follow and easier to maintain/expand.

0
Frank FreeseAuthor Commented:
I appreciiate your feedback, as always. I went with the corrected sql string from capricorn. I've not worked with recordsets but since I've discovered the need for additional INSERT INTO funtions the next time I have the need I'll ask for a recordset solution.
0
Rey Obrero (Capricorn1)Commented:
correction for the typo

dim sql as string
sql="INSERT INTO tblHistoryChangeDesignatedEmply([fieldName1],[fieldname2],[fieldname3],[fieldname4]) values("& Me.StoreID.Column(0) & "," & Me.cboDistrictManager.Column(0) & "," & Me.cboDistrictManage.Column(2) & ",#" & Now & "#)"
currentdb.execute sql,dbfailonerror

fh_freese,

you can ask to reopen the thread and select the correct post as the accepted answer.
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
mbizupCommented:
No worries there - I've put on my Zone Advisor hat and switched it, keeping the point distribution the same.

Thanks for posting that, cap.

0
Frank FreeseAuthor Commented:
thanks folks...did catch the typo and felt good about catching and correcting it. appreciate all of you
0
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.