Controlling query criteria from an unbound form field

[DeZZar]

Hi all,

Unfortunately I am quite a novice with Access!! I've created a number
of data bases for my work however becuase my skills are limited to
really built in functionality and wizards my programs are not really
user friendly.

I have searched and searched and tried numerous times to get the
following right to no avail - and I think its really becuase of my
lack of understanding.

A little background to assist:

Goal of my database. We archive customer files - and they are
archived via a barcoded box. So essentially I have two tables and two
forms setup. One table contains records of the Barcoded boxes. The
other contains the all the files. They link & filter simply by
connecting the Barcode. Ie: you open the form for the box - then
click "show files" and it simply opens the second form filtering by
the Barcode.

Unfotunately when I create a new file record attached to a box - I
need to retype the barcode so I can be sure these mary up.
Pathetically simple I know - but I can live with that.

THE ISSUE. I then have a query that filters the file records table by
a given barcode. Attached to this is a report that runs from the
query (query is set to display data from both tables) and simply
displays all filtered files according to barcode.

What I want is for my main menu screen (unbound form - which contains
all the buttons to various forms & searches) to use an unbound field
to tell my query/report to filter by what I type in.

ie: (bank field - type barcode) [Press a button that opens the
filtered report]. thats it...

Right now, I have a button that opens the query. I then paste in the
barcode I want a report on. I hit save, close it and then open the
associated report.

To most of you guys the solution is probably incredibly simple, so
please don't laugh.

Please help!

Cheers
Dezzar!

[DeZZar]

can no one help with this??

[Wayne Gillespie]

On Thu, 21 Aug 2008 20:47:05 -0700 (PDT), DeZZar <de**************@gmail.com>
wrote:

>Hi all,

Unfortunately I am quite a novice with Access!! I've created a number
of data bases for my work however becuase my skills are limited to
really built in functionality and wizards my programs are not really
user friendly.

I have searched and searched and tried numerous times to get the
following right to no avail - and I think its really becuase of my
lack of understanding.

A little background to assist:

Goal of my database. We archive customer files - and they are
archived via a barcoded box. So essentially I have two tables and two
forms setup. One table contains records of the Barcoded boxes. The
other contains the all the files. They link & filter simply by
connecting the Barcode. Ie: you open the form for the box - then
click "show files" and it simply opens the second form filtering by
the Barcode.

Unfotunately when I create a new file record attached to a box - I
need to retype the barcode so I can be sure these mary up.
Pathetically simple I know - but I can live with that.

THE ISSUE. I then have a query that filters the file records table by
a given barcode. Attached to this is a report that runs from the
query (query is set to display data from both tables) and simply
displays all filtered files according to barcode.

What I want is for my main menu screen (unbound form - which contains
all the buttons to various forms & searches) to use an unbound field
to tell my query/report to filter by what I type in.

ie: (bank field - type barcode) [Press a button that opens the
filtered report]. thats it...

Right now, I have a button that opens the query. I then paste in the
barcode I want a report on. I hit save, close it and then open the
associated report.

To most of you guys the solution is probably incredibly simple, so
please don't laugh.

Please help!

Cheers
Dezzar!

Open your query and in the criteria line under your barcode field instead of
typing the barcode in, put -

Forms!NameOfMenuForm!NameOfUnboundField

(replace names with your form/control names)

Now when you open the query/report it will use whatever value you have typed
into the unbound textbox as the criteria.

Obviously the menu form needs to be open at the time and a value entered into
the unbound control for it to return the correct records.
Wayne Gillespie
Gosford NSW Australia

[Ron2006]

Also:
if you open/run the query when that menu form is NOT open then Access
will open an input form asking for the value to be entered manually.

Ron

[Wayne Gillespie]

On Mon, 25 Aug 2008 05:34:39 -0700 (PDT), Ron2006 <ro******@hotmail.comwrote:

>Also:
if you open/run the query when that menu form is NOT open then Access
will open an input form asking for the value to be entered manually.

Ron

True. But it is bad interface design and likely to confuse the user.

Wayne Gillespie
Gosford NSW Australia

[Ron2006]

On Aug 25, 9:17*am, Wayne Gillespie
<[e-mail] :

On Mon, 25 Aug 2008 05:34:39 -0700 (PDT), Ron2006 <[e-mail]:

Also:
if you open/run the query when that menu form is NOT open then Access
will open an input form asking for the value to be entered manually.

Ron

True. But it is bad interface design and likely to confuse the user.

Wayne Gillespie
Gosford NSW Australia

Wayne, I agree completely with your comment. I was just trying to
tell DeZZar what was going to happen if the query was executed when
the form was NOT open.

Ron.

[DeZZar]

Wayne,

Thank you so much for your help! Such a simple solution has opened up
a world of functionality!!

Much appreciated!!

[jonceramic]

On Aug 25, 10:27*pm, DeZZar <[e-mail]:

Wayne,

Thank you so much for your help! *Such a simple solution has opened up
a world of functionality!!

Much appreciated!!

Dezzar,

I use the method above a lot if your query is going to be hardcoded
and not reused.

But, sometimes you want that window to be opened by 2 different
forms. If that's the case, then hard coding a pointer in the query to
the form won't work.

Here's some other methods to consider for your toolbox.
1. Limiting records in your window.
Another fairly easy way is to trigger a filter based on the "after
update" of a text box, list box, or combo box. You use the me.filter
and me.filteron commands to set a filter and apply it. Check the help
files on these. (You'd think the filter and filteron methods would be
used when you create a wizard combo box with "Find a record on my form
based on the value I selected in my combo box". But, that just
creates a "FindFirst" in the AfterUpdate event.)

Basically, if you wanted to narrow the records on a results screen,
first create an unbound combo called "cmbRecordIDChooser" in your form
header with "RecordID" as the bound column. Then, you could enter
afterupdate code that looks like this:

dim strFilter as string
strFilter = "[RecordID] = " & me.cmbRecordIDChooser
me.Filter = strFilter
me.FilterOn = True

2. Use the "criteria" method to open a form/report.
The "Open" event for forms and reports contains an option called
"criteria". You can set the criteria just like strFilter is set above
when you run the Open command. (This code is used by the button
wizard when you make a button with the "Open the form and find
specific data to display" option.)

The button Wizard will create code in this format.
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRecordDisplay"

stLinkCriteria = "[RecordID]=" & Me![cmbRecordIDChooser]
DoCmd.OpenForm stDocName, , , stLinkCriteria

3. Combine the 2 concepts.
Now, here's the trick. Why set something and then have a button to
click a separate time? There's NO reason you can't just use this code
in your combo box's after update event. Just cut and paste the Wizard
Code from the button into the Wizard Code for the dropdown. And,
boom.... Someone picks something in the combo box, and immediately,
your window pops up.

You can use these kind of methods in lots of "After Update" senarios -
text boxes, list boxes, etc.

In fact, I think it's one of the earliest ways I started using VBA
code.

Best of luck,

Jon