asked on

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

SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

omgang

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

Rey Obrero (Capricorn1)

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

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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.

Frank Freese

ASKER

thanks folks - great solutions - lludden came from a different angle. hope this distributuion is OK

mbizup

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

Thanks!

Frank Freese

ASKER

great point....and to add to that why a solution wass choosen over another.

mbizup

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.

Frank Freese

ASKER

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.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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.

Frank Freese

ASKER

thanks folks...did catch the typo and felt good about catching and correcting it. appreciate all of you