Using DAO to search strings (1 Viewer)

[dolano]

This is a snippet of code that I used for a DB that I am currently upgrading. I used this module to update a table that had a couple of thousand records. The problem was there were a number of string values which had different integer values associated with them, for example, if the char 'p' appeared in the field it meant that a cable had 3 wires etc. So, I had to show in another table the value associated with each code (a code contained numerical and character values; MV-12p-200sq. 12p = 12 * 3 = 36 So, by using DAO and some built in string functions I was able to extract the information I required.

This I feel is a useful example and therefore I am posting the entire code along with comments, if anyone wishes to expand on the comments or make alterations to the code they are free to do so.

If I had had more time I would have created a seperate function to search the strings.

Pauli

Private Sub cmdAssignTTR_Click()
On Error GoTo Err_cmdAssignTTR_Click

'Using constants can save time and prevent calculation error
'Useful where a value remains the same throughout best example would be Pi = 3.14

Const Base = 1
Const IV = 1
Const Opt = 1
Const Comm = 1
Const c = 1
Const p = 3
Const t = 4
Const System = 4

Dim findPaired As String
Dim findBase As String
Dim findTwisted As String
Dim findCore As String
Dim findGround As String
Dim findOpt As String
Dim findSys As String
Dim findComm As String
Dim strCode As String

Dim getTT As Integer
Dim inTT As Integer
Dim i As Integer

'setting up DAO connections

Dim dbs As DAO.Database     'create a DAO connection to database
Dim rsCode As DAO.Recordset 'create a DAO recordset similar to a table in access
Dim qdfCode As DAO.QueryDef 'the QueryDef is basically an instance of the table in which you will do your operations on
                            'create an sql query based on the table you wish to operate on
Dim iResponse As Variant

'setting up the DAO variables to call VB methods within Access

Set dbs = CurrentDb
Set qdfCode = dbs.QueryDefs("sqlCode")
Set rsCode = qdfCode.OpenRecordset

'This is a built in VB method to create a message box, which can be useful, makes user aware of the operation they are about to complete

iResponse = MsgBox("you are about to update data do you wish to continue?", vbYesNoCancel, "Update Alert")

    
    Select Case iResponse
        Case vbYes
            DoCmd.SetWarnings False

                'rsCode was the DAO recordset created and will be referenced until end of 'With' statement
                With rsCode
                
                Do Until .EOF = True

                'using the InStr method is very useful for string minipulation, basically, below we are searching
                'for certain characters within the field 'Code' within the rsCode recordset
                'with InStr we can start from a specified string position, ie the first, third etc. so, it will not
                'examine any character prior to that starting position

                findComm = InStr(1, !Code, "COMMUNICATION")
                findSys = InStr(1, !Code, "F")
                findGround = InStr(1, !Code, "IV")
                findOpt = InStr(1, !Code, "Opt")
                findCore = InStr(3, !Code, "c")
                findTwisted = InStr(3, !Code, "t")
                findPaired = InStr(3, !Code, "p")
                findBase = InStr(3, !Code, "Base")

                'this part of the code checks to see if the desired string has being found within each condition
                'InStr returns '0' when a string is not found 'Not Null'

                    If (findBase = 0) = False Then
                        findBase = 1
                        getTT = Int(findBase)
                        inTT = getTT * Base
                    ElseIf (findComm = 0) = False Then
                        findComm = 1
                        getTT = Int(findComm)
                        inTT = getTT * Comm
                    ElseIf (findSys = 0) = False Then
                        findSys = 1
                        getTT = Int(findSys)
                        inTT = getTT * System
                    ElseIf (findGround = 0) = False Then
                        findGround = 1
                        getTT = Int(findGround)
                        inTT = getTT * IV
                    ElseIf (findOpt = 0) = False Then
                        findOpt = 1
                        getTT = Int(findOpt)
                        inTT = getTT * Opt
                    ElseIf (findCore = 0) = False Then
                        getTT = Int(Mid(!Code, findCore - 2, 2))
                        inTT = getTT * c
                    ElseIf (findTwisted = 0) = False Then
                        getTT = Int(Mid(!Code, findTwisted - 2, 2))
                        inTT = getTT * t
                    ElseIf (findPaired = 0) = False Then
                        getTT = Int(Mid(!Code, findPaired - 2, 2))
                        inTT = getTT * p
                    End If
		    'The above 'Mid' function will (along with Int) search field 'Code' from the middle of the field
                    'we want to look at the two characters preceeding the character we searched for using InStr

                    'each code is temporiarly stored in a string 'strCode' until it moves to the next record

                    strCode = !Code

                    'the sql will update the specified fields in the below table taking in the integer value stored in inTT instead of
                    'a field value and also the value in the field of the rsCode record set '!Cable No. The '!' is used
                    'in DAO instead of '.' when referencing a field name

                   DoCmd.RunSQL "UPDATE [CABLE DATA SUPPLEMENT] SET [CABLE DATA SUPPLEMENT].TTR = " & inTT & ", [CABLE DATA SUPPLEMENT].TFR = " & inTT & " WHERE [CABLE DATA SUPPLEMENT].[Cable No]= '" & ![Cable No] & "'"

                   .MoveNext
                Loop
                End With
                    
            DoCmd.SetWarnings True
        End Select
                                                       
Exit_cmdAssignTTR_Click:
    Exit Sub

Err_cmdAssignTTR_Click:
    MsgBox Err.Description
    Resume Exit_cmdAssignTTR_Click
End Sub