Query a Form's Underlying RecordSource

  • Thread starter Thread starter Linus
  • Start date Start date
L

Linus

I have exhausted my search for a solution...I would appreciate your help.

I have a Main Menu from where I launch a small pop-up form that lets the
user select a "department" name from a combo-box. After he does this, a
standard form is displayed that shows specific information about each person
in the department selected. This works fine.

After the user displays this latter form, I am trying to get a command
button on the form to permit him to further search the individual records
associated with the "department" initially selected from the combo-box. This
really amounts to a "drill-down" which will enable the user to now locate
people within the "department" that have a unique qualification, based on a
parameter search through several fields.

The paramter search is not the problem, for it works fine, as well...EXCEPT
it searches the entire company's database, rather than the selected
departmental subset of the company Db.

I need assistance in restricting the second search routine to the
pre-selected department's list of people.

Much thanks, in advance...
 
I have exhausted my search for a solution...I would appreciate your help.

I have a Main Menu from where I launch a small pop-up form that lets the
user select a "department" name from a combo-box. After he does this, a
standard form is displayed that shows specific information about each person
in the department selected. This works fine.

After the user displays this latter form, I am trying to get a command
button on the form to permit him to further search the individual records
associated with the "department" initially selected from the combo-box. This
really amounts to a "drill-down" which will enable the user to now locate
people within the "department" that have a unique qualification, based on a
parameter search through several fields.

The paramter search is not the problem, for it works fine, as well...EXCEPT
it searches the entire company's database, rather than the selected
departmental subset of the company Db.

I need assistance in restricting the second search routine to the
pre-selected department's list of people.

Much thanks, in advance...
Click to expand...


I would have a form with the comboboxes in the form header. The form opens
showing no records. The form's recordsource is changed based on what is selected
in the comboboxes.
An example: http://www.psci.net/gramelsp/temp/Subform_Example2.zip
 
Thank you for your quick response. I reviewed your sample and though it is a
possible solution, it is limited by the fact the user will have to enter
several fields to continue the "drill-down" process. The second query to
which I am referring currently interrogates 7 fields with one push of the
button. This less user involved solution will be more acceptable to the user
community.

I will keep your solution in mind, however, for less-involved drill-downs.

Thank you.
 
You'll need to add some criteria like;

