Inputbox variable to filter report

[Dusty]

Code:

Private Sub Report_Open(Cancel As Integer)
Dim RptValue As String

RptValue = InputBox("Please type a Wroker ID." & vbCr & vbCr & "To
view all workers leave the box blank.", "OCS ~ CMR")

Me.Filter = "WorkerID = """ & RptValue & """"
Me.FilterOn = True

DoCmd.Maximize
End Sub

On open the input box appears. I enter a User ID (which does exist) and
the report opens with no records.

I am familiar with the parameter quesry and DO NOT need instructions on
that! I need to use the value from the input box to filter the report.

Any help is appreciated.

 

[strive4peace]

Hi Dusty,

What is the data type for WorkerID in the table design? If you have it
set to be a lookup in the table design, perhaps it is really storing a
number but showing you text...

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Dusty]

Crystal:

The data type is text and it is a lookup. That's it! You are awesome!
The value being displayed in the table is not what is actually being
stored. The users network ID is being stored...This is different the
the WorkerID which is what is displayed...Thus a blank report when
entering the WorkerID in the input box. I think this has something to
do with the lookup and when it is being created....Seems like I recall
something about what field I want to have Access store.

At any rate, I think you for your help! You've made my day!

Dusty

 

[Dusty]

I read the Access Web link you provided me with. I understand now what
you mean about the number being stored. My follow-up question would be
"What's the alternative to lookup fields in tables?"

If I build a combo box in a form would I experienve the same problems?

Dusty

 

[strive4peace]

Hi Disty,

you are welcome

"If I build a combo box in a form would I experienve the same problems?"

No, that is where combos should be... just make sure they are not in the
table design. Users should never enter data directly into tables -- the
only people to look at the tables should be administrators and so the
real value as well as the real fieldname (don't use Captions either)
should show.

to help you understand a bit better, send me an email and request my
30-page Word document on Access Basics (for Programming) -- it doesn't
cover VBA, but prepares you for it because it covers essentials in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

oops... I meant Dusty

Warm Regards,
Crystal
*
have an awesome day
*

 

[strive4peace]

Hi Dusty,

{blush} ... thanks!

using lookups in table design before you learn much about Access is
actually very common... I recognized the "symptoms" <g>

happy to make your day! knowing that makes mine

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Dusty]

Thanks for the info.
One followup question...

Will the response time of the form increase if I change all of the
table fields to a text field and then build the lookup using the combo
box from object? My assumption is yes but just wanted your opinion.

Thanks again!
Dusty

 

[strive4peace]

Hi Dusty,

I am not sure what you are asking...

In the table design, the display control should not be set to combo for
any of the fields ...

Forms are the place for combos. Here is an example to pattern after:

*** Combobox Example ***

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*