DoCmd.RunSQL INSERT INTO

[David]

I am trying to insert an employee number into the EmpNbr field in my
main table from a form where I add a new employee to my employee table.
I was hoping this command would work, but it isn't.

DoCmd.RunSQL "INSERT INTO [tblCernerHours] VALUES ([EmpNbr])", 0

This is the only field I want to populate in the main table from this
form. there are three other fields in the main table that I want to
leave unpopulated.

Thanks in advance,

David

[Wayne Morgan]

If you build the query in the design grid, Access give this as the SQL.

DoCmd.RunSQL "INSERT INTO tblCernerHours ( EmpNbr ) SELECT 0 AS Expr1;"

--
Wayne Morgan
MS Access MVP
"David" <da****@mail.heartland-health.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

I am trying to insert an employee number into the EmpNbr field in my
main table from a form where I add a new employee to my employee table.
I was hoping this command would work, but it isn't.

DoCmd.RunSQL "INSERT INTO [tblCernerHours] VALUES ([EmpNbr])", 0

This is the only field I want to populate in the main table from this
form. there are three other fields in the main table that I want to
leave unpopulated.

Thanks in advance,

David

[Terrell Miller]

David wrote:

I am trying to insert an employee number into the EmpNbr field in my
main table from a form where I add a new employee to my employee table.
I was hoping this command would work, but it isn't.

DoCmd.RunSQL "INSERT INTO [tblCernerHours] VALUES ([EmpNbr])", 0

David, try something like this for your SQL clause:

"INSERT INTO [tblCernerHours] ([EmpNbr]) VALUES ( employee number you
wish to insert into the EmpNbr field goes here);"
basic syntax:
INSERT INTO
table to insert into
fields to populate, in parens
VALUES
values to dump in those fields, also in parens

....in that order
If EmpNbr is a string/character you need single quotes around the value.

HTH,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison

[Tony Toews]

"Wayne Morgan" <co***************************@hotmail.com> wrote:

If you build the query in the design grid, Access give this as the SQL.

DoCmd.RunSQL "INSERT INTO tblCernerHours ( EmpNbr ) SELECT 0 AS Expr1;"

Trouble is Access doesn't support the VALUES functionality (which I use all the time)
in the design grid.

One of these days I'm going to build some kinda automated routine where I pick the
fields from a table and get a copy of a VALUES query in the clipboard for pasting in
the code.

No time today to do it so maybe tomorrow. Or next week. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David]

Thank you Wayne, Terrell, and Tony for your responses. Terrell, I tried
your suggestion, and was able to get the new record inserted into the
main tbl., but the new data (EmpNbr) that was typed in the form for the
new employee is not being inserted into the new record. (It is being
inserted into the Emp. table first). Here is what puts a new blank
record in the main table:

DoCmd.RunSQL "INSERT INTO [tblCernerHours] ([EmpNbr]) VALUES
([EmpNbr]);"
Here is what my code looks like for the Enter button on my form:

Private Sub cmdEntNewRep_Click()
On Error GoTo Err_cmdEntNewRep_Click

DoCmd.GoToRecord , , acNewRec
DoCmd.RunSQL "INSERT INTO [tblCernerHours] ([EmpNbr]) VALUES
([EmpNbr]);"
cboSelEmp.Requery
cboSelEmp.SetFocus
cboSelEmp = ""

Exit_cmdEntNewRep_Click:
Exit Sub

Err_cmdEntNewRep_Click:
MsgBox "This Employee number is already in this database!",
vbCritical
Resume Exit_cmdEntNewRep_Click
End Sub

Thanks again for your time.
David

[Terrell Miller]

David wrote:

DoCmd.RunSQL "INSERT INTO [tblCernerHours] ([EmpNbr]) VALUES
([EmpNbr]);"

David, you need to concatenate a couple of strings here. You are trying
to insert the current value of the EmpNbr field *in the record you are
creating* into that field *in the record you are creating*.

But that field doesn't have a value yet, since you just created the
record. So you are taking a null field value and inserting it back into
the same field in the same record.

try "INSERT INTO [tblCernerHours] ([EmpNbr]) VALUES (" & 12345 & ")"

where 12345 is whatever the actual employee number happens to be.

So you are starting off with the first part of a string that includes
your SQL clause up to the left-parens after VALUES, then concatenating
in the actual value of your EmpNbr control on the form, then adding the
closing parens in a third string. All that gets rolled into one long
string, and that is executed by the RunSQL command.

HTH,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison

[David]

Hi Terrell, thanks for your help. I tried your last code, and it isn't
working. if you are interested in taking a look at it, I will send the
db to you. It is only 69 kb's zipped. My E-mail add. is:
da****@heartland-health.com

Thanks again for your good help.

David