[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

INSERT INTO

Posted on 2011-10-31
13
Medium Priority
?
241 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Frank Freese
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 200 total points
ID: 37056756
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
 
LVL 28

Expert Comment

by:omgang
ID: 37056758
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37056767

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 200 total points
ID: 37056781

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
 
LVL 61

Expert Comment

by:mbizup
ID: 37056796
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
 

Author Comment

by:Frank Freese
ID: 37056864
thanks folks - great solutions - lludden came from a different angle. hope this distributuion is OK
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37057121
The point distribution is okay with me, but can you let us know how each of the solutions worked in your database?

Thanks!
0
 

Author Comment

by:Frank Freese
ID: 37057644
great point....and to add to that why a solution wass choosen over another.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37060183
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
 

Author Comment

by:Frank Freese
ID: 37062154
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 37062341
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37062913
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
 

Author Comment

by:Frank Freese
ID: 37062932
thanks folks...did catch the typo and felt good about catching and correcting it. appreciate all of you
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question