Help using data stored in strings

[anon]

Hello,

Below I have the VBA code posted for a module I'm trying to
create. I want the module to ask the user which type of
records they want to update (A, B, or C) and then
repeatedly ask them which record they want to update. The
record type is a field in the table (FieldOne), along with
the record number (FieldTwo), and a True/False field
(FieldThree). The problem I'm having is that once the user
has selected the type of records they want to update, they
can still enter a record number which has a different
record type and it will still update that record. That
prompted me to try and throw in a few if statements to
check and make sure that the record type of the record
number entered to update, is the same as their original
choice. This is where I'm having problems.

Below I have the line "If ???? = strType Then". I have
tried to enter several things in place of the ???? such as
"If TableName.FieldOne = strType Then" etc. where FieldOne
is the record type of the current record the user chose,
and strType has the original type of records stored that
the user wants to update. This is where I want the if
statement to check and make sure that the record types are
the same (type of current record compared to type
originally stated in strType) and I can't seem to get this
to work at all. If any of you could help with what to
place on this line it would be GREATLY appreciated. Thanks
in advance!

-----------------------------------------------------------
Public Sub RunQueryUntilDone()

Dim strSQL As String
Dim dbs As DAO.Database
Dim strType As String
Dim strID As String
Set dbs = CurrentDb

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

Do
strID = InputBox("Enter Record ID or enter nothing
to stop:")
If strID <> "" Then
strSQL = "SELECT TableName.FieldTwo,
TableName.FieldOne" & _
"FROM TableName" & _
"WHERE [FieldTwo]=" & strID & ";"
If ???? = strType Then
strSQL = "UPDATE TableName SET
FieldThree = True " & _
"WHERE [FieldTwo]=" & strID & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "Record type of choice does not
match the type of records you selected to update"
End If
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
----------------------------------------------------------

 

[Alex Dybenko]

as i understand you - try the following:

dim strFieldOne as string
strSQL = "SELECT TableName.FieldOne" & _
"FROM TableName" & _
"WHERE [FieldTwo]=" & strID & ";"
'better to check fisrt if record exist, but below ok to show idea...
strFieldOne=dbs.execute(strSQL).fields(0) & ""

If strFieldOne = strType Then
strSQL = "UPDATE TableName SET FieldThree = True " & _
"WHERE [FieldTwo]=" & strID & ";"
dbs.Execute strSQL, dbFailOnError
....

HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hello,

Below I have the VBA code posted for a module I'm trying to
create. I want the module to ask the user which type of
records they want to update (A, B, or C) and then
repeatedly ask them which record they want to update. The
record type is a field in the table (FieldOne), along with
the record number (FieldTwo), and a True/False field
(FieldThree). The problem I'm having is that once the user
has selected the type of records they want to update, they
can still enter a record number which has a different
record type and it will still update that record. That
prompted me to try and throw in a few if statements to
check and make sure that the record type of the record
number entered to update, is the same as their original
choice. This is where I'm having problems.

Below I have the line "If ???? = strType Then". I have
tried to enter several things in place of the ???? such as
"If TableName.FieldOne = strType Then" etc. where FieldOne
is the record type of the current record the user chose,
and strType has the original type of records stored that
the user wants to update. This is where I want the if
statement to check and make sure that the record types are
the same (type of current record compared to type
originally stated in strType) and I can't seem to get this
to work at all. If any of you could help with what to
place on this line it would be GREATLY appreciated. Thanks
in advance!

-----------------------------------------------------------
Public Sub RunQueryUntilDone()

Dim strSQL As String
Dim dbs As DAO.Database
Dim strType As String
Dim strID As String
Set dbs = CurrentDb

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

Do
strID = InputBox("Enter Record ID or enter nothing
to stop:")
If strID <> "" Then
strSQL = "SELECT TableName.FieldTwo,
TableName.FieldOne" & _
"FROM TableName" & _
"WHERE [FieldTwo]=" & strID & ";"
If ???? = strType Then
strSQL = "UPDATE TableName SET
FieldThree = True " & _
"WHERE [FieldTwo]=" & strID & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "Record type of choice does not
match the type of records you selected to update"
End If
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
----------------------------------------------------------

 

[Marshall Barton]

Below I have the VBA code posted for a module I'm trying to
create. I want the module to ask the user which type of
records they want to update (A, B, or C) and then
repeatedly ask them which record they want to update. The
record type is a field in the table (FieldOne), along with
the record number (FieldTwo), and a True/False field
(FieldThree). The problem I'm having is that once the user
has selected the type of records they want to update, they
can still enter a record number which has a different
record type and it will still update that record. That
prompted me to try and throw in a few if statements to
check and make sure that the record type of the record
number entered to update, is the same as their original
choice. This is where I'm having problems.

Below I have the line "If ???? = strType Then". I have
tried to enter several things in place of the ???? such as
"If TableName.FieldOne = strType Then" etc. where FieldOne
is the record type of the current record the user chose,
and strType has the original type of records stored that
the user wants to update. This is where I want the if
statement to check and make sure that the record types are
the same (type of current record compared to type
originally stated in strType) and I can't seem to get this
to work at all. If any of you could help with what to
place on this line it would be GREATLY appreciated. Thanks
in advance!

-----------------------------------------------------------
Public Sub RunQueryUntilDone()

Dim strSQL As String
Dim dbs As DAO.Database
Dim strType As String
Dim strID As String
Set dbs = CurrentDb

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

Do
strID = InputBox("Enter Record ID or enter nothing
to stop:")
If strID <> "" Then
strSQL = "SELECT TableName.FieldTwo,
TableName.FieldOne" & _
"FROM TableName" & _
"WHERE [FieldTwo]=" & strID & ";"
If ???? = strType Then
strSQL = "UPDATE TableName SET
FieldThree = True " & _
"WHERE [FieldTwo]=" & strID & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "Record type of choice does not
match the type of records you selected to update"
End If
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
----------------------------------------------------------

You could just update records that match both criteria and
check if any updates were done. Here's some air code to
give you the idea:

. . .
If strID <> "" Then
strSQL = "UPDATE TableName " _
& "SET FieldThree = True " _
& "WHERE [FieldTwo] = " & strID _
& " And FieldOne = '" & strType & "'"
dbs.Execute strSQL, dbFailOnError
If dbs.RecordsAffected = 0 Then
MsgBox "Invalid Type and ID combination"
End If
Else
Exit Do
End If
. . .