How to automatically Insert Incrementing Values into my Database

[cw via AccessMonster.com]

I'm using the following code to automatically create "X" records in my
database:
------------------------------------------------------------------------------
------------------------
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 = "ja"

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
---------------------------------------------------------------------
This works fine!

I also need it to fill-in the field in my Table called VrzPair field, but do
it incrementally based a range of values.

On my Form I have added Unbound Text boxes called BegValue & EndValue.

How would I make the Code not only Create the records as before, but also
fill in each records VrzPair field with the Incremented value?

Thanks again for all your help,
cw

 

[Michel Walsh]

Hi,


add the field in the list field, and the counter, in the values:


....
For i = beginValue To endValue
DoCmd.RunSQL "INSERT INTO ... (..., VrzPair ) VALUES( ... ," & i & ")",
dbFailOnError
Next i



Hoping it may help,
Vanderghast, Access MVP

 

[cw via AccessMonster.com]

Michael, Thanks for the reply!

This is the second time I have used VB to insert records automatically so I'm
still learning the ropes on this.

Anyway, I'm not sure how to combine your suggestion with my currently working
code & make the code do both "For intStep = 1 To intCount" and "For i =
BeginVerizonPair To EndVerizonPair" statements?

Is this the right Logic here:
1) My code correctly inserts X number of records into the table
2) Then your code runs & populates the Range of Values?

I have added my Dim statements:
---------------------------------------------
Dim VRZPair As String
Dim BeginVerizonPair As String
Dim EndVerizonPair As String
---------------------------------------------
but not sure what to do next.

Do I add your code after my "Next intStep" line?

Thanks again,
cw

Hi,

add the field in the list field, and the counter, in the values:

...
For i = beginValue To endValue
DoCmd.RunSQL "INSERT INTO ... (..., VrzPair ) VALUES( ... ," & i & ")",
dbFailOnError
Next i

Hoping it may help,
Vanderghast, Access MVP

I'm using the following code to automatically create "X" records in my
database:

[quoted text clipped - 56 lines]

Thanks again for all your help,
cw

 

[Michel Walsh]

Hi,


If I take the last lines of your code, they will become ( I added <--- to
signal a modification) :


Set dbCurr = CurrentDb()

For intStep = BEGINVALUE To ENDINGVALUE ' <------

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

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub




that should insert ENDINGVALUE - BEGINBVALUE + 1 records, with
sequential values, under the field YOUR_FIELD_NAME_HERE




Hoping it may help,
Vanderghast, Access MVP

Michael, Thanks for the reply!

This is the second time I have used VB to insert records automatically so
I'm
still learning the ropes on this.

Anyway, I'm not sure how to combine your suggestion with my currently
working
code & make the code do both "For intStep = 1 To intCount" and "For i =
BeginVerizonPair To EndVerizonPair" statements?

Is this the right Logic here:
1) My code correctly inserts X number of records into the table
2) Then your code runs & populates the Range of Values?

I have added my Dim statements:
---------------------------------------------
Dim VRZPair As String
Dim BeginVerizonPair As String
Dim EndVerizonPair As String
---------------------------------------------
but not sure what to do next.

Do I add your code after my "Next intStep" line?

Thanks again,
cw

Hi,

add the field in the list field, and the counter, in the values:

...
For i = beginValue To endValue
DoCmd.RunSQL "INSERT INTO ... (..., VrzPair ) VALUES( ... ," & i &
")",
dbFailOnError
Next i

Hoping it may help,
Vanderghast, Access MVP

I'm using the following code to automatically create "X" records in my
database:

[quoted text clipped - 56 lines]

Thanks again for all your help,
cw

 

[cw via AccessMonster.com]

Michael, Thanks for the post!
I updated the code with my values & ran the code but am stuck with the
following message:
Run-time error 3346: number of query values and destination fields are not
the same

Question: I commented out the line 'intCount = Me.HowMany but it makes no
difference?
Does your code take into account my section on How Many records I want to
create?
I realize now that when my Form asks for BeginVerizonPair To EndVerizonPair I
won't need to ask the How Many question...right? It should just create
however many records are in the range?

Anyway, I would appreciate your help again. I'm starting to understand strSQL
much better though.
Thanks,
cw
--------------------------------------
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 VRZPair As Integer
Dim BeginVerizonPair As Integer
Dim EndVerizonPair As Integer

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 = BeginVerizonPair To EndVerizonPair ' <------

