Update query for numbering the records

[kumar.dkr]

Hi there,

Could anyone Pls. help me to form an expression in the update query which will be using to update the numbers in increasing orders for a set of records in a table. For example, a table contains fields like (Customer Name/S.No/Items Description/Unit/Price/Invoice no.). The update query shall be used to enter the Invoice no. in increasing order for the set of Customer name in the fields.

Regards,
Kumar D.

[maximus]

what I understand from you post:

1) You have a data in your table.
2) There is a Field Invoice No in which u will like to update in the increasing order with numbers.

This what i have done:

I have a table tblEmp the table has a field EmpId which is a number Field which is blank. I have entered data in the other fields like EmpName, EmpDOB, EmpAddress. Now I would like to update my EmpID Field with Numbers in the ascending

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click
Set rs = CurrentDb.OpenRecordset("tblEmp")
If rs.EOF And rs.BOF Then
MsgBox "No Record"
End If
intRecordCount = 0
Do While Not rs.EOF
rs.Edit
rs!EmpID = intRecordCount + 1
rs.Update
intRecordCount = rs!EmpID
rs.MoveNext
Loop
Set rst = Nothing '--- reclaim the memory the recordset was using
Me.Requery


Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub


I have used a integer variable whose value is set to Zero. Then I use a RecordSet to Loop through the table populating the EmpID Field with the Numbers. Every Time the EmpID is updated it passes on its value to the variable intRecordCount this ensures that the next Number which is used to populate the EmpID again is exactly incremented by 1

[kumar.dkr]

Thanks Mr.Maximus.

The below suggestion may give us the unique numbering to the invoice field in ascending order for every records in the table, but I need to update the set of records in a table with the same numbers. To strengthen my words, I enclosed below the table for your reference. The field in red to be updated with the query.

Customer NameS.NoItem DescriptionUnit in Nos. Price in $Total in $Invoice No.John1T-Shirt110101John2Trouser220401John3Innerwears215301Antony Williams1Innerwears515752Antony Williams2Trouser5201002Antony Williams3T-Shirt610602Peter1Trouser11202203Peter2T-Shirt210203Peter3Innerwears215303Peter4Shoes125253
Any other thoughts pls.

Regards,
Kumar D.

what I understand from you post:

1) You have a data in your table.
2) There is a Field Invoice No in which u will like to update in the increasing order with numbers.

This what i have done:

I have a table tblEmp the table has a field EmpId which is a number Field which is blank. I have entered data in the other fields like EmpName, EmpDOB, EmpAddress. Now I would like to update my EmpID Field with Numbers in the ascending

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click
Set rs = CurrentDb.OpenRecordset("tblEmp")
If rs.EOF And rs.BOF Then
MsgBox "No Record"
End If
intRecordCount = 0
Do While Not rs.EOF
rs.Edit
rs!EmpID = intRecordCount + 1
rs.Update
intRecordCount = rs!EmpID
rs.MoveNext
Loop
Set rst = Nothing '--- reclaim the memory the recordset was using
Me.Requery


Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub


I have used a integer variable whose value is set to Zero. Then I use a RecordSet to Loop through the table populating the EmpID Field with the Numbers. Every Time the EmpID is updated it passes on its value to the variable intRecordCount this ensures that the next Number which is used to populate the EmpID again is exactly incremented by 1

[kumar.dkr]

Sorry, the table is missing. Its attached in the thread.

Thanks Mr.Maximus.

The below suggestion may give us the unique numbering to the invoice field in ascending order for every records in the table, but I need to update the set of records in a table with the same numbers. To strengthen my words, I enclosed below the table for your reference. The field in red to be updated with the query.

Customer NameS.NoItem DescriptionUnit in Nos. Price in $Total in $Invoice No.John1T-Shirt110101John2Trouser220401John3Innerwears215301Antony Williams1Innerwears515752Antony Williams2Trouser5201002Antony Williams3T-Shirt610602Peter1Trouser11202203Peter2T-Shirt210203Peter3Innerwears215303Peter4Shoes125253
Any other thoughts pls.

Regards,
Kumar D.

[maximus]

This is what I have done:

I have a table with the following Fields:
tblEmp:{EmpID,EmpName,EmpAddress,EmpDOB,Selected(Y es/No)}

Now I want to assign the same ID to a particular ID:

Like Maximus=1
RuralGuy=2

so in My Name Field I type Maximus 3 Times and RuralGuy 3 Times so the EmpID for all the three maximus will be 1 and RuralGuy=2

I am running this code from a Commanbutton of a form. I have introduced a Yes/No Field Selected. This is set to true when ever a Row is assigned a EmpId.

Now I have used two record set. The First RecordSet will Loop through all the records which selected = false.

Now when the Loop Reaches the first name maximus I use the rs!EmpName as a parameter to open another Recordset all records where Empname is maximus. I populate the EmpID with the RecordCounter and Check selected True. Once this is done the first record set Loops on to the next record which is another name<>maximus. The whole process is repeated again and every name set is isolated and assigned a unique incremental number.

Private Sub Command17_Click()
Dim intRecordcounter As Integer
intRecordcounter = 0
Set rs = CurrentDb.OpenRecordset("Select * from tblEmp Where Selected=False")
Do While Not rs.EOF
intRecordcounter = intRecordcounter + 1
Set rst = CurrentDb.OpenRecordset("Select * from tblEmp Where EmpName='" & rs!EmpName & "'")
Do While Not rst.EOF
rst.Edit
rst!EmpID = intRecordcounter
rst!Selected = True
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Requery
End Sub