Automatically Add 24 records to my tblCP from my frmNewCalixCard

[cw via AccessMonster.com]

I'm sorry to ask this "often asked question" again, but after 2 days of
reading thru posts I'm still stuck.

I have a small Form with 4 fields on it and a Command Button to "ADD RECORDS
to DATABASE":
(These are the only "Required" fields.)
--------------------------------------------------
txtCityCode (value is : 3)
txtColloCode (value is : 3)
txtCalixNode (value is : 4)
txtCalixType (value is : 1)
cmdAdd

How do I take the code below (thanks Danny Lesandrini) & insert 24 identical
records into my tblCP?
--------------------------------------------------
Private Sub CmdAdd_Click()

Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

DoCmd.SetWarnings False
For intStep = 0 To intCount
strSQL = "INSERT INTO tblCP(CityCode, ColloCode,CalixNode, CalixType)
VAUES( val_1, val_2,
val_3, val_4)
DoCmd.RunSQL strSQL

Next
DoCmd.SetWarnings True

End Sub
----------------------------------------------

I'm still learning about strSql & INSERTS & don't quite understand how to
populate 24 records automatically with data from my Form?

Thanks for your help,
cw

 

[Douglas J. Steele]

It's very rare to have a need to insert mutliple records at once. If you're
just inserting them as place-holders, consider not doing so, but inserting
them when they're needed.

To answer your specific question, though, try:

Private Sub CmdAdd_Click()

Dim dbCurr As DAO.Database
Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

