How to input a string in another column based on the data in another column?

[shawnntjr]

Hi all,

I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.

Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.

These are the codes that I am using to achieve this, however nothing is being input into the EventType column. What am I doing wrong?

Private Sub Command11_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Final")
 
    If Not rst.EOF Then
        Do
            rst.Edit
            If InStr(rst![AgentName], " ") Then
                rst![EventType] = "IBM Director"
            End If
            rst.Update
            rst.MoveNext
        Loop Until rst.EOF
    End If
    If Not rst.EOF Then
        Do
            rst.Edit
            If InStr(rst![Details], ">>>>") Then
                rst![EventType] = "TEC Notice"
            ElseIf InStr(rst![Details], "SERVICE NOT RUNNING") Then
                rst![EventType] = "Wintel Services"
            ElseIf InStr(rst![Details], "_ntService") Then
                rst![EventType] = "Wintel Services"
            ElseIf InStr(rst![Details], "LOGICAL DRIVE UTIL") Then
                rst![EventType] = "Winte Logical Disk"
            ElseIf InStr(rst![Details], "ntDiskFree") Then
                rst![EventType] = "Winte Logical Disk"
            ElseIf InStr(rst![Details], "Ntdriveutil") Then
                rst![EventType] = "Winte Logical Disk"
            ElseIf InStr(rst![Details], "FILESYSTEM:") Then
                rst![EventType] = "Unix/Linux Filesystems"
            ElseIf InStr(rst![Details], "Filesysuse") Then
                rst![EventType] = "Unix/Linux Filesystems"
            ElseIf InStr(rst![Details], "CPU OFFLINE:") Then
                rst![EventType] = "UNIX CPU Offline"
            ElseIf InStr(rst![Details], "Missing") Then
                rst![EventType] = "Unix/Linux Process"
            ElseIf InStr(rst![Details], "CPU UTILIZATION:") Then
                rst![EventType] = "CPU Utilization"
            ElseIf InStr(rst![Details], "errpt") Then
                rst![EventType] = "INFRA Logfile"
            ElseIf InStr(rst![Details], "SWAP") Then
                rst![EventType] = "UNIX/Linux Memory"
            ElseIf InStr(rst![Details], "Thrashing:") Then
                rst![EventType] = "UNIX/Linux Memory"
            ElseIf InStr(rst![Details], "realmem") Then
                rst![EventType] = "UNIX/Linux Memory"
            ElseIf InStr(rst![Details], "PAGING SPACE:") Then
                rst![EventType] = "Wintel Memory"
            ElseIf InStr(rst![Details], "nonpage") Then
                rst![EventType] = "Wintel Memory"
            ElseIf InStr(rst![Details], "Ntmem") Then
                rst![EventType] = "Wintel Memory"
            ElseIf InStr(rst![Details], "oqsql") Then
                rst![EventType] = "Database"
            ElseIf InStr(rst![Details], "DB2DIAG") Then
                rst![EventType] = "Database"
            ElseIf InStr(rst![Details], "uddb2") Then
                rst![EventType] = "Database"
            ElseIf InStr(rst![Details], "Zombie") Then
                rst![EventType] = "UNIX/Linux Zombie Process"
            ElseIf InStr(rst![Details], "uxphysicalmem") Then
                rst![EventType] = "UNIX/Linux Memory"
            ElseIf InStr(rst![Details], "uxPageScan") Then
                rst![EventType] = "UNIX/Linux Memory"
            ElseIf InStr(rst![Details], "ntCPUUtilization") Then
                rst![EventType] = "CPU Utilization"
            ElseIf InStr(rst![Details], "same process") Then
                rst![EventType] = "Wintel Process"
            ElseIf InStr(rst![Details], "_ntproc") Then
                rst![EventType] = "Wintel Process"
            ElseIf InStr(rst![Details], "Event_ID") Then
                rst![EventType] = "Wintel Eventlog"
            ElseIf InStr(rst![Details], "MQ error") Then
                rst![EventType] = "INFRA Logfile"
            ElseIf InStr(rst![Details], "MQMONITORLOG") Then
                rst![EventType] = "INFRA Logfile"
            ElseIf InStr(rst![Details], "_MSG") Then
                rst![EventType] = "INFRA Logfile"
            ElseIf InStr(rst![Details], "logline") Then
                rst![EventType] = "Other logfile"
            ElseIf InStr(rst![Details], "strscan") Then
                rst![EventType] = "Other logfile"
            ElseIf InStr(rst![Details], "NTSrvc") Then
                rst![EventType] = "Wintel Services"
            ElseIf InStr(rst![Details], "Ntdrivutil") Then
                rst![EventType] = "Winte Logical Disk"
            ElseIf InStr(rst![Details], "uxFilesys") Then
                rst![EventType] = "Unix/Linux Filesystems"
            ElseIf InStr(rst![Details], ":") Then
                rst![EventType] = "Others"
            End If
            rst.Update
            rst.MoveNext
        Loop Until rst.EOF
    End If