strSQL = "INSERT INTO tblCPx(CityID, ColloCityID, " & "NodeID, CalixTypeID,
SerTypID, CWCPtypeID, strModifiedBy,VRZPair) VALUES(" & CityID & ", " &
ColloCityID & ", " & NodeID & ", " & CalixTypeID & ", " & SerTypID & ", " &
CWCPtypeID & ",'" & strModifiedBy & "," & intStep & "')"
dbCurr.Execute strSQL, dbFailOnError

Next intStep

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

Hi,

If I take the last lines of your code, they will become ( I added <--- to
signal a modification) :

Set dbCurr = CurrentDb()

For intStep = BEGINVALUE To ENDINGVALUE ' <------

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

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

that should insert ENDINGVALUE - BEGINBVALUE + 1 records, with
sequential values, under the field YOUR_FIELD_NAME_HERE

Hoping it may help,
Vanderghast, Access MVP

Michael, Thanks for the reply!

[quoted text clipped - 43 lines]

 

[Michel Walsh]

Hi,


The error occur if the number of fields is not the same as the number of
values. In this case, we intend to supply 8 values, but something can go
wrong, like a decimal number printed with a coma rather than with a dot as
decimal delimiter, a string containing a quote, and so on.

can you


Debug.Print strSQL



just before

dbCurr.Execute strSQL, dbFailOnError



and cut and paste the result (from the Debug Immediate Window, Ctrl_G),
here, so we can take a look at it ?




Hoping it may help,
Vanderghast, Access MVP

Michael, Thanks for the post!
I updated the code with my values & ran the code but am stuck with the
following message:
Run-time error 3346: number of query values and destination fields are not
the same

Question: I commented out the line 'intCount = Me.HowMany but it makes no
difference?
Does your code take into account my section on How Many records I want to
create?
I realize now that when my Form asks for BeginVerizonPair To
EndVerizonPair I
won't need to ask the How Many question...right? It should just create
however many records are in the range?

Anyway, I would appreciate your help again. I'm starting to understand
strSQL
much better though.
Thanks,
cw
--------------------------------------
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 VRZPair As Integer
Dim BeginVerizonPair As Integer
Dim EndVerizonPair As Integer

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 = BeginVerizonPair To EndVerizonPair ' <------

strSQL = "INSERT INTO tblCPx(CityID, ColloCityID, " & "NodeID,
CalixTypeID,
SerTypID, CWCPtypeID, strModifiedBy,VRZPair) VALUES(" & CityID & ", " &
ColloCityID & ", " & NodeID & ", " & CalixTypeID & ", " & SerTypID & ", "
&
CWCPtypeID & ",'" & strModifiedBy & "," & intStep & "')"
dbCurr.Execute strSQL, dbFailOnError

Next intStep

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

Hi,

If I take the last lines of your code, they will become ( I added <---
to
signal a modification) :

Set dbCurr = CurrentDb()

For intStep = BEGINVALUE To ENDINGVALUE ' <------

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

dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

that should insert ENDINGVALUE - BEGINBVALUE + 1 records, with
sequential values, under the field YOUR_FIELD_NAME_HERE

Hoping it may help,
Vanderghast, Access MVP

Michael, Thanks for the reply!

[quoted text clipped - 43 lines]

Thanks again for all your help,
cw

 

[cw via AccessMonster.com]

Here is the Debug results:

INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy,VRZPair) VALUES(2, 2, 3, 1, 1, 2,'jalexander,0')

Hi,

The error occur if the number of fields is not the same as the number of
values. In this case, we intend to supply 8 values, but something can go
wrong, like a decimal number printed with a coma rather than with a dot as
decimal delimiter, a string containing a quote, and so on.

can you

Debug.Print strSQL

just before

dbCurr.Execute strSQL, dbFailOnError

and cut and paste the result (from the Debug Immediate Window, Ctrl_G),
here, so we can take a look at it ?

Hoping it may help,
Vanderghast, Access MVP

Michael, Thanks for the post!
I updated the code with my values & ran the code but am stuck with the

[quoted text clipped - 99 lines]

 

[Michel Walsh]

Hi,


ah. 'jalexander,0' is ONE value, not TWO, as intended. It should be
'jalexander',0

so, instead of
....",'" & strModifiedBy & "," & intStep & "')"

try
",'" & strModifiedBy & ","' & intStep & ")"




Hoping it may help,
Vanderghast, Access MVP

Here is the Debug results:

INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy,VRZPair) VALUES(2, 2, 3, 1, 1, 2,'jalexander,0')

Hi,

The error occur if the number of fields is not the same as the number of
values. In this case, we intend to supply 8 values, but something can go
wrong, like a decimal number printed with a coma rather than with a dot as
decimal delimiter, a string containing a quote, and so on.

can you

Debug.Print strSQL

just before

dbCurr.Execute strSQL, dbFailOnError

and cut and paste the result (from the Debug Immediate Window, Ctrl_G),
here, so we can take a look at it ?

Hoping it may help,
Vanderghast, Access MVP

Michael, Thanks for the post!
I updated the code with my values & ran the code but am stuck with the

[quoted text clipped - 99 lines]

Thanks again for all your help,
cw

 

[cw via AccessMonster.com]

Ok, I adjusted that line & now get the following Debug results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy, VRZPair) VALUES(2,2,3,1,1,2,jalexander,0)

with Run-time error 3061 : Too few parameters. Expected 1.


Here is my complete code thus far:
--------------------------------------------------
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 VRZPair As Integer
Dim BeginVerizonPair As Integer
Dim EndVerizonPair As Integer

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 = BeginVerizonPair To EndVerizonPair ' <------

strSQL = "INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID,
SerTypID, CWCPtypeID, strModifiedBy, VRZPair) VALUES(" & CityID & "," &
ColloCityID & "," & NodeID & "," & CalixTypeID & "," & SerTypID & "," &
CWCPtypeID & "," & strModifiedBy & "," & intStep & ")"
Debug.Print strSQL
dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

-------------------------------------------------------

Hi,

ah. 'jalexander,0' is ONE value, not TWO, as intended. It should be
'jalexander',0

so, instead of
...",'" & strModifiedBy & "," & intStep & "')"

try
",'" & strModifiedBy & ","' & intStep & ")"

Hoping it may help,
Vanderghast, Access MVP

Here is the Debug results:

[quoted text clipped - 27 lines]

 

[Michel Walsh]

Hi,




you still need the single quotes around jalexander, else, it is assumed to
be a field name, not a string constant.


You typed:

.... CWCPtypeID & "," & strModifiedBy & "," & intStep & ")"

while it should be

.... CWCPtypeID & ",'" & strModifiedBy & "'," & intStep & ")"


The single quote is easy to miss, it is "glued" to a ". If I add a space (2
places) :

.... CWCPtypeID & ",' " & strModifiedBy & " '," & intStep & ")"

may make it easier to spot, but the real syntax is without space.



Hoping it may help,
Vanderghast, Access MVP

Ok, I adjusted that line & now get the following Debug results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy, VRZPair) VALUES(2,2,3,1,1,2,jalexander,0)

with Run-time error 3061 : Too few parameters. Expected 1.


Here is my complete code thus far:
--------------------------------------------------
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 VRZPair As Integer
Dim BeginVerizonPair As Integer
Dim EndVerizonPair As Integer

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 = BeginVerizonPair To EndVerizonPair ' <------

strSQL = "INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID,
SerTypID, CWCPtypeID, strModifiedBy, VRZPair) VALUES(" & CityID & "," &
ColloCityID & "," & NodeID & "," & CalixTypeID & "," & SerTypID & "," &
CWCPtypeID & "," & strModifiedBy & "," & intStep & ")"
Debug.Print strSQL
dbCurr.Execute strSQL, dbFailOnError

Next intStep

End Sub

-------------------------------------------------------

Hi,

ah. 'jalexander,0' is ONE value, not TWO, as intended. It should be
'jalexander',0

so, instead of
...",'" & strModifiedBy & "," & intStep & "')"

try
",'" & strModifiedBy & ","' & intStep & ")"

Hoping it may help,
Vanderghast, Access MVP

Here is the Debug results:

[quoted text clipped - 27 lines]

Thanks again for all your help,
cw

 

[cw via AccessMonster.com]

Michel, It ran without any errors(Great !) but only created 1 record in the
database and the 1 record has a value of 0 for VRZPair.

The Debug code results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy, VRZPair) VALUES(2,2,3,1,1,2,'jalexander',0)

On my Form I entered 201 & 301 in my 2 textboxes so there seems to be an
issue with the:

For intStep = BeginVerizonPair To EndVerizonPair

Question:
1) Is it ok to have the Text Boxes named BeginVerizonPair & EndVerizonPair?
2) Have I declared them properly in the code ?

