ID validation

[ebo2006]

I have this code for Id validation. The ID is entered in a text box, and validates the entry in GotFocus event of a command button. However, it does not work when I use it for the GotFocus in a Label Object. It is giving me an error message "Run time error 3251, Operation is not supported for this type of object".in statement the: Rst.FindFirst "[CID] = " & Me.txtReps
Below is the complete GotFocus function. What should be changed to make it work? Thanks.

Private Sub Text21_GotFocus()
Dim DB As DAO.Database
Dim Rset As DAO.Recordset
Set DB = CurrentDb()
Set Rset = DB.OpenRecordset("TblReps")
Rset.FindFirst "[CID] = " & Me.txtReps
If Rset.NoMatch Then
    GoTo ExitHere
End If
:
ExitHere:
    Set DB = Nothing
    Rset.Close
    Set Rset = Nothing
 
End Sub
 

[missinglinq]

I have to tell you, not much of what you've posted here makes any sense at all!

The ID is entered in a text box, and validates the entry in GotFocus event of a command button.

So you enter the ID in your textbox, then you validate it by placing the focus on a command button, not by clicking on the button? This makes absolutely no sense!
Validation of data is usually done in the BeforeUpdate event for the textbox (if you want immediate feedback) or in the form's BeforeUpdate event, if you only want to know when you're getting ready to save the record.

As for why you get the "Error 3251, Operation is not supported for this type of object," I'm guessing, despite the line that's hilited (Access isn't always terribly accurate with this) that's because a Label cannot receive focus! More confusing, you say this code is for a label, yet the sub is named

Sub Text21_GotFocus

which to most people would say that it's a textbox.

Linq ;0)>

[Jim Doherty]

I have this code for Id validation. The ID is entered in a text box, and validates the entry in GotFocus event of a command button. However, it does not work when I use it for the GotFocus in a Label Object. It is giving me an error message "Run time error 3251, Operation is not supported for this type of object".in statement the: Rst.FindFirst "[CID] = " & Me.txtReps
Below is the complete GotFocus function. What should be changed to make it work? Thanks.

Private Sub Text21_GotFocus()
Dim DB As DAO.Database
Dim Rset As DAO.Recordset
Set DB = CurrentDb()
Set Rset = DB.OpenRecordset("TblReps")
Rset.FindFirst "[CID] = " & Me.txtReps
If Rset.NoMatch Then
GoTo ExitHere
End If
:
ExitHere:
Set DB = Nothing
Rset.Close
Set Rset = Nothing
 
End Sub
 

Hi ebo,

A label doesnt have a setfocus event!

To validate the entry you could use the afterupdate event of the textbox but in order to do that when retrieving a recordset and you need to move to the first record so that the find command has a startpoint so to speak.

 
Rst.MoveFirst
Rst.FindFirst "[CID] = " & Me!txtReps
 

The 'find' method has its useages but for me it is not relevant in all situations. From what I see you are doing you are simply wishing to find the CID number within the table by opening it up as a recordset and asking the FIND method to find it for you.

Now, I do not know if the CID field is your primary key in which case you 'must' have and can only have a single unique value in the table. which is fine.... but if it isnt and you think ahead you could have duplicates in which case the FIND method only finds the first occurrence of the value it is requested to find. What happens if its the second row value you wanted or maybe the eighth row value and so on if you understand me.

In addition to this you are opening a 'table' but is there any need to open all the table as it is? what happens when the table gets to thirty thousand rows or so maybe. You would have to open the entire table first before you execute the 'find' command in which case your system will grind to a halt eventually.

The more efficient way of doing this is to only open a recordset containing only the value you are wishing to retrieve, ie not the entire table and not all columns either, only the one column and the single value is necessary merely to determine if it exists. This way you limit the traffic relevant if your db is on a network.

Try this in your textbox afterupdate and see if it works the same for you. You can remove the msgboxes included for clarity rather than need.

 
    Dim db As DAO.Database
    Dim Rst As DAO.Recordset
    Set db = CurrentDb()
    Dim mycount As Long
    Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)
    If (Rst.BOF And Rst.EOF) Then
        MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
    Else
        Rst.MoveLast
        mycount = Rst.RecordCount
        Select Case mycount
        Case Is = 1
            MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
        Case Is = 2
            MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
        End Select
        ''whatever you want to happen here
    End If
:
ExitHere:
    Set db = Nothing
    Rst.Close
    Set Rst = Nothing
 
 

Regards

Jim :)

[ebo2006]

I have to tell you, not much of what you've posted here makes any sense at all!



