Increment record

[lbunch]

I am appending 1 record I want to increment by 1 into tbl_19_1colorrev and (I need to increment "revision" it by one) . This is what I have but it is incrementing the table linked to the main form. This revision table is in a subform sfrm19_1. The main form has a revision field also. but I just want to increment the record in tbl_19_1colorrev"......

This is what I have that is not working.

Set rs = Currentdb.OpenRecordset("SELECT CRM_NBR,revision FROM tbl_19_1colorrev where crm_nbr ='" & Me.cmbCRM & Me.revision & "'")

Me.sfrm19_1!revision = (revision) + 1 <trying to revise subform
'Me![revision] = (revision) + 1 <I tried rs(revision) but got an error.

Me.Requery

'End If
rs.Close
Set rs = Nothing
Set db = Nothing

 

[Remou]

You seem to be mixing up two ideas, one to use a recordset to update a table, and two to update the table directly from the form.

If this is a small databse with just a few users, I think you can do this more simply by setting the Default Value for the revision number:

[tt]=Nz(DMax("[Revision]","tbl_19_1colorrev"),0)+1[/tt]

I am not sure where CRM_NBR comes into the story, but if you want the next highest revision number only where CRM_NBR is the same as your current record, you could say:

[tt]=Nz(DMax("[Revision]","tbl_19_1colorrev","CRM_NBR=" & [cmbCRM]),0)+1[/tt]

Assuming that CRM_NBR is numeric. If it is not, you will need single quotes.

You will need to play around a bit, for example, if you delete records, you will need to requery.