"Where [Department] = """ & Me![Department] & """"

It's difficult to suggest where you should put the criteria without knowing
more.
You may want to post the code behind the combo box on your pop-up form
as well as the code behind the command button on your main form. Someone
may be able to offer more specific advice.
 
Beetle,

Thank you, also, for your quick response. Your solution, though will still
require multiple (in this scenario...7 individual) parameter entries by the
user to do the all encompassing "drill-down" I am trying to provide.

After opening the "Main Form" from the initial "Departmental" selection
pop-up combo-box, it seems it should be an easy task to save this
departmental subset (RecordsetClone?) of the company's records and use the
command button on the "Main Form" to further query this subset.

Regards,

Linus





--
Linus


Beetle said:
You'll need to add some criteria like;

"Where [Department] = """ & Me![Department] & """"

It's difficult to suggest where you should put the criteria without knowing
more.
You may want to post the code behind the combo box on your pop-up form
as well as the code behind the command button on your main form. Someone
may be able to offer more specific advice.
--
_________

Sean Bailey


Linus said:
Thank you for your quick response. I reviewed your sample and though it is a
possible solution, it is limited by the fact the user will have to enter
several fields to continue the "drill-down" process. The second query to
which I am referring currently interrogates 7 fields with one push of the
button. This less user involved solution will be more acceptable to the user
community.

I will keep your solution in mind, however, for less-involved drill-downs.

Thank you.
Click to expand...
Click to expand...
 
Thank you, also, for your quick response. Your solution, though will still
require multiple (in this scenario...7 individual) parameter entries by the
user to do the all encompassing "drill-down" I am trying to provide.
Click to expand...

No it wouldn't. At least not if the criteria is used correctly, but without
knowing how your code is currently written, it's hard to offer anything
further.
--
_________

Sean Bailey


Linus said:
Beetle,

Thank you, also, for your quick response. Your solution, though will still
require multiple (in this scenario...7 individual) parameter entries by the
user to do the all encompassing "drill-down" I am trying to provide.

After opening the "Main Form" from the initial "Departmental" selection
pop-up combo-box, it seems it should be an easy task to save this
departmental subset (RecordsetClone?) of the company's records and use the
command button on the "Main Form" to further query this subset.

Regards,

Linus





--
Linus


Beetle said:
You'll need to add some criteria like;

"Where [Department] = """ & Me![Department] & """"

It's difficult to suggest where you should put the criteria without knowing
more.
You may want to post the code behind the combo box on your pop-up form
as well as the code behind the command button on your main form. Someone
may be able to offer more specific advice.
--
_________

Sean Bailey


Linus said:
Thank you for your quick response. I reviewed your sample and though it is a
possible solution, it is limited by the fact the user will have to enter
several fields to continue the "drill-down" process. The second query to
which I am referring currently interrogates 7 fields with one push of the
button. This less user involved solution will be more acceptable to the user
community.

I will keep your solution in mind, however, for less-involved drill-downs.

Thank you.
--
Linus


:

I have exhausted my search for a solution...I would appreciate your help.

I have a Main Menu from where I launch a small pop-up form that lets the
user select a "department" name from a combo-box. After he does this, a
standard form is displayed that shows specific information about each person
in the department selected. This works fine.

After the user displays this latter form, I am trying to get a command
button on the form to permit him to further search the individual records
associated with the "department" initially selected from the combo-box. This
really amounts to a "drill-down" which will enable the user to now locate
people within the "department" that have a unique qualification, based on a
parameter search through several fields.

The paramter search is not the problem, for it works fine, as well...EXCEPT
it searches the entire company's database, rather than the selected
departmental subset of the company Db.

I need assistance in restricting the second search routine to the
pre-selected department's list of people.

Much thanks, in advance...


I would have a form with the comboboxes in the form header. The form opens
showing no records. The form's recordsource is changed based on what is selected
in the comboboxes.
An example: http://www.psci.net/gramelsp/temp/Subform_Example2.zip
Click to expand...
Click to expand...
Click to expand...
 
No it wouldn't. At least not if the criteria is used correctly, but without
knowing how your code is currently written, it's hard to offer anything
further.
Click to expand...

Yes, it appears that the OP has a wrong mind-set. If, for example, this is the
initial query:

SELECT * FROM SomeTable WHERE Department = 'Accounting';

then the follow-on query becomes:

SELECT * FROM SomeTable WHERE Department = 'Accounting' AND
column_one = 'this' AND column_two = 'that' AND .............
column_seven = 'something else';

It is just a query. A where clause with one criteria or seven criteria, it is
really the same basic query. The first criteria can be on a separate control,
but the second control with the other seven criteria would still have to pick up
the criteria from the first control.


' open form with where clause
DoCmd.OpenForm "SomeForm", , , "Department = '" & "Accounting" & "'"

' command button on form thus opened
Private Sub some_command_Click()

Dim s As String
Dim c As Variant

' add to WHERE clause criteria
If Not IsNothing(MeFilter) Then
c = (c + " AND ") & "department = '" & Me.Filter & "'"
End If

' add to WHERE clause criteria
If Not IsNothing(Me.cbo_column_one) Then
c = (c + " AND ") & "column_one = '" & Me.cbo_column_one & "'"
End If

' and so forth for all other criteria

' eliminate initial AND
If Left(c, 5) = " AND " Then
c = Mid(c, 6)
End If

' if c is empty then use zero
If IsNothing(c) Then c = "0"

' combine initial SQL string and WHERE clause criteria
s = "SELECT * FROM SomeTable WHERE " & c & ";"


Me.RecordSource = s

End Sub
 
I discovered a simplistic solution through trial and error that works quite
well. I simply placed a new command button on the second form that displays
the initial query results (i.e., the listing of the employees in the
department I selected from the intial selection form).

I then applied the following code to open the same form again with a link to
the groupID (i.e., department code):


Dim stFormName As String
Dim stLinkCriteria As String

stFormName = "Resumes"

stLinkCriteria = "[AFSGroup]=" & "'" & Me![AFSGroup] & "'"
DoCmd.OpenForm stFormName, , "Resumes - SuperQ", stLinkCriteria

This drill-down works perfectly.

Thanks, for all of your help.
 
Back
Top