Adding data from code turns into geometric progression

[Bruce]

Hi, I have the following code with the purpose of setting a header made up of
an autonumber [ID] and a text string [DESCRIPTION] in my table tbl_Grade_Hdr.
When I run my code it should create a record and take the text string and add
to the [DESCRIPTION] field. [ID] looks after itself since its an autonumber.

This sort of works but with the following side effects.
If my table is empty it will not create a record. If I manually add a fake
one, then it works once.
The second time its run, it adds not 1 but 2 records, next time 4, 8 and so
on. A geometric progression.

How is this happening? What can I do to fix it and run as I intend?

See code below...

Bruce

Sub test()

'Set HDR
Dim strInput As String, strMsg As String

strMsg = "Enter a Description for the data that is being imported"
strInput = InputBox(Prompt:=strMsg, Title:="User Info", XPos:=2000,
YPos:=2000)

Msg = "Do you want to continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Response = MsgBox(Msg, Style)
If Response = vbYes Then
MyString = "Yes"
ElseIf Response = vbNo Then
MyString = "No"
Exit Sub
Else
MsgBox ("An error occurred...")
Exit Sub
End If

mySQL = "INSERT INTO tbl_Grade_Hdr ( DESCRIPTION ) SELECT '" & strInput
& "' FROM tbl_Grade_Hdr;"
CurrentDb.Execute mySQL

End Sub

 

[mcescher]

Hi, I have the following code with the purpose of setting a header made up of
an autonumber [ID] and a text string [DESCRIPTION] in my table tbl_Grade_Hdr.
When I run my code it should create a record and take the text string and add
to the [DESCRIPTION] field. [ID] looks after itself since its an autonumber.

This sort of works but with the following side effects.
If my table is empty it will not create a record. If I manually add a fake
one, then it works once.
The second time its run, it adds not 1 but 2 records, next time 4, 8 and so
on. A geometric progression.

How is this happening? What can I do to fix it and run as I intend?

See code below...

Bruce

Sub test()

'Set HDR
Dim strInput As String, strMsg As String

strMsg = "Enter a Description for the data that is being imported"
strInput = InputBox(Prompt:=strMsg, Title:="User Info", XPos:=2000,
YPos:=2000)

Msg = "Do you want to continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Response = MsgBox(Msg, Style)
If Response = vbYes Then
MyString = "Yes"
ElseIf Response = vbNo Then
MyString = "No"
Exit Sub
Else
MsgBox ("An error occurred...")
Exit Sub
End If

mySQL = "INSERT INTO tbl_Grade_Hdr ( DESCRIPTION ) SELECT '" & strInput
& "' FROM tbl_Grade_Hdr;"
CurrentDb.Execute mySQL

End Sub

You're pulling FROM tbl_Grade_Hdr, so the first time it pulls one
record and appends it, then two records exist. The second time it
pulls two records to append, and you end up with four, etc...

Try this:
mySQL = "INSERT INTO tbl_Grade_Hdr (DESCRIPTION) SELECT '" &
strInput & "' AS [Desc]; "

HTH,

Chris M.

 

[John Spencer]

Try using the alternative syntax for the INSERT query

mySQL = "INSERT INTO tbl_Grade_Hdr ( DESCRIPTION ) Values( '" & strInput &
"' ) "

Your current query is going to add one record for each record that is
already in the table. So each time you run your current query it will
DOUBLE the number of records in the table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..