Using user selections in a form to display info in a subform

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

VERY new user here, completely lost!

In FormA I present an Option Group whereby a user can
choose to display records in a table called CARs for a
particular EmployeeID or by a particular ProcessID. If
the user selects the option for Employee, the EmployeeID
Combo Box is enabled. If the user selects Process, the
ProcessID Combo Box is enabled. After making their
selection, they click Search Records (cmdSearchRecords).
The screen looks something like this:

Select your search critiera: (Option Group Choice)

[] Employee (EmployeeID Combo Box)
[] Process (ProcessID Combo Box)

Search Records (cmdSearchRecords)

The row sources for the Combo Boxes are based on queries:

Row Source for Process: SELECT ProcessQuery.CARs_Process,
ProcessQuery.Processes_Process FROM ProcessQuery;

SELECT CAREmployees.OriginatorID, CAREmployees.FullName
FROM CAREmployees;

The action of enabling and disabling the checkboxes in
the Option Group works fine, as do the Combo Boxes.

Below the search criteria screen information, on the same
FormA in the detail section, I have a subform
(subfrmSearchResults), in which I want to display all the
records that meet the user's search criteria. The records
will change as the user alters their choices. The
information in the subform is currently based on a query
(shown below) and displays in datasheet view.

SELECT CARReview.CARNum, CARReview.CreateDate,
CARReview.CARType, CARReview.FullName, CARReview.Process,
CARReview.Source, CARReview.Problem,
CARReview.CARApproveStatus FROM CARReview;

The problem is that I don't know what procedure to put
into cmdSearchRecords. I suspect it has something to do
with a filter and/or a query with WHERE, and setting up
strings that can be changed on the fly, but have no idea
what to write nor where.

Can someone provide me with the code? Pretty basic stuff,
I know, but there are so many places to put code, and so
many possible commands to use I'm lost.

Many thanks,
Christine
 
-----Original Message-----
VERY new user here, completely lost!

In FormA I present an Option Group whereby a user can
choose to display records in a table called CARs for a
particular EmployeeID or by a particular ProcessID. If
the user selects the option for Employee, the EmployeeID
Combo Box is enabled. If the user selects Process, the
ProcessID Combo Box is enabled. After making their
selection, they click Search Records (cmdSearchRecords).
The screen looks something like this:

Select your search critiera: (Option Group Choice)

[] Employee (EmployeeID Combo Box)
[] Process (ProcessID Combo Box)

Search Records (cmdSearchRecords)

The row sources for the Combo Boxes are based on queries:

Row Source for Process: SELECT ProcessQuery.CARs_Process,
ProcessQuery.Processes_Process FROM ProcessQuery;

SELECT CAREmployees.OriginatorID, CAREmployees.FullName
FROM CAREmployees;

The action of enabling and disabling the checkboxes in
the Option Group works fine, as do the Combo Boxes.

Below the search criteria screen information, on the same
FormA in the detail section, I have a subform
(subfrmSearchResults), in which I want to display all the
records that meet the user's search criteria. The records
will change as the user alters their choices. The
information in the subform is currently based on a query
(shown below) and displays in datasheet view.

SELECT CARReview.CARNum, CARReview.CreateDate,
CARReview.CARType, CARReview.FullName, CARReview.Process,
CARReview.Source, CARReview.Problem,
CARReview.CARApproveStatus FROM CARReview;

The problem is that I don't know what procedure to put
into cmdSearchRecords. I suspect it has something to do
with a filter and/or a query with WHERE, and setting up
strings that can be changed on the fly, but have no idea
what to write nor where.

Can someone provide me with the code? Pretty basic stuff,
I know, but there are so many places to put code, and so
many possible commands to use I'm lost.

Many thanks,
Christine

.
Click to expand...
Hi Christine,
1. In the properties of the subform click the build button
of the record source and then click save to save the sql
as a query, perhaps named qrySearchResults.

Add to this query the fields EmployeeID and ProcessID.

This will make a later step easier as you can reference
the name of the query rather than the entire sql string.
Plus if required, you can change the design of the query
without having to redo your code.

2. Use the following air code as an example for the
button, cmdSearchRecords. I have not included error
handling.

private sub cmdSearchRecords_OnClick()
dim strSQL as string

If Option_Group_Choice = 1 then
if not isnull(cboEmployeeID) then
stSQL="[EmployeeID]=" & cboEmployeeID
end if
Else
if not isnull(cboProcessID) then
strSQL="[ProcessID]=" & cboProcessID
end if
End If

If len(strSQL)>0 then
strSQL=" WHERE (" & strSQL & ")"
End If

strSQL="SELECT * FROM qrySearchResults" _
& strSQL & ";"

' following line assumes that the subform control is named
subfrmSearchResults
subfrmSearchResults.form.RecordSource=strSQL

End Sub

Luck
Jonathan
 
Thank you, Jonathan. I have done as you suggested, but
keep getting "Compile error: Can't find project or
library" when it gets to...

If Not IsNull(cboCARNum) Then
stSQL = "[CARNum]=" & cboCARNum
End If

My original Record Source for the subform was "SELECT
CARReview.CARNum, CARReview.CreateDate,
CARReview.CARType, CARReview.FullName, CARReview.Process,
CARReview.Source, CARReview.Problem,
CARReview.CARApproveStatus FROM CARReview;

I added the table CARs, and with it the field
OriginatorID (OriginatorID links to the Employees table,
so I can fetch the EmployeeID to assign to the
OriginatorID. The Employees table also has a query so I
can get the FullName of the employee.)

From the table CARs, I also added ProcessID. ProcessID is
linked to a table called Processes so I can get the name
of the process.

After adding the above fields to the query, it asked me
to save it, which surprised me since I thought I was just
modifying the exiting one. I called
it "qryCARReviewSearchResults".

So, the record source for the subform
is "qryCARReviewSearchResults". However, the fields
within the subform are all from the main table called
CARs. It doesn't give the option of changing the record
source to the query. This is where *I think* the problem
lies: too many queries looking at other queries - The
poor programme it doesn't know where it's supposed to
look! Can't say I blame it!!

Any more help you can provide will be deeply appreciated!

Christine
P.S. I have looked in Help for the "cbo" (as in If Not
IsNull(cboCARNum), but can't find it. What does "cbo"
mean?
 
Back
Top