previous record+1 as default

[Song Su]

I have frmPO (main form) with frmPODetail (subform) as one to many
relationship tracking purchase orders. When entering record in subform, how
to have previous record plus 1 as default of current record?

For example: if user enter 1000300001 in LACCDTag as first record, on enter
of 2nd record, how to give 1000300002 (numerical) by default?

LACCDTag Description OtherFields
1000300001 HP Computer

 

[Allen Browne]

The answer will depend on factors such as:
a) Are multiple people entering data? Or just one?
b) Is it 1 plus the last entry? Or one more than the highest value?
c) Is it actually a Number field? Or a Text field?
d) Is it the last number related to the value in the main form?
Or the highest number (regardless of how it is related)?

This example assumes the subform is bound to SubTable, which has a field
named SubID that relates to the MainID field on the main form. It gets the
highest related value, adds 1, and assigns the value as soon as the user
starts typing into any box in the subform.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere as String
Dim strMsg As String

With Me.Parent
If .NewRecord Then
Cancel = True
strMsg = strMsg & "Enter the main form record first." & vbCrLf
Else
strWhere = "SubID = " & !MainID
Me.LACCDTag = Nz(DMax("LACCDTag", "SubTable", strWhere),0) + 1
End If
End With
End Sub

 

[Song Su]

Hi, Allen Browne,

a) just one person enter data
b) one more than the highest value (they migh skip LACCDTag for any PO)
c) LACCDTag is Number field, not Text
d) in PO table, PONumber (Number type) is primary key. in PODetail table,
PODetailID is AutoNumber primary key and has a field PONumber which related
to PO table

If they do skip LACCDTag, they can manually change it for the first record.
From 2nd record and on, it should give default previous+1. It'll be a great
help if they need to enter 50 records in single PO.

I notice you put BeforeInsert event. Is it ok to have OnEnter event so when
they 'click' the new record, the default value is there?

Thanks

Song Su

 

[Song Su]

And some POs might not have LACCDTag. So if it provides a default value and
this particular PO in detail does not require LACCDTag, user can just press
DEL. If user wants to skip LACCDTag, they can just change the default on the
first item and it should privide correct default on the 2nd item and on.

 

[Allen Browne]

I would not use the Enter event. It dirties the record before you really
know that you want one, and it could mess up existing records.