The following is code I am currently using in an A2K database to add new work units to a table when the combo box detects an entry is not in the list. This was provided to me from someone who knows a whole lot more about programming access than I. (Self taught and still lost with VB functions.) It works in this database.
**************
Private Sub cboWorkUnit_NotInList(NewData As String, Response As Integer)
Dim dbs As Database, rst As Recordset
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("WorkUnits")
' Prompt user to verify they wish to add new value.
If MsgBox("This Work Unit is not in the list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
rst.AddNew
rst!WorkUnit = NewData
rst.Update
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
Undo
End If
dbs.Close
End Sub
******************
I have just created another database and I am attempting to use the same code for a not in list combo box.
When I use or try to compile it I am sent back to VB and “Dbs as Database” is highlighted and an Error Message “Compile Error: User-defined type not defined.”
I tried typing the dim but when I type the “as” I do not appear to have the choice for “database”.
I recently reinstalled MSOffice on the computer but cannot understand why the code worked before and not now.
Can anyone tell me what I am doing wrong? Thanks in anticipation.
**************
Private Sub cboWorkUnit_NotInList(NewData As String, Response As Integer)
Dim dbs As Database, rst As Recordset
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("WorkUnits")
' Prompt user to verify they wish to add new value.
If MsgBox("This Work Unit is not in the list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
rst.AddNew
rst!WorkUnit = NewData
rst.Update
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
Undo
End If
dbs.Close
End Sub
******************
I have just created another database and I am attempting to use the same code for a not in list combo box.
When I use or try to compile it I am sent back to VB and “Dbs as Database” is highlighted and an Error Message “Compile Error: User-defined type not defined.”
I tried typing the dim but when I type the “as” I do not appear to have the choice for “database”.
I recently reinstalled MSOffice on the computer but cannot understand why the code worked before and not now.
Can anyone tell me what I am doing wrong? Thanks in anticipation.
