Populate Other Fields with Result after On Click Event

[martin DH]

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

Private Sub cmdCountNotices_Click()
On Error GoTo Err_cmdCountNotices_Click
 
  Dim stQryName1, stQryName2 As String
 
  stQryName1 = "qryOpenNotices"
    DoCmd.OpenQuery stQryName1, acNormal, acEdit
      stQryName2 = "qryClosedNotices-90"
    DoCmd.OpenQuery stQryName2, acNormal, acEdit
 
Exit_cmdCountNotices_Click:
    Exit Sub
Err_cmdCountNotices_Click:
    MsgBox Err.Description
    Resume Exit_cmdCountNotices_Click
 
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

SELECT COUNT(tblNoticeBase.NoticeID) AS [Open Notices]
FROM tblNoticeBase
WHERE tblNoticeBase.ClientID Like (Forms!frmKeyAccounts!txtClientID) And ((tblNoticeBase.Status) Is Null Or (tblNoticeBase.Status)="U");

qryClosedNotices-90 SQL

SELECT COUNT(tblNoticeBase.NoticeID) AS [Closed Notices - 90 days]
FROM tblNoticeBase
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!

[Rabbit]

You can either use a recordset or the DLookup() function.