error in code

A

Arnie

Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL StrSQL it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie
 
D

Douglas J. Steele

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it, as in my example.
 
A

Arnie

ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select case
(which i got from you yesterday (Thank You)) it adds a new record, thats fine
exactly what i want
but as it goes through and gets to the next piece of data this also adds a
new record whereas i need it to stay on the same record just insert into the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

Douglas J. Steele said:
strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie
 
A

Arnie

sorry just saw your other comments. i used name for an example as i would
prefer not to publish what i am working on. Thanks for the comments much
appreciated.

Douglas J. Steele said:
strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie
 
D

Douglas J. Steele

My assumption was that you'd read the entire file and then execute as many
SQL statements as were needed. (i.e. if your data was to go to 1 row in a
single table, you'd have 1 INSERT INTO statement, if it was to go into two
separate tables, you'd have 2 INSERT INTO statements and so on)

Perhaps the recordset alternative I mentioned would be better for you:

Dim rsNew As DAO.Recordset
Dim booCreated As Boolean
Dim intFile As Integer
Dim intEqualSign As Integer
Dim strFile As String
Dim strBuffer As String
Dim strBefore As String
Dim strAfter As String
Dim strSQL As String

strSQL = "SELECT IPAddress, DefaultGateway, PreferredDNS " & _
"FROM MyTable"

Set dbNew = CurrentDb.CreateRecordset(strSQL)

strFile = "C:\Folder\File.abc"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
If booCreated = True Then
rsNew.Edit
Else
rsNew.AddNew
booCreated = True
End If
rsNew.Fields(strBefore) = strAfter
rsNew.Update
End If
Loop
Close #intFile
rsNew.Close
Set rsNew = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select case
(which i got from you yesterday (Thank You)) it adds a new record, thats
fine
exactly what i want
but as it goes through and gets to the next piece of data this also adds a
new record whereas i need it to stay on the same record just insert into
the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

Douglas J. Steele said:
strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my
May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square
brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch
window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL
StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie
 
A

Arnie

Douglas thanks for that any reason why CurrentDb.CreateRecordset(strSQL)
causes an error "Method or Data member not found it seems its the
"Createrecordset thats causing a problem.

Douglas J. Steele said:
My assumption was that you'd read the entire file and then execute as many
SQL statements as were needed. (i.e. if your data was to go to 1 row in a
single table, you'd have 1 INSERT INTO statement, if it was to go into two
separate tables, you'd have 2 INSERT INTO statements and so on)

Perhaps the recordset alternative I mentioned would be better for you:

Dim rsNew As DAO.Recordset
Dim booCreated As Boolean
Dim intFile As Integer
Dim intEqualSign As Integer
Dim strFile As String
Dim strBuffer As String
Dim strBefore As String
Dim strAfter As String
Dim strSQL As String

strSQL = "SELECT IPAddress, DefaultGateway, PreferredDNS " & _
"FROM MyTable"

Set dbNew = CurrentDb.CreateRecordset(strSQL)

strFile = "C:\Folder\File.abc"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
If booCreated = True Then
rsNew.Edit
Else
rsNew.AddNew
booCreated = True
End If
rsNew.Fields(strBefore) = strAfter
rsNew.Update
End If
Loop
Close #intFile
rsNew.Close
Set rsNew = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select case
(which i got from you yesterday (Thank You)) it adds a new record, thats
fine
exactly what i want
but as it goes through and gets to the next piece of data this also adds a
new record whereas i need it to stay on the same record just insert into
the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

Douglas J. Steele said:
strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my
May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square
brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch
window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL
StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie
 
D

Douglas J. Steele

Sorry, my typo. That should be CurrentDb.OpenRecordset(strSQL)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
Douglas thanks for that any reason why CurrentDb.CreateRecordset(strSQL)
causes an error "Method or Data member not found it seems its the
"Createrecordset thats causing a problem.

Douglas J. Steele said:
My assumption was that you'd read the entire file and then execute as
many
SQL statements as were needed. (i.e. if your data was to go to 1 row in a
single table, you'd have 1 INSERT INTO statement, if it was to go into
two
separate tables, you'd have 2 INSERT INTO statements and so on)

Perhaps the recordset alternative I mentioned would be better for you:

Dim rsNew As DAO.Recordset
Dim booCreated As Boolean
Dim intFile As Integer
Dim intEqualSign As Integer
Dim strFile As String
Dim strBuffer As String
Dim strBefore As String
Dim strAfter As String
Dim strSQL As String

strSQL = "SELECT IPAddress, DefaultGateway, PreferredDNS " & _
"FROM MyTable"

Set dbNew = CurrentDb.CreateRecordset(strSQL)

strFile = "C:\Folder\File.abc"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
If booCreated = True Then
rsNew.Edit
Else
rsNew.AddNew
booCreated = True
End If
rsNew.Fields(strBefore) = strAfter
rsNew.Update
End If
Loop
Close #intFile
rsNew.Close
Set rsNew = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arnie said:
ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select
case
(which i got from you yesterday (Thank You)) it adds a new record,
thats
fine
exactly what i want
but as it goes through and gets to the next piece of data this also
adds a
new record whereas i need it to stay on the same record just insert
into
the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

:

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter &
""")"

Note that that will fail if strAfter contains a double quote. Check my
May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access"
for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a
reserved
word, and using reserved words for your own purposes can lead to
issues.

For a comprehensive list of names to avoid (as well as a link to a
free
utility to check your application for compliance), see what Allen
Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square
brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,
can anyone help the code below seems to be working however, i am
being
asked for
the strAfter parameter Value. runing through the code in a watch
window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL
StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top