So you enter the ID in your textbox, then you validate it by placing the focus on a command button, not by clicking on the button? This makes absolutely no sense!
Validation of data is usually done in the BeforeUpdate event for the textbox (if you want immediate feedback) or in the form's BeforeUpdate event, if you only want to know when you're getting ready to save the record.

As for why you get the "Error 3251, Operation is not supported for this type of object," I'm guessing, despite the line that's hilited (Access isn't always terribly accurate with this) that's because a Label cannot receive focus! More confusing, you say this code is for a label, yet the sub is named

Sub Text21_GotFocus

which to most people would say that it's a textbox.

Linq ;0)>

Hi. Don't get confused.
You are right, the function was used as, mentioned in the original posting, for a command button. Only now I don't want the user to choose but just press enter and the focus is brought back to the Text box to enter another ID.

Labels do receive focus. In one of the other fucntions where the user enters an item number in a Combo box, the validation is done in a GotFocus event of the Label next to it. You cannot make the validation in the object where you make the entry because you cannot set focus back to itself. Text21 is just a name, sorry for the naming convention, I 'll change it to lblCommentID.

The reason why I need help is that I used the same codes, changed object names, and using a Label instead of Command button, but I am getting the error message. Maybe there is someone out there who may know how to revise the codes to make it work. But thanks anyway.

[ebo2006]

Hi ebo,

A label doesnt have a setfocus event!

To validate the entry you could use the afterupdate event of the textbox but in order to do that when retrieving a recordset and you need to move to the first record so that the find command has a startpoint so to speak.

 
Rst.MoveFirst
Rst.FindFirst "[CID] = " & Me!txtReps
 

The 'find' method has its useages but for me it is not relevant in all situations. From what I see you are doing you are simply wishing to find the CID number within the table by opening it up as a recordset and asking the FIND method to find it for you.

Now, I do not know if the CID field is your primary key in which case you 'must' have and can only have a single unique value in the table. which is fine.... but if it isnt and you think ahead you could have duplicates in which case the FIND method only finds the first occurrence of the value it is requested to find. What happens if its the second row value you wanted or maybe the eighth row value and so on if you understand me.

In addition to this you are opening a 'table' but is there any need to open all the table as it is? what happens when the table gets to thirty thousand rows or so maybe. You would have to open the entire table first before you execute the 'find' command in which case your system will grind to a halt eventually.

The more efficient way of doing this is to only open in a recordset containing only the value you are wishing to retrieve, ie not the entire table and not all columns either, only the one column and the single value is necesary merely to determine if it exists. This way you limit the traffic relevant if your db is on a network.

Try this in your textbox afterupdate and see if it works the same for you. You can remove the msgboxes included for clarity rather than need.

 
    Dim db As DAO.Database
    Dim Rst As DAO.Recordset
    Set db = CurrentDb()
    Dim mycount As Long
    Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)
    If (Rst.BOF And Rst.EOF) Then
        MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
    Else
        Rst.MoveLast
        mycount = Rst.RecordCount
        Select Case mycount
        Case Is = 1
            MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
        Case Is = 2
            MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
        End Select
        ''whatever you want to happen here
    End If
:
ExitHere:
    Set db = Nothing
    Rst.Close
    Set Rst = Nothing
 
 

Regards

Jim :)

Hi, Jim. I'll try this. Thanks.

[ebo2006]

My bad, Got confused myself. It is a text box not a label. LABELS DO NOT RECIEVE FOCUS !!!

Jim, I have not tried it yet. I'll let you know.

I have to tell you, not much of what you've posted here makes any sense at all!



So you enter the ID in your textbox, then you validate it by placing the focus on a command button, not by clicking on the button? This makes absolutely no sense!
Validation of data is usually done in the BeforeUpdate event for the textbox (if you want immediate feedback) or in the form's BeforeUpdate event, if you only want to know when you're getting ready to save the record.

As for why you get the "Error 3251, Operation is not supported for this type of object," I'm guessing, despite the line that's hilited (Access isn't always terribly accurate with this) that's because a Label cannot receive focus! More confusing, you say this code is for a label, yet the sub is named

Sub Text21_GotFocus

which to most people would say that it's a textbox.

Linq ;0)>

[ebo2006]

Jim, I entered the codes but I am getting a syntax error in

Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)

I can't find the syntax error (error 3075 missing operator in query expression "[CID] =". Tried to change the [CID] with tblReps.CID, same error.

Hi ebo,

A label doesnt have a setfocus event!

To validate the entry you could use the afterupdate event of the textbox but in order to do that when retrieving a recordset and you need to move to the first record so that the find command has a startpoint so to speak.

 
Rst.MoveFirst
Rst.FindFirst "[CID] = " & Me!txtReps
 

