M
melwester
I'm having problems with the "NotInList" function with my CTAName_Combo where
I want the users to be able to add or delete from the combo listing. I've
taking this code right from the book "Microsoft Access 2000" as I'm working
in Access 2000. I have an unbound main form with an unbound combo box. I
have the viewing section working fine just need to get the "Add, Delete,
Save, Cancel" functions working.
I had called the Edit screen "frm_MainEdit". Looks the same as the
CTAMainView except I have more buttons at the top to either "Add, Delete,
Save, etc".
Here's the problems:
1. When I click on the combo box, & let's say I want to pull in "Benecare".
I get the message that "Seleted CTAName Not Found" when it is indeed in the
selection. I click OK and it pulls in the info.
2. When I try to add "Alpha Omega" and tab over I get the message "CTAName
"Alpha Omega" is not in the system. Do you want to add?" YES I do. It
brings up the popup to add but when you try to type in the Address and Zip it
won't let you. Those are the only 2 fields it won't let you type in. Also,
when I press the Add button it comes up with this error message "Object
deosn't support this property or method".
3. When I try to delete CTA Name "1036" I get this message "Data type
mismatch in criteria expression".
Any ideas as to how I can get this working correctly?
Here is the code:
Private Sub CTAName_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[CTAName] = '" & Me![CTAName_Combo] & "'"
If rs.NoMatch = True Then
MsgBox "Selected CTA Name not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub
Private Sub CTAName_Combo_NotInList(NewData As String, Response As Integer)
Dim strCTAName As String
Dim intReturn As Integer, varName As Variant
strCTAName = NewData
intReturn = MsgBox("CTAName " & strCTAName & _
" is not in the system. Do you want to add this CTA Name?", _
vbQuestion + vbYesNo)
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frm_mypopup", _
DataMode:=acAdd, WindowMode:=acDialog, OpenArgs:=strCTAName
If IsNull(DLookup("CTAName", "tbl_CTAMain")) Then
Response = 0
Else
Response = acDataErrAdded
End If
Exit Sub
End If
Response = acDataErrDisplay
End Sub
Private Sub CTAName_Combo_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler
With Me.CTAName_Combo
If IsNull(.Value) And Not IsNull(.OldValue) Then
Cancel = True
.Undo
Me.Undo
RunCommand acCmdDeleteRecord
End If
End With
Exit_Point:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point
End Sub
I want the users to be able to add or delete from the combo listing. I've
taking this code right from the book "Microsoft Access 2000" as I'm working
in Access 2000. I have an unbound main form with an unbound combo box. I
have the viewing section working fine just need to get the "Add, Delete,
Save, Cancel" functions working.
I had called the Edit screen "frm_MainEdit". Looks the same as the
CTAMainView except I have more buttons at the top to either "Add, Delete,
Save, etc".
Here's the problems:
1. When I click on the combo box, & let's say I want to pull in "Benecare".
I get the message that "Seleted CTAName Not Found" when it is indeed in the
selection. I click OK and it pulls in the info.
2. When I try to add "Alpha Omega" and tab over I get the message "CTAName
"Alpha Omega" is not in the system. Do you want to add?" YES I do. It
brings up the popup to add but when you try to type in the Address and Zip it
won't let you. Those are the only 2 fields it won't let you type in. Also,
when I press the Add button it comes up with this error message "Object
deosn't support this property or method".
3. When I try to delete CTA Name "1036" I get this message "Data type
mismatch in criteria expression".
Any ideas as to how I can get this working correctly?
Here is the code:
Private Sub CTAName_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[CTAName] = '" & Me![CTAName_Combo] & "'"
If rs.NoMatch = True Then
MsgBox "Selected CTA Name not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub
Private Sub CTAName_Combo_NotInList(NewData As String, Response As Integer)
Dim strCTAName As String
Dim intReturn As Integer, varName As Variant
strCTAName = NewData
intReturn = MsgBox("CTAName " & strCTAName & _
" is not in the system. Do you want to add this CTA Name?", _
vbQuestion + vbYesNo)
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frm_mypopup", _
DataMode:=acAdd, WindowMode:=acDialog, OpenArgs:=strCTAName
If IsNull(DLookup("CTAName", "tbl_CTAMain")) Then
Response = 0
Else
Response = acDataErrAdded
End If
Exit Sub
End If
Response = acDataErrDisplay
End Sub
Private Sub CTAName_Combo_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler
With Me.CTAName_Combo
If IsNull(.Value) And Not IsNull(.OldValue) Then
Cancel = True
.Undo
Me.Undo
RunCommand acCmdDeleteRecord
End If
End With
Exit_Point:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point
End Sub