473,222 Members | 1,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,222 software developers and data experts.

DoCmd.RunSQL INSERT INTO


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

Nov 13 '05 #1
6 68319
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

Nov 13 '05 #2
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
Nov 13 '05 #3
"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
Nov 13 '05 #4
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

Nov 13 '05 #5
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
Nov 13 '05 #6
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

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Andy_HR | last post by:
i have a table named tblClanovi now i would like to make a sql insert into command that will add new record in the database... Private Sub btnSpremi_Click() Dim broj Dim imepr broj =...
7
by: Richard Hollenbeck | last post by:
Help! I don't know why this isn't working: Private Sub Combo9_Change() Dim UsersCourseSelection As String UsersCourseSelection = Me("Combo9").Value Combo13.Visible = True 'the following...
3
by: | last post by:
I have the following lines in a macro: sql = "INSERT INTO IM (ITEM, DESC) VALUES (vitem, ndesc)" DoCmd.RunSQL sql Every time I execute the macro, I get a run time error saying there is a...
1
by: Bob Bridges | last post by:
I don't see it in the documentation, but it seems whenever I try an append command adding a value to a memo field, the query bombs. Here's a sample: DoCmd.RunSQL "INSERT INTO Who (Type, Name,...
3
by: deejayquai | last post by:
Hi I'm attempting to insert multiple records from one table into another based on rows selected in a multiple list box. This all seems to be working OK until I get to a field where the value is...
3
by: tricard | last post by:
Good afternoon, I am having troubles with an insert into statement in Access 2002 that I am performing through VBA. I am inserting a single record into a table with one yes/no field. For some...
8
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table...
3
kcdoell
by: kcdoell | last post by:
Hello: I have a Insert Into command that currently works: DoCmd.RunSQL "INSERT INTO TypeEorD ( EorDName, EorD ) SELECT Trim( & '_' & Trim(Category)), Trim(CatType);" "Category" is a manual...
0
BenRatcliffe
by: BenRatcliffe | last post by:
Hi there, I was wondering if anyone could help me. I have a comlpex database with a number of forms that have data entered on them and then saved into the correct table etc. In this instance I am...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.