The 'find' method has its useages but for me it is not relevant in all situations. From what I see you are doing you are simply wishing to find the CID number within the table by opening it up as a recordset and asking the FIND method to find it for you.

Now, I do not know if the CID field is your primary key in which case you 'must' have and can only have a single unique value in the table. which is fine.... but if it isnt and you think ahead you could have duplicates in which case the FIND method only finds the first occurrence of the value it is requested to find. What happens if its the second row value you wanted or maybe the eighth row value and so on if you understand me.

In addition to this you are opening a 'table' but is there any need to open all the table as it is? what happens when the table gets to thirty thousand rows or so maybe. You would have to open the entire table first before you execute the 'find' command in which case your system will grind to a halt eventually.

The more efficient way of doing this is to only open in a recordset containing only the value you are wishing to retrieve, ie not the entire table and not all columns either, only the one column and the single value is necesary merely to determine if it exists. This way you limit the traffic relevant if your db is on a network.

Try this in your textbox afterupdate and see if it works the same for you. You can remove the msgboxes included for clarity rather than need.

 
    Dim db As DAO.Database
    Dim Rst As DAO.Recordset
    Set db = CurrentDb()
    Dim mycount As Long
    Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)
    If (Rst.BOF And Rst.EOF) Then
        MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
    Else
        Rst.MoveLast
        mycount = Rst.RecordCount
        Select Case mycount
        Case Is = 1
            MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
        Case Is = 2
            MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
        End Select
        ''whatever you want to happen here
    End If
:
ExitHere:
    Set db = Nothing
    Rst.Close
    Set Rst = Nothing
 
 

Regards

Jim :)

[Jim Doherty]

Jim, I entered the codes but I am getting a syntax error in

Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)

I can't find the syntax error (error 3075 missing operator in query expression "[CID] =". Tried to change the [CID] with tblReps.CID, same error.

ebo just paste everything that which I gave you into your procedure it will work. I have tested it here upteen times already and it returns exactly what I expect it to, syntactically it is correct. Let me know how you go on.

Jim :)

[ebo2006]

ebo just paste everything that which I gave you into your procedure it will work. I have tested it here upteen times already and it returns exactly what I expect it to, syntactically it is correct. Let me know how you go on.

Jim :)

Jim, Thank you for your patience. I copied your codes into the AfterUpdate event of the Text box. I am not getting in the routines, meaning I don't get the expected messages. If I press <enter> without entering any ID, the cursor goes straight to the next text box, no message. If I enter a 3 chracter ID (existing or not) it gives a "run time error 3061. Too few parameters. Expected 1." error message. Will it help to know that CID is not the primary key, but it should be uunique for the entire file. Here is the AfterUpdate procedure: I don't know what I am missing, since the procedure is tested and running at your end.

 Private Sub txtReps_AfterUpdate() 
Dim db As DAO.Database
Dim Rst As DAO.Recordset
Set db = CurrentDb()
Dim mycount As Long
 Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]='" & Me!txtReps & "'", dbOpenSnapshot)
If (Rst.BOF And Rst.EOF) Then
MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
Else
Rst.MoveLast
 mycount = Rst.RecordCount
Select Case mycount
Case Is = 1
MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
Case Is = 2
MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
End Select
''move cursor to select item from combo box
 
End If
:
ExitHere:
Set db = Nothing
Rst.Close
Set Rst = Nothing
 
End Sub
 

[Jim Doherty]

Jim, Thank you for your patience. I copied your codes into the AfterUpdate event of the Text box. I am not getting in the routines, meaning I don't get the expected messages. If I press <enter> without entering any ID, the cursor goes straight to the next text box, no message. If I enter a 3 chracter ID (existing or not) it gives a "run time error 3061. Too few parameters. Expected 1." error message. Will it help to know that CID is not the primary key, but it should be uunique for the entire file. Here is the AfterUpdate procedure: I don't know what I am missing, since the procedure is tested and running at your end.

 Private Sub txtReps_AfterUpdate() 
Dim db As DAO.Database
Dim Rst As DAO.Recordset
Set db = CurrentDb()
Dim mycount As Long
 Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]='" & Me!txtReps & "'", dbOpenSnapshot)
If (Rst.BOF And Rst.EOF) Then
MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
Else
Rst.MoveLast
 mycount = Rst.RecordCount
Select Case mycount
Case Is = 1
MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
Case Is = 2
MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
End Select
''move cursor to select item from combo box
 
End If
:
ExitHere:
Set db = Nothing
Rst.Close
Set Rst = Nothing
 
End Sub
 

Hi ebo :)

