By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,982 Members | 1,738 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,982 IT Pros & Developers. It's quick & easy.

Using Not In List and a Query?

P: n/a
I have 3 boxes that essentially use the same
code for not in List events (Thank you to David for all of your help on
that one). The code opens another form if the user wants to enter data
that is not on the list. This works fantastic now. My problem is now
with another box. It shows results in a dropdown box based on a query
of the results in the box above. The query works great. However, I
can not find a way to use the not in list event if needed. In other
words, how do I get around the query if I want to add a new Claim Rep?
SELECT CRtable.CRName, CRtable.CompanyNameLookup
FROM CRtable
WHERE
(((CRtable.CompanyNameLookup)=*FORMS!CLIENTform!In suranceComp*anyLookup))

ORDER BY CRtable.CRName;
Right now if I add a name it opens up my Claim Rep form for addition .
But when I return to my main form it tells me that it isn't in the
list and to choose another name.

Here is the Not in List event I am using:
Private Sub ClaimRepLookup_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String
On Error GoTo Errorhandler
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Look for the claim rep the user created in the CRform.
Result = DLookup("[CRName]", "CRtable", "[CRName]='" & NewData
& "'")
If IsNull(Result) Then
' If the claim rep was not created, set the Response
Argument to suppress an error message and Undo changes.
Response = acDataErrContinue
'Display a customized message.
'MsgBox "Please try again"
Else
'If the claim rep was created,set the response argument to
indicuate that new data is being added
Response = acDataErrAdded
'The value exists, so let's exit and not open the form
GoTo ClaimRepLookup_Exit
End If
' Ask the user if he or she wishes to add the new claim rep.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the CRs form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Company form's Form_Load
event
' procedure.
DoCmd.OpenForm "CRform", acNormal, , , acAdd,
acWindowNormal, NewData
End If
ClaimRepLookup_Exit:

Exit Sub
Errorhandler:
If Err.Number > 0 Then
MsgBox "Number: " & Err.Number & vbCrLf & "Description: " &
Err.Description, vbCritical, "Error"
End If
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Have you established the linkage between the newly-added value and the
parent table? Have you requeried the combo box after adding the new rep?

Larry Linson
Microsoft Access MVP
<ti*********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I have 3 boxes that essentially use the same
code for not in List events (Thank you to David for all of your help on
that one). The code opens another form if the user wants to enter data
that is not on the list. This works fantastic now. My problem is now
with another box. It shows results in a dropdown box based on a query
of the results in the box above. The query works great. However, I
can not find a way to use the not in list event if needed. In other
words, how do I get around the query if I want to add a new Claim Rep?
SELECT CRtable.CRName, CRtable.CompanyNameLookup
FROM CRtable
WHERE
(((CRtable.CompanyNameLookup)=*FORMS!CLIENTform!In suranceComp*anyLookup))

ORDER BY CRtable.CRName;
Right now if I add a name it opens up my Claim Rep form for addition .
But when I return to my main form it tells me that it isn't in the
list and to choose another name.

Here is the Not in List event I am using:
Private Sub ClaimRepLookup_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String
On Error GoTo Errorhandler
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Look for the claim rep the user created in the CRform.
Result = DLookup("[CRName]", "CRtable", "[CRName]='" & NewData
& "'")
If IsNull(Result) Then
' If the claim rep was not created, set the Response
Argument to suppress an error message and Undo changes.
Response = acDataErrContinue
'Display a customized message.
'MsgBox "Please try again"
Else
'If the claim rep was created,set the response argument to
indicuate that new data is being added
Response = acDataErrAdded
'The value exists, so let's exit and not open the form
GoTo ClaimRepLookup_Exit
End If
' Ask the user if he or she wishes to add the new claim rep.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the CRs form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Company form's Form_Load
event
' procedure.
DoCmd.OpenForm "CRform", acNormal, , , acAdd,
acWindowNormal, NewData
End If
ClaimRepLookup_Exit:

Exit Sub
Errorhandler:
If Err.Number > 0 Then
MsgBox "Number: " & Err.Number & vbCrLf & "Description: " &
Err.Description, vbCritical, "Error"
End If
End Sub
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.