Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

goslincm

MIS
May 23, 2006
292
US
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.

 
The easiest method I can think of is to include a field in the table where the data is stored, called IsEditable or some such. This should be a yes/no or bit is using a SQL backend. Default this value to Yes (or 1). When the user prints this data, you need to include some way to mark the printed record to be No (or 0). You can usually do this through the process that you use to print....the command button to print the data you have created also includes an update sub or query to update the records that it just printed.

Then, you need to include a check anywhere where the users could change the data, and check that IsEditable field. If Yes (or 1), allow them to make changes. otherwise I would give them a message samying the data is locked for editing.

If you need some specific help on a part of this, ask away.

=======================================
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
 
I didn't think this sounded like an easy thing to do.

The report is such that, it gathers information from three different tables. So based on what you are saying, I should include a field in each of these three tables that I could store the "Is Editable" answer in.

Correct on that much?

 
Not necessarily. If one of the tables is "primary" and the others just hold additional data, such as relational information, then you only need to IsEditable field in the main table. That is because you can always check against that main record to see if the record in related tables is editable.

I never said it was going to be an easy thing....just that this is the easiest method I could think of at the time. Don't hesitate to ask questions as you work on this, if you decide to go this route...Good luck!

=======================================
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
 
Morning Robert, yes I do have one table that is my "primary" table with others related to it and I've added the yes/no IsEditable field to it.

Now my next step that you indicate should be to mark the printed records from yes to no I also would need your help with if you can.

Thanks
 
Let me start this by saying....PLEASE create a copy of your database if this is a live version. I suggest a backup copy anyway to work on in case something doesn't work right you can "rollback" easily. Only work on backup/copies of your database....never work in an acitve/live copy. Once the backup/copy is acceptable, you make it the current/live version :)

***********

Sure thing. Somehow you are allowing the users to view and print a group of records. I hope you are using a query of some sort to determine which records are being displayed. What we need to do is take that same set of records and mark them from yes to no.

Start by make a new query exactly the same as the one you are using to display the records to print. I suggest copying and pasting that query. Give it a new name that makes sense that this is a query to "lock" records. Now open the query in dsign mode. Find and change the query type from SELECT to UPDATE. In the designer make sure that the update to field for the IsEditable field is set to No and that any other field that does not have a criteria is not showing. So you should only have No in the IsEditable's UpdateTo area and only other fields that have criteria under them.

Finally, in the code where the user prints the data (hopeuflly you have a command button for PRINT), just before or just after the print command - depending on your preference - you should add this line:

DoCmd.OpenQuery "query name"

Replace query name with the name you made above.

This should set those records that are about to be or just have been printed to not be editable.

=======================================
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
 
Hi Robert, just following your instructions right now. Currently my report prints for the users when they are viewing their current [voucher_id].

I do have the preview button with that code behind it. I tried adding to it, your code for the query, but obviously I am doing something wrong. I am getting Compile Error, wrong number of arguments or invalid property assignment.

Private Sub Command41_Click()
On Error GoTo Err_Command41_Click

Dim stDocName As String

DoCmd.OpenReport "rpt_travel_expense_report", acViewPreview, , "[voucher_id] = " & Me![voucher_id]
DoCmd.OpenQuery "qry_record_lockdown", acViewPreview, , "[voucher_id] = " & Me![voucher_id]

Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click

End Sub
 
Any other suggestions to help me move forward with this securing of my records after the user prints the form? Unfortunately I'm still stuck at this end.

Thanks
 
goslincm,

Been gone at a coference the last few days....just getting back into the daily routine. [smile]

Based on what you posted, I think the following line:

DoCmd.OpenQuery "qry_record_lockdown", acViewPreview, , "[voucher_id] = " & Me![voucher_id]

Should actually read something like:

DoCmd.OpenQuery "qry_record_lockdown"

and this part - "[voucher_id] = " & Me![voucher_id] - should be addressed in the query directly. Put Forms![FormName]![voucher_id} in the criteria section underneath the voucher_id field in the query. And you need to replace FormName with the name of the form you are working with.

=======================================
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
 
Thanks for posting again. I'm going to paste my current query that is used when printing the report. With it as it, I get prompted to input the voucher id number.

