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]

My apologies. Firstly I assumed (wrongly) that you had experience with Access and understood about Tables/Queries/Forms. Before we go to far down this road, it would be best for you to undertake a short tutorial on Access. Give that you already have a database, let's try to solve the immediate issue. SQL is the underlying language of Queries in databases. In some you have to write a SQL statement to get results. In Access you can build a query using the query builder. In the background, Access creates a SQL statement from the Graphic User Interface you used to create the query. Because I thought you understood queries, I gave you a sample SQL statement. If you build your query in Access using the GUI, then the SQL statement is created automatically and you do not need to try and write it or use the one I gave you.

Does your new query give you the results you were looking for. If yes, then nothing further is required.

 

[behedwin]

Thanks for your response.
No my new query does not give me the answer im looking for.
i get all records from the column HRWEBB CHECK

so i guess i need some kind of statement that makes the querry only display those that does not have the value KLAR in column HRWEBB CHECK.

 

[alansidman]

With your query open in Design view, in the upper left corner, you should have an icon that looks like a data sheet, click on it and select SQL. Copy your SQL statement and print it here so we can see exactly what your query looks like and attempt to fix it.

 

[behedwin]

This is the SQL code i get in my query

SELECT Log_Table.[HRWEBB CHECK], Profile_Table.FirstName, Profile_Table.LastName, Log_Table.StartDate, Log_Table.EndDate, Log_Table.Reason, Log_Table.Comment INTO CheckLog_Table
FROM Profile_Table INNER JOIN Log_Table ON Profile_Table.Profile_ID = Log_Table.Profile_ID_SK;

 

[alansidman]

Need a where statement. In your QBE, on the criteria line of the field [HRWEBB CHECK], type <>"KLAR"

This should then appear in your SQL statement as

WHERE [HRWEBB CHECK] <> "KLAR"

 

[behedwin]

That seems like it worked.. a little bit.

Now i get a different result.
My sql code is this

SELECT Log_Table.[HRWEBB CHECK], Profile_Table.FirstName, Profile_Table.LastName, Log_Table.StartDate, Log_Table.EndDate, Log_Table.Reason, Log_Table.Comment INTO CheckLog_Table
FROM Profile_Table INNER JOIN Log_Table ON Profile_Table.Profile_ID = Log_Table.Profile_ID_SK
WHERE (((Log_Table.[HRWEBB CHECK])<>"KLAR"));

This gives me tho the wrong result.
I have 14 records in log_table that does not contain the word KLAR
but in the result i only get 2 records... the other 12 are not showing.


edit
here are some screens that might help to see the error
http://prntscr.com/h7eh82
http://prntscr.com/h7ehkp

 

[behedwin]

An update

i changed the last row to this

WHERE (((Log_Table.[HRWEBB CHECK]) Is Null));

changing the question to look for empty cells instead of looking for NOT EQUAL to "KLAR"

But even with IS NULL
Im not getting all records when im checking the result.
I miss two two this times
So its like some records are not registrated as "empty" or not "KLAR" even tho they are....

edit

i figured out what record was wrong.
i removed those and then it pretty much work.
however this code

CurrentDb.Execute "UPDATE log_table SET [HRWEBB CHECK]='' WHERE Log_ID=" & Form_Profile_Form.Log_Listbox
Form_Profile_Form.Log_Listbox.Requery

i use to unset the value KLAR
so when i do that the sql query is not picking up on the new empty row
it like the code to unmark the record of the value KLAR is not registrated as null.

 

[behedwin]

Wohoo i solved it

I changed so i instead always have a default value of "*" in the HRWEBB CHECK column.
Then i search for that instead with the query.

So now i get the result i want.

But how do i use this table then.
How do i run the query question every time someone press a button so i can display the latest version of my table?

 

[alansidman]

So if I understand correctly, you want to run this query from a command button. To do that you need to have a Form created. Put the command button on the Form and link your code to the Command Button. Users should never have access to your tables. This is DB no-no. They should only see information in Forms, queries or reports.

It might be helpful if you walked through this tutorial on Access if you are going to take this further.

http://www.accessmvp.com/strive4peace/

 

[behedwin]

Think i did it... or atleast it works a bit as i want.

I have a few forms now and i can present my information :D

Uploaded my file here: https://ufile.io/ujbk6
If you want to have a look.

Im sure improvements can be done since this is my first access project.
I already have ideas of how to divide up the information in more tables... but first i want to build everything... get users to start using it and then i can start over building a better version

if you find any major errors, let me know