CODEWITHSUNDEEP
ms-accessvba
Pangu
Pangu

Reputation: 3819

How to assign the next incremented alpha-numeric string value in a text box using VBA Access?

Currently, I have a datasheet that contains the following data as an example:

FieldOne       FieldTwo
TestOne.01     TestOne.01.01
TestOne.01     TestOne.01.02
TestTwo.02     TestTwo.02.01
TestThree.03   TestThree.03.03
TestFour.02    TestFour.02.02
TestFour.02    TestFour.02.03

I have a form that contains a Combo Box. The values of the combo box is all the values in FieldOne. I also have a Text Box on the same form where I would like, upon selecting a value from the Combo Box, assign the next incremented value in FieldTwo of that specific record into the Text Box.

For example, if I select from the Combo Box, the value TestThree.03, I would like to populate the Text Box with TestThree.03.04. If I select the value TestTwo.02, the Text Box should be populated with TestTwo.02.02

What I've tried to get started with so far:

Dim frmDS As SubForm
Dim criteria As String

Set frmDS = getSubForm("form", "datasheet")

criteria = "FieldOne = '" & Me.comboBox & "'"

With frmDS.Form
    .RecordsetClone.FindFirst criteria

    If Not .RecordsetClone.NoMatch Then
        MsgBox .RecordsetClone.Fields("FieldTwo")
    End If
End With

However I face 2 difficult problems:

  1. If I select TestOne.01, since there are 2 records containing TestOne.01, it will display TestOne.01.01 instead of the last value TestOne.01.02
  2. I am not sure how to extract the number after the right-most decimal point in the value of FieldTwo, increment it, and append it back to the value in FieldOne so that it can be assigned to the Text Box

I'm not sure if my initial approach is heading in the right direction anyways.

How can I overcome these issues?

Thanks

Upvotes: 0

Views: 203

Answers (1)

dbmitch
dbmitch

Reputation: 5386

For question 1, the answer is simple

Instead of .RecordsetClone.FindFirst criteria

use .RecordsetClone.FindLast criteria

For question 2, you just need a bit of VBA code to first get the last code and then format it back to append.

You don't give all the details of your formatting so I'll assume the simplest solution - that is that the last code is 2 digits long. If not you can use a variation of Instr and Mid or Right functions to find the last code

Example:

Dim intLastCode as Integer
Dim strLastCode as String

With frmDS.Form
    ' Get last two digits from Field 2
    strLastCode = Right(.RecordsetClone.Fields("FieldTwo"),2)
    intLastCode = CInt(strLastCode) + 1

    ' Format and append incremented value to Field 1
    MyTextBox.Value = .RecordsetClone.Fields("FieldOne") & "." & Format(intLastCode,"00")
End With

Upvotes: 1

Related Questions