Quite a few jumbled thoughts (Access XP)

Topic

New Reply

Ok

Reply | Quote

Replies

> How to mask passwords?

Create a form with a text box to enter the password. Set the Input Mask property of the text box to PASSWORD.

>… is there a change password macro/code …

Create a form with three text boxes: ‘Old password’, ‘New password’ and ‘New password (confirm)’. Set the Input Mask property of each to PASSWORD. Put a command button ‘OK’ on the form, and in the On Click code of the button, check whether the new password meets all requirements, and if so, store it wherever you store the password.

> … an example script to put in autoexec that would say …

Don’t use AutoExec. Set the password entry form to be the startup form for the database (in Tools | Startup…)

> … is there a way to lock certain columns for editing on a query?

End users should NEVER work directly with queries, but with forms. In a form, you can set the Locked property of text boxes to Yes to prevent editing.

> … a quick lesson in user-level security …

See WendellB’s tutorial the Access Experts: Custom Database Solutions. It contains many useful links. Jack MacDonald has a good security paper. While you’re at it, also look at WendellB’s the Access Experts: Custom Database Solutions.

Reply | Quote

I would create an auxiliary table tblRank to define the rank grouping:

RankGroup	RankLetter
E	E
O	O
O	W

Create a query to extract the first letter of the rank:

SELECT Table1.*, Left([Rank],1) AS RankLetter
FROM Table1;

Save it as – say – qryTable1. Then, create a crosstab query based on this query and on tblRank:

TRANSFORM Count(*) AS V
SELECT qryTable1.Service
FROM tblRank INNER JOIN qryTable1 ON tblRank.RankLetter=qryTable1.RankLetter
GROUP BY qryTable1.Service
PIVOT tblRank.RankGroup;

See attached database.

Reply | Quote

Hans, once again you come to the rescue. My only problem with queries (I’ve already set the security on forms) is that the end users need to create excel documents from these queries to report to higher-ups. They can’t exactly do that on a form. That’s why I’m requesting to lock certain parts. I input the original data, rank, name, ssn, company…things like that. But I want the end users to edit personal information on these guys…like different addresses, etc. Maybe that can offer a little more clarification. I’ve got a form that shows upon opening, and shift-key bypass doesn’t work either. So I’m not worried about them changing the queries/tables/forms, etc…all they work with is forms, reports, and read-only queries and tables. Thanks!

Reply | Quote

It isn’t possible to lock individual fields in a query.

Reply | Quote

There is a workaround for that, though, Hans. If you make a field a calculated expression by concatenating an empty space (“”) to its value, that prevents it from being edited. It might be sufficient in this case.

Reply | Quote

That’s a clever idea.

Reply | Quote

Charlotte, Thanks a lot! Now how exactly do I specify concatenate in access? I know how to in excel….

Reply | Quote

Say that your table tblData contains a field MyField that you want to lock in the query. In an empty column of the query grid, enter

MyField: [tblData].[MyField] & ""

You must add the table name, otherwise you’ll get a circular reference. As an alternative, you could give the column an alias different from the field name; you can omit the field name then:

MyLockedField: [MyField] & ""

Reply | Quote

Hans, thanks a lot for your help. Is there a way, without running a query, to have that action take place? Upon data entry, I want to take the first letter of the last name, and the last 4 of the ssn and put them together in another column. I have this code, but I don’t know where to put it :

=left([lastname],1)&””&right([ssn,4])

Thanks!
Jeremy

Reply | Quote

You can create a select query based on your table, and add a column

LetterSSN: Left([LastName],1)&Right([SSN],4)

(no need to insert an empty string in between; you had the second ] in the wrong place) Use this query as record source of your form. Alternatively, place a text box on the form with control source

=Left([LastName],1)&Right([SSN],4)

Reply | Quote