Originally Posted by
dancaw
Where do i add this code on the form load event?
This code is an independent function. You would place it in a module, or you could put it in form's code module. You would then call it from an event like this:
Code:
Dim new_num As String
new_num = NextTenderNum( "E1234A" )
Now the string new_num would contain the incremented tender number.
It's likely that you'll need to look up the last tender number first. I assume you would look up the latest Tender_No in tbl_Tenders? And that if you sort by Tender_No in descending order than the first Tender_No is the latest Tender_No to increment? Here is a quick function that will lookup the latest tender no:
Code:
Public Function LastTenderNum() As String
On Error GoTo ErrHandler_LastTenderNum
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rslt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 Tender_No FROM tbl_Tenders ORDER BY Tender_No DESC;")
If Not (rs.BOF And rs.EOF) Then
'Query found some records
rslt = rs!Tender_No
Else
'Query didn't find anything
End If
rs.Close
ExitHandler_LastTenderNum:
Set rs = Nothing
Set db = Nothing
LastTenderNum = rslt
Exit Function
ErrHandler_LastTenderNum:
MsgBox Err.Description, vbInformation, "LastTenderNo: Error #" & Err.Number
Resume ExitHandler_LastTenderNum
End Function
That is another independent function that can be placed in a code module.
So, now you can modify my first example like so to get the next tender no:
Code:
Dim new_num As String
new_num = NextTenderNum( LastTenderNum() )
*Note: your tbl_TenderView doesn't have a primary key. It looks like Tender_No used to be but it's index isn't flagged as unique so it won't function as a unique primary key. Also, you'd be better off just using an autonumber as a primary key instead.
So now you need to decide how you want to call these functions to set the Tender_No field. The method I like to use would be to in the form's OnLoad event, I would check if the form was launched in data entry mode, and if it is I would set the *Default* value of the Tender_No textbox control using the code above like this:
Code:
Private Sub Form_Load()
If Me.DataEntry Then 'form was loaded in data entry mode to enter a new record
Me.Tender_No.DefaultValue = NextTenderNum(LastTenderNum())
Else 'form was loaded to browse existing records
Me.Tender_No.DefaultValue = ""
End If
End Sub
You would open the form in data entry mode like this:
Code:
docmd.OpenForm "frm_TenderView", , , , acFormAdd
This is all untested!
[Edit] On second thought I'd probably move that last bit to the On Current event of the form. Also noticed an error in regard to enclosing the default value with quotation marks
Code:
Private Sub Form_Current()
If Me.NewRecord Then 'form has moved to a new record
Me.Tender_No.DefaultValue = """" & NextTenderNum(LastTenderNum()) & """"
Else 'form is browsing existing records
Me.Tender_No.DefaultValue = "" 'probably not necessary
End If
End Sub
Windows 10 
Office 365
Reply With Quote
