Form Serial Number

[mike60smart]

Hi Everyone

I have the following Form layout.

Main Form - frmCustomerOrders
Subform1 - frmOrderItemsSubform
Subform2 - frmOrderItemSizesSubform

To save the user having to use a Combobox to insert a RowNr for each Record on Subform2
On subform2 I have an Unbound Control named "txtSN" and a Control named "RowNr"

The Record Source of "txtSN" is set as =SerialNumber([Form])

Then in the AfterUpdate of A Control named cboCustomer

Private Sub cboCustomer_AfterUpdate()
Me.RowNr = Me.txtSN
End Sub

The After Update does not work using the following Function:-

Any help appreciated/

On Subform 2 I have the following Function:-

Private Function SerialNumber(ByVal sourceForm As Form) As Variant
10        On Error GoTo Catch
20        SerialNumber = Null
30        With sourceForm.RecordsetClone
40            .Bookmark = sourceForm.Bookmark
50            SerialNumber = .AbsolutePosition + 1
60        End With
Done:
70        Exit Function
Catch:
80        If Err.Number <> 3021 Then MsgBox Err.Number & ":" & Err.Description, vbExclamation, "SerialNumber"
90        Resume Done
End Function

 

[arnelgp]

try to Requery textbox txtSN first before assigning to textbox RowNr.

 

[mike60smart]

try to Requery textbox txtSN first before assigning to textbox RowNr.

I tried this but no joy:-

Private Sub cboCustomer_AfterUpdate()
Me.txtSN.Requery
Me.RowNr = Me.txtSN
End Sub

 

[arnelgp]

you make the function Public and Put it in a Module.

 

[Gasman]

Have you walked through the code?
I would have thought you would need Me to be passed to the function? You need the form object not just Form?

 

[Gasman]

I would probably have a simple function in the form as the source = GetSerialNumber()
That in turn would call your function
GetSerialNumber = SerialNumber(Me)

 

[mike60smart]

you make the function Public and Put it in a Module.

Hi arnelgp

I made the Module Public and now when I select a Customer from the Combobox nothing happens
But if I click into a new record the Serial Number populates and when I reselect the Customer the RowNr populates.

Thoughts?

 

[mike60smart]

I would probably have a simple function in the form as the source = GetSerialNumber()
That in turn would call your function
GetSerialNumber = SerialNumber(Me)

Hi Gasman
When you say "I would probably have a simple function in the form as the source = GetSerialNumber()"

Where would I put =GetSerialNumber() ?

 

[Gasman]

Hi Gasman
When you say "I would probably have a simple function in the form as the source = GetSerialNumber()"

Where would I put =GetSerialNumber() ?

In the source for the control? where you had =SerialNumber([Form]) previously

 

[mike60smart]

In the source for the control? where you had =SerialNumber([Form]) previously

Ok so that now displays #Name?

Where should I place GetSerialNumber = SerialNumber(Me)

 

[mike60smart]

Hi Everyone

I managed to fix it by using the code provided by Allan Browne Here

Many thanks for help

 

[Pat Hartman]

Wow, I would never have suspected you needed to assign a sequence number from your initial and subsequent posts.

Take a look at this sample to see if it makes sense.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk