Member Avatar for mrthomasbkk

Hello, i need help please, i need to create an trigger in ms access 2007, probably by vb code, but don't know how. The issue: i created a database in ms access 2007 and i need to make an time trigger to show every time when opening the database (it opens and shows first switchboard) a 6 weeks ahead (trigger) of expiring time of different data entries in the "permit expire" fields of one of my created tables or reports. If possible flashing dates or anything similar. I was checking thru 3 different tutorials for ms access 2007 but can't find an solution. What are my options? And how can i go about it?

  • 3 Contributors
  • 3 Replies
  • 256 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

A practical, low‑maintenance approach that builds on ’s “open a form instead of the switchboard” idea and ’s date‑checking suggestion is: 1) create a query that finds permits expiring within six weeks, 2) bind a small “alerts” continuous form to that query and make it the startup form (or call it from an AutoExec macro), and 3) use conditional formatting (not raw blinking) to make near‑expiry rows obvious. This keeps logic in a single place and is easy to maintain in Access 2007.

An example Access SQL for the query (save as qryExpiring6Weeks):

SELECT *
FROM Permits
WHERE PermitExpire BETWEEN Date() AND DateAdd("ww", 6, Date());

Bind a continuous form (frmExpiries) to that query. In the form’s design use Conditional Formatting for the PermitExpire control with an expression like
[PermitExpire] <= DateAdd("d",42,Date()) to change background color for near‑expiry items. To have the alerts show on startup either set frmExpiries as the Display Form in Office Button → Access Options → Current Database, or create a macro named AutoExec with an OpenForm action pointing to frmExpiries.

If you really want a “blinking” visual, avoid toggling control colors on continuous forms (Access will apply property changes to every row). Instead blink a small unbound header label or icon; it draws attention without breaking per‑row formatting. Example safe code in the form module:

Private Sub Form_Load()
    Me.TimerInterval = 500
End Sub

Private Sub Form_Timer()
    Static Blink As Boolean
    Blink = Not Blink
    Me!lblAlert.Visible = Blink
End Sub

Troubleshooting notes: ensure PermitExpire is a Date/Time field (or convert only valid text dates), place the front‑end in a trusted location so AutoExec runs, and prefer color/icons or a modal dialog for accessibility instead of rapid flashing.

Recommended Answers

All 3 Replies

Member Avatar for DenisOxon

Have you got a query and possibly a form that show the data you wish to display ?

If so then open that instead of switchboard on startup.

Denis

Member Avatar for mrthomasbkk

Have you got a query and possibly a form that show the data you wish to display ?

If so then open that instead of switchboard on startup.

Denis

Thanks Denis, but the database should work like an application so i would need to have then at least still the dates which appear as to expire in 6 weeks in the fields, to then flash as for a warning or similar. How do I do that?

Member Avatar for AndreRet

Try the following -

Dim myDate As Date, ExpiryDate As Date

rsDates.Open "SELECT TheDate FROM MyTableDates", cnDate, adOpenStatic, adLockOptimistic

MyDate = rsDates!TheDate
ExpiryDate = Now

If MyDate < ExpiryDate Then
   'Whatever to happen here
      Else
   'Flash code here
End If

You can refine this much more by using the DateDiff Function of which you can search using google and the term "DateDiff Function in VB6". This will return plenty of code samples to use.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.