Solved Looking for a record (1 Viewer)

[mloucel]

Hello all:
Have you ever been in a moment where you cannot find the most obvious answer to a simple question?
That's where I am..
I have an issue with a particular continuous Form and verifying data without using a button and Using DLOOKUP..

I have a Table Of Users that will have a user Name / password / Level / Active (FORM SOURCE is of course my table UsersT)

- so all is OK, I created the Form (Continuous) so that I can see all the users and ADD or Set INACTIVE.
- The form can only be used by ADMINS so when open the form they MUST enter a password, [easy and is done.]
- I check that ALL the fields are NOT NULL (you never know with users even if they are admins) I know the focus is always in Username when they start, so I use the On Lost Focus event to check if the field is null, and so with all the other fields except ACTIVE [set to true] and LEVEL [set to user] forcing the user to enter something there.

Issue:
- When they enter the user name I have to check if that field is already in the table [UsersT], and if so display a message to the user and force a UserName.setfocus [Which does not work BTW, it does not go back to UserName],

The form record source is the table [UsersT], but no matter how I try, the dlookup gives me errors and glitches and so forth:
[dlookup("UserName", "UserT", "RIGHT HERE IS MY PROBLEM, WHAT DO I DO?")]

I know I can solve that with a different type of form [Not Continuous Form] and a button, sure, but I love the idea of the continuous form since it is easier to check for all the other users and make changes in a snap.

But how can I check that the UserName does not exist in UserT, and if it does MsgBox "User Name already exists, must use a different one ", vbokonly
then UserName.SetFocus, and force the user to correct the user name, I am using UserName=" " before SetFocus

Yes for many of you is so simple but I am a newbie here and is the first time working with continuous forms,

ANY HELP will be appreciated.

Maurice.

 

[theDBguy]

Hi. Try using the textbox before update event. If you set cancel = true, the focus should stay on that textbox.

 

[Josef P.]

The form record source is the table [UsersT], but no matter how I try, the dlookup gives me errors and glitches and so forth:
[dlookup("UserName", "UserT", "RIGHT HERE IS MY PROBLEM, WHAT DO I DO?")]
[...]
But how can I check that the UserName does not exist in UserT, and if it does MsgBox "User Name already exists, must use a different one ", vbokonly
then UserName.SetFocus, and force the user to correct the user name, I am using UserName=" " before SetFocus

Steps to the solution:
1) how do you show only the record in a table that has a specific username?

select * from UserT where Username = 'mloucel'

2) Transformed to DLookup

x = DLookup("UserName", "UserT", "UserName = 'mloucel'")

3) Adaptation for easier Exists return:

dim UserExists as Boolean
UserExists = Nz(DLookup("True", "UserT", "UserName = 'mloucel'"), False)

4) UserName as parameter:

dim UserNameToCheck as String
UserNameToCheck = "mloucel"
UserExists = Nz(DLookup("True", "UserT", "UserName = '" & UserNameToCheck & "'"), False)

5) Create a reusable function:

Public function UserExists(Byval UserNameToCheck as String) as Boolean
     UserExists = Nz(DLookup("True", "UserT", "UserName = '" & UserNameToCheck & "'"), False)
end function

6) add SQL injection protection

Public function UserExists(Byval UserNameToCheck as String) as Boolean
     UserExists = Nz(DLookup("True", "UserT", "UserName = '" & Replace(UserNameToCheck, "'", "''") & "'"), False)
end function

7) use the function

if UserExists(YourUserNameVariable) then
   ...
end if

In keeping with the theme of "Exists":

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

 

[gemma-the-husky]

What I would do.

Have a continuous form sorted by username, showing limited info if necessary. Add an expand button to show the full record as a single record.

I pretty well always use a continuous form over a single form when presenting records.

Have a unique index on the username field, and let access warn you that the attempted entry is a duplicate, so you don't have to do a lookup. Add a form error event to intercept the message and replace it with a user friendly one.

You can find a record by placing the cursor in the username forks, a and then doing a docmd.findrecord, (rather than searching the recorder) but you need not to be editing a record to do that.

 

[CJ_London]

I check that ALL the fields are NOT NULL (you never know with users even if they are admins) I know the focus is always in Username when they start, so I use the On Lost Focus event to check if the field is null, and so with all the other fields

Only problem with that is a user may not click on a control for it to then subsequently lose focus, so a check is never done - from what you have provided, the remaining two fields are completed by default.

You say you have a continuous form - so when the form opens, the cursor goes to to the username control on the first record. That does not mean that when the user goes to enter a new record, they will go to the username control.

By all means check a username immediately, but you would be better to use the form before update event to check the status of all other fields

Further, as described, a user could go to the username control of an existing record and delete the contents - your check will then look for a username of "" - and won't find one so, based on the code, that would be OK?

Other thoughts - don't allow punctuation characters, perhaps password needs to be of a minimum length and contain certain character types (upper/lower case, numbers)

 

[mloucel]

Only problem with that is a user may not click on a control for it to then subsequently lose focus, so a check is never done - from what you have provided, the remaining two fields are completed by default.

