Access Structure Design (form process), open message box with multiple options on error

[aytee111]

Oh, right, misunderstanding on my part. That is a tough one as close doesn't have a cancel button. Maybe the before or after update events, try a few things. This is a constant battle and you will find it here and many other forums. Just takes a bit of work to get it done.

[Heathey94]

OK, I'll have a ponder.

Here's the database: Assessment Database.zip

when trying to Add an assessor, it will automatically pull District and Organisation through, even though nothing is selected in team.

The AddAssessment form doesn't seem to work

I have a message to check if the userID (for the assessor) is already in the database, but because it is a mixture of text and numbers, the code from child (AS Long) does not work.

Thanks for your help, Merry Christmas (if you celebrate it, Happy Holidays if not) and - if you're not available between - Happy New Year!

[Heathey94]

Hope you had a good Christmas!

So, the problems I mentioned above still stand - have you got any suggestions?

[aytee111]

Hi, welcome back! Yes, great thx, it was 80 F here, nice!

Will reacquaint myself with the db.

[aytee111]

I'm not sure why the TeamID has a value when you open the Add Assessor form. It is the value on the first record of the table, it must be a timing thing. In the OnLoad event of the form change the first line to read - If Me.Filter="" Then

[aytee111]

Can't figure out your second problem. Firstly, there is no such form or button ass assessment, can you be more specific? Where is the bit about checking the assessor user ID?

[Heathey94]

I'm not sure why the TeamID has a value when you open the Add Assessor form. It is the value on the first record of the table, it must be a timing thing. In the OnLoad event of the form change the first line to read - If Me.Filter="" Then

Ok, I'll give that a go tomorrow.

Can't figure out your second problem. Firstly, there is no such form or button ass assessment, can you be more specific?

Sorry - that's on the assessment record form (accessed via the child record form)

Where is the bit about checking the assessor user ID?

On the after update event for the user ID (I think that's what it's called, can't remember for sure and can't see it at home to check) on the assessor record form

[aytee111]

That's on the assessor record, not assessment! OK, in the AfterUpdate: you don't need the HoldLoginID, you will not lose it (like on the other forms) so you can get rid of that and just reference the one on the form. It should be a String variable anyway. Also, get rid of the "Me.Undo" - that will wipe out everything the user has entered, very irritating! In the first part just delete the line, in the second rather set the login to blank.

Is it a required field? I would assume so, if you want to add any user security later. I hope at some time you are going to clean up your table field definitions, 255 lengths for text field shows lack of knowledge of what the fields are actually going to contain. It makes the processing slower and more inefficient and the database will grow in size much faster.

[aytee111]

I don't know what "doesn't seem to work" means. It works for me. You have a problem going to the Behaviours form when adding a new assessment, you must do the same as your other forms - filter when it exists and new record when it doesn't.

[Heathey94]

I'm not sure why the TeamID has a value when you open the Add Assessor form. It is the value on the first record of the table, it must be a timing thing. In the OnLoad event of the form change the first line to read - If Me.Filter="" Then

OK, I've done this and it's still happening, I checked the filter when I selected Add Assessor, and it still has an "AssessorID=11" in the filter, so I suspect that is the problem?

That's on the assessor record, not assessment! OK, in the AfterUpdate: you don't need the HoldLoginID, you will not lose it (like on the other forms) so you can get rid of that and just reference the one on the form. It should be a String variable anyway. Also, get rid of the "Me.Undo" - that will wipe out everything the user has entered, very irritating! In the first part just delete the line, in the second rather set the login to blank..

Ok, I think this is what you mean? If so, it highlights what I have placed in italics.

Private Sub LoginID_AfterUpdate()
    If IsNull(DLookup("LoginID", "tblAssessor", "LoginID=" & Me!LoginID)) Then
    Else
        Dim Msg, Style, Title, Response
        Msg = "This User ID has already been entered onto the system. Please check you have entered the correct ID and the assessor is not already in the system. Do you want to view the record for the assessor with this ID?"
        Style = vbYesNo
        Title = "ID already exists!"
        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
            Me.Undo
            Me.Filter = "LoginID=" & LoginID
            Me.FilterOn = True
            Me.Requery
        Else
            LoginID = ""
            LoginID.SetFocus
        End If
    End If
