How to "remove" or "secure" records after report prints

[goslincm]

I'm not sure how to do this or where. After users print a monthly report, I need the data in that report to be placed into a state where the user can no longer makes changes to it, yet it would be available should the supervisor need to make changes.

There will be several different users each printing a different monthly report.

 

[SgtJarrow]

Leslie,

I think that is what the problem was in the current step. goslincm was referencing the subform control incorrectly. If you helped correct that reference, that should have solved the query that sets the bit to "lock" the appropriate records.

But I guess we have to wait to get some more info at this point. Thanks for assisting though...your experience and knowledge has helped me in the past

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[lespaul]

Back at you!

Have a great day!

les

 

[goslincm]

Alright folks, we are ready for the next step as YES Leslie did help me get past the hiccup in the query. The records now update to "Not Editable"...

 

[SgtJarrow]

Great.

That hopefully should have been the harder of the two parts. But that depends on how you see the next step. The next step is basically setting the controls on the page where the users view the data to be "locked" if the IsEditable bit is false or 0.

The question at this point is how the form for viewing is setup. Is it one continuous form or is it one record per page? Do the users select a group of records based on filters or do they see all at once? Please give us a description of your users interface so we can give the best suggestion for "locking" the controls.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

The data entry form is set up so the voucher is viewed as one record per page. The users will be accessing the database at off-site locations using a shared drive.

 

[SgtJarrow]

Great. This is actually probably the easiest of the scenarios to work with.

Basically, you need to check the IsEditable field anytime a user moves from voucher to voucher. This can be accomplished with the OnCurrent Event of the form. if you have a smaller number of controls on the form the users can update, place the following in the OnCurrent event:

Me.[fieldname].Locked = Not Me.[IsEditable].Value
Me.[fieldname].Locked = Not Me.[IsEditable].Value
Me.[fieldname].Locked = Not Me.[IsEditable].Value

Replace fieldname in each line with a control on the form to lock and repeat as many as needed.

If you have a LOT of controls on the form, then post back here and I will try to explain some code to loop through all controls on the form.

Finally, you may need to play with the Not Me.[IsEditable].Value portion. You may need to remove the Not. You will have to try it as it will depend on how you defaulted the IsEditable field.

Let us know how it goes

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

Private Sub Form_Current()

Me.[voucher_id].Locked = Not Me.[IsEditable].Value
Me.[voucher_number].Locked = Not Me.[IsEditable].Value
Me.[voucher_fiscal_year].Locked = Not Me.[IsEditable].Value
Me.[voucher_end_date].Locked = Not Me.[IsEditable].Value
Me.[voucher_begin_date].Locked = Not Me.[IsEditable].Value
Me.[voucher_date.Locked] = Not Me.[IsEditable].Value
Me.[voucher_IsEditable.Locked] = Not Me.[IsEditable].Value
Me.[emp_id].Locked = Not Me.[IsEditable].Value

When I go to open the form, I receive a Compile Error, Method or data member not found.

It lands on the third line down (voucher_fiscal_year) and highlights the word "Locked

 

[SgtJarrow]

What kind of control is that? Is it a text box, a check box, etc....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

Evidentally a non-visible field because I cannot see if on my form. Perhaps that is my problem with that field, it is in the query but I don't see to have it on my form. It should be just a text box, let me add it.

 

[goslincm]

Alright, having added that voucher_fiscal_year control to my form, the code now stops and highlights my whole first line of code.

It indicates that it cannot find the field referenced in my expression.

Would my underscore in the names have anything to do with this?

 

[SgtJarrow]

Most likely not. The wrapping of the name in the square brackets "[]" allows for spaces and other characters in names. So you are covered there and the brackets are not even needed with underscores.

The problem is related the the IsEditable field. If this field part of the query that the form runs from? Is the IsEditable field on the form? One coll thing is you can put it on the form anywhere, then set the visible property to false and it will not show to the users but you can still reference it. Also, if it is on the form, check the Name property. Is should be IsEditable per our code.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

