Query by form

  • Thread starter Thread starter Iona via AccessMonster.com
  • Start date Start date
I

Iona via AccessMonster.com

Hi-d-hi...

Im building a search engine for my db through query by form. Its a standard
contacts search. So Im building a query that will search by name, position,
location linked to a form through a macro. I have included an "or is null"
criteria.

I can combine the field parameters and entering a valid value in each field,
returns the right record, but when I try to enter one field paramter and
leave one blank, the null field triggers a return of all records. Can I
sidestep this neatly?

If any kind hearted hero is out there, please hear my call!!

iona
 
Iona said:
Hi-d-hi...

Im building a search engine for my db through query by form. Its a
standard contacts search. So Im building a query that will search by
name, position, location linked to a form through a macro. I have
included an "or is null" criteria.

I can combine the field parameters and entering a valid value in each
field, returns the right record, but when I try to enter one field
paramter and leave one blank, the null field triggers a return of all
records. Can I sidestep this neatly?

If any kind hearted hero is out there, please hear my call!!

iona
Click to expand...

You need to group each "or is null" within parenthesis so that the OR is only
applied to that one grouping. You might need to switch to SQL view on the query
to see this clearly. Consider the two examples below. (Line-separated and
indented for clarity)

(this one is wrong)

WHERE SomeField = Forms!FormName!TextBox1
OR Forms!FormName!TextBox1 Is Null
AND
SomeOtherField = Forms!FormName!TextBox2
OR Forms!FormName!TextBox2 Is Null

(this one is correct)

WHERE (SomeField = Forms!FormName!TextBox1
OR Forms!FormName!TextBox1 Is Null)
AND
(SomeOtherField = Forms!FormName!TextBox2
OR Forms!FormName!TextBox2 Is Null)

In the first example the two OR criteria stand on there own negating criteria
from any of the other AND'd criteria. In the second example they only negate
the criteria that is within the same parenthetic group without negating any
criteria outside the parenthesis.
 
Hi Rick,

thanks for taking the time out on ur saturday, I fooled around with some
commands and managed to get what looks like a working system. it seems too
simple to be true, what do u think?

In my query page, I built each field criteria as (Form 1, being the search
form with unbound text boxes to search the fields):

Like [Forms]![Form1]![surname] & "*"
Like [Forms]![Form1]![position] & "*"

etc.

It seems to give me exactly the results Im looking for. What do u think?

The other thing Im playing with now is populating a list with the resulting
records, which I can scroll, and then choose a single record to look at
closer.

I can create the search form, and the resulting form, but not the bit in
between, the list to be populated, with a button to fill open the correct
record.

Any suggestions?

Iona
 
Iona said:
Hi Rick,

thanks for taking the time out on ur saturday, I fooled around with
some commands and managed to get what looks like a working system. it
seems too simple to be true, what do u think?

In my query page, I built each field criteria as (Form 1, being the
search form with unbound text boxes to search the fields):

Like [Forms]![Form1]![surname] & "*"
Like [Forms]![Form1]![position] & "*"

etc.

It seems to give me exactly the results Im looking for. What do u
think?
Click to expand...

The difference between that strategy and the "or is null" strategy is that in
the former the wildcard is *always* affecting the criteria, not just when the
control on the form is not filled in. So if the Combo contained "John" it would
match "John" as well as "Johnson". If that behavior is not a problem in your
case then what you did is fine.
The other thing Im playing with now is populating a list with the
resulting records, which I can scroll, and then choose a single
record to look at closer.

I can create the search form, and the resulting form, but not the bit
in between, the list to be populated, with a button to fill open the
correct record.

Any suggestions?
Click to expand...

Just use your query as the RowSource for the ListBox making sure the bound
column setting is to the Primary Key field. One of the wizard options for a new
command button can do the part about opening the form to the record selected in
the list.
 
Hey Rick,

Can I impose on you again? Nearly there, so hopefully the last time!

Okay, Query is running smoothly.

2 Things.

I cant get the listbox to populate from the query properly. When I run my
command button, it opens up the results form. The listbox, populates once,
but wont refresh, even though the form is opening and showing the correct
results.

The results form should be embedded as a subform, but my command button is
still opening it as a form in its own right.

Help!?!

Iona
 
Iona said:
Hey Rick,

Can I impose on you again? Nearly there, so hopefully the last time!

Okay, Query is running smoothly.

2 Things.

I cant get the listbox to populate from the query properly. When I
run my command button, it opens up the results form. The listbox,
populates once, but wont refresh, even though the form is opening and
showing the correct results.

The results form should be embedded as a subform, but my command
button is still opening it as a form in its own right.

Help!?!

Iona
Click to expand...

Where is the ListBox?

What is the code behind your button?

If you have the results form embedded as a subform then your button does not
need to "open" anything.
 
Rick said:
Hey Rick,
Click to expand...
[quoted text clipped - 15 lines]
Iona
Click to expand...

Where is the ListBox?
Click to expand...

Im trying to tie it all to the same main form. The ListBox is directly under
the main search boxes. Following research, Im wondering do I need some kind
of refresh built into the listbox?(what would this look like?)
What is the code behind your button?
Click to expand...

I am running a macro from the button, I used the macro wizard, and it
specifies to open the queryresult form. Its this form that Im trying to embed
as a subform.

Just to further complicate things, what I ACTUALLY want is the command button
to populate the listbox AND the subform, while I can additionally
navigate/populate the subform through the listbox.
If you have the results form embedded as a subform then your button does not
need to "open" anything.
Click to expand...


Im open to simplifying things or trying alternatives if suggested. Where are
u from? Thanks for letting me bug you.
 
Iona said:
Rick said:
Hey Rick,
Click to expand...
[quoted text clipped - 15 lines]
Iona
Click to expand...

Where is the ListBox?
Click to expand...

Im trying to tie it all to the same main form. The ListBox is
directly under the main search boxes. Following research, Im
wondering do I need some kind of refresh built into the listbox?(what
would this look like?)
Me!ListBoxName.Requery

What is the code behind your button?
Click to expand...

I am running a macro from the button, I used the macro wizard, and it
specifies to open the queryresult form. Its this form that Im trying
to embed as a subform.
Click to expand...

So add a subform control to your main form while in design view and then make
your other form the SourceObject.
Just to further complicate things, what I ACTUALLY want is the
command button to populate the listbox AND the subform, while I can
additionally navigate/populate the subform through the listbox.
Click to expand...

In my opinion you don't need both. I would either have the subform (in
continuous view) without the ListBox or keep the ListBox and continue to open
the second form as a separate form (to a single record).
 
Thanks for the help Rick,

Star quality MVP work. Thanks.. I think I need to keep tweaking, and a few
more things to iron out. For now, I have to admit defeat, will tackle again
in the morning. Thanks for the great input.

Iona
 
Back
Top