Dear Dan:
I was contemplating a response to your question. When I came to make a
response I saw you had already posted your solution.
My first impression was that your problem seemed to be one that could be
done with sequential processing between two recordsets in code, as you seem
to have found. No need for me to elaborate on that.
Another approach would be to use a non-procedural approach. Whether this
can be made to work would depend on the ability to uniquely rank the members
of the two sets, one set being the set of unused numbers from your "customer
number" table (there would seem to be no challenge in ranking those
uniquely) and the other being the set of newly imported customers. If you
can define a unique ordering for these that matches the way you want to
assign the customer numbers, you could then JOIN on the ranking between the
two to perform the desired assignments.
It might be that the above could be more efficient, as it uses Fourth
Generation Programming Language capabilities. If you don't have a
performance problem with the solution you have, you'll probably want to
leave well enough alone.
Either way you do it, you might want to give some thought to preventing the
simultaneous processing of two sets of new customers. With either approach
discussed, the simple solutions are going to be specifically single-user
approaches.
Tom Ellison
Solution, need to use VB. I hope someone else finds this code useful
because it took me all afternoon to code.
Public Sub BreakDownVacationCert()
'Opens recordset and searches for tracking #.
Dim PhoneNum As String
Dim AreaCode As String
Dim FirstName As String
Dim LastName As String
Dim CustomerNumber As String
Dim VacCheck As String
Dim sql1 As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim vacnumber
Dim count As Integer
Dim codes()
' Retrofit any orders that already have vacation Cert #'s
DoCmd.RunSQL "UPDATE tblImportCustomerNumber INNER JOIN tblvaccert ON
tblImportCustomerNumber.CustomerNumber = tblvaccert.[customer#] SET
tblImportCustomerNumber.[Vac#] =
Code:
;"
sql1 = "SELECT * From tblImport WHERE tblImport.[Vac#] is null"
rst.ActiveConnection = CurrentProject.Connection
rst.Open sql1
If rst.EOF Then
MsgBox "All the Records have already recieved a Vacation Cert # and
have been updated accordingly"
Exit Sub
End If
Do While Not rst.EOF And Not rst.BOF
count = count + 1
rst.MoveNext
Loop
rst.Close
ReDim codes(count)
sql1 = "SELECT Top " & count & " Code FROM [tblvaccert] WHERE
[tblvaccert].[used] is null;"
rst.ActiveConnection = CurrentProject.Connection
rst.Open sql1
If rst.EOF Then
MsgBox "There are no more vacation cert #'s left!"
Exit Sub
End If
Dim i As Integer
Do While Not rst.EOF And Not rst.BOF
codes(i) = rst("CODE")
rst.MoveNext
i = i + 1
Loop
rst.Close
sql1 = "SELECT * From tblImport WHERE tblImport.[Vac#] is null"
rst.ActiveConnection = CurrentProject.Connection
rst.Open sql1
If rst.EOF Then
MsgBox "All the Records have already recieved a Vacation Cert # and
have been updated accordingly"
Exit Sub
End If
DoCmd.SetWarnings False
i = 0
Do While Not rst.EOF And Not rst.BOF
PhoneNum = Trim(rst("Home Phone"))
AreaCode = Trim(rst("Area Code"))
FirstName = Trim(rst("First Name"))
LastName = Trim(rst("Last Name"))
CustomerNumber = Left(FirstName, 1) & Trim(LastName) &
Trim(AreaCode & Replace(PhoneNum, "-", ""))
DoCmd.RunSQL "UPDATE tblImport SET tblImport.[Vac#] = '" & codes(i)
& "' WHERE [tblImport].[Home Phone] = '" & PhoneNum & "' AND
[tblImport].[Vac#] is Null;"
DoCmd.RunSQL "UPDATE tblvaccert SET tblvaccert.[Customer#] = '" &
CustomerNumber & "', tblvaccert.[Used] = 'Y' WHERE [tblvaccert].[code]
= '" & codes(i) & "' AND [tblvaccert].[used] is null;"
i = i + 1
rst.MoveNext
Loop
MsgBox i & " Records have recieved a new Vacation Cert #."
rst.Close
DoCmd.SetWarnings True
Set rst = Nothing
End Sub
[/QUOTE]