txtCityCode = 3
txtColloCode = 3
txtCalixNode = 4
txtCalixType = 1

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES(" & _
txtCityCode & ", " & txtColloCode & ", " & _
txtCalixNode & ", " & txtCalidType & ")"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

The main thing is that you put the variables that contain the values outside
of the SQL string, so that you get the values, not the names.

Note that I substituted the Execute method for the DoCmd.RunSQL. There are 2
reasons for this. First is that the Execute method suppresses the pop-up
messages. The second is that it will raise a trappable error if something
goes wrong running the SQL. Assuming you put appropriate error trapping in
your code, this can help prevent problems.

I'm assuming that CityCode, ColloCode, CalixNode and CalixType are all
numeric fields in the database: that you aren't storing numbers in text
fields. If they're text, you'll need to put quotes around them:

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES('" & _
txtCityCode & "', '" & txtColloCode & "', '" & _
txtCalixNode & "', '" & txtCalidType & "')"

Exagerating for clarity, that's

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES( ' " & _
txtCityCode & " ', ' " & txtColloCode & " ', ' " & _
txtCalixNode & " ', ' " & txtCalidType & " ' )"

 

[jalexander via AccessMonster.com]

Douglas, Thanks for the response.

Shouldn't this section be variables instead of actual values?
--------------------
txtCityCode = 3
txtColloCode = 3
txtCalixNode = 4
txtCalixType = 1

These will change over time as I add other City's
As to why I need to add these ahead of time. It's a valid although long story.
..
Thanks again,
Jerry

It's very rare to have a need to insert mutliple records at once. If you're
just inserting them as place-holders, consider not doing so, but inserting
them when they're needed.

To answer your specific question, though, try:

Private Sub CmdAdd_Click()

Dim dbCurr As DAO.Database
Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

txtCityCode = 3
txtColloCode = 3
txtCalixNode = 4
txtCalixType = 1

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES(" & _
txtCityCode & ", " & txtColloCode & ", " & _
txtCalixNode & ", " & txtCalidType & ")"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

The main thing is that you put the variables that contain the values outside
of the SQL string, so that you get the values, not the names.

Note that I substituted the Execute method for the DoCmd.RunSQL. There are 2
reasons for this. First is that the Execute method suppresses the pop-up
messages. The second is that it will raise a trappable error if something
goes wrong running the SQL. Assuming you put appropriate error trapping in
your code, this can help prevent problems.

I'm assuming that CityCode, ColloCode, CalixNode and CalixType are all
numeric fields in the database: that you aren't storing numbers in text
fields. If they're text, you'll need to put quotes around them:

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES('" & _
txtCityCode & "', '" & txtColloCode & "', '" & _
txtCalixNode & "', '" & txtCalidType & "')"

Exagerating for clarity, that's

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES( ' " & _
txtCityCode & " ', ' " & txtColloCode & " ', ' " & _
txtCalixNode & " ', ' " & txtCalidType & " ' )"

I'm sorry to ask this "often asked question" again, but after 2 days of
reading thru posts I'm still stuck.

[quoted text clipped - 44 lines]

Thanks for your help,
cw

 

[Douglas J. Steele]

Well, yes, you need to assign those 4 variables however is appropriate.

Your post gave those values.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, Thanks for the response.

Shouldn't this section be variables instead of actual values?
--------------------
txtCityCode = 3
txtColloCode = 3
txtCalixNode = 4
txtCalixType = 1

These will change over time as I add other City's
As to why I need to add these ahead of time. It's a valid although long
story.
.
Thanks again,
Jerry

It's very rare to have a need to insert mutliple records at once. If
you're
just inserting them as place-holders, consider not doing so, but inserting
them when they're needed.

To answer your specific question, though, try:

Private Sub CmdAdd_Click()

Dim dbCurr As DAO.Database
Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

txtCityCode = 3
txtColloCode = 3
txtCalixNode = 4
txtCalixType = 1

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES(" & _
txtCityCode & ", " & txtColloCode & ", " & _
txtCalixNode & ", " & txtCalidType & ")"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

The main thing is that you put the variables that contain the values
outside
of the SQL string, so that you get the values, not the names.

Note that I substituted the Execute method for the DoCmd.RunSQL. There are
2
reasons for this. First is that the Execute method suppresses the pop-up
messages. The second is that it will raise a trappable error if something
goes wrong running the SQL. Assuming you put appropriate error trapping in
your code, this can help prevent problems.

I'm assuming that CityCode, ColloCode, CalixNode and CalixType are all
numeric fields in the database: that you aren't storing numbers in text
fields. If they're text, you'll need to put quotes around them:

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES('" & _
txtCityCode & "', '" & txtColloCode & "', '" & _
txtCalixNode & "', '" & txtCalidType & "')"

Exagerating for clarity, that's

strSQL = "INSERT INTO tblCP(CityCode, ColloCode, " & _
"CalixNode, CalixType) VAUES( ' " & _
txtCityCode & " ', ' " & txtColloCode & " ', ' " & _
txtCalixNode & " ', ' " & txtCalidType & " ' )"

I'm sorry to ask this "often asked question" again, but after 2 days of
reading thru posts I'm still stuck.

[quoted text clipped - 44 lines]

Thanks for your help,
cw

 

[cw via AccessMonster.com]

Douglas, Thanks for your help thus far. Here is my code after fixing issues
with my actual fieldnames in my table. (I also made a copy of my tblCP called
xtblCP & am using it for testing)
----------------------------------
Private Sub Command6_Click()
Dim dbCurr As DAO.Database
Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim txtServType As Integer

Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

CityID = Me.txtCityCode
ColloCityID = Me.txtColloCode
NodeID = Me.txtCalixNode
CalixTypeID = Me.txtCalixType
SerTypID = Me.txtServType

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO xtblCP(CityID, ColloCityID, " & _
"NodeID, CalixTypeID, SerTypID) VALUES(" & _
txtCityCode & ", " & txtColloCode & ", " & _
txtCalixNode & ", " & txtCalixType & ", " & txtServType & ")"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub
-------------------------------

When I ran the code it actually posted the 24 new records! Great.
But all the values posted are 0?
I double-checked that the fields in my table are Numeric, and that my Text
fields on my form actually have the values assigned.

Any ideas would be much appreciated.
jalexander

Well, yes, you need to assign those 4 variables however is appropriate.

Your post gave those values.

Douglas, Thanks for the response.

[quoted text clipped - 84 lines]

 

[jalexander via AccessMonster.com]

I also have verified that my Form values are present in the txtCityCode
section but are 0 in the Values section of the SQL.

Douglas, Thanks for your help thus far. Here is my code after fixing issues
with my actual fieldnames in my table. (I also made a copy of my tblCP called
xtblCP & am using it for testing)
----------------------------------
Private Sub Command6_Click()
Dim dbCurr As DAO.Database
Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim txtServType As Integer

Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

CityID = Me.txtCityCode
ColloCityID = Me.txtColloCode
NodeID = Me.txtCalixNode
CalixTypeID = Me.txtCalixType
SerTypID = Me.txtServType

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO xtblCP(CityID, ColloCityID, " & _
"NodeID, CalixTypeID, SerTypID) VALUES(" & _
txtCityCode & ", " & txtColloCode & ", " & _
txtCalixNode & ", " & txtCalixType & ", " & txtServType & ")"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub
-------------------------------

When I ran the code it actually posted the 24 new records! Great.
But all the values posted are 0?
I double-checked that the fields in my table are Numeric, and that my Text
fields on my form actually have the values assigned.

Any ideas would be much appreciated.
jalexander

Well, yes, you need to assign those 4 variables however is appropriate.

[quoted text clipped - 5 lines]

 

[Douglas J. Steele]

You've assigned the values to values to variables named CityID, ColloCityID
and so on, but you're using txtCityID, txtColloCityID, etc. in your SQL
statements.

The fact that your code runs without you encountering any errors makes me
suspect that you haven't set Option Explicit on. To me, it's extremely
dangerous to try and develop without that option set.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, Thanks for your help thus far. Here is my code after fixing issues
with my actual fieldnames in my table. (I also made a copy of my tblCP called
xtblCP & am using it for testing)
----------------------------------
Private Sub Command6_Click()
Dim dbCurr As DAO.Database
Dim txtCityCode As Integer
Dim txtColloCode As Integer
Dim txtCalixNode As Integer
Dim txtCalixType As Integer
Dim txtServType As Integer

Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = 24

CityID = Me.txtCityCode
ColloCityID = Me.txtColloCode
NodeID = Me.txtCalixNode
CalixTypeID = Me.txtCalixType
SerTypID = Me.txtServType

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO xtblCP(CityID, ColloCityID, " & _
"NodeID, CalixTypeID, SerTypID) VALUES(" & _
txtCityCode & ", " & txtColloCode & ", " & _
txtCalixNode & ", " & txtCalixType & ", " & txtServType & ")"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub
-------------------------------

When I ran the code it actually posted the 24 new records! Great.
But all the values posted are 0?
I double-checked that the fields in my table are Numeric, and that my Text
fields on my form actually have the values assigned.

Any ideas would be much appreciated.
jalexander

Well, yes, you need to assign those 4 variables however is appropriate.

Your post gave those values.

Douglas, Thanks for the response.

[quoted text clipped - 84 lines]

Thanks for your help,
cw

 

[jalexander via AccessMonster.com]

Douglas, I figured it out. I was referencing the wrong word.

strSQL = "INSERT INTO xtblCP(CityID, ColloCityID, " & _
"NodeID, CalixTypeID, SerTypID) VALUES(" & _
CityID & ", " & ColloCityID & ", " & _
NodeID & ", " & CalixTypeID & ", " & SerTypID & ")"

Thanks so much for your help & code.
jalexander

I also have verified that my Form values are present in the txtCityCode
section but are 0 in the Values section of the SQL.

Douglas, Thanks for your help thus far. Here is my code after fixing issues
with my actual fieldnames in my table. (I also made a copy of my tblCP called

[quoted text clipped - 49 lines]

 

[jalexander via AccessMonster.com]

Thanks again for the post. To be honest I'm not sure what you mean.
I'm glad to be closer to understanding this technique though.

Once I implement this I definitely want it to be safe as you recommended.

What am I doing wrong?
Thanks,
jalexander

You've assigned the values to values to variables named CityID, ColloCityID
and so on, but you're using txtCityID, txtColloCityID, etc. in your SQL
statements.

The fact that your code runs without you encountering any errors makes me
suspect that you haven't set Option Explicit on. To me, it's extremely
dangerous to try and develop without that option set.

Douglas, Thanks for your help thus far. Here is my code after fixing issues
with my actual fieldnames in my table. (I also made a copy of my tblCP called

[quoted text clipped - 53 lines]

 

[Douglas J. Steele]

Option Explicit means that VBA will check to ensure that you've declared all
variables. If not, an error will be raised.

In this particular case, you don't have declarations for the misnamed
variables you used (CityID, ColloCityID, etc.), so presumably an error would
have been raised, hopefully helping you figure out what was wrong a little
sooner.

You can type Option Explicit at the top of each module, or (far better), you
can have Access automatically add the tag for you. To have Access do it
automatically, go into the VB Editor, and select Tools | Options. Select the
Module tab, and ensure that "Require Variable Declaration" is checked.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks again for the post. To be honest I'm not sure what you mean.
I'm glad to be closer to understanding this technique though.

Once I implement this I definitely want it to be safe as you recommended.

What am I doing wrong?
Thanks,
jalexander

You've assigned the values to values to variables named CityID, ColloCityID
and so on, but you're using txtCityID, txtColloCityID, etc. in your SQL
statements.

The fact that your code runs without you encountering any errors makes me
suspect that you haven't set Option Explicit on. To me, it's extremely
dangerous to try and develop without that option set.

Douglas, Thanks for your help thus far. Here is my code after fixing issues
with my actual fieldnames in my table. (I also made a copy of my tblCP

called
[quoted text clipped - 53 lines]

Thanks for your help,
cw

 

[jalexander via AccessMonster.com]

Douglas, Thanks for all your help & suggestions.
This is my final code & it's works perfectly.
------------------------------------------
Private Sub Command6_Click()
Dim dbCurr As DAO.Database
Dim CityID As Integer
Dim ColloCityID As Integer
Dim NodeID As Integer
Dim CalixTypeID As Integer
Dim SerTypID As Integer
Dim CWCPtypeID As Integer
Dim strModifiedBy As String

Dim intCount As Integer
Dim intStep As Integer
Dim strSQL As String

intCount = Me.HowMany

CityID = Me.txtCityCode
ColloCityID = Me.txtColloCode
NodeID = Me.txtCalixNode
CalixTypeID = Me.txtCalixType
SerTypID = Me.txtServType
CWCPtypeID = Me.txtCWCPtype
strModifiedBy = "jalexander"

Set dbCurr = CurrentDb()

For intStep = 1 To intCount

strSQL = "INSERT INTO tblCP(CityID, ColloCityID, " & _
"NodeID, CalixTypeID, SerTypID, CWCPtypeID, strModifiedBy) VALUES(" &
_
CityID & ", " & ColloCityID & ", " & _
NodeID & ", " & CalixTypeID & ", " & SerTypID & ", " & CWCPtypeID & ",
'" & strModifiedBy & "')"

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub
-----------------------------------------

Option Explicit means that VBA will check to ensure that you've declared all
variables. If not, an error will be raised.

In this particular case, you don't have declarations for the misnamed
variables you used (CityID, ColloCityID, etc.), so presumably an error would
have been raised, hopefully helping you figure out what was wrong a little
sooner.

You can type Option Explicit at the top of each module, or (far better), you
can have Access automatically add the tag for you. To have Access do it
automatically, go into the VB Editor, and select Tools | Options. Select the
Module tab, and ensure that "Require Variable Declaration" is checked.

Thanks again for the post. To be honest I'm not sure what you mean.
I'm glad to be closer to understanding this technique though.

[quoted text clipped - 18 lines]