combobox filter

[bbrazeau via AccessMonster.com]

I have tables Products and Customers. they are related thru pk in Customer
and fk in Products. The customer field in products is a lookup field from
Customers. I have a form for Products which I have set to not allow edits. I
have a combobox control on the form that lists the customers and its control
source is the Customers from table. I want to be able to select a customer
from the combobox and press a button to have the form filter on only Products
for that Customer. I have done this but only by turning allow edits back on
via the comboboxes got focus event Me.AllowEdits= True. This defeats the
purpose as the end user can mistakenly edit the record and my purpose was to
have a "viewing" only form that is safe from corruption. Any Ideas?

 

[bbrazeau via AccessMonster.com]

Just answered my own question. I used the comboboxes lost focus event and Me.
AllowEdits=False seems to have worked does anyone know of a problem this
could create?

 

[tina]

i've never used that solution, so i can't say. normally i would do one of
the following:

1) set the Locked property of all the form controls (except the "pick a
company" combobox control) to Yes, in form Design; and leaving the form's
AllowUpdates property set as Yes, in form Design. that solution needs no
code at all.

or 2) use a mainform/subform setup. use the subform to display the records,
with AllowUpdates set to No in subform form Design. and put the combobox
control on the main form, with AllowUpdates set to Yes in mainform form
Design.

The customer field in products is a lookup field from
Customers.

strongly recommend that you get rid of any and all Lookup fields in your
tables. for more information, see
http://home.att.net/~california.db/tips.html#aTip8.

hth

 

[bbrazeau via AccessMonster.com]

Thanks for the reply Tina, I'm going to take heed of what was written about
"lookup fields" in your link. I do have a question though, both ways that you
mention still seem to allow the user to alter records, and my intension was
to make a secure form that cant be edited. I want to have a form where the
user can find/view/print information but change nothing, leaving all record
edit/deletion/addition to a databse adminstrator. It seems to me with all the
databases out there this would be a common requirement and would therefore
have a commonly accepted and thouroughly tested proceedure for this. Is
there a "generic" way of accomplishing this, for a simple database? Anyone??

 

[Rick Brandt]

Thanks for the reply Tina, I'm going to take heed of what was written
about "lookup fields" in your link. I do have a question though, both
ways that you mention still seem to allow the user to alter records,
and my intension was to make a secure form that cant be edited. I
want to have a form where the user can find/view/print information
but change nothing, leaving all record edit/deletion/addition to a
databse adminstrator. It seems to me with all the databases out there
this would be a common requirement and would therefore have a
commonly accepted and thouroughly tested proceedure for this. Is
there a "generic" way of accomplishing this, for a simple database?
Anyone??

Set all controls on the form to Locked = True or set the RecordSetType property
of the form to Snapshot.

 

[bbrazeau via AccessMonster.com]

Tina, I eliminated all lookup fields, tried your 2nd "mainform/subform
"solution and instead set the locked property of what is now a textbox to
"true". The link between mainform/subform still seems to work and after also
setting the allow addition/deletetions properties for the form to "no" the
form won't allow anyone to alter the info in any way. If I can make a couple
buttons that will open various reports I think I will have a simple interface
I'm trying to get. Does anyone use this or a similar method?? Is there a
"standard" way of accomplishing this??

 

[bbrazeau via AccessMonster.com]

Hi Rick, will I be able to put buttons on the form that will open reports
based on the main and subforms current data? Could I put a password on the
form that would allow me to change to a dynaset type property so I alone
could edit/add/delete records? This would save having to build a seperate
user interface for editing/adding/deleting.

Thanks for the reply Tina, I'm going to take heed of what was written
about "lookup fields" in your link. I do have a question though, both

[quoted text clipped - 7 lines]

there a "generic" way of accomplishing this, for a simple database?
Anyone??

Set all controls on the form to Locked = True or set the RecordSetType property
of the form to Snapshot.

 

[Rick Brandt]

Hi Rick, will I be able to put buttons on the form that will open
reports based on the main and subforms current data?

Yes. That only requires reading from the form, not editing.

Could I put a
password on the form that would allow me to change to a dynaset type
property so I alone could edit/add/delete records? This would save
having to build a seperate user interface for editing/adding/deleting.

Is this form opened from another form or from a menu or do you just have users
double-clicking in the db window? If not the last then you can open the form
using the acReadOnly option for all users except yourself. That could be based
on a password if you prefer, but depending on how you distribute your app that
won't be very difficult to get around.

If you merely want to provide guidance, then many things are possible. If you
really want to *prevent* users from changing data then you have numerous ways to
build barriers. The effectiveness of those will be proportional to the skill
level and cooperation of your users.

For example, if you use code to implement a password protected form then you
would have to give your users an MDE file. Otherwise anyone will be able to
examine the code to determine the password. Similarly they could just bypass
your read only form and open the table directly to change data. They could even
link to the tables from another file and change data.

True data security from *Users* cannot be achieved when using an MDB file to
store the data. For that you need a server database like SQL Server. The
closest you can come with an Access/Jet database is to implement User Level
Security and even that is hackable if the one desiring to do so knows how to
search the internet and is willing to spend a few dollars.

 

[bbrazeau via AccessMonster.com]

Thank you Rick, currently it is only an mdb file. The form is opened from a
button on the switchboard.I'd like to develope it as an mdb and make it into
an mde when the time comes. I sure some users would attempt to hack into it
but for the most part I'm worried about people's mistakes. ie, duplicating
entries,mispelling customer names, creating new records everytime they try to
enter something.Thanks again to all.