By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,292 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,292 IT Pros & Developers. It's quick & easy.

ID validation

P: 30
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text21_GotFocus()
  2. Dim DB As DAO.Database
  3. Dim Rset As DAO.Recordset
  4. Set DB = CurrentDb()
  5. Set Rset = DB.OpenRecordset("TblReps")
  6. Rset.FindFirst "[CID] = " & Me.txtReps
  7. If Rset.NoMatch Then
  8.     GoTo ExitHere
  9. End If
  10. :
  11. ExitHere:
  12.     Set DB = Nothing
  13.     Rset.Close
  14.     Set Rset = Nothing
  15.  
  16. End Sub
  17.  
Dec 15 '07 #1
Share this Question
Share on Google+
16 Replies


missinglinq
Expert 2.5K+
P: 3,532
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)>
Dec 15 '07 #2

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text21_GotFocus()
  2. Dim DB As DAO.Database
  3. Dim Rset As DAO.Recordset
  4. Set DB = CurrentDb()
  5. Set Rset = DB.OpenRecordset("TblReps")
  6. Rset.FindFirst "[CID] = " & Me.txtReps
  7. If Rset.NoMatch Then
  8. GoTo ExitHere
  9. End If
  10. :
  11. ExitHere:
  12. Set DB = Nothing
  13. Rset.Close
  14. Set Rset = Nothing
  15.  
  16. End Sub
  17.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Rst.MoveFirst
  3. Rst.FindFirst "[CID] = " & Me!txtReps
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim db As DAO.Database
  3.     Dim Rst As DAO.Recordset
  4.     Set db = CurrentDb()
  5.     Dim mycount As Long
  6.     Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)
  7.     If (Rst.BOF And Rst.EOF) Then
  8.         MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
  9.     Else
  10.         Rst.MoveLast
  11.         mycount = Rst.RecordCount
  12.         Select Case mycount
  13.         Case Is = 1
  14.             MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
  15.         Case Is = 2
  16.             MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
  17.         End Select
  18.         ''whatever you want to happen here
  19.     End If
  20. :
  21. ExitHere:
  22.     Set db = Nothing
  23.     Rst.Close
  24.     Set Rst = Nothing
  25.  
  26.  
Regards

Jim :)
Dec 15 '07 #3

P: 30
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.
Dec 15 '07 #4

P: 30
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Rst.MoveFirst
  3. Rst.FindFirst "[CID] = " & Me!txtReps
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim db As DAO.Database
  3.     Dim Rst As DAO.Recordset
  4.     Set db = CurrentDb()
  5.     Dim mycount As Long
  6.     Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)
  7.     If (Rst.BOF And Rst.EOF) Then
  8.         MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
  9.     Else
  10.         Rst.MoveLast
  11.         mycount = Rst.RecordCount
  12.         Select Case mycount
  13.         Case Is = 1
  14.             MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
  15.         Case Is = 2
  16.             MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
  17.         End Select
  18.         ''whatever you want to happen here
  19.     End If
  20. :
  21. ExitHere:
  22.     Set db = Nothing
  23.     Rst.Close
  24.     Set Rst = Nothing
  25.  
  26.  
Regards

Jim :)
Hi, Jim. I'll try this. Thanks.
Dec 15 '07 #5

P: 30
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)>
Dec 15 '07 #6

P: 30
Jim, I entered the codes but I am getting a syntax error in
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Rst.MoveFirst
  3. Rst.FindFirst "[CID] = " & Me!txtReps
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim db As DAO.Database
  3.     Dim Rst As DAO.Recordset
  4.     Set db = CurrentDb()
  5.     Dim mycount As Long
  6.     Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]=" & Me!txtReps, dbOpenSnapshot)
  7.     If (Rst.BOF And Rst.EOF) Then
  8.         MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
  9.     Else
  10.         Rst.MoveLast
  11.         mycount = Rst.RecordCount
  12.         Select Case mycount
  13.         Case Is = 1
  14.             MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
  15.         Case Is = 2
  16.             MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
  17.         End Select
  18.         ''whatever you want to happen here
  19.     End If
  20. :
  21. ExitHere:
  22.     Set db = Nothing
  23.     Rst.Close
  24.     Set Rst = Nothing
  25.  
  26.  
Regards

Jim :)
Dec 15 '07 #7

Jim Doherty
Expert 100+
P: 897
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 :)
Dec 15 '07 #8

P: 30
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub txtReps_AfterUpdate() 
  2. Dim db As DAO.Database
  3. Dim Rst As DAO.Recordset
  4. Set db = CurrentDb()
  5. Dim mycount As Long
  6. Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]='" & Me!txtReps & "'", dbOpenSnapshot)
  7. If (Rst.BOF And Rst.EOF) Then
  8. MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
  9. Else
  10. Rst.MoveLast
  11. mycount = Rst.RecordCount
  12. Select Case mycount
  13. Case Is = 1
  14. MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
  15. Case Is = 2
  16. MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
  17. End Select
  18. ''move cursor to select item from combo box
  19.  
  20. End If
  21. :
  22. ExitHere:
  23. Set db = Nothing
  24. Rst.Close
  25. Set Rst = Nothing
  26.  
  27. End Sub
  28.  
Dec 16 '07 #9

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub txtReps_AfterUpdate() 
  2. Dim db As DAO.Database
  3. Dim Rst As DAO.Recordset
  4. Set db = CurrentDb()
  5. Dim mycount As Long
  6. Set Rst = db.OpenRecordset("SELECT [CID] FROM TblReps WHERE [CID]='" & Me!txtReps & "'", dbOpenSnapshot)
  7. If (Rst.BOF And Rst.EOF) Then
  8. MsgBox "No match found on the CID field for " & Me!txtReps, vbInformation, "System Message"
  9. Else
  10. Rst.MoveLast
  11. mycount = Rst.RecordCount
  12. Select Case mycount
  13. Case Is = 1
  14. MsgBox "Found specific value " & mycount & " that matches", vbInformation, "System Message"
  15. Case Is = 2
  16. MsgBox "Found " & mycount & " value(s) that match", vbInformation, "System Message"
  17. End Select
  18. ''move cursor to select item from combo box
  19.  
  20. End If
  21. :
  22. ExitHere:
  23. Set db = Nothing
  24. Rst.Close
  25. Set Rst = Nothing
  26.  
  27. End Sub
  28.  
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 :)
Dec 16 '07 #10

P: 30
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.
Dec 16 '07 #11

Jim Doherty
Expert 100+
P: 897
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 :)
Dec 16 '07 #12

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 16 '07 #13

P: 64
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
Dec 17 '07 #14

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 17 '07 #15

Jim Doherty
Expert 100+
P: 897
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 :)
Dec 17 '07 #16

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 17 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.