automatically generate an autofield

[TheRiver]

How can I get a my form to automatically generate a autono based on two fields:

Surname and dob

DOB is in the following format:

dd/mm/yyyy

I want the following for example to appear:

SMITH01011980
JONES10121975

 

[SQLBI]

Hi,

Is the Surname and DOB already on the form in 2 separate controls?

If so, i think this will do it.

Dim strDOB As String
Dim strArray As Variant
Dim intX As Integer
Dim strResult As String

With Me

    strDOB = .DOB
    strArray = Split(strDOB, "/")

    For intX = LBound(strArray) to UBound(strArray)
        strResult = strResult & strArray(intX)
    Next intX

    .Reference = UCase(.Surname) & strResult

End With

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

Hi,

in addition to the above, you may need to convert your date field to a String datatype.

Replace

strDOB = .DOB

with

strDOB = CStr(.DOB)

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TheRiver]

THank for this, this is the first time I designed a form so where would this code go?

 

[SQLBI]

Hi,

If you have the Surname and DOB already on the form, create a TextBox control called Reference.

Then, copy the code below in it's entireity into the Form Module.

Private Sub Form_Open(Cancel As Integer)

    Call SetRef 'Updates Reference when Form is opened.

End Sub

Private Sub Surname_AfterUpdate()

    Call SetRef 'Updates Reference when Surname is changed.

End Sub

Private Sub DOB_AfterUpdate()

    Call SetRef ''Updates Reference when DOB is changed.

End Sub

Private Sub SetRef()

Dim strDOB As String
Dim strArray As Variant
Dim intX As Integer
Dim strResult As String

With Me

    strDOB = CStr(.DOB)
    strArray = Split(strDOB, "/")

    For intX = LBound(strArray) to UBound(strArray)
        strResult = strResult & strArray(intX)
    Next intX

    .Reference = UCase(.Surname) & strResult

End With

End Sub

Hope this helps, let me know if you have any trouble.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TheRiver]

I tried this but it throws up an error, invalid use of null, it then highlights the following line:
strDOB = CStr(.DOB)

 

[TheRiver]

if i take out:

Private Sub Form_Open(Cancel As Integer)

Call SetRef 'Updates Reference when Form is opened.

End Sub

Private Sub Surname_AfterUpdate()

Call SetRef 'Updates Reference when Surname is changed.

End Sub


then it seems to work just fine, is this ok to do?

 

[TheRiver]

extra complication, what if i just wanted to take only the first 5 characters from the surname?

 

[jimb0ne]

y0, you could just use code like the following to generate the string

Left(LastNameField, 5) & DOBstuff

that would get the first 5 characters of the surname

 

[SQLBI]

Hi,

I assumed that the form was populated with the surname and DOB when it opened, if this is not the case, use this code instead.

Private Sub Form_Open(Cancel As Integer)

    If IsNull(Me.Surname) = False AND IsNull(Me.DOB) = False Then
    Call SetRef 'Updates Reference when Form is opened.
    End If

End Sub

Private Sub Surname_AfterUpdate()

    If IsNull(Me.Surname) = False AND IsNull(Me.DOB) = False Then
    Call SetRef 'Updates Reference when Surname is changed.
    End If

End Sub

Private Sub DOB_AfterUpdate()

    If IsNull(Me.Surname) = False AND IsNull(Me.DOB) = False Then
    Call SetRef ''Updates Reference when DOB is changed.
    End If

End Sub

This should deal with the Null values.

Then use jimbOne's LEFT() function to trim the surname to 5 Chars.

Let me know how you get on.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk