force user to enter 8digits only

[bluemoon9]

Hi,
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)

Private Sub cboID_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Response = acDataErrContinue
strMsg = "The Medical Record Number'" & NewData & "' you selected is not in the list. "
        strMsg = strMsg & "Do you want to add it?"
 
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Patient?") = vbYes Then
 
            DoCmd.GoToRecord , , acNewRec
            [txtMEDREC] = NewData
            [txtMEDREC].Enabled = True
            [txtPATFNM].Enabled = True
            [txtPATLNM].Enabled = True
            [txtPATSEX].Enabled = True
            txtPATLNM.SetFocus
            cmdAddNewPtAccount.Enabled = True
        Else
            Response = acDataErrContinue
        End If
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?
thanks!

[patjones]

@bluemoon9
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:

If Len(Me!cboMRN) <> 8 Then 
...Execute Error Code...
Else
...Proceed to data entry for new record...
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.

Pat

[ADezii]

@bluemoon9
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:

Private Sub cboID_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim MyDB As Database
Dim rstEmployee As DAO.Recordset
 
Set MyDB = CurrentDb()
Set rstEmployee = MyDB.OpenRecordset("tblEmployee", dbOpenDynaset, dbAppendOnly)
 
strMsg = "The Medical Record Number you entered [" & NewData & "] is not in the list. "
strMsg = strMsg & "Do you want to add it?"
 
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Patient?") = vbYes Then
  With rstEmployee
    .AddNew
      ![MRN] = NewData
      ![LastName] = "Blah, Blah"
      ![FirstName] = "Blah, Blah, Blah"
    .Update
  End With
  Response = acDataErrAdded
Else
  Response = acDataErrContinue
End If
 
rstEmployee.Close
Set rstEmployee = Nothing
End Sub

[bluemoon9]

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

Private Sub cboMRN_AfterUpdate(Cancel As Integer) 
  If Len(Me.cboMRN) <> 8 Then 
    MsgBox "The MRN must be exactly 8 digits. Enter a new MRN"
    Cancel = True 
    cboMRN.setfocus
    cboMRN.requery
  End If 
End Sub 
 

Do I need to have an else and then in te else, excecute the Not in list?
thanks for your help.
bluemoon

[patjones]

Hi:

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?

Pat

[bluemoon9]

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
bluemoon

[bluemoon9]

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

bluemoon

[patjones]

Hi:

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.

Pat