Field ditto with autoincrement on subform

[dennis giasson]

I have a subform for which I want to have the previous
record's field value incremented and placed in the new
record's field. (Similar to ctrl + ', but with
incrementation). For example, on the subform, the user
wants to take the previous field value, say 123, and fill
in the present field value with 124, by using a ctrl + i
key combination.

Can this 'easily' be done with VB? I have tried using
recordsetclones, but with Access 97, recordsetclones are
not supported for subforms (as they are with forms). I
also tried using sendkeys, but that didn't work!

Thanks in advance

Cheers,

Dennis

 

[Allen Browne]

How about automatically supplying the next available number as soon as the
user starts entering a record in the subform?

This example assumes the main form is entering CD albums, and the subform
the tracks on the CD. It gives the next TrackID number for the AlbumID in
the main form.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a main form record first."
Else
strWhere = "AlbumID = " & Me.Parent!AlbumID
Me.TrackID = Nz(DMax("TrackID", "tblTrack", strWhere), 0) + 1
End If
End Sub


Subforms do have their own RecordsetClone, but it would be messy to do it
that way, and could result in duplicates if the subform is filtered.

 

[TC]

??? yes they are ???

(david)

They'd better be, or my main application has been failing for the past 10
years

TC

 

[david epsom dot com dot au]

recordsetclones, but with Access 97, recordsetclones are

not supported for subforms (as they are with forms). I

??? yes they are ???

(david)

 

[dennis]

Thanks Allen, but the next value in the sequence may not
be based purely on a number. Also, it may be that the user
does not want to have a sequencial value every time.

 

[Allen Browne]

Guess I misunderstood what you meant by "autoincrement" then.

BTW, the way you did not see the RecordsetClone might have been that you
left out the ".Form" bit? The subform control does not have a
recordsetclone, but the form in the control does. More info
http://allenbrowne.com/casu-04.html

 

[dennis]

Alright, then... can you create a recordsetclone for a
subform or not. If you look at the other posts, I was
corrected into thinking that a subform can have a
recordsetclone!?

 

[Allen Browne]

Yes, subforms have a RecordsetClone.

The important thing is that there is a difference between a subform control,
and the form in that control. A subform control does not have a
RecordsetClone, nor a Dirty property, nor a NewRecord property, nor ... The
form in the subform controls has all those things.

Example:
Mainform named "MyMain".
Subform control named "MySub".

Forms!MyMain!MySub does not have a RecordsetClone.
Forms!MyMain!MySub.Form does.

 

[TC]

recordsetclone is a *form* property. The fact that a form may be used as a
subform, does not change that fact. Perhaps you were looking at the
properties of the wrong object? (eg. the subform control?)

HTH,
TC