I assumed your datatype for the CID field was numeric given your first posting. I do not have your table structure :( so... the reason it will not be working your end is that your datatype will be set to TEXT in your table.

On the basis of my assumption the WHERE clause of the SQL statement will not work, because it will expect single quotes wrapped around the passed value. I have amended it now to wrap with single quotes to reflect the fact that your table data type is text not numeric.

Copy the ENTIRE revised code block above back into your
textbox afterupdate event and let me know how you go on please. You will see that I have bolded the revised line and also a slight formatting error that I saw in it.

Regards

Jim :)

[ebo2006]

Hi ebo :)

I assumed your datatype for the CID field was numeric given your first posting. I do not have your table structure :( so... the reason it will not be working your end is that your datatype will be set to TEXT in your table.

On the basis of my assumption the WHERE clause of the SQL statement will not work, because it will expect single quotes wrapped around the passed value. I have amended it now to wrap with single quotes to reflect the fact that your table data type is text not numeric.

Copy the ENTIRE revised code block above back into your
textbox afterupdate event and let me know how you go on please. You will see that I have bolded the revised line and also a slight formatting error that I saw in it.

Regards

Jim :)

Jim, you're the man. It works. Thank you so much for your patience and time. I am trying to learn access own my own. A little at a time. Your'e help is a big thing. Now I don't have to reinvent the wheel. I've learned so much from this exercise: use of double qoutes and single qoutes, remembering that labels don't recieve focus, etc. Thank you again and to you too, missinglinq.

[Jim Doherty]

Jim, you're the man. It works. Thank you so much for your patience and time. I am trying to learn access own my own. A little at a time. Your'e help is a big thing. Now I don't have to reinvent the wheel. I've learned so much from this exercise: use of double qoutes and single qoutes, remembering that labels don't recieve focus, etc. Thank you again and to you too, missinglinq.

You're welcome glad we could help

Jim :)

[NeoPa]

Jim, you're the man. It works. Thank you so much for your patience and time. I am trying to learn access own my own. A little at a time. Your'e help is a big thing. Now I don't have to reinvent the wheel. I've learned so much from this exercise: use of double qoutes and single qoutes, remembering that labels don't recieve focus, etc. Thank you again and to you too, missinglinq.

Indeed they do a good job Ebo :)
I read this post and thought you might be interested in an article about quotes (Quotes (') and Double-Quotes (") - Where and When to use them). I hope you find it interesting.

[tbeers]

Hi,

I also use a validation for several of my programs and I don't want to be over simplistic or make a short story long, but why would you not use a dlookup or dcount? They can be used in either the before update or after update.

-Tom

[NeoPa]

I'm not very familiar with the code in this particular thread, but the most common reason why the Domain Aggregate functions (DLookup(); DMax(); DSum(); etc) SHOULD NOT be used in situations such as you describe is because of the performance hit.
Consider that every time one of these functions is run a whole query has to be executed to get the result. If you run the whole query (albeit with a WHERE clause normally) for each record, the work done is exponentially heavier than running the query once and getting the relevant information for all records.

Clearly, it's not always possible to organise things so that they dovetail so nicely, but where possible Domain Aggregate functions should be avoided if a single query can be used in their place.

[Jim Doherty]

Hi,

I also use a validation for several of my programs and I don't want to be over simplistic or make a short story long, but why would you not use a dlookup or dcount? They can be used in either the before update or after update.

-Tom

Hi Tom,

You have a fair point MS didnt create them for no reason at all:

The posters thrust was with the recordsetobject method and given the attempt was structured around that I expanded and modified it within that methodology, my perception being that ebo was attempting to understand that way of doing it.

Domain aggregate functions certainly have purpose. For me however, they are exactly that a 'function' another level of intermediary that performs a sequence of logical code based on parameters fed to it within the boundaries of Access. You do not 'see' the routines domain functions rely on to do their bit, whereas here you have precise control over recordset behaviour.

It really boils down to a question of how 'much' control do you perceive 'necessary' am I likely to encounter performance problems if I use this particular command in favour of another.

The same 'Which is best' curiosity is valid for comparisons such as:

DoCmd.RunSql "Update tblBlah SET Myfield=2 where MyField =1"

set against the recordset object method where rows can be traversed and updated either singularly or on batch based on logic contained within an routine or simply .execute the command based on certain conditions.

You will find lots of references for benchmarking and best practice most of the time for me thought its simply a question of choosing which best suits the scope of your system. Domain aggregates may very well work fine for you. but I know some systems I have encountered crawled because of extensive use and efficient substitution rectified matters.


Regards

Jim :)

[NeoPa]

It occurs to me that I may have been a little unclear. I'm not remotely anti-Domain Aggregate functions. I just understand that they are there for a purpose, and when they are used in place of a JOINed table in a query, that is definitely NOT the purpose.
I am happy to use them quite extensively in my code as and when they're required.