Reset Auto generation number

[SaRa Ahmad]

hi everybody , i need to solve this case as soon as possible :

i have the master table that include the following data :
(SerialNo - integer (P.K), civil-bit, mechanical-bit, etc)

and form that include the serial no text field that automatic increase by one each time the user click the new button what i need is when the user check the Civil check box the serial start from 1, when the user check on mechanical the serial should reset to 1 and continue, how i can do this witout change the structure of table , help me please ???

[mmcdonald]

Can we see the code that is executed when the user checks mechanical...

[rishif2]

when the user check the Civil check box the serial start from 1, when the user check on mechanical the serial should reset to 1 and continue,

actually i can't differentiate both line separated by ,

please make your statement more clear so we can identify the exact problem.

[SaRa Ahmad]

this is the code when the user click on the new to insert the serial number for civil :

  If rs.State = adStateOpen Then rs.Close
    rs.Open "select max(SerialNo) from [Master_ShopDrawing] where  Projectno = '" & ProjectNo1 & "'", db, adOpenDynamic, adLockReadOnly
    If rs(0) <> 0 Then SerialNo.Text = rs(0) + 1 Else SerialNo.Text = 1
    Set rs = Nothing
    newflag = 1
SerialNo.Enabled = False
ToCons.SetFocus
rs.Open "select Consultants,ConsEng,Copy,Att from [Main data for the project] where projectno = '" & ProjectNo1 & "'", db, adOpenDynamic, adLockReadOnly
If Not rs.EOF Then
ToCons.Text = rs(0)
Att1.Text = ""
Copy.Text = rs(2)
att2.Text = ""
End If
End If


i need the same code to valid when chick to mechanical to start from 1