Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 9
  • 6
1 Solution
 
mbizupCommented:
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"?
0
 
mbizupCommented:
Correction:

strSQL = "INSERT INTO tblDesignatedEmployee (StoreID,EmployeeID,EmployeeTypeID) VALUES(" & me.storeID & "," & me.cboDistrictMg.Column(0) & "," & me.cboDistrictMgr.Column(2) & ")"
0
 
Frank FreeseAuthor Commented:
did spell the control work...got error on dbfailoneror?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Frank FreeseAuthor Commented:
i noticed the column error - corrected it already - good catch
0
 
mbizupCommented:
Try this - I think you sdid have a typo in your original post:


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


Double-check ALL field names.

If this does not work, post the rowsource of your combo box.
0
 
mbizupCommented:
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?
0
 
MINDSUPERBCommented:
fh_freese,

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

Ed
0
 
Frank FreeseAuthor Commented:
your assumptions are correct - I'm getting the VALUES but the record is not being written.
0
 
mbizupCommented:
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?
0
 
Frank FreeseAuthor Commented:
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.
0
 
mbizupCommented:
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

0
 
mbizupCommented:
After trying that, goto Tools -> References in the VB Editor and look for any references labeled "Missing"
0
 
mbizupCommented:
And if CurrentDB.execute doesnt work, try this instead:

docmd.SetWarnings False
Docmd.runSQL strSQL
docmd.SetWarnings True
0
 
Frank FreeseAuthor Commented:
OK! I found MY problem - I now have a written record.
Many thanks
0
 
Frank FreeseAuthor Commented:
Thank you
0
 
mbizupCommented:
<< I found MY problem >>

Curious - what was the issue?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now