End Sub

Is it a required field? I would assume so, if you want to add any user security later. I hope at some time you are going to clean up your table field definitions, 255 lengths for text field shows lack of knowledge of what the fields are actually going to contain. It makes the processing slower and more inefficient and the database will grow in size much faster.

Yes, I could probably add restrictions like that, I didn't realise it made such a difference. The user security wouldn't use this userID - I have used the term UserID in both places, as it is the same type of ID (what is used to log onto the system), but the LoginID for the Assessor will not be used to log onto the system and those in tblUser will not be in the Assessor table (unless in very rare cases, but the tables aren't linked, so this shouldn't matter.

I don't know what "doesn't seem to work" means. It works for me. You have a problem going to the Behaviours form when adding a new assessment, you must do the same as your other forms - filter when it exists and new record when it doesn't.

When I select the add a new assessment button, it highlights the following code in the final Else section and "Compile Error: Duplicate declaration in scope" message box appeared, does this not happen to you?

Dim NewBr As Long

[aytee111]

and it still has an "AssessorID=11" in the filter

This sometimes happens when you are in design mode of the form and then save it and it saves the filter. It happened to me too, so I cleared the filter and then it worked.

it highlights what I have placed in italics.

LoginID is a text field and requires quotes around it: "LoginID='" & Me!LoginID & "'"

You need to get used to compiling your code each time you make a change. Yes I get compile errors with your database all the time but I just go in and fix it so that I can get on with other things.

LoginID for the Assessor will not be used to log onto the system

Don't get this. Login is usually the word used when someone logs in to the operating system (e.g. Windows) and is the Environ("username"). But sounds like you have it handled.

[Heathey94]

This sometimes happens when you are in design mode of the form and then save it and it saves the filter. It happened to me too, so I cleared the filter and then it worked.

Yeah, works for me too, thanks.

LoginID is a text field and requires quotes around it: "LoginID='" & Me!LoginID & "'"

You need to get used to compiling your code each time you make a change. Yes I get compile errors with your database all the time but I just go in and fix it so that I can get on with other things.

I don't know where or what the errors are, so I can't go and make the changes, and even if I did, it's unlikely I know how to fix it. Are there any other things you have to do each time?

Don't get this. Login is usually the word used when someone logs in to the operating system (e.g. Windows) and is the Environ("username"). But sounds like you have it handled.

LoginID is the username used to log onto the computer system by the assessor, Login (TblUser) is the username used to login to the computer system by the user, and will be used to restrict who can access the database - they are both in the same format, and both used to login to the system, but only those in the user table can access the database, if that makes any sense? if not, hopefully it won't matter.

I did find a problem with the duplicate assessor LoginID message box - but I think I've managed to get around it.

So, I think my only problems left are:
The view assessments button is taking you to the first assessment for that child but I want it to take you to the last, pretty sure this has been covered earlier in the thread, so I'll have another look next week.
If there are no previous assessments for that child, it takes you to the first assessment for another child - again probably already covered, I'll have a look next week.
The Add new Assessment button doesn't work as mentioned earlier.

I have attached my database for your use, if you could assist in anyway I would be most appreciative, but I'm (probably, unless it's something I can reply to on my phone at home) signing off until Tuesday, so Happy New Year!

[aytee111]

I don't know where or what the errors are

In VBA, click on Debug>Compile and it will tell you what the errors are. For instance, in your previous db I got "Duplicate declaration" (or some such) and it highlights the error, pretty easy to fix.

Going to the assessment record does need some work, I will think on it.

Happy New Year to you too!

[Heathey94]

Ok, I've Compiled the database as you suggested and corrected two errors, but the compile database option is now greyed out, does that just mean there are no errors, or have I done something wrong?

Also, have you had any thoughts on the assessment record?

[aytee111]

No, when it is greyed out that means that it is compiled. You will notice that as soon as you type something in it will come back on.

Another no, I was hoping you would do it, then come to me with problems!

(I have noticed there are two kinds of answerers on this list: those who help and those who do. I am the of the former! But I will do it if you want me to.)