Auto fill entry if condition is true

[bnickelsen]

I have a table

(Not real names)

AutonumberPK
RequestType (Lookup to TypeTable)
RequestNumber
RequestName

This table then Relates to a request detail table.

The question here,

There are two types or request (T1 and T2)

In my form to enter new Requests I will have a dropdown box to select the type of request.

The next field is the request number. If the type of request is T1 then the person entering the request will have a 6 digit number to enter. But if the request is T2 then I need to auto populate the field.

Is this possible?
Or should I just build two forms, one for each type.

Also what would be the best way to auto fill in a number that follow this pattern.

05040001
05 = year
04 = month
0001 = the number of the request for this month.

Every month starts over at 0001

 

[RichO]

Here is a piece of code that will autofill your request number field it 'T2' is selected.

It uses the DMax function to look up the highest request number in your table based on the current date, and adds 1 to it as the next increment. If you are building this number using an entered date as opposed to the current date you will need to substitute the name of your date field in place of "Date" in the code.

This code should go in the After Update event of your request type combo box. Be sure to substitute the appropriate text box/field & table names if they are different.

If Me.cboRequestType = "T2" then
    Dim strMax  As String
    Dim strYYMM As String
            
    strYYMM = Format$(Date, "yymm")
    
    strMax = Nz(DMax("RequestNumber", "MyRequestTable", "Left$(RequestNumber, 4) = '" & strYYMM & "' "), Format$(Date, "yymm") & "0000")
    Me.txtRequestNumber = Left$(strMax, 4) & Format$(Val(Right$(strMax, 4)) + 1, "0000")
End If

 

[bnickelsen]

Solved.

Thank you, That worked perfect.