G
Guest
I am doing a loop and updating copied numbers per the highest number where
the first two sections are the same. I am finding that if I have added a new
number with my update query that number is not found when I do the dlookup?
How can I get the dlookup to find the updates I have just run? Below is this
part of my code. If I have existing numbers C-1000-120 and C-1000-121 and
C-2222-333, I am trying to give my copied record the greatest number where
the first two sections match i.e. zC-1000- my next number would be
C-1000-122. After that update if I encounter another zC-1000 number would be
C-1000-123 and I would need to update the zC-100-120 to C-100-123, but the
dlookup does not find the C-1000-122 that my last loop added to the table, it
places another C-1000-122. Any help?
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
If Me.Filter = "" Then
strSql = "Select circuit from elecircuitlist where ((circuit like 'z%'));"
Else
strSql = "Select circuit from elecircuitlist where ((circuit) like 'z%' and
" & strFilter & "')"
End If
rs.Open strSql, cn, 1
Do While rs.EOF = False
strCirc = rs!Circuit
'first need to check for unique number
Set db = CurrentDb()
strNewCirc = Right(strCirc, Len(strCirc) - 1)
strFiltCirc = Left(strNewCirc, Len(strNewCirc) - 3)
strSql1 = "circuit = '" & strNewCirc & "'"
If IsNull(DLookup("[circuit]", "[Elecircuitlist]", strSql1)) Then
strNewCirc = strNewCirc
Else
strSql1 = "Select voltage from elecircuitlist where circuit
like '" & strFiltCirc & "%' order by cint(voltage) desc"
rs1.Open strSql1, cn, 1
strVolt = rs1!Voltage + 1
strNewCirc = strFiltCirc & strVolt
rs1.Close
End If
strSql1 = "UPDATE elecircuitlist SET circuit = '" & strNewCirc & "'
Where circuit = '" & strCirc & "'"
db.Execute strSql1, dbFailOnError
Set db = Nothing
rs.MoveNext
Loop
the first two sections are the same. I am finding that if I have added a new
number with my update query that number is not found when I do the dlookup?
How can I get the dlookup to find the updates I have just run? Below is this
part of my code. If I have existing numbers C-1000-120 and C-1000-121 and
C-2222-333, I am trying to give my copied record the greatest number where
the first two sections match i.e. zC-1000- my next number would be
C-1000-122. After that update if I encounter another zC-1000 number would be
C-1000-123 and I would need to update the zC-100-120 to C-100-123, but the
dlookup does not find the C-1000-122 that my last loop added to the table, it
places another C-1000-122. Any help?
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
If Me.Filter = "" Then
strSql = "Select circuit from elecircuitlist where ((circuit like 'z%'));"
Else
strSql = "Select circuit from elecircuitlist where ((circuit) like 'z%' and
" & strFilter & "')"
End If
rs.Open strSql, cn, 1
Do While rs.EOF = False
strCirc = rs!Circuit
'first need to check for unique number
Set db = CurrentDb()
strNewCirc = Right(strCirc, Len(strCirc) - 1)
strFiltCirc = Left(strNewCirc, Len(strNewCirc) - 3)
strSql1 = "circuit = '" & strNewCirc & "'"
If IsNull(DLookup("[circuit]", "[Elecircuitlist]", strSql1)) Then
strNewCirc = strNewCirc
Else
strSql1 = "Select voltage from elecircuitlist where circuit
like '" & strFiltCirc & "%' order by cint(voltage) desc"
rs1.Open strSql1, cn, 1
strVolt = rs1!Voltage + 1
strNewCirc = strFiltCirc & strVolt
rs1.Close
End If
strSql1 = "UPDATE elecircuitlist SET circuit = '" & strNewCirc & "'
Where circuit = '" & strCirc & "'"
db.Execute strSql1, dbFailOnError
Set db = Nothing
rs.MoveNext
Loop