Making records un-editable

[Haggrr]

Sample of table I would like to lock.

Job Number	SetterCompany	Date To	Date From	Amount	File Number	Text	Code
849505	ABC Manufacturer		12/30/14 1:11:59 PM		2588811
849505	Mike Byod	12/30/14 1:35:07 PM			2588842	Wednesday

Where, only records that fit this parameter is editable.

Function LastThurs() As Date
'This will find last Thursday's date
Dim strDates As Date
Dim intDay As Integer
intDay = Weekday(Date) 'Finds today's day of the week
Select Case intDay
Case 1 'Sunday!
strDates = Date - 3
Case 2 'Monday
strDates = Date - 4
Case 3 'Tuesday
strDates = Date - 5
Case 4 'Wednesday
strDates = Date - 6
Case 5 'Thursday
strDates = Date
Case 6 'Friday
strDates = Date - 1
Case 7 'Saturday


strDates = Date - 2
End Select
LastThurs = strDates
End Function

And I know simply changing the computer's date will negate, but that doesn't concern me as of now! Thanks Haggrr

[June7]

User's should not be working directly with tables and queries. Bind form to filtered query.

[Haggrr]

I hear you and completely agree! However, the database I use has been work in progress for more than ten years (my main table is labeled [table1]), and I can't go to every form to accomplish that. How I achieve this now is limiting the 'Validation Rule' for both [table][date to] and [table][date from]. Might I use my 'Last Thurs ()' module to accomplish my need?

[June7]

Tables and queries can't have code behind them. If users can get to the Navigation pane and ribbon, they can do whatever they want.

I've never set those properties on fields in table. And setting Validation Rule will not limit the records viewed or editable. It controls what users are allowed to enter into the field.

[Haggrr]

Okay. Would it be possible for you to give me an example of a bound form based on the info I provided?

[June7]

Build query object with static filter criteria. Set form RecordSource to that query object. Or put an SQL statement directly in the RecordSource.

The calculation to determine date of a particular day of week can be done without VBA. What do you consider the beginning of week - Sunday?

ThurThisWeek: Date - Weekday(Date, vbSunday) + 5

[Haggrr]

Beginning of the work week is Thursday and ending on Wednesday.

[June7]

Did you try the expression? Adjust as needed.

[Haggrr]

You have me on the right track here. Let me show you more of what I'm looking for.

Once any user enters data the only way to access those records is through a query labeled 'search'. Where [job number] is the parameter being sought. See below.

SELECT Table1.[Job Number], Table1.SetterCompany, Table1.[Date To], Table1.[Date From], Table1.Amount, Table1.[File Number], Table1.Text, Table1.Code
FROM Table1
WHERE (((Table1.[Job Number])=[Enter Job #]))
ORDER BY Table1.[File Number];

I'm trying to exclude any record, where [date to] or [date from] is outside the current work week of Thursday through Wednesday from being changed - is any way?

[June7]

That gets complicated. Try:

SELECT * FROM Table1 WHERE
Not ([Date To] BETWEEN Date - Weekday(Date, vbThursday) + 1 AND Date - Weekday(Date, vbThursday) + 7
Or
[Date From] BETWEEN Date - Weekday(Date, vbThursday) + 1 AND Date - Weekday(Date, vbThursday) + 7);

[Haggrr]

I'm sincerely sorry, but can you complete my SQL to include your addition? And remember, I don't mind these records being accessed, just not changed. Thanks for your patience with me!

[June7]

The only way to prevent editing records is to not present them to user by filtering them out of the dataset.

WHERE (((Table1.[Job Number])=[Enter Job #])) AND {insert my suggested criteria here without curly braces}

Also, I NEVER use dynamic parameter input popups. Cannot validate user input. User input of filter criteria should be done on a form and query refers to input control on form. Actually, I don't even do that - I use VBA to set form Filter property.

[Haggrr]

Well, thanks for telling me what I shouldn't do and assuming I'm as well versed as you. There used to be a site where people like Spencer and someone named Gary would really try to help us novices out. Do you know where that might be?

[June7]

Try the query anyway and see if it works with your popup input.

If you are interested in the alternatives we can explore those options.