Validation in VBA based on values in list (Access 2000 SR-1)

[melhado]

I just learned that the LimitToList property set to YES in a combo box does not prevent data entry of any value not in the List when one has added code to the form that takes user input as criteria for a search and creates a new record if it doesn't find it. Although it is not critical, basically just wasting space with orphaned records, I do want to clean this up.

I am having trouble figuring out how best to trap the bad entry as the InputBox is processed, if possible. It seems best to set the Validation rule in VBA following my Input box code, and not at the table or form levels. As I have assigned the input to variables, I am unclear on how to go about setting it -- to the field or the variable, or where to set it on? I would like to set it to the row source/single column of the combo box, so that is dynamically updated. I should note that this form started life with a dropdown for this field, I later added the code and disabled the combo box (user can see list for reference, but cannot change value).

Here is the code snippet showing the input, variables and when it creates the new record. Code is DAO, all references and declarations are set correctly, and it works perfectly -- I just need to trap this error. Then I can use a MsgBox to tell the user to re-enter the 2 inputs. It is the second one "strPN" that I need to validate. (PtNum is the field name it is being stored in the table, cboPN is the control holding the list of acceptable values.) I could use some input on where to put the code and its proper syntax.
<font color=448800>
' Asks for User Input</font color=448800>
<font color=blue>strSN = Trim(InputBox("Enter Billet ID - # only", "Serial Number"))
<font color=red>strPN = Trim(InputBox("Enter Part Number", "Part Number"))
<font color=448800>
'this didn't work<font color=red>Me!PtNum.ValidationRule = Me!cboPN.Column(0)
<font color=448800>'Now I store it as variables
<font color=blue>strSearch = "[SerNum] = '" & strSN & "' AND "</font color=blue>
<font color=red>strSearch = strSearch & "[PTnum] = '" & strPN & "'"</font color=red>
<font color=448800>
'The search begins</font color=448800>
<font color=blue>rst.FindFirst strSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
<font color=448800>
'The new record is created with the variables planted.</font color=448800>
<font color=blue>DoCmd.GoToRecord , , acNewRec
With rst
.AddNew
!SerNum = strSN
!PTnum = strPN
.Update
End With
</font color=blue>
<font color=black>Thank you for the input

'dave

[charlotte]

LimitToList determines whether the combobox allows users to enter items that aren't in the list. If the property is set to Yes, you can use the NotInList event to trap the value and handle it.

<hr>does not prevent data entry of any value not in the List when one has added code to the form that takes user input as criteria for a search and creates a new record if it doesn't find it<hr>

LimitToList only applies to that combobox and only triggers the NotInList event if the user enters something new.

You put code in your post, but you didn't include the entire routine so there's no way to tell *where* this code is running. And I can't figure out what it's supposed to be doing. What is this supposed to do?

<hr>Me!PtNum.ValidationRule = Me!cboPN.Column(0)<hr>

[melhado]

Sorry, I didn't think it necessary to post the entire code. All that I left out were the DIM statements, error trapping and exit stuff, as well as closing the recordset.
What I posted was where I guessed the validation rule should have gone.

All this code does is sits on a click event of a command control that asks for 2 pieces of information, searches the records, brings it up with the related records on the subform if it finds it, otherwise it creates a new record to be subsequently filled in with information. What happened is that I never had any trapping if they put in an invalid entry in the strPN InputBox variable, and it would not find the record and create a new one. I was trying to get some validation rules, since I realized that setting the LimitToList property only applied with direct data entry. It works fine and no errors occured until the regular person went on vacation and the replacement is not as accurate. Thus I now realize I need more.
<font color=red>
--------------------------------------------------------------------------------
Me!PtNum.ValidationRule = Me!cboPN.Column(0)
--------------------------------------------------------------------------------
</font color=red>
This was one of my attempts to set the validation rule affecting the input regarding the variable strPN was checked against the combo box control whose row source has the only valid entries. PtNum is the table field, cboPN is the control on the form where the user can see the Part Number. I was thinking that the Validation rule property of the table field was what I wanted. I also tried it using the '"& strPN '" variable, but that didn't accept the Validation rule property. I need help understanding how I can check the entry of the Input box for strPN against the row source the form's combo box is populated from. Apparently, the code above is not the answer.

Am I going about it the right way or do I need more to trap the invalid entries at the input stage? The message box telling the user to start over I can do. Here is the code in all its glory (BTW, what has happened to the [pre] tags -- even when it is shorter than the bar, it goes too big?):
<font color=blue>
Private Sub cmdFndBlt_Click()
On Error GoTo Err_cmdFndBlt_Click

Dim rst As DAO.Recordset
Dim strSearch As String, strSN As String, strPN As String

Set rst = Me.RecordsetClone
<font color=448800>'Get criteria input (strPN is the variable I need to keep clean)</font color=448800>
strSN = Trim(InputBox("Enter Billet ID - # only", "Serial Number"))
strPN = Trim(InputBox("Enter Part Number", "Part Number"))

strSearch = "[SerNum] = '" & strSN & "' AND "
strSearch = strSearch & "[PTnum] = '" & strPN & "'"

rst.FindFirst strSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
With rst
.AddNew
!SerNum = strSN
!PTnum = strPN
.Update
End With
Me!sbfDE_Piece.Requery
End If

Set rst = Nothing

Exit_cmdFndBlt_Click:
Exit Sub

Err_cmdFndBlt_Click:
MsgBox Err.Description
Resume Exit_cmdFndBlt_Click

End Sub

[charlotte]

If you want to limit the items they can type into the combobox, then use the NotInList event. There are samples in the on-line help. If you run your code from a command button, the combobox LimitToList propety has nothing to do with it. And the inputboxes have nothing to do with a combobox.

All referring to Column(0) of the combobox does is return the current selection in the combobox. If you're trying to refer to the entire list of the combobox to see if the inputbox value exists in it, don't. Open a recordset based on the same SQL with the value they entered as the critiera to match. If the recordset comes up empty, then that value doesn't exist.

If they enter a record that doesn't exist, then the .FindFirst method with return a .NoMatch, which tells you right there that the record doesn't already exist. Your code is automatically moving to a new record and then creating a new record from code. Is that what you intended to do? You should at least validate the input before you create a new record.

[melhado]

They are not typing into a combo box or selecting from it. I overlooked that LimitToList would not do anything now that the input boxes were taking reign. The initial design had them selecting from the cbox, it remained only as it was a handy reference. All I did to the combo box after coding the search, was disable it so that they couldn't change entry. NotInList wouldn't work, as they are not supposed to add anything to this list.

OK on what the Column(0) returns -- I was looking for some way to return the list, and couldn't find anyway to have the control's RowSource returned as the list. I so badly wanted to avoid typing 21 pairs of numbers. I see now from your input that doing a second recordset with that SQL would have been the way, although it still would mean naming all 21 parts. I did find a help article on doing ValidateOnSet and setting validation rules in code, but it involved more than I felt necessary for this; I was hoping for a simpler way to reference a rowsource or query as the rule.

I succumbed to typing out the 21 pairs and sticking it in the table level validation rule fro the field. It may not be as pretty as stopping it at input, but it prevents the bad record from being created, and seems not to have affected performance. So, for now, it is the solution.

Thank you for your ideas.

'dave