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.
' 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"))
'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>
'The search begins</font color=448800>
<font color=blue>rst.FindFirst strSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
'The new record is created with the variables planted.</font color=448800>
<font color=blue>DoCmd.GoToRecord , , acNewRec
!SerNum = strSN
!PTnum = strPN
<font color=black>Thank you for the input