N
number1hatfielder
Hi,
If i have a list box with, lets say for example, 10 records on. 3 of
the records are for client A, 3 of the records are for client B and 4
of the records are for Client C. How can i run code which will group
the records by ClientID and then run a certain code for each group of
records?
At present the user selects which customer to search for using a combo
box and then the results are sent to another table where there are
used in another procedure.
Each group of records will need to be given a unique number which is
assigned during the current code process.
Private Sub RunBatchTags_Click()
Dim sqlStr As String
Dim sql As String
If Me!lbBatchUpdates = Null Then
MsgBox "This Batch is empty. No Update will be carried out", ,
"ShredIT - Assigning Tags"
DoCmd.Close
Else
sqlStr = "SELECT tblShreddingHistory.ShredConfirmationCode,
tblShreddingHistory.ShreddingDate FROM tblShreddingHistory;"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sqlStr, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
With rs
..AddNew
!ShreddingDate = Now()
!ShredConfirmationCode = Nz(DMax("ShredConfirmationCode",
"tblShreddingHistory"), 550012789) + 1
..UpdateBatch
ShredCC = !ShredConfirmationCode
shredW = DSum("Weight", "tblBatchUpdates")
End With
rs.Close
shredMBB = "N/A"
sql = "UPDATE tblTagDetails INNER JOIN tblBatchUpdates ON
tblTagDetails.TagNumber = tblBatchUpdates.TagNumber SET
tblTagDetails.DateShredded = #" & Now() & "#, tblTagDetails.Weight =
tblBatchUpdates.Weight, tblTagDetails.ShredConfirmationCode = " &
ShredCC
CurrentProject.Connection.Execute sql
MsgBox "A new Shredding Confirmation Code: " & ShredCC & " has been
created!", vbInformation, "ShredIT - New Confirmation Code"
DoCmd.OpenForm "frmCreateShreddingCodeBatch", acNormal
CurrentDb.Execute "DELETE * FROM tblBatchUpdates", dbFailOnError
End If
End Sub
The Above code generates the Shred Confirmation Code and updates the
relevant Tag Numbers with that number. What i need to do as add more
code which then loops through to see if there are any other customer
codes [like in the example of the list box there are 3] and if a
different code is found to create another ShredConfirmationCode adn
assign that to the Tag/s in question.
I know what the end result is, but i am finding it hard to explain it
- sorry.
Anthony
If i have a list box with, lets say for example, 10 records on. 3 of
the records are for client A, 3 of the records are for client B and 4
of the records are for Client C. How can i run code which will group
the records by ClientID and then run a certain code for each group of
records?
At present the user selects which customer to search for using a combo
box and then the results are sent to another table where there are
used in another procedure.
Each group of records will need to be given a unique number which is
assigned during the current code process.
Private Sub RunBatchTags_Click()
Dim sqlStr As String
Dim sql As String
If Me!lbBatchUpdates = Null Then
MsgBox "This Batch is empty. No Update will be carried out", ,
"ShredIT - Assigning Tags"
DoCmd.Close
Else
sqlStr = "SELECT tblShreddingHistory.ShredConfirmationCode,
tblShreddingHistory.ShreddingDate FROM tblShreddingHistory;"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sqlStr, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
With rs
..AddNew
!ShreddingDate = Now()
!ShredConfirmationCode = Nz(DMax("ShredConfirmationCode",
"tblShreddingHistory"), 550012789) + 1
..UpdateBatch
ShredCC = !ShredConfirmationCode
shredW = DSum("Weight", "tblBatchUpdates")
End With
rs.Close
shredMBB = "N/A"
sql = "UPDATE tblTagDetails INNER JOIN tblBatchUpdates ON
tblTagDetails.TagNumber = tblBatchUpdates.TagNumber SET
tblTagDetails.DateShredded = #" & Now() & "#, tblTagDetails.Weight =
tblBatchUpdates.Weight, tblTagDetails.ShredConfirmationCode = " &
ShredCC
CurrentProject.Connection.Execute sql
MsgBox "A new Shredding Confirmation Code: " & ShredCC & " has been
created!", vbInformation, "ShredIT - New Confirmation Code"
DoCmd.OpenForm "frmCreateShreddingCodeBatch", acNormal
CurrentDb.Execute "DELETE * FROM tblBatchUpdates", dbFailOnError
End If
End Sub
The Above code generates the Shred Confirmation Code and updates the
relevant Tag Numbers with that number. What i need to do as add more
code which then loops through to see if there are any other customer
codes [like in the example of the list box there are 3] and if a
different code is found to create another ShredConfirmationCode adn
assign that to the Tag/s in question.
I know what the end result is, but i am finding it hard to explain it
- sorry.
Anthony