Procedure with 2 record sets one select, one update
Trying to do some data cleanup by looking up a field, doing using some
string functions to clean it up, then updating the record. I tried to do
this by opening one recordset with a select statement, then modifying the
variables, updating the record by opening a second record set. All my
variable values work great, and I am able to build the proper update
statement, by I get and "invalid use of property" when I try to use:
set RS1 = db.OpenRecordset(UpdateString)
where the UpdateString is the value of the update sql statement. Any help is
appreciated. My code is attached below.
Private Sub Garret()
Dim db As Database
Dim RS As Recordset
Dim RS1 As Recordset 'second recordset to do the record update
Dim Count As Integer
Dim SelectString As String 'SQL string to select all the records from the
table
Dim UpdateString As String 'SQL string to update the records with the new
job id
Dim NewJobId As String 'Right4 plus the right left RMJ, F, WRJ
Dim Right4 As String '4 characters from right of jobID
Dim Left1 As String 'First character of job id to determine if it is
rmj, f, or wrj
SelectString = "SELECT * FROM garret Where [Job Number] is Not Null"
Set db = CurrentDb()
Set RS = db.OpenRecordset(SelectString)
Do While Not RS.EOF
'Cut the rightmost 4 characters from JOBID
Right4 = Right(RS![Job Number], 4)
'Determine what the starting character of the JOBID is
Left1 = Left(RS![Job Number], 1)
'MsgBox ("left1 =" & Left1 & " Righ4 =" & Right4)
'Add the appropriate begin to the right4 depend if it is an
outofwarranty RMJ in warranty wrJ or F
If Left1 = "R" Then
NewJobId = "RMJ" & Right4
ElseIf Left1 = "W" Then
NewJobId = "WRJ" & Right4
ElseIf Left1 = "F" Then
NewJobId = "F" & Right4
Else
NewJobId = RS![Job Number]
End If
'MsgBox ("NewJobID = " & NewJobId)
'Create the UPDATE string to update the record with the NewJobID
UpdateString = "Update Garret Set Garret![Job Number] = "
UpdateString = UpdateString & "'" & NewJobId & "'" & " Where Garret![Job
Number] = " & "'" & RS![Job Number] & "';"
'MsgBox (UpdateString)
Set RS1 = db.OpenRecordset(UpdateString)
RS1.Close
RS.MoveNext
Loop
RS.Close
End Sub
aka G
