check if value exist in table, if not then....

[behedwin]

Hey

I have a table in access called Log_Table

Id like the user to press a button that checks if the value "KLAR" is in the column named "HRWEBB CHECK"

How do i do this?
Ive done it with Excel, but dont know how to make this code work in access

(code from excel, how to make this work in access)

Dim ws As Worksheet
Dim lr As Long
Dim myKey
Dim myDict As Object
Dim myMsg As String
Dim i As Long

Set ws = Worksheets("LOG") 'change sheet name to the one being checked
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
Set myDict = CreateObject("Scripting.Dictionary")
For i = 4 To lr 'change the 2 to the first row with data
    If ws.Range("E" & i).Value = vbNullString Then
        'myDict.Add i, ws.Range("A" & i).Value
        myDict.Add i, ws.Range("A" & i).Value & " " & ws.Range("B" & i).Value & " - " & ws.Range("C" & i).Value & ": "
    End If
Next i
For Each myKey In myDict.Keys
    myMsg = myMsg & myDict(myKey) & "" & vbCrLf
Next myKey

If Len(myMsg) > 0 Then
MsgBox ("Följande frånvaro rapporter finns ej i HR-WEBB." & vbCrLf & "Kontrollera om rapport finns och markera rad som klar. " & vbCrLf & vbCrLf & Left(myMsg, Len(myMsg) - 1))
Else
MsgBox ("Alla rapporter är kollade mot HR-WEBB")
End If

Set myDict = Nothing

The button the user will press is called
LogCheck_Button

The form that this is placed in is called
profile_form


Then when the user press the button and checks what rows have not the value "KLAR" in the column "HRWEBB CHECK" id like to
display all rows that does not have the value in a msgbox or in something else. With the data from the other columns in the table log_table.

 

[alansidman]

You can modify this code to achieve what you want. It gives you the basic set up.

http://www.datapigtechnologies.com/flashfiles/vbadoloop.html

 

[behedwin]

Link does not work for me.
Do you mind posting the code here?

 

[alansidman]

Its a video that explains the entire way to update a recordset in VBA. Not sure why you cant see it. But without the narrative, the code within the video may be meaningless. I would have to recreate the code as it cannot be copied. Suggest you try a different PC or check you internet connections.

 

[james_lankford]

> Not sure why you cant see it.

when I click on the link it takes me to a page that is blank except for the message "click to enable adobe flash player"

 

[behedwin]

Link worked on another computer.

So i have now watched it and did this code based of it.

Sub Check_Log()

Dim myDB As DAO.Database
Dim recordset As DAO.recordset
Dim logstatus As String

Set myDB = CurrentDb
Set recordset = myDB.OpenRecordset("Log_table")

Do Until recordset.EOF
logstatus = recordset![HRWEBB CHECK]
MsgBox logstatus

recordset.MoveNext
Loop


End Sub

This is almost working.
First two rows in column HRWEBB CHECK have the value "KLAR"
So i do get two msgbox popups with the text KLAR

But then there are some empty fields in the column HRWEBB CHECK
So i get the error message error 94, not allowed to use Null

How to fix the error?



I also dont get the result i want.
I want to get one msgbox with the result of all cells.

If row 1 have the value "KLAR" in the column HRWEBB CHECK i dont want that value printed
I want to know what rows does not have the value "KLAR" and what are the values of those rows in all other columns except the HRWEBB CHECK column.

 

[alansidman]

Wouldn't this be easier to write a query in Access to find all rows that do not have the value KLAR in field HRWEBB CHECK?

A simple Select Statement

Select * from [yourTable]
Where [HRWEBB CHECK] <> "KLAR";

 

[behedwin]

Wouldn't this be easier to write a query in Access to find all rows that do not have the value KLAR in field HRWEBB CHECK?

A simple Select Statement

Select * from [yourTable]
Where [HRWEBB CHECK] <> "KLAR";

Im willing to try this.
But quite new to access.

So where do i enter this "select" statement?

 

[alansidman]

Its time you learned the power of queries in Access. What I gave you is the SQL for a query. To build a query, see below.

https://www.wikihow.com/Create-Action-Queries-in-Microsoft-Access

SQL is the background language for what you build in Access GUI.

 

[behedwin]

Its time you learned the power of queries in Access. What I gave you is the SQL for a query. To build a query, see below.

https://www.wikihow.com/Create-Action-Queries-in-Microsoft-Access

SQL is the background language for what you build in Access GUI.

This looks interessting... i followed the steps from the link.
Im tho still confused on where to use the select statement..

I now got a new table called CheckLog_Table
And a new Query called CheckLog_Query

Here are some screenshots
http://prntscr.com/h731mm
http://prntscr.com/h731x0