Auto Poplulate textbox with Combobox Selection + String

[Anthony123]

Hello, I have searched all over and tried Many many things but cant seem to get a solution.

I have a form with a combobox (from a table, the field name is Agency). The combobox options are: TMS, HRD, and TYM.

I also have a textbox (from a table, the field name is Case_Code) *THIS IS ALSO A PRIMARY KEY*

When a user selects a Agency from the combobox (lets say 'HRD'), i need the text box to automatically populate with 'HRD-2013-001'. If on the next record, they chose 'TMS' the combobox would say 'TMS-2013-002'

I have tried editing the contol source of the textbox but i end up getting a "Index or Primary Key cannot be Null" Error

Note: This is a simple way of putting it, eventually i will need to be able to start over with the numbers based on the Agency selected. Example: if the user chooses 'TYM', the textbox would say "TYM-2013-001', they save that record and the next time they choose 'HRD' the text box would say 'HRD-2013-001', if they chose 'TYM' for the next record it would be '....002, 003, 004' and so forth. Same for anyother Agancy selected.

If you can help with this example that would be great! Otherwise a simple solution to the first example will get me a long way! Thanks in advance!!

 

[jzwp22]

This thread discusses a very similar question. Just ignore the code part in the first post of the thread.

Since 'TMS-2013-002' is essentially a calculated value, it would not be stored in the table; you would, however, store the components that make it up. I assume that you will also want to start over at the beginning of each new year. That is addressed in the link I provided as well.

 

[Mr. B]

Here is some code that will do what you want.

Create a new module and paste the code below into it.

Option Compare Database
Option Explicit
Dim rs As DAO.Recordset
Dim strSql As String
Dim strMaxNum As String
Public Function NextCaseCode(AgencyCode As String, CodeYear As String) As String
'return the max number used for the selected company from the table
strSql = "SELECT TOP 1 Right([Case_Code],2) AS [MaxNumber]FROM tblTest " _
       & "WHERE (((Left([case_code], 3)) = '" & AgencyCode & "') " _
       & "And ((Mid([case_code], 5, 4)) = '" & CodeYear & "')) " _
       & "ORDER BY Right([Case_Code],2) DESC;"
Set rs = CurrentDb.OpenRecordset(strSql)
strMaxNum = rs.Fields("MaxNumber").Value
rs.Close
Set rs = Nothing
NextCaseCode = AgencyCode & "-" & CodeYear & "-" & Format(Val(strMaxNum) + 1, "00")
End Function
 
Public Function MyTest()
Dim NxtCode As String
NxtCode = NextCaseCode("HRD", 2013)
End Function

You will need to edit the code where the table name is "tblTest" to the name of your table where you are storing the case code string.

To test this code use the public function MyTest and pass in the Agency code which would come from your combo box and the Year you want to use when creating the next case code.

The NextCaseCode public fuction uses a sql string as a query and returns the maximum two character string from the record where first three characters of the Case_Code field are equal to the agency code that is passed to the function and the four character year in the Case_code field matches the Year value you pass to it. The NextCaseCode is then created by concatenating the three characters of the Agency code to a dash and the four character year, another dash and he maximun number returned by the query incremented by 1.

You can use the AfterUpdate event of your combo box to call the function and make your text box to be equal to the returned value of the NextCaseCode function.