By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,556 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Populate Other Fields with Result after On Click Event

100+
P: 114
Hi all,

I think I'm just blanking on some basic concept here...in Access 2003 on XP.
Here's the process - the user enters an ID number, clicks the command button, and the other two fields are filled with the result of two queries. How do I fill the other two fields? Details below...

I have a form (frmKeyAccounts) that contains three textboxes and one command button (see below):

txtClientID
txtOpenNoticesCount
txtClosedNoticesCount
cmdCountNotices

frmKeyAccounts VBA
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCountNotices_Click()
  2. On Error GoTo Err_cmdCountNotices_Click
  3.  
  4.   Dim stQryName1, stQryName2 As String
  5.  
  6.   stQryName1 = "qryOpenNotices"
  7.     DoCmd.OpenQuery stQryName1, acNormal, acEdit
  8.       stQryName2 = "qryClosedNotices-90"
  9.     DoCmd.OpenQuery stQryName2, acNormal, acEdit
  10.  
  11. Exit_cmdCountNotices_Click:
  12.     Exit Sub
  13. Err_cmdCountNotices_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_cmdCountNotices_Click
  16.  
  17. End Sub
I have two queries that run with the On Click event of the command button (qryOpenNotices, qryClosedNotices-90). Both queries do count based on the client ID entered into txtClientID (full SQL below).

qryOpenNotices SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(tblNoticeBase.NoticeID) AS [Open Notices]
  2. FROM tblNoticeBase
  3. WHERE tblNoticeBase.ClientID Like (Forms!frmKeyAccounts!txtClientID) And ((tblNoticeBase.Status) Is Null Or (tblNoticeBase.Status)="U");

qryClosedNotices-90 SQL

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(tblNoticeBase.NoticeID) AS [Closed Notices - 90 days]
  2. FROM tblNoticeBase
  3. WHERE tblNoticeBase.ClientID Like (Forms!frmKeyAccounts!txtClientID) And ((tblNoticeBase.Status)="R") And tblNoticeBase.DTRes Between (Now()) And (Now()-90);
How do I fill txtOpenNoticesCount and txtClosedNoticesCount based on txtClientID by the On Click event of cmdCountNotices? Thanks!
Dec 10 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,396
You can either use a recordset or the DLookup() function.
Dec 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.