Expiry Date, Invisible queries & using code to check a field

[ConfusedA]

Happy Holidays everyone!!

I had a question but I'm not sure if it's best suited for queries, coding or macros, it involves a query so i'm putting it here. However, I believe that code and/or a macro may be necessary to accomplish what I am looking for, so i'm going to break it into the three parts. If anyone feels this post is best suited for another location or if there are related posts (I was unable to find something similar in what I am looking for) please point me in the right direction. I will start with some background.

Background: I have built a database to keep track of memberships, and the memberships have different expiry dates. I have been able to automate determining when a membership expires, however I am trying to create a flashing button to the expiry list, only when a membership has expired.

Query: I have a query that produces the client name on expiry, so what I want to do is be able to check if the name field is null (no expirations), and if this is not true, then the button will flash.

I understand timer concepts for the flashing button, the issue I am having is running a query in the background (invisible on form loading) and the code required to compare the field value.

Macro: I imagine this is needed to make the query run on form loading, I have figured out how to get the query to run, but cannot figure out how to make it invisible.

Code: I figure I will need some pseudocode like so;
-if query.queryfield isnot null, then run macro, else do nothing, end if
however I am not sure what the actual code would look like

Any help would be appreciated.

Thanks,
ConfusedA

 

[ConfusedA]

Something that I forgot to mention that could be of importance: I am currently working in Access 2003.
Thanks in advance,
ConfusedA

 

[vbaInet]

Queries have no events.

Aren't you supposed to be checking against a customer's ID?

 

[ConfusedA]

Thanks for the response, I'm not sure if my initial question explained my problem correctly so I am going to try to clarify.

All I need is a way to show my users that IDs in the list have expired so that they can note them.

I don't need an event on my query, i just need to figure out how to run it in the background and use VB code in my form load to see if any values are returned via the query.

Essentially what I need is a timer function like so:

Private Sub Form_Timer()

'Checking the Query for values
'If there aren't values
If QueryExpired.Value = null then***1
CommandExpired.Visible = True
'If There are values
Else
'Causing the button to flash (until clicked)
If [CommandExpired].OnGotFocus = No Then***2
If [CommandExpired].Visible = True Then
[CommandExpired].Visible = False
Else
[CommandExpired].Visible = True
End If
Else
[CommandExpired].Visible = True
End If
End If
End Sub

The issues I am having are the starred lines
***1: I am not sure how to use this to check the query without running it, however I know that queries can be chained together so that running the last query will provide the information from the queries it relies on without opening them, so I imagine VB code can do something similar. Also, the value used here would be either name or expiry date, as both are used within the query.
***2: I am not sure what the right syntax is for this, however I know that if the item has focus I cannot make it invisible, so I need something to stop errors when this is the case.

Thanks once again,
ConfusedA

 

[ConfusedA]

I was wondering if the line above marked ***1 could use a Dlookup like so;
If DLookup("[Client]", "qryYearcheck").IsNull = True Then
This would check the client value within a query that I use to check expiry dates, and would only cause a change if the values returned as not null (this is what I want to achieve) however when I run this I get an error of 'object required'. Am I missing something in my code?

 

[ConfusedA]

I solved this, used a DCount instead for the If statement at ***1, and used simple intergers to solve problem ***2 by placing a variable set to 0 in the code and then changing it to 1 after a click, using an if variable <= 0 then...flash(see code above), else stop(see code above).

Thanks for the help!

 

[vbaInet]

Glad you got it all worked out on your own.

The DCount() function was the right choice and the use of a variable is also sensible.

Happy holidays!