I do appreciate your assistance & had never used Debug before this.
Thanks,
cw

Hi,

you still need the single quotes around jalexander, else, it is assumed to
be a field name, not a string constant.

You typed:

... CWCPtypeID & "," & strModifiedBy & "," & intStep & ")"

while it should be

... CWCPtypeID & ",'" & strModifiedBy & "'," & intStep & ")"

The single quote is easy to miss, it is "glued" to a ". If I add a space (2
places) :

... CWCPtypeID & ",' " & strModifiedBy & " '," & intStep & ")"

may make it easier to spot, but the real syntax is without space.

Hoping it may help,
Vanderghast, Access MVP

Ok, I adjusted that line & now get the following Debug results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,

[quoted text clipped - 68 lines]

 

[cw via AccessMonster.com]

I found the problem! I commented out the lines:
'Dim BeginVerizonPair As Integer
'Dim EndVerizonPair As Integer

and it correctly added the 100 records.

Michel, You have done a tremendous service here.
Thanks again for helping a novice move forward on more step.
cw

Michel, It ran without any errors(Great !) but only created 1 record in the
database and the 1 record has a value of 0 for VRZPair.

The Debug code results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy, VRZPair) VALUES(2,2,3,1,1,2,'jalexander',0)

On my Form I entered 201 & 301 in my 2 textboxes so there seems to be an
issue with the:

For intStep = BeginVerizonPair To EndVerizonPair

Question:
1) Is it ok to have the Text Boxes named BeginVerizonPair & EndVerizonPair?
2) Have I declared them properly in the code ?

I do appreciate your assistance & had never used Debug before this.
Thanks,
cw

Hi,

[quoted text clipped - 24 lines]

 

[Michel Walsh]

Hi,


if you used:

For intStep = BeginVerizonPair To EndVerizonPair


with 101 in control BeginVerizonPair, and it ends up that

strSQL = "INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID,
SerTypID, CWCPtypeID, strModifiedBy, VRZPair) VALUES(" & CityID & "," &
ColloCityID & "," & NodeID & "," & CalixTypeID & "," & SerTypID & "," &
CWCPtypeID & "," & strModifiedBy & "," & intStep & ")"



prints with a 0 in position where intStep should supply the value, then,
clearly, there is a problem of what is what. Do you have AND a control AND a
variable with the name BeginVerizonPair? Or maybe your code is not at the
right place. Have you an OPTION EXPLICIT as first line of the VBA code
module? Maybe there is a typo and your control names are similar but NOT
exactly BeginVerizonPair neither EndVerizonPair.


Anyhow, you can always type, instead of

For intStep = BeginVerizonPair To EndVerizonPair

something like:

For intStep = 101 To 109

and you should get 9 new records. I know that is not what you want, but that
would CLEARLY show that the problem is NOT in the SQL statement, if that
works, but withing your VBA code, and more precisely with the "variables"
BeginVerizonPair and EndVerizonPair.



Hoping it may help,
Vanderghast, Access MVP

Michel, It ran without any errors(Great !) but only created 1 record in
the
database and the 1 record has a value of 0 for VRZPair.

The Debug code results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,
CWCPtypeID, strModifiedBy, VRZPair) VALUES(2,2,3,1,1,2,'jalexander',0)

On my Form I entered 201 & 301 in my 2 textboxes so there seems to be an
issue with the:

For intStep = BeginVerizonPair To EndVerizonPair

Question:
1) Is it ok to have the Text Boxes named BeginVerizonPair &
EndVerizonPair?
2) Have I declared them properly in the code ?

I do appreciate your assistance & had never used Debug before this.
Thanks,
cw

Hi,

you still need the single quotes around jalexander, else, it is assumed to
be a field name, not a string constant.

You typed:

... CWCPtypeID & "," & strModifiedBy & "," & intStep & ")"

while it should be

... CWCPtypeID & ",'" & strModifiedBy & "'," & intStep & ")"

The single quote is easy to miss, it is "glued" to a ". If I add a space
(2
places) :

... CWCPtypeID & ",' " & strModifiedBy & " '," & intStep & ")"

may make it easier to spot, but the real syntax is without space.

Hoping it may help,
Vanderghast, Access MVP

Ok, I adjusted that line & now get the following Debug results:
INSERT INTO tblCPx(CityID, ColloCityID, NodeID, CalixTypeID, SerTypID,

[quoted text clipped - 68 lines]

Thanks again for all your help,
cw