End Sub

UPDATE:

I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.

 

[CJ_London]

you can do this with a single query

UPDATE FINAL SET EventType= iif(nz(AgentName='','IBM Director',Switch(instr(Details,'SERVICE NOT RUNNING'),'Wintel Services',instr(Details='SERVICE NOT RUNNING'),'Wintel Services',.....))

 

[bob fitz]

I think the problem with your code is that you loop through the recordset to its end and then try to validate that it is not at the end of the recordset.

        Loop Until rst.EOF
    End If
    If Not rst.EOF Then

That said, if CJ_London's query works, which I'm sure it will, then that would seem to be a better option.

 

[shawnntjr]

I just tried the query method, however I am receiving error 3075. Am I doing it wrongly?

CurrentDb.Execute "UPDATE Final SET EventType = IIF(Nz(AgentName='', 'IBM Director', Switch(InStr(Details, '>>>>'), 'TEC Notice', InStr(Details, 'SERVICE NOT RUNNING'), 'Wintel Services', InStr(Details, '_ntService'), 'Wintel Services', InStr(Details, 'LOGICAL DRIVE UTIL'), 'Winte Logical Disk', InStr(Details, 'ntDiskFree'), 'Winte Logical Disk', InStr(Details, 'Ntdriveutil'), 'Winte Logical Disk', InStr(Details, 'FILESYSTEM:'), 'Unix/Linux Filesystems', InStr(Details, 'Filesysuse'), 'Unix/Linux Filesystems', InStr(Details, 'CPU OFFLINE:'), 'UNIX CPU Offline', InStr(Details, 'Missing'), 'Unix/Linux Process', InStr(Details, 'CPU UTILIZATION'), 'CPU Utilization', InStr(AgentName, ':MS'), 'Database', InStr(AgentName, ':UD'), 'Database', InStr(AgentName, ':VA'), 'VIOS', InStr(Details, 'errpt'), 'INFRA Logfile', InStr(Details, 'SWAP'), 'UNIX/Linux Memory', InStr(Details, 'Thrashing:'), 'UNIX/Linux Memory', InStr(Details, 'realmem'), 'UNIX/Linux Memory', InStr(Details, 'PAGING SPACE:'), 'Wintel Memory'," & _
"InStr(Details, 'nonpage'), 'Wintel Memory', InStr(Details, 'Ntmem'), 'Wintel Memory', InStr(Details, 'oqsql'), 'Database', InStr(Details, 'DB2DIAG'), 'Database', InStr(Details, 'uddb2'), 'Database', InStr(Details, 'Zombie'), 'UNIX/Linux Zombie Process', InStr(Details, 'uxphysicalmem'), 'UNIX/Linux Memory', InStr(Details, 'uxPageScan'), 'UNIX/Linux Memory', InStr(Details, 'ntCPUUtilization'), 'CPU Utilization', InStr(Details, 'same process'), 'Wintel Process', InStr(Details, '_ntproc'), 'Wintel Process', InStr(Details, 'Event_ID'), 'Wintel Eventlog', InStr(Details, 'MQ error'), 'INFRA Logfile', InStr(Details, 'MQMONITORLOG'), 'INFRA Logfile', InStr(Details, '_MSG'), 'INFRA Logfile', InStr(Details, 'logline'), 'Other Logfile', InStr(Details, 'strscan'), 'Other Logfile', InStr(Details, 'NTSrvc'), 'Wintel Services', InStr(Details, 'Ntdrivutil'), 'Winte Logical Disk', InStr(Details, 'uxFileSys'), 'Unix/Linux Filesystems', InStr(Details, ':'), 'Others'));", dbFailOnError

 

[CJ_London]

what is error 3075?