Locking entries

[jo15765]

I have imported excel files into an access database and made the table for my form. I now have a form that I designed, that allows the user to query the information on the table, and I have created a tick box to allow the user to check if the file is on site or off site at our storage unit. How can I set the form, to "lock" all aspects of the data, except the tickbox? I.E. I don't want the user to be able to edit any of the information, only have the option to check the tick box, and once the tick box is checked, completely lock the record and allow no further editing.

Also, when searching the database is it possible to search on two fields at the same time, or can you only search one field at a time?

[HiTechCoach]

1) Set the locked property to Yes for controls you do not want he user to be able to change.

2) use the on current event of the form to set the form's allow edits prperty

3) How are you searching?

I normally search using a query. This will allow you to have as many criteria as needed.

[jo15765]

The locking worked perfectly! Thanks for the information. How I am searching is I added a button, and from the menu that popped up I chose Record Navigation, then Find Record. But that is pulling up a windows search box, only allowing me to search off the top most field on the form. I think there is a way to search off of multiple fields by using a combo box, but I am not sure.....

[HiTechCoach]

Ah .. the built in find feature. You can also get to this with Ctrl+F.

I normally only use this by clicking a control then pressing crtl+f to search that single column.

There is a built-in feature for searching a combination of multiple controls on a form in Access called Filter By Form. This allows you to filter the current recordset of a form with a combination of multiple controls.


If the user will commonly search by the same few fields then I create a search form that has controls to search just the these few fields.

[jo15765]

That's great to learn that you can use ctrl+f to use this feature! The only potential problem that I am running into is that I am going to (for the most part) search only one field, BUT this field will be in the database multiple times. For example lets think of it like a phone number, 111-222-3333. The area code (111) will be the same ALWAYS, but the prefix (222 - this field will change, and can be duplicated) and last 4 (3333 - is duplicated pending the prefix). So I really need to be able to search the prefix for all numbers beginning with 222, and then be able to search from those results for the ONE entry that ends with 3346.

I hope that provides a further explanation of what I am trying to do. Currently, I am searching off of the prefix field, and then just hitting next record until I arrive at the entry I am needing. I know there HAS to be an easier way to do this!

[HiTechCoach]

The only potential problem that I am running into is that I am going to (for the most part) search only one field, BUT this field will be in the database multiple times.

Why would you be duplicating a field in multiple places? That usually indicates normalization issues.

For example lets think of it like a phone number, 111-222-3333. The area code (111) will be the same ALWAYS, but the prefix (222 - this field will change, and can be duplicated) and last 4 (3333 - is duplicated pending the prefix). So I really need to be able to search the prefix for all numbers beginning with 222, and then be able to search from those results for the ONE entry that ends with 3346.

A phone number field should exist only once in a database. If you need multipel phone numbers then yu would have multiple records not multiple fields.

Are you storing the phone number is three separate field or as a single field.

If as a single field then:

You would just search using the ? wildcard character like this: ???2223346 or maybe ???-222-3346

If multiple fields the use Filter By Form.

TIP 1: Access wildcard character reference


TIP 2: Even if you are searching a sinlge field, Filter by Form has really powerful searching/filtering options.

Have you tried the Filter By Form?

[jo15765]

No I haven't tried the filter by form option. I just googled it and am researching it now. A phone number is a bad example, let me think of a better one to try to explain. Let's say that a mechanic has paper records of all of his clientel and repairs. He contracts a company to store his records, and the way that the contractor inventories the records is they assign a alpha numeric Box # to each box, and then assign a id # to each record in the box, so let's say that Joe Dirt goes to have his oil changed. Once the sub contractor picks up this record, it will be inventoried to AAA111-14. Then let's say that 6 months later, Joe Dirt goes in to get his oil changed, once the sub contractor picks up that record he will be inventoried to ABB222-21.

So what I am working on both fields can be duplicated, box AAA111 can have up to 100 different peoples paper in there, so that can range from AAA111-01 up to AAA111-100. And the other box that Joe Dirt has records in ABB222 can range from ABB222-01 up to ABB222-100. So what I am needing to do is, be able to query off of both the box # and the id #, so I can find the exact record I am holding in my hand, not query either from the box number or id # and sort thro.

[HiTechCoach]

Is this new example actually what you are working on or is it another hypothetical example?

I do not really see any difference between these two examples if the box # and the id # are store in the same field.

It would help to know more about how you would design the database in your last example.

If I were normalizing this data, the box # and the id # would be fields in two separate tables. If this how you have it?

[jo15765]

Yes, this is example is actually what I am working on. And the box # and id # are in two separate fields because that is how I received it from the contractor (It is probably close to 600,000 different records). I received an inventory list in 15 different .csv files from the sub contractor, that contained the box #, id #, year, and customer name. I compiled all of these files into one .csv file and then imported them into access into one table, and from that designed a form. The form that I designed shows all of the data the Customer name, Year customer came in, the box # and the Id #, but I added a tick box to determine if I have the file, or if the file is off site with the sub contractor. And thanks to your previous post, on this form, I have locked all fields except the tick box, that I will need to be checked.

The sub contractor put a sticker on the back of each piece of paper that contains the box # and the id #, so what I am trying to do, is go through the paper that I have on my desk, and search the entries on the form and find the exact piece of paper I have in my hand, and of course mark the tick box.

Then once I have all of that done, I want to be able to build a query to produce a spreadsheet to show me all entries where the box is checked, but that is turning out harder than what I anticipated as well

Maybe this screenshot will help some...
[IMG]file:///C:/Users/Owner/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/Owner/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

[HiTechCoach]

Unfortunately you image did not attach properly.

One possible solution for the searching:
In the record source of the form you could combine the box # and the Id # into a singe field. This field could be displayed on the form and used for searching.

[jo15765]

That worked perfectly! I was trying to aviod trying to combine the two fields, since there were over 260,000 records, but a simple google search provided me with multiple options on how to combine the cells, and then I just re imported the data to the access table, thanks so much for your assistance!!

Now onto another question, I may need to start a new thread....

How do I gray out the fields that I do not want users to be able to change? I locked the fields, so there is no way they can modify the data, but I thought it may be just as easy to gray out the fields as it is to lock them. May not, any insight?

[HiTechCoach]

That worked perfectly! I was trying to aviod trying to combine the two fields, since there were over 260,000 records, but a simple google search provided me with multiple options on how to combine the cells, and then I just re imported the data to the access table, thanks so much for your assistance!!

No need to reimport. You could have used an updarte query.

Really there it not a need to combine the data in the table. It could have been done in a query as needed. If it were mine, I would keep them in separarte fields.

Now onto another question, I may need to start a new thread....

How do I gray out the fields that I do not want users to be able to change? I locked the fields, so there is no way they can modify the data, but I thought it may be just as easy to gray out the fields as it is to lock them. May not, any insight?

Simple. Change the back ground color to gray

You could also set the property Enabled = False ** Warning: This will prevent the ablility to click in the field and search.