asked on

Insert Into

Experts,
I need help on an Insert Into sql statement.
My insert into table is tblDesignatedEmployee
DesignatedEmployeeID PK (autonumber)
StoreID
EmployeeID
EmployeeTypeID
Where:
StoreID comes from control StoreID on my form
EmployeeID comes from control cboDistrictMg Column.0 on my form
EmployeeTypeId comes from control cboDistrictMgr.Column2 on my form

mbizup

From a command button on your form:

Dim strSQL as string
strSQL = "INSERT INTO tblDesignatedEmployee (StoreID,EmployeeID,EmployeeTypeID) VALUES(" & me.storeID & "," & me.cboDistrictMg.Column(0) & "," & me.cboDistrictMgr.Column(0) & ")"
Currentdb.execute strSQL, dbfailonerror

Also check this combo name --->>> cboDistrictMg
Should it be "cboDistrictMgr"?

mbizup

Correction:

strSQL = "INSERT INTO tblDesignatedEmployee (StoreID,EmployeeID,EmployeeTypeID) VALUES(" & me.storeID & "," & me.cboDistrictMg.Column(0) & "," & me.cboDistrictMgr.Column(2) & ")"

Frank Freese

ASKER

did spell the control work...got error on dbfailoneror?

Frank Freese

ASKER

i noticed the column error - corrected it already - good catch

ASKER CERTIFIED 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

I'm assuming a couple of things:
1. All ID fields are numeric
2. You are running the code from the same form as your combo box

Are those assumptions correct?

MINDSUPERB

fh_freese,

You may try to check the data types of your fields. Do all the three fields have Number data types?

Ed

Frank Freese

ASKER

your assumptions are correct - I'm getting the VALUES but the record is not being written.

mbizup

Are you getting an error message? ---->>> Which one?

Did you include this line in your code?
Currentdb.execute strSQL, dbfailonerror

What is the rowsource of your combo box? ---> do we have the column numbers correct?

Is the back end an Access database, or is it linked to SQL Server or something else?

Frank Freese

ASKER

error message: dbfailonerror - variable not defined
my combo box is unbound
no back end linking
column numbers are correct - after VALUES I am getting what I am expect.

mbizup

Where are you running your code from? dbFailOnError is a VB constant that *should* be globally recognized in your database.

Try this instead:

Currentdb.execute strSQL, 128

mbizup

After trying that, goto Tools -> References in the VB Editor and look for any references labeled "Missing"

mbizup

And if CurrentDB.execute doesnt work, try this instead:

docmd.SetWarnings False
Docmd.runSQL strSQL
docmd.SetWarnings True

Frank Freese

ASKER

OK! I found MY problem - I now have a written record.
Many thanks

Frank Freese

ASKER

Thank you

mbizup

<< I found MY problem >>

Curious - what was the issue?