Increase field by 10

  • Thread starter Thread starter K
  • Start date Start date
K

K

Hello,

I have a subform that has a field called OPSEQ which is short for operation
sequnce number. How do I code this field to increase by 10 from the last
record in the subform For example. 10, 20 , 30 etc.

Any help is appreciated. Thank you for your time.

Kyle.
 
It would help if you provided a little more information.

1. What is the name and structure of the table being used for the subform?
2. What is the field in your main/subForm that is used to link the two?
3. When do you want to do this? As soon as a new record is created in the
subform?
4. What do you want to do if a record, lets say the one where the value is
20 gets deleted before the next record gets created? or is this an issue?
Do you just use 20 over again, or do you want to use 30?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Dale Fye said:
It would help if you provided a little more information.

1. What is the name and structure of the table being used for the subform?
Click to expand...
It is a table called rates linked by ID (AutoNumber) main form to Number
(Subform)
One too many
 
Because of your answer to question #4, I think you are going to have to add a
table to track the last used value for your CCSubID, as it relates to IDNum
(just those two fields).

Whenever you create a new record in your main table, you need to insert a
record into this new table with the IDNum and the starting value for CCSubID
(0?).

Then, in the current event of your subform, you need to test to see whether
you are on a new record. If you are, then you need to retrieve the previous
value of CCSubID from the new table, increment it by 10, and store it back to
the new table and into the CCSubID field of your subform. The code in your
subform might look something like:

Private Sub Form_Current

dim rs as dao.recordset
Dim strSQL as string
Dim lngNextSubID as long

if me.newrecord = false then exit sub

strSQL = "SELECT IDNUM, CCSubID FROM yourNewTable " _
& "WHERE IDNum = " & me.parent.IDNUM
set rs = currentdb.openrecordset strsql

if rs.eof then
rs.addnew
rs.IDNUM = me.parent.IDNum
rs.CCSubID = 0
rs.update
endif

rs.Edit
lngNextSubID = rsCCSubID + 10
rs.CCSubID = lngNextSubID
rs.update
rs.close
set rs = nothing

me.CCSubID = lngNextSubID

End sub

Hope this makes sense.

Had you wanted to just use 20 again (answer to #4) then you could have
gotten away with querying the table you are using for the subform to
determine the maximum CCSubID for the given IDNUM, and then adding 10 to it.
Something like:

if me.newrecord then
me.CCSubID = NZ(DMAX("CCSubID", "tblSubTable", "IDNUM = " &
me.parent.IDNUM), 0) + 10
endif

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top