Limit form to each dropdown option appearing once, enable check box if other check box is true

[aytee111]

Combining the functions into one:

Me.Undo
On Error Resume Next
DoCmd.RunSQL "DELETE * FROM Table1 WHERE ID=" & Me!ID & ";"
Me.Requery

The Me.Undo is like hitting escape. The Delete will then bring up an error as the record no longer exists, hence the resume next.

[Heathey94]

OK, I must be blind - I can't see Conditional Formatting on the Format section?

Also, I must have entered the code wrong, the undo feature works, but it doesn't delete the old rows. I just copied what you posted, but changed the section you typed "ID" to "ResponseID" (Ignore this, I've just realised I hadn't changed Table1 to the AssessmentDetails table, it now works - haven't got time to re-publish the database though.)

I've made quite a few changes, so here's the database again

[aytee111]

See attached for conditional formatting. Note: You must have a control selected, and that control cannot be a checkbox.

[aytee111]

Things I have to change every time you send a new database -
- setting scrollbars to both on all your forms!! Some of us have smaller screens than you do and there is a lot of empty space on your form design - you can't have both.
- changing the "Cycle" property on your forms - I try and get around having no scrollbars by using the tab key but on your forms where there is only one record it causes the dataset to drop off the record and go to the end of the recordset (a blank record) producing errors and I have to start again from the beginning!

[Heathey94]

I realised last night that might be the case, I was looking on the property sheet... :/ Thanks, that works now

Things I have to change every time you send a new database -
- setting scrollbars to both on all your forms!! Some of us have smaller screens than you do and there is a lot of empty space on your form design - you can't have both.
- changing the "Cycle" property on your forms - I try and get around having no scrollbars by using the tab key but on your forms where there is only one record it causes the dataset to drop off the record and go to the end of the recordset (a blank record) producing errors and I have to start again from the beginning!

Okay, I'll look to change that before I post my database again (I'm sure it'll happen)

I do have a bit of a query, although I think I know the answer.

I have seen on various sites that Access 2007-10 does not allow you to password protect it's databases any longer, is that still the case or has someone found a way around it? Have the newer versions returned to allowing it?

[aytee111]

Passwords on files are a VERY bad thing - people leave, forget the password, etc. What exactly are you trying to accomplish? There are many different ways of setting up security.

[Heathey94]

well, 2 things - it's sensitive data, so we don't want to chance it falling into the wrong hands. Also, I don't want the queries/forms/tables messed with by accident, so ideally, they could only access the Homepage from the sidebar -or nothing at all, since the Homepage opens when the database opens. I thought passwords would be the easiest way to do this, have you got a better idea then?

[aytee111]

Here are some of the ways:
- operating system security, setting user access to folders
- having a table of user names, allowing only those users access to the database (the function : Environ("username") provides the user name, no need for the user to log in to the database or to provide a password
- the production version of the database will be an accde
- create a customized ribbon with only the features you decide on
- set options, such as don't allow access to the navigation pane, don't allow shortcut menus or bypass keys
- you MUST split the database - have the tables in a separate database (back-end) and link to them from the front-end (I have not done this myself, but I have seen it around that you would put a password on this file (grrr) and when you link to the tables you will include that password. Breaks the rules of forgetting but it would be a good idea to put the password in the front-end, maybe in linking code, so that no one has to remember what it is)
- any local tables will be hidden

[aytee111]

When you are getting nearer the time to go into production, I would start another thread here about securing a database - there are lots of different ways of doing it, and different things you can do.

[Heathey94]

Ok, I'll bare that in mind - thanks.

I've been working on applying the modifications to the Low Risk Assessment Form to the Medium and High Risk forms, but it seems to pull the data from the Low Risk Assessment form (because it has the same Assessment ID). If you make any changes on the Low Risk form - add multiple behaviours, change no to yes, add comments etc. then click on the "next page" button to go to the Medium risk Assessment form, these come through with it. I think the behaviours would as well, but I have restricted the dropdown so that only Medium Risk Behaviours are pulled through. Below is an example of what is appearing:


I have attached my database again, I think I have turned all scroll bars back to both, if not just let me know which ones and I'll change it for next time.

Having thought about it again, I think I could create a separate ID - combining Assessment ID and Risk or something, but if there's a better/simpler method, I'd prefer to use that as I may have to re-do quite a few sections of the queries and forms.

[aytee111]

You need to set filters on all subforms that use the same table. You can do this by changing the record source of each to be a query with a criteria of which assessments you wish to see - low risk on the low risk subform, medium on the medium, etc. On the BeforeInsert event you will need to populate that field.

[Heathey94]

As I need to add data, does it need to be an Append query?

[aytee111]

No. First add the field to TblAssessmentDetails (if it doesn't already exist). Then put it as a non-visible field on the subform and BeforeInsert code Me!fieldname=1 (or whatever).

This brings up the question - if all of these subforms look and behave the same then you might want to have only one subform for them all - low, medium, high. That would seriously cut down on maintenance issues for you e.g. every time you make a change to one you must change them all. If that is the case - and I would recommend it if so - then you will need to do this slightly differently.

[Heathey94]

Because there are so many behaviours, I felt it would be easier for the user if they were split down, into low, medium and high, as this would mean there are less options - and so they are less likely to be missed. Also, there is a behaviour which appears on both the low and medium risk behaviours, which - if all of the behaviours were in one dropdown - it would be harder to determine which is the one that should be selected.

I've called it RiskID and it's currently in the TblBehaviour, which is linked to TblAssessmentDetails by behaviour ID. As it's in the database already, is it still required in the TblAssessmentDetails? I don't want to duplicate data if I don't need to.

[aytee111]

No, that's good - altho how are you handling the duplicate? If I were you, I would repeat the description with the different RiskID, otherwise things will get too complicated. That will make it easier to switch them on or off in the future, do reports, etc.

So I was wondering why you have a different form for each riskID, do the users enter them at different times? You could have one form to enter them all?