Hi HS,
I appreciate you patience with this problem - thanks for
taking the time to help me sort it out.
Here's the answer and background to your questions
1 If I open my frmSearch only and do not apply any
filters and click the cmdbutton with your code it says the
OpenForm action was canceled. If I open the form, apply a
search it still says the same.
2 My search form consists of a number of combo
boxes, operators and text boxes. It was adapted from the
Dynamic Search Form available at Dev Ashish's site. It
generates dynamic SQL based on the table you select, the
field you select from that table and the critera you
enter. So for example, if I want to search all names that
match Smith I would select tblFarmers from a combo box,
select the field called Surname from another combo box,
select my operator =, and then enter my criteria Smith.
The search form then applies a Where statement to the SQL
of the subform and filters it accordingly. The search
form works perfect.
3 Currently I can go back to my main form using
a 'double-click' code (published below for info) to take
me back to a particular record but, very often, situations
arise where this is not the record the user wants to see
so they have to go back to the search form, double-click
the next record to see if that is the correct record and
it can get monotonous if the user has to go through a
number of records. Although the search form is well
designed to cater for most searches some staff are scared
by its detail and they prefer to look at found records
instead of drilling down for a deeper search - theres
always one!
3 That is what I am trying to do with your code is
to return me back to the main form with the 'searched'
records. As the search form allows users to search on
different fields other than Surname, which can be found on
my main form, other fields like Measures and Amounts are
not directly available on my main form but are availabe on
subforms on my main form. That is why I am trying to link
the searched records to the FarmerID, which is found on my
frmSearchSubForm and my Main Form (frmMainForm).
4
I hope this all makes sense and sheds light on my query.
Cheers
Tony
-----Original Message-----
It does not matter that the subform has SQL as the recordsource.
First, try opening the form without the filter. Let us see what happens.
Next, exactly what you are searching for in your search form and what are
you getting back?
For example, the search uses a Last Name criteria. When i enter "Smith"
i get 10 records for the smiths in the database
Next, How would you link this back to the form you want to open?
example: I want the new form to show me all the Smiths.
HS
Hi,
The search subform is being controlled by SQL - not sure
if I can pass it to main form as main form does not have
same fields.
My form is not open when I click my button which has your
code. Totally stumped on this one...!
CHeers
Tony
-----Original Message-----
is the form already open? if yes, you'll have to close
it first.
message
Hi
I'm getting the error...
The OpenForm Action was canceled
...any clues?
Tony
-----Original Message-----
Looks ok to me. what is the error you are getting?
message
Thanks for your reply - I note your point about the
search
functionality - it has caused me other problems
having
it
on a different form - I will include it on my main
form
in
future.
Anyway, my txtFarmerID appears as a text box
(datsheet
view) on a subform on my search form - this is how
the
code looks now but I can't get it working...can you
see
what is wrong.
Dim stDocName As String
stDocName = "frmMainForm"
DoCmd.OpenForm stDocName, acNormal, , "[FarmerID]
= """
& [frmSearchSubForm]![txtFarmerID] & """"
Thanks
Tony
-----Original Message-----
yes, it is possible, and there are a few ways to do
it.
First, I would include the search functionality in
the
main form rather than
using a separate form.
anyway, since you are using a separate form:
use the DoCmd.OpenForm and set a filter
example:
DoCmd.OpenForm mymainform, acNormal, , "[FarmerID]
= """ &
Me.txtFarmerID &
""""
HS
message
Hi Group,
Is this request possible?
Is it possible to return the records of a search
form to
my main form so that it shows 'searched' records
only.
For example, if my search form returns 10 Mr
Smith's
I
would like to open my main form to show these 10
Mr
Smith's only. The search criteria can vary as its
not
always surname that is searched on but both forms
do
have
a field called FarmerID - maybe I could use this
to
filter
the main form.
Over to the experts
Tony
.
.
.
.