Yes, the IsEditable is part of the query that the form runs on.

Initially I had that named voucher_IsEditable but changed it to just IsEditable.

It is displayed on my form as well. So at this point this is what my code is looking like on the OnCurrent event for the voucher subform:

Private Sub Form_Current()
Me.[voucher_id].Locked = Not Me.[IsEditable].Value
Me.[voucher_number].Locked = Not Me.[IsEditable].Value
Me.[voucher_fiscal_year].Locked = Not Me.[IsEditable].Value
Me.[voucher_end_date].Locked = Not Me.[IsEditable].Value
Me.[voucher_begin_date].Locked = Not Me.[IsEditable].Value
Me.[IsEditable].Locked = Not Me.[IsEditable].Value
Me.[emp_id].Locked = Not Me.[IsEditable].Value

End Sub

But it is still not running...

 

[SgtJarrow]

hmmmm.....First, hide that IsEditable field and don't include it in the locking procedure. Since it is a code-only item that we are using for control, don't even let the users see it. And since they can't see it, we don't have to worry about locking it.

I just need to confirm the control on the form where the IsEditable data is displayed....It has a name property. That should be IsEditable. If you originally named is voucher_IsEditable and added it to your form, it would have assumed that name. If you then changed the table column name, the form does not necessarily change the name of the control. So please check the name of the control on the form.

The reason I think this is the issue is because the error you describe lends me towards that. Me in Me.[IsEditable].Value is a shorthand for the current form. [IsEditable] is the name of the control. Value is the property we which to use. Since the form can't find a control named IsEditable, you are getting the error....or so I think.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

Alright, I have the IsEditable as not-visible and I deleted it from the code.

Now, when I open the form, I receive NO error ;-)

 

[SgtJarrow]

Good. Do the controls "lock" when you are on a "locked" record? You can test this by going to a record that should be locked and seeing if you can change any values.

Also, you might want to think about a visual queue for your users that a record is locked. Something like a small square in a corner that is colored red if locked or some such. I guess this depends on your users.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

The records in my voucher form lock GREAT, however, the records in the related subform do not lock.

This is where I wondered if that IsEditable had to go into each of the tables. I cannot change my voucher date, etc. however I can change the expense amounts that are entered into my expense table (which is a subform).

I know you've helped TONS, and feel free to step-aside if you wish, I understand.

 

[goslincm]

I guess worse than that, when I want to enter a voucher for a new employee, and my employee form opens I am receiving a "invalid use of null" highlighted in my first line of code for the voucher subform.

Switchboard opens Frm_Employees where user is prompted to enter their userid.

Once userid is entered, frm_employees opens up, which contains the subform vouchers where we just put all the code.

I don't get that error for existing employees, but when I add a new employee, I do.

 

[goslincm]

Good morning, I like how this record locking is working. The only problem I am left with is this. When I want to add a new voucher, "invalid use of null" is displayed. The voucher table has the voucher_id as an auto number field, and in that same table, the IsEditable field, it defaulted to yes.

So, after printing a voucher, the records are locking, but I am also encountering this invalid use of null error when trying to add a new voucher. Is there any way around that?

 

[SgtJarrow]

goslincm,

Can you elaborate a bit more?? It sounds like you have some code somewhere that is running and it is seeing a Null that you haven't handled. Can you perhaps describe the process and maybe post the code that is producing the error??

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[goslincm]

Hi Robert, I'm running the code you suggested earlier in this post to me. I'm running it in the OnCurrent event for my voucher form.

Me.[voucher_id].Locked = Not Me.[IsEditable].Value
Me.[voucher_number].Locked = Not Me.[IsEditable].Value
Me.[voucher_fiscal_year].Locked = Not Me.[IsEditable].Value
Me.[voucher_end_date].Locked = Not Me.[IsEditable].Value
Me.[voucher_begin_date].Locked = Not Me.[IsEditable].Value

The main form is called frm_employees, it prompts a user to enter their employee id. When the user enters their employee id, my vouchers form opens, is a subform.