Ammendment to KB 125648 Not-in-List routine

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This was more challenging that I expected.

for KB 125648 if you use a form to enter a record that is not in the list,
how can it be further adjusted to accept a totally different value from what
is originally entered?


For example: you enter 1111 and the routine says:
"1111 is not in the list would you like to add it?"

You respond - YES, and then your AddForm comes up

You decide that you really wanted to add 2222 so you change it in the AddForm.

however, the not-in-list still wants 1111 so NADA!
YET, the record in your ADDForm WAS added (of course)

I thought all I needed to do was change the NewData variable,
but it makes no difference because the routine just assumes that you'll
enter the same thing that you originally typed on the main form.

How can I make this routine run and accept what was entered in the form no
matter what?
 
In the code that runs, when the user says Yes, include this line of code
before the AddForm opens:

Response = acDataErrContinue
 
My apology... I typed my first reply too quickly.

The step needs to be

Response = acDataErrAdded
 
No, so you're correct to ask your question < g >. As noted in the KB article
that you mentioned, the setting of the Response variable is done after the
user selects what's desired.

Perhaps you could post the code that you're using in all the forms, and
we'll help you insert the step in the right place.
--

Ken Snell
<MS ACCESS MVP>
 
Ok, thanks for your help so far.

Here is the routine... it's pretty much the same as the one in KB 125648
Keep in mind that the this routine needs to take into account that the
'AddNewGroup'
form could NOT be sending the same thing as the variable NewData

Private Sub GroupNum_NotInList(NewData As String, Response As Integer)

Dim result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new group.
Msg = "The group number: '" & NewData & "' as you have typed it,
does not exist" & CR & CR
Msg = Msg & "Do you want to add : '" & NewData & "'?" & CR

If MsgBox(Msg, vbQuestion + vbYesNo + vbQuestion, "Add " &
NewData & "?") = vbYes Then
' If the user chose Yes, start the AddNewGroup form in
data entry
' mode as a dialog form, passing the new group name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in AddNewGroup form's
Form_Load event
' procedure.


DoCmd.OpenForm "AddNewGroup", , , , acAdd, acDialog,
NewData


End If

' Look for the group number the user created in the Complaints Form.


result = DLookup("[Group #]", "tblGroups", _
"[Group #]='" & NewData & "'")

If IsNull(result) Then
'set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrAdded
result = DLookup("[Group #]", "tblGroups", _
"[Group #]='" & Me.GroupNum & "'")

If IsNull(result) Then

Response = acDataErrContinue
MsgBox "Please try again!", vbInformation, "Group
not added"

End If

Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If



End Sub
 
jonefer said:
This was more challenging that I expected.

for KB 125648 if you use a form to enter a record that is not in the list,
how can it be further adjusted to accept a totally different value from what
is originally entered?


For example: you enter 1111 and the routine says:
"1111 is not in the list would you like to add it?"

You respond - YES, and then your AddForm comes up

You decide that you really wanted to add 2222 so you change it in the AddForm.

however, the not-in-list still wants 1111 so NADA!
YET, the record in your ADDForm WAS added (of course)

I thought all I needed to do was change the NewData variable,
but it makes no difference because the routine just assumes that you'll
enter the same thing that you originally typed on the main form.

How can I make this routine run and accept what was entered in the form no
matter what?
Click to expand...


This is a really complicated thing to do that kind of
scrambles my brain trying to keep track of what the code is
doing.

I think Graham explains it pretty well in a response to a
simialr question. See the google archives at
http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#88a3c324c4266753
 
Thanks for the code...this helps me see what you're doing more clearly.

If you want to use the code from the KB article, then the only way to
accomplish what you want is to cancel the addition of the wrong value and
then put the "right" value in the combo box and go through the NotInList
process again.

May I suggest that you scrap this approach from the KB article, and instead
set the LimitToList property of the combo box to No, and instead provide a
command button on the form to allow the user to enter a new value. The user
clicks the button, and the button's code opens (in dialog mode) your
AddNewGroup form (which should have an OK and Cancel button on it). This
AddNewGroup form should be bound to the table where the data will go. The
Cancel button should run code that uses Me.Undo to reverse the entry of the
value, and then closes the AddNewGroup form. The OK button should save the
record, requery the combo box on the original form, and then close the
AddNewGroup form.

--

Ken Snell
<MS ACCESS MVP>



jonefer said:
Ok, thanks for your help so far.

Here is the routine... it's pretty much the same as the one in KB 125648
Keep in mind that the this routine needs to take into account that the
'AddNewGroup'
form could NOT be sending the same thing as the variable NewData

Private Sub GroupNum_NotInList(NewData As String, Response As Integer)

Dim result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new group.
Msg = "The group number: '" & NewData & "' as you have typed it,
does not exist" & CR & CR
Msg = Msg & "Do you want to add : '" & NewData & "'?" & CR

If MsgBox(Msg, vbQuestion + vbYesNo + vbQuestion, "Add " &
NewData & "?") = vbYes Then
' If the user chose Yes, start the AddNewGroup form in
data entry
' mode as a dialog form, passing the new group name in
' NewData to the OpenForm method's OpenArgs argument.
The
' OpenArgs argument is used in AddNewGroup form's
Form_Load event
' procedure.


DoCmd.OpenForm "AddNewGroup", , , , acAdd, acDialog,
NewData


End If

' Look for the group number the user created in the Complaints
Form.


result = DLookup("[Group #]", "tblGroups", _
"[Group #]='" & NewData & "'")

If IsNull(result) Then
'set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrAdded
result = DLookup("[Group #]", "tblGroups", _
"[Group #]='" & Me.GroupNum & "'")

If IsNull(result) Then

Response = acDataErrContinue
MsgBox "Please try again!", vbInformation, "Group
not added"

End If

Else
' If the customer was created, set the Response argument
to
' indicate that new data is being added.
Response = acDataErrAdded
End If



End Sub




Ken Snell said:
No, so you're correct to ask your question < g >. As noted in the KB
article
that you mentioned, the setting of the Response variable is done after
the
user selects what's desired.

Perhaps you could post the code that you're using in all the forms, and
we'll help you insert the step in the right place.
Click to expand...
Click to expand...
 
Back
Top