CODEWITHSUNDEEP
ms-accessvba
scb998
scb998

Reputation: 909

Create an ID number based off form fields - Access

I have built an excel VBA form which is able to generate a unique ID number based on: the "record number" (i.e. how many non-null cells in a range), a letter or letters, the financial year (i.e. 17,for financial year 2016/17), and another letter.

The code, financial year, and other letter are all based on inputs from the user, and the number is obviously auto generated, resulting in a code in the following format: 001-A17S

below is the excel code to yield the above result:

Application.ScreenUpdating = False


Worksheets("Project Tracking").Activate

DeliveryPartnerCode = lbl_DeliveryPartnerCode
FinancialyearString = txt_FinancialYear.Text
Financialyear = Right(FinancialyearString, 2)

nonfilledCells = WorksheetFunction.CountA(Range("A5:A100"))
AWRNumber = nonfilledCells + 1
AWRNumberstring = CStr(AWRNumber)

If (AWRNumber < 9) Then
    AWRPrefix = "00"
ElseIf (AWRNumber < 99) Then
    AWRPrefix = "0"
Else
    AWRPrefix = ""
End If


If cmb_State.ListIndex = 0 Then
    State = "S"
ElseIf cmb_State.ListIndex = 1 Then
    State = "N"
ElseIf cmb_State.ListIndex = 2 Then
    State = "Q"
ElseIf cmb_State.ListIndex = 3 Then
    State = "V"
ElseIf cmb_State.ListIndex = 4 Then
    State = "A"
End If



lbl_AWRReference = AWRPrefix & AWRNumberstring & "-" & DeliveryPartnerCode & Financialyear & State
Application.ScreenUpdating = True

This bastardised excel spreadsheet is no longer appropriate for our use, and I've decided to create a proper database in Access.

I have set up a table with the appropriate columns (Autonumber with a mask of 000), a textbox for the financial year, and drop down boxes for the code and the last letter. but I've got no idea now how to concatenate all this together to give the right format, the place the result into a textbox on the form. I can't use expression builder (doesn't let me pick the autonumber), and I can't find the syntax or logic for writing a VBA code to perform a similar calculation like the excel VBA code above.

What is the best way to achieve what I am looking for? I will admit it has been over 5 years since I've played with Access, so I am a bit fuzzy :P

Thanks.

Upvotes: 0

Views: 699

Answers (1)

Minty
Minty

Reputation: 1626

lbl_AWRReference = AWRPrefix & AWRNumberstring & "-" & DeliveryPartnerCode & Financialyear & State .

Glad you have it working.

Upvotes: 1

Related Questions