Form's txtbx Control lookup SQL
Posted on 2004-10-25
In all the Access Forms is a Header where Client Demographic Info is displayed, so User can confirm what Client they are working on.
The Header (Which is actually in the forms detail area) contains:
Record #, Record Date, Clinic, Last Name, First Name, DOB.
But I have re-designed how Record Date and Branch is entered and stored: From Client Table one to many ServiceList Table.
Key field in both is CustomerID.
Both the Record Date and Branch now needs to come from ServiceList Table.
I need the fields(form controls) Date and Branch to lookup to the ServiceList Table and get the latest (last) [ServiceBranch] for the current Client (CustomerID) selected in the Form.
I got a query to work with a static value (23837) in the WHERE clause to isolate the latest/last record, but I need it to get the current forms CustomerID. Which is in the current recordset.
I don't know how to get the WHERE clause right, but have a bunch of guesses.
See the "OR WHERE" below.
========= CODE START ==========
SELECT TOP 1 dbo.ServiceList.ServiceBranch AS MaxSB
FROM dbo.Clients INNER JOIN
dbo.ServiceList ON dbo.Clients.CustomerID = dbo.ServiceList.CustomerID
WHERE ((dbo.Clients.CustomerID) = (23837))
-- or WHERE ((dbo.Clients.CustomerID) = (Me.CustomerID))
-- or WHERE ((dbo.Clients.CustomerID) = (Me.RecordSource ))
-- or WHERE (dbo.Clients.CustomerID = Me.Form.RecordSource.CustomerID)
-- or WHERE (dbo.Clients.CustomerID = Me.Form.txtbxCustomerID.value)
ORDER BY dbo.ServiceList.[NSCL-Date] DESC
========= CODE END ==========
What is the WHERE, that I need.
oh yea that is clear as mud. :-)
I have also thought about, leaving the txtbox Controls unbound and setting them in the Open or Current event of the Form.
But I don't know how to embedd a SQL query into a Access event and properly state the WHERE clause.
Private Sub Form_Current()