SELECT tbl_EMPLOYEES.emp_lname, tbl_EMPLOYEES.emp_fname, tbl_EMPLOYEES.emp_bus_phone, tbl_EMPLOYEES.emp_street, tbl_voucher.voucher_begin_date, tbl_NATURE_OF_BUSINESS.audit_type, tbl_NATURE_OF_BUSINESS.audit_id, tbl_EXPENSE.exp_amount, tbl_EXPENSE.exp_type, tbl_EXPENSE.exp_date, tbl_EXPENSE.exp_comments, tbl_EXPENSE.exp_miles_travelled, tbl_voucher.voucher_fiscal_year, tbl_EMPLOYEES.emp_city, tbl_EMPLOYEES.emp_state, tbl_EMPLOYEES.emp_zip, tbl_EMPLOYEES.emp_work_station, tbl_EMPLOYEES.emp_contact, tbl_EMPLOYEES.emp_contact_phone, tbl_LUG.Name, tbl_EMPLOYEES.emp_comments, tbl_voucher.voucher_id, tbl_EMPLOYEES.emp_id, tbl_voucher.voucher_end_date

FROM (tbl_EMPLOYEES INNER JOIN tbl_voucher ON tbl_EMPLOYEES.emp_id = tbl_voucher.emp_id) INNER JOIN (tbl_LUG INNER JOIN (tbl_NATURE_OF_BUSINESS INNER JOIN tbl_EXPENSE ON tbl_NATURE_OF_BUSINESS.audit_id = tbl_EXPENSE.Audit_id) ON (tbl_NATURE_OF_BUSINESS.audit_id = tbl_LUG.AUDIT_ID) AND (tbl_LUG.AUDIT_ID = tbl_EXPENSE.Audit_id) AND (tbl_LUG.LUG_ID = tbl_EXPENSE.LUG_id)) ON tbl_voucher.voucher_id = tbl_NATURE_OF_BUSINESS.voucher_id

GROUP BY tbl_EMPLOYEES.emp_lname, tbl_EMPLOYEES.emp_fname, tbl_EMPLOYEES.emp_bus_phone, tbl_EMPLOYEES.emp_street, tbl_voucher.voucher_begin_date, tbl_NATURE_OF_BUSINESS.audit_type, tbl_NATURE_OF_BUSINESS.audit_id, tbl_EXPENSE.exp_amount, tbl_EXPENSE.exp_type, tbl_EXPENSE.exp_date, tbl_EXPENSE.exp_comments, tbl_EXPENSE.exp_miles_travelled, tbl_voucher.voucher_fiscal_year, tbl_EMPLOYEES.emp_city, tbl_EMPLOYEES.emp_state, tbl_EMPLOYEES.emp_zip, tbl_EMPLOYEES.emp_work_station, tbl_EMPLOYEES.emp_contact, tbl_EMPLOYEES.emp_contact_phone, tbl_LUG.Name, tbl_EMPLOYEES.emp_comments, tbl_voucher.voucher_id, tbl_EMPLOYEES.emp_id, tbl_voucher.voucher_end_date
HAVING (((tbl_voucher.voucher_id)=[Forms]![frm_vouchers]![voucher_id]));
 
If you are getting prompted for the voucher_id, it's because frm_vouchers isn't open and doesn't have a value in the control voucher_id.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins
 
But it is open because its a subform on the main form that is opened.
 
I'm pretty sure the syntax for a subform is different, I'd have to look around to see what it is.

Leslie
 
Oh, well at least that would make some sense to me, thanks.
 
Lespaul, that worked.

But so I can learn here, how do you know WHEN something like this should be placed in your query, or be put in the code behind your button?
 
If you have a form that already contains the information, I would use that form in the query rather than in the code, but that's just my preference.

Some of it is preference and some is just what you know. I'm sure there are way better ways to do some of the things that I've programmed, but I used the skills I had at that time.

Now that my skills have increased I look at previous code and think 'Why on earth did I do THAT?'...well because that was all I knew at the time.

Good luck and post back with any other issues!

Leslie
 
Lespaul, thanks appreciate that.

I'm going to see if I hear back from mstrmagel1768 as he was walking me through marking these records within this report via update query as editable, and then noneditable once my report printed so that users could not go back and change them.

Waiting to see what I do with the records once they are marked that they cannot be edited.

 
gos,

Are you saying that Leslie helped you get the bit set working??? So that now when the users print the report, the records get the IsEditable field set correctly??


=======================================
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
 
I don't think that's what I did!

I thought I supplied the correct syntax for referencing a control on a subform within a query!

Leslie
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top