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

force user to enter 8digits only

P: 56
I have a field call MRN. The property is text and max length is 8. In the data entry form, I have it as a combo box names cboMRN. I would like to force user to enter exactly 8 digits, if user enter less or more than 8, I won't allow user to add.

Here is my code in the "NOT IN LIST" feature. (If user enter a new MRN, it'll prompt user to add new MRN, if user enter an exiting MRN, it'll show the record info)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboID_NotInList(NewData As String, Response As Integer)
  2. Dim strMsg As String
  3. Response = acDataErrContinue
  4. strMsg = "The Medical Record Number'" & NewData & "' you selected is not in the list. "
  5.         strMsg = strMsg & "Do you want to add it?"
  7.         If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Patient?") = vbYes Then
  9.             DoCmd.GoToRecord , , acNewRec
  10.             [txtMEDREC] = NewData
  11.             [txtMEDREC].Enabled = True
  12.             [txtPATFNM].Enabled = True
  13.             [txtPATLNM].Enabled = True
  14.             [txtPATSEX].Enabled = True
  15.             txtPATLNM.SetFocus
  16.             cmdAddNewPtAccount.Enabled = True
  17.         Else
  18.             Response = acDataErrContinue
  19.         End If
  20. End Sub
I tried to set the INPUT MASK as =000000000 in the property of the cboMRN to force user to enter 8 digits, it works but then it unable the search in the combo box, when I type the MRN into the cboMRN box, it won't do search.

Can anyone please help?
Dec 29 '08 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 931
First, are you saying that it saved the new record successfully, and then when you try to look for the new record in cboMRN, that it doesn't come up? If the new MRN isn't appearing in the combo box, there might be a problem with the manner in which the MRN is being stored in the record.

Second, another method you might want to try instead of using an input mask is to use the Len string function in VBA. For instance, in the AfterUpdate event for the combo box, you could put:

Expand|Select|Wrap|Line Numbers
  1. If Len(Me!cboMRN) <> 8 Then 
  2. ...Execute Error Code...
  3. Else
  4. ...Proceed to data entry for new record...
  5. End If
If you stick with the input mask though, just make sure that you raise an error box telling the user to input an MRN exactly eight characters long.

Let me know how this works out for you.

Dec 29 '08 #2

Expert 5K+
P: 8,638
You may wish to change your syntax, since you need to add the newly entered Item to the Row Source of the Combo Box, then Requery it by setting the Response Argument to acDataErrAdded, something similar to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboID_NotInList(NewData As String, Response As Integer)
  2. Dim strMsg As String
  3. Dim MyDB As Database
  4. Dim rstEmployee As DAO.Recordset
  6. Set MyDB = CurrentDb()
  7. Set rstEmployee = MyDB.OpenRecordset("tblEmployee", dbOpenDynaset, dbAppendOnly)
  9. strMsg = "The Medical Record Number you entered [" & NewData & "] is not in the list. "
  10. strMsg = strMsg & "Do you want to add it?"
  12. If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Patient?") = vbYes Then
  13.   With rstEmployee
  14.     .AddNew
  15.       ![MRN] = NewData
  16.       ![LastName] = "Blah, Blah"
  17.       ![FirstName] = "Blah, Blah, Blah"
  18.     .Update
  19.   End With
  20.   Response = acDataErrAdded
  21. Else
  22.   Response = acDataErrContinue
  23. End If
  25. rstEmployee.Close
  26. Set rstEmployee = Nothing
  27. End Sub
Dec 30 '08 #3

P: 56
Hi Pat,
I did. I've tried to put the code in the AfterUpdate Feature but didn't work either.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMRN_AfterUpdate(Cancel As Integer) 
  2.   If Len(Me.cboMRN) <> 8 Then 
  3.     MsgBox "The MRN must be exactly 8 digits. Enter a new MRN"
  4.     Cancel = True 
  5.     cboMRN.setfocus
  6.     cboMRN.requery
  7.   End If 
  8. End Sub 
Do I need to have an else and then in te else, excecute the Not in list?
thanks for your help.
Dec 31 '08 #4

Expert 100+
P: 931

Combo boxes have a NotInList event associated with them, which ADezii refers to in his post. If the user types in an eight-digit number, but it is not in the combo box list, the code in the NotInList event (in your case, code to enter a new record) will execute.

The AfterUpdate event would contain the check for eight digits, as you have written out, and I'm not sure why it isn't working. Did you breakpoint the code to make sure that it is indeed going into the AfterUpdate routine? If so, what is showing for the value of Me.cboMRN?

Dec 31 '08 #5

P: 56
Hi Pat,
I beleive because in the property of the cboMRN box, I have the "Limit to List" =Yes; therefore, the code which I posted onto the AfterUpdate feature to check for the length doesn't work.

What I did was I set "Limit to List" = No, and entered some test data. It worked, the error mesage pop up when I entered the MRN which is <>8 digits, but then the code in the "Not In List" feature does not execute, I think because I set the "Limit to List"=No, this makes the "Not In List" feature doesn't work properly

any idea?

thanks for your help
Jan 7 '09 #6

P: 56
Hi Pat,
I've found out a way to fix the problem. Instead of putting the add new MRN code in the NOT IN LIST function. I moved the code to the AFTER UPDATE and placed it after the code of checking for the MRN required length. All I did was modifying the code a bit so it'll search for the MRN in the table. It worked well.

thanks for your help

Jan 7 '09 #7

Expert 100+
P: 931

Glad that you managed to fix your problem. That's how Access is sometimes - trial and error. And you learn a lot in the process also.

Jan 7 '09 #8

Post your reply

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