?
Solved

Run query from an Event Procedure MS Access

Posted on 2014-12-08
3
Medium Priority
?
561 Views
Last Modified: 2014-12-09
I have an Access form (MyForm) that has an event procedure under its On Activate event.

MyForm has a textbox on it (MyTextBox).

I have a table (MyTable) that contains a small list of specific dates.

In the On Activate event procedure, I want to query MyTable to see if the current date is one of the values in the table (field name is DateField).  If it is, set the value of MyTextBox to the current date. If it isn't, do something else.

I don't want the query results displayed in a datasheet that I have to wind up closing or anything like that. I just want to query the table for the current date value. If it's in there, put that value in the textbox and be done with the query.

I do not want to use DLookup in any way, shape or form to accomplish this. I cannot stand DLookup. It always produces a noticeable annoying delay between the time it executes and the time it populates the control.

Thank you.
0
Comment
Question by:dbfromnewjersey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 1200 total points
ID: 40488200
Dlookup or DCount is the straight-forward way to do this.

dim intCount as integer
intCount = DCount("*","MyTable", "format(DateField,'yyyymmdd') = '" & format(Date(),"yyyymmdd") & "'")
if intCount > 0 then
    '* The current date exists in MyTable
    MyTextBox = Date()
Else
    '* The current date does not exist in MyTable
End if

Open in new window

0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 40488244
What pdebaets posted should get you going...

Just FYI
In some cases dcount may be just as slow (or slower) than Dlookup...
(Because Dcount will force a count of all matching values..., whereas Dlookup will only find the first match)

I cannot stand DLookup. It always produces a noticeable annoying delay between the time it executes and the time it populates the control.
I have never had a real problem with the speed of any aggregate function unless the underlying data (or container object) was too complex...

JeffCoachman
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 600 total points
ID: 40488370
> I have never had a real problem with the speed of any aggregate function
> unless the underlying data (or container object) was too complex...

Neither have I, so maybe you have (had) other issues.

So, it is as simple as:

If IsNull(DLookup("DateField", "MyTable", "DateField = Date()")) Then
    ' Do something else.
Else
    Me!MyTextBox.Value = Date
End If

Of course, if you insist, you can open a recordset and check for records:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select DateField From MyTable Where DateField = Date()")
If rst.RecordCount = 0 Then
    ' Do something else.
Else
    Me!MyTextBox.Value = Date
End If
rst.Close
Set rst = Nothing
Set dbs = Nothing

In any case, to speed up things, add an index on DateField.

/gustav
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question