You say you have a continuous form - so when the form opens, the cursor goes to to the username control on the first record. That does not mean that when the user goes to enter a new record, they will go to the username control.

By all means check a username immediately, but you would be better to use the form before update event to check the status of all other fields

Further, as described, a user could go to the username control of an existing record and delete the contents - your check will then look for a username of "" - and won't find one so, based on the code, that would be OK?

Other thoughts - don't allow punctuation characters, perhaps password needs to be of a minimum length and contain certain character types (upper/lower case, numbers)

You are right, but that's a gamble I didn't thought about it, at the end I am thinking to display all data in a continuous form and then if the user wants to edit a record in particular use a button to use a form and control all the fields, as well as another button to create a new record, this way I don't care about the issues with the continuous form.

Thanks.

 

[Gasman]

Use a double click instead of some button?

 

[mloucel]

Hi. Try using the textbox before update event. If you set cancel = true, the focus should stay on that textbox.

I tried pal and it didn't work..

But I am trying a different approach. Now I am going to SIMPLY use the continuous form to SHOW all the existing records, then the user will have to either DOUBLE CLICK on a record to UPDATE fields and I control all the records better with a button.
Also add a button to ADD a new record, so I believe I will avoid DLOOKUP at all costs.

 

[theDBguy]

I tried pal and it didn't work..

But I am trying a different approach. Now I am going to SIMPLY use the continuous form to SHOW all the existing records, then the user will have to either DOUBLE CLICK on a record to UPDATE fields and I control all the records better with a button.
Also add a button to ADD a new record, so I believe I will avoid DLOOKUP at all costs.

Glad to hear you got it sorted out. Good luck with your project.

 

[Gasman]

I tried pal and it didn't work..

But I am trying a different approach. Now I am going to SIMPLY use the continuous form to SHOW all the existing records, then the user will have to either DOUBLE CLICK on a record to UPDATE fields and I control all the records better with a button.
Also add a button to ADD a new record, so I believe I will avoid DLOOKUP at all costs.

I was only offering another method to do what you want?
The logic does not really change?

 

[mloucel]

Steps to the solution:
1) how do you show only the record in a table that has a specific username?

select * from UserT where Username = 'mloucel'

2) Transformed to DLookup

x = DLookup("UserName", "UserT", "UserName = 'mloucel'")

3) Adaptation for easier Exists return:

dim UserExists as Boolean
UserExists = Nz(DLookup("True", "UserT", "UserName = 'mloucel'"), False)

4) UserName as parameter:

dim UserNameToCheck as String
UserNameToCheck = "mloucel"
UserExists = Nz(DLookup("True", "UserT", "UserName = '" & UserNameToCheck & "'"), False)

5) Create a reusable function:

Public function UserExists(Byval UserNameToCheck as String) as Boolean
     UserExists = Nz(DLookup("True", "UserT", "UserName = '" & UserNameToCheck & "'"), False)
end function

6) add SQL injection protection

Public function UserExists(Byval UserNameToCheck as String) as Boolean
     UserExists = Nz(DLookup("True", "UserT", "UserName = '" & Replace(UserNameToCheck, "'", "''") & "'"), False)
end function

7) use the function

if UserExists(YourUserNameVariable) then
   ...
end if

In keeping with the theme of "Exists":

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

Thanks so much, I will use it in a different approach I have, Instead of using the [CONTINUOUS FORM] I will simply ask the user to PICK one record if they want to edit [Double click event] to open a form that will allow me to do as I please with the data and use a bit of your code as well, then use a button to open a new form where they will add a new record, any how I'm sure you got the picture, continuous forms are great but not for what I need.

Again thanks for your help.

 

[mloucel]

What I would do.

Have a continuous form sorted by username, showing limited info if necessary. Add an expand button to show the full record as a single record.

I pretty well always use a continuous form over a single form when presenting records.

Have a unique index on the username field, and let access warn you that the attempted entry is a duplicate, so you don't have to do a lookup. Add a form error event to intercept the message and replace it with a user friendly one.

You can find a record by placing the cursor in the username forks, a and then doing a docmd.findrecord, (rather than searching the recorder) but you need not to be editing a record to do that.

Kind of the approach I am taking.. Thanks

 

[mloucel]

I MUST say thanks so much to ALL of you for your great input, I am amazed at the amount of help received, unfortunately for me, what I wanted to do it was pretty much impossible at my level, I am sure my way of explaining things has a lot to do with the confusion on what I wanted to accomplish, but nevertheless, I was able to do it, all of you:

Gasman​

theDBguy​

CJ_London​

gemma-the-husky​

Josef P.​

were part of the solution and I am in debt..
The solution, NEVER try to check fields on a continuous form..
I locked all the fields and add a "Open the selected record" button, from there I am able to control precisely all the fields and make sure the user does what they are supposed to do, of course nobody will ensure that they won't type "garvaje" instead of "gdrbage" or "garbage" but I am no AI to check for every word in the dictionary, but we do best we can, I took every one of the suggestions and apply your knowledge to my code in the solution.

Thank you so much.

Maurice.. << the newbie

 

[theDBguy]

Glad to hear you got it sorted out. Good luck with your project.