combo box - warning if new data entered (not in list)

[Andrew]

I have a combo box that is populated with data from a query. I have the
properties of the combo box set so that entries do not have to be from the
list. However...

If the entry chosen is NOT from the list then I would like an alert to appear
asking the user if they really want to add to the list or re-type their entry.


Following instructions on another post, I've linked the combo box to an event
procedure. The code is below. But nothing is happening.

Please advise on where I am going wrong.

Thanks in advance,
Andrew


Option Compare Database
'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub SchoolT1_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not a school in the list " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add the new school to the current list?
"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new school?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset) ' must define
table name
On Error Resume Next
rs.AddNew
rs!School = NewData 'must define field name
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[Jeff Boyce]

Andrew

I don't understand. You mention that your combo box does NOT have to be
from the list (does this mean you've set the LimitToList property to "No"?).

But the code you're attempting to use in an event (you don't say which one)
is the NotInList code, which would only be triggered if you set LimitToList
to "Yes".

If I'm reading between the lines correctly, you've told Access not to pay
any attention to whether an entry is in the list, then tried to get it to
trigger when the entry is not in the list. I'm confused! (and I'll bet
Access is, too).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[subscriptions via AccessMonster.com]

Aaah! Thanks Jeff. Yes, that would be confusing. I've now set set the
LimitToList property to "Yes" and now I'm getting action.

However, now I'm getting a "Microsoft Visual Basic" error that says, "Compile
Error, User-defined type not defined". It opens my scripting window.
Highlighted in yellow is the "Private Sub..." line and in the following line,
"db As DAO.Database" is selected.

I have no clue what to due next. Please advise.

Thanks again,
Andrew

 

[subscriptions via AccessMonster.com]

Well, I figured out the error. I needed to choose References on the Tools
menu, then check the box beside "Microsoft 3.6 DAO". Now it works just fine.

NOW my question is, will this run without any problems on other PC's,
especially a PC using the runtime version of Access2000?

Thanks,
Andrew

 

[Jeff Boyce]

Andrew

"will this run without any problems on [any] other PC's...?"

Would you believe me if I said "Yes"? Or if I said "No"?

There are way too many things that could break, or might be different from
one PC to the next. Give it a try and see...

Regards

Jeff Boyce
Microsoft Office/Access MVP