Type Mismatch

[osmondb]

Hi all,

I'm extremely new to VB, let alone VB with Access. I
started off by trying to create a query that would "loop"
(repeat) but I was told that it was better to include the
SQL code into the VBA code.

First off, I have a table with three fields...a Type (A, B,
or C), then a reference number for each record, and a
true/false field. I want to be able to run the module and
it asks "which type of records would you like to update, a
b or c ...) and then once this is selected, you just keep
entering the reference numbers for each record and the
true/false field is automatically set to True. The code I
have so far is:
-------------------------------------------
Public Sub RunQueryUntilDone()
Dim strSQL As String
Dim dbs As DAO.Database
Dim strID As String
Set dbs = CurrentDb
strID = "starting"
strID = InputBox("Enter Type of Records You Want To
Update (A, B, or C):")
If strID <> "" Then
strSQL = "SELECT TypeIdBool.FieldOne,
TypeIdBool.FieldTwo, TypeIdBool.FieldThree" & _
"FROM TypeIdBool" & _
"WHERE (((TypeIdBool.FieldOne)=))" &
CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
End If
Do
strID = InputBox("Enter Record ID or enter nothing
to stop:")
If strID <> "" Then
strSQL = "UPDATE TypeIdBool SET FieldThree =
True " & _
"WHERE [FieldTwo]=" & CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
-------------------------------------------

As I said, I'm extremely new to VB...most of this code was
generated by an MVP on another newsgroup, I just edited it
and tailored it to suit my database. TypeIdBool is the
table name, FieldOne is the record type (a, b or c),
FieldTwo is the record number, and FieldThree is the
true/false field. Whenever I run the database, I'm getting
an error on Line 10 (first strSQL) that says Run-Time Error
13, Type Mismatch and I have no idea how to get this code
to run =\ . Any and all help/input is appreciated! Thanks
a bunch!

OsmondB

 

[MDW]

If you're referencing a string/text column in the data, you need to use single quotes in your WHERE clause. It looks like the code you pilfered was refrencing a numeric column.

Try this new statement:

strSQL = "SELECT TypeIdBool.FieldOne,
TypeIdBool.FieldTwo, TypeIdBool.FieldThree" & _
"FROM TypeIdBool" & _
"WHERE TypeIdBool.FieldOne)='" & strID & "'"

Also, I would suggest you implement a more foolproof approach to validating user input.

strID = UCase(InputBox("Enter Type of Records You Want To
Update (A, B, or C):"))
Select Case Trim(strID)

Case "A","B","C"

strSQL = "SELECT TypeIdBool.FieldOne,
TypeIdBool.FieldTwo, TypeIdBool.FieldThree" & _
"FROM TypeIdBool" & _
"WHERE TypeIdBool.FieldOne)='" & strID & "'"
dbs.Execute strSQL, dbFailOnError

Case Else
MsgBox "No value entered. Query ending now."

End Select

The way your code is currently structured, the user could enter anything (A,B,C,G,6,!,##,L,etc.) and the system would still accept it.

Hi all,

I'm extremely new to VB, let alone VB with Access. I
started off by trying to create a query that would "loop"
(repeat) but I was told that it was better to include the
SQL code into the VBA code.

First off, I have a table with three fields...a Type (A, B,
or C), then a reference number for each record, and a
true/false field. I want to be able to run the module and
it asks "which type of records would you like to update, a
b or c ...) and then once this is selected, you just keep
entering the reference numbers for each record and the
true/false field is automatically set to True. The code I
have so far is:
-------------------------------------------
Public Sub RunQueryUntilDone()
Dim strSQL As String
Dim dbs As DAO.Database
Dim strID As String
Set dbs = CurrentDb
strID = "starting"
strID = InputBox("Enter Type of Records You Want To
Update (A, B, or C):")
If strID <> "" Then
strSQL = "SELECT TypeIdBool.FieldOne,
TypeIdBool.FieldTwo, TypeIdBool.FieldThree" & _
"FROM TypeIdBool" & _
"WHERE (((TypeIdBool.FieldOne)=))" &
CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
End If
Do
strID = InputBox("Enter Record ID or enter nothing
to stop:")
If strID <> "" Then
strSQL = "UPDATE TypeIdBool SET FieldThree =
True " & _
"WHERE [FieldTwo]=" & CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
-------------------------------------------

As I said, I'm extremely new to VB...most of this code was
generated by an MVP on another newsgroup, I just edited it
and tailored it to suit my database. TypeIdBool is the
table name, FieldOne is the record type (a, b or c),
FieldTwo is the record number, and FieldThree is the
true/false field. Whenever I run the database, I'm getting
an error on Line 10 (first strSQL) that says Run-Time Error
13, Type Mismatch and I have no idea how to get this code
to run =\ . Any and all help/input is appreciated! Thanks
a bunch!

OsmondB

 

[Wayne Morgan]

Ok, a few things.

1) CLng(strID), strID should be A, B, or C. To convert to a Long Integer,
this would have to be a number stored as a string, not a letter. Also, what
data type is TypeIdBool.FieldOne?

2) Your first SQL statement is a SELECT statement. You then try to Execute
this statement. The Execute method will only work on Action Queries (update,
append, make table, delete).

3) The actual work will be done by your second SQL statement. However, there
is no need to put this in a Do loop. The query will automatically go through
all the records in the table, updating the ones that match the criteria.

4) At the end you have "dbs.Close" and "Set dbs = Nothing". You would close
a recordset, but no the reference to the database. Just the "Set dbs =
Nothing" is sufficient. The general rule is, "if you open it, close it and
if you set it, set it to Nothing." This would also mean that if you don't
open it, don't close it.

5) <<strSQL = "UPDATE TypeIdBool SET FieldThree =True " & _
"WHERE [FieldTwo]=" & CLng(strID) & ";">>

For this to work, FieldTwo would need to contain the values A, B, or C. The
WHERE clause should be modified to

"WHERE [FieldTwo]=""" & strID & """;"

This will put quotes around the text value strID in the result.
Example result:
WHERE [FieldTwo]="A";

6) In your If statements, you check for an entry (If strID <> "" Then), but
you don't verify a correct entry of A, B, or C.

--
Wayne Morgan
Microsoft Access MVP

Hi all,

I'm extremely new to VB, let alone VB with Access. I
started off by trying to create a query that would "loop"
(repeat) but I was told that it was better to include the
SQL code into the VBA code.

First off, I have a table with three fields...a Type (A, B,
or C), then a reference number for each record, and a
true/false field. I want to be able to run the module and
it asks "which type of records would you like to update, a
b or c ...) and then once this is selected, you just keep
entering the reference numbers for each record and the
true/false field is automatically set to True. The code I
have so far is:
-------------------------------------------
Public Sub RunQueryUntilDone()
Dim strSQL As String
Dim dbs As DAO.Database
Dim strID As String
Set dbs = CurrentDb
strID = "starting"
strID = InputBox("Enter Type of Records You Want To
Update (A, B, or C):")
If strID <> "" Then
strSQL = "SELECT TypeIdBool.FieldOne,
TypeIdBool.FieldTwo, TypeIdBool.FieldThree" & _
"FROM TypeIdBool" & _
"WHERE (((TypeIdBool.FieldOne)=))" &
CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
End If
Do
strID = InputBox("Enter Record ID or enter nothing
to stop:")
If strID <> "" Then
strSQL = "UPDATE TypeIdBool SET FieldThree =
True " & _
"WHERE [FieldTwo]=" & CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
-------------------------------------------

As I said, I'm extremely new to VB...most of this code was
generated by an MVP on another newsgroup, I just edited it
and tailored it to suit my database. TypeIdBool is the
table name, FieldOne is the record type (a, b or c),
FieldTwo is the record number, and FieldThree is the
true/false field. Whenever I run the database, I'm getting
an error on Line 10 (first strSQL) that says Run-Time Error
13, Type Mismatch and I have no idea how to get this code
to run =\ . Any and all help/input is appreciated! Thanks
a bunch!

OsmondB