Not in list event

[CD Tom]

I have a combo box that is set for a not in list event if the customer that
the user types in isn't in the current db. When the not in list event is
entered the user is presented with a message if they want to add the user?
If they respond yes the I bring up the master input form. The user then
fills out the form and then exits, I'm back at the original form but the new
customer isn't available to display, if I exit the form and then reenter it
the new customer is there. What I would like to have happen is when the user
leaves the master input form the new customer is displayed on the original
form. How do I make this happen. Thanks for any help. I always get great
answers here when I'm stumped.
Tom

 

[Arvin Meyer [MVP]]

You can use the following constants with event procedures for the NotInList
event:

acDataErrAdded
acDataErrContinue
acDataErrDisplay

The one you want is:

Response = acDataErrAdded

which sends the constant to the Response arguement.

 

[Klatuu]

Howdy,

The problem is you have updated the table, but not the form's recordset.
That is why you don't see it in the form.

To get it into the form's recordset, you need to do a requery:
Me.Requery

Simple enough, but, Now your form jumps back to the first record in the
recordset. So, what you have to do is keep track of the key of the current
record, do the requery, then reposition the form recordset to the original
current record.

Application.Echo False
lngRecId = Me.txtPrimeKey
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngRecId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
Application.Echo True

Note: The Echo may not be necessary. It is only to prevent the screen
jumping around. If you do use it, be sure it will get turned on in your
error handler.

 

[CD Tom]

does this go into the master input form or the one that calls the form?

Howdy,

The problem is you have updated the table, but not the form's recordset.
That is why you don't see it in the form.

To get it into the form's recordset, you need to do a requery:
Me.Requery

Simple enough, but, Now your form jumps back to the first record in the
recordset. So, what you have to do is keep track of the key of the current
record, do the requery, then reposition the form recordset to the original
current record.

Application.Echo False
lngRecId = Me.txtPrimeKey
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngRecId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
Application.Echo True

Note: The Echo may not be necessary. It is only to prevent the screen
jumping around. If you do use it, be sure it will get turned on in your
error handler.


--
Dave Hargis, Microsoft Access MVP

I have a combo box that is set for a not in list event if the customer that
the user types in isn't in the current db. When the not in list event is
entered the user is presented with a message if they want to add the user?
If they respond yes the I bring up the master input form. The user then
fills out the form and then exits, I'm back at the original form but the new
customer isn't available to display, if I exit the form and then reenter it
the new customer is there. What I would like to have happen is when the user
leaves the master input form the new customer is displayed on the original
form. How do I make this happen. Thanks for any help. I always get great
answers here when I'm stumped.
Tom

 

[Klatuu]

The calling form.
--
Dave Hargis, Microsoft Access MVP

does this go into the master input form or the one that calls the form?

Howdy,

The problem is you have updated the table, but not the form's recordset.
That is why you don't see it in the form.

To get it into the form's recordset, you need to do a requery:
Me.Requery

Simple enough, but, Now your form jumps back to the first record in the
recordset. So, what you have to do is keep track of the key of the current
record, do the requery, then reposition the form recordset to the original
current record.

Application.Echo False
lngRecId = Me.txtPrimeKey
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngRecId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
Application.Echo True

Note: The Echo may not be necessary. It is only to prevent the screen
jumping around. If you do use it, be sure it will get turned on in your
error handler.


--
Dave Hargis, Microsoft Access MVP

I have a combo box that is set for a not in list event if the customer that
the user types in isn't in the current db. When the not in list event is
entered the user is presented with a message if they want to add the user?
If they respond yes the I bring up the master input form. The user then
fills out the form and then exits, I'm back at the original form but the new
customer isn't available to display, if I exit the form and then reenter it
the new customer is there. What I would like to have happen is when the user
leaves the master input form the new customer is displayed on the original
form. How do I make this happen. Thanks for any help. I always get great
answers here when I'm stumped.
Tom

 

[CD Tom]

I tried putting that into the not in list event and it still doesn't work.
Here's what I have in the event
If MsgBox("'" & NewData & "' not on file. Enter as new?", vbOKCancel,
"Add New Competitor?") = vbOK Then
'Remove new data from combo box so control can be requeried
'After the Add Team form is closed
DoCmd.RunCommand acCmdUndo
stDocName = "Master Input"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
'continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Do I place that right after the last end if? Sorry to sound so stupid but
I've always had a problem with the not in list event.

The calling form.
--
Dave Hargis, Microsoft Access MVP

does this go into the master input form or the one that calls the form?

Howdy,

The problem is you have updated the table, but not the form's recordset.
That is why you don't see it in the form.

To get it into the form's recordset, you need to do a requery:
Me.Requery

Simple enough, but, Now your form jumps back to the first record in the
recordset. So, what you have to do is keep track of the key of the current
record, do the requery, then reposition the form recordset to the original
current record.

Application.Echo False
lngRecId = Me.txtPrimeKey
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngRecId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
Application.Echo True

Note: The Echo may not be necessary. It is only to prevent the screen
jumping around. If you do use it, be sure it will get turned on in your
error handler.


--
Dave Hargis, Microsoft Access MVP


:

I have a combo box that is set for a not in list event if the customer that
the user types in isn't in the current db. When the not in list event is
entered the user is presented with a message if they want to add the user?
If they respond yes the I bring up the master input form. The user then
fills out the form and then exits, I'm back at the original form but the new
customer isn't available to display, if I exit the form and then reenter it
the new customer is there. What I would like to have happen is when the user
leaves the master input form the new customer is displayed on the original
form. How do I make this happen. Thanks for any help. I always get great
answers here when I'm stumped.
Tom

 

[Klatuu]

I understand, it is one of the hardest to get your head around.
This is untested, but should then move to the newly added record

If MsgBox("'" & NewData & "' not on file. Enter as new?", vbOKCancel,
"Add New Competitor?") = vbOK Then
'Remove new data from combo box so control can be requeried
'After the Add Team form is closed
stDocName = "Master Input"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
'continue without displaying default error message.
Response = acDataErrAdded

Me.Requery

With Me.RecordsetClone
.FindFirst "[Primary Key Field] = " & NewData
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Else
Response = acDataErrContinue
End If

--
Dave Hargis, Microsoft Access MVP

I tried putting that into the not in list event and it still doesn't work.
Here's what I have in the event
If MsgBox("'" & NewData & "' not on file. Enter as new?", vbOKCancel,
"Add New Competitor?") = vbOK Then
'Remove new data from combo box so control can be requeried
'After the Add Team form is closed
DoCmd.RunCommand acCmdUndo
stDocName = "Master Input"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
'continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Do I place that right after the last end if? Sorry to sound so stupid but
I've always had a problem with the not in list event.

The calling form.
--
Dave Hargis, Microsoft Access MVP

does this go into the master input form or the one that calls the form?

:

Howdy,

The problem is you have updated the table, but not the form's recordset.
That is why you don't see it in the form.

To get it into the form's recordset, you need to do a requery:
Me.Requery

Simple enough, but, Now your form jumps back to the first record in the
recordset. So, what you have to do is keep track of the key of the current
record, do the requery, then reposition the form recordset to the original
current record.

Application.Echo False
lngRecId = Me.txtPrimeKey
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngRecId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
Application.Echo True

Note: The Echo may not be necessary. It is only to prevent the screen
jumping around. If you do use it, be sure it will get turned on in your
error handler.


--
Dave Hargis, Microsoft Access MVP


:

I have a combo box that is set for a not in list event if the customer that
the user types in isn't in the current db. When the not in list event is
entered the user is presented with a message if they want to add the user?
If they respond yes the I bring up the master input form. The user then
fills out the form and then exits, I'm back at the original form but the new
customer isn't available to display, if I exit the form and then reenter it
the new customer is there. What I would like to have happen is when the user
leaves the master input form the new customer is displayed on the original
form. How do I make this happen. Thanks for any help. I always get great
answers here when I'm stumped.
Tom

 

[John]

I have noticed that when you open a form in dialog mode that the code
stops executing till the dialog form is closed. Then when the dialog
form is closed simply requery the combo box.

Example:
'open your master imput form in dialog mode
DoCmd.OpenForm " master input form", acNormal, "", "", , acDialog
'requery your combo box
Form_FormName.ComboBoxName.Requery

There are bout to be a dozen ways of doing this, but I have used this
same type of code at least a dozen times.

John
MS Access Programmer
'Code Monkey'

 

[CD Tom]

Well, I'm still having problems and maybe it's my fault. Let me explain, the
combo box that has the not in list looks up the customer by last name but
finds the customer by their IDNumber. The newdata that is sent to the master
input is the users last name, in the master input form the user enters the
competitors IDNumber (the customer already has a idnumber assigned by a
different group altogether). When the user returns to the original form how
do I pass this IDNumber back so I can get the new competitor to display on
the screen.

I understand, it is one of the hardest to get your head around.
This is untested, but should then move to the newly added record

If MsgBox("'" & NewData & "' not on file. Enter as new?", vbOKCancel,
"Add New Competitor?") = vbOK Then
'Remove new data from combo box so control can be requeried
'After the Add Team form is closed
stDocName = "Master Input"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
'continue without displaying default error message.
Response = acDataErrAdded

Me.Requery

With Me.RecordsetClone
.FindFirst "[Primary Key Field] = " & NewData
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Else
Response = acDataErrContinue
End If

--
Dave Hargis, Microsoft Access MVP

I tried putting that into the not in list event and it still doesn't work.
Here's what I have in the event
If MsgBox("'" & NewData & "' not on file. Enter as new?", vbOKCancel,
"Add New Competitor?") = vbOK Then
'Remove new data from combo box so control can be requeried
'After the Add Team form is closed
DoCmd.RunCommand acCmdUndo
stDocName = "Master Input"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
'continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Do I place that right after the last end if? Sorry to sound so stupid but
I've always had a problem with the not in list event.

The calling form.
--
Dave Hargis, Microsoft Access MVP


:

does this go into the master input form or the one that calls the form?

:

Howdy,

The problem is you have updated the table, but not the form's recordset.
That is why you don't see it in the form.

To get it into the form's recordset, you need to do a requery:
Me.Requery

Simple enough, but, Now your form jumps back to the first record in the
recordset. So, what you have to do is keep track of the key of the current
record, do the requery, then reposition the form recordset to the original
current record.

Application.Echo False
lngRecId = Me.txtPrimeKey
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngRecId
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
Application.Echo True

Note: The Echo may not be necessary. It is only to prevent the screen
jumping around. If you do use it, be sure it will get turned on in your
error handler.


--
Dave Hargis, Microsoft Access MVP


:

I have a combo box that is set for a not in list event if the customer that
the user types in isn't in the current db. When the not in list event is
entered the user is presented with a message if they want to add the user?
If they respond yes the I bring up the master input form. The user then
fills out the form and then exits, I'm back at the original form but the new
customer isn't available to display, if I exit the form and then reenter it
the new customer is there. What I would like to have happen is when the user
leaves the master input form the new customer is displayed on the original
form. How do I make this happen. Thanks for any help. I always get great
answers here when I'm stumped.
Tom

 

[CD Tom]

John, I can't seem to get the requery to work. When I'm back at the main
form it shows the new users name in the combo box but has the first user in
the database showing, when I try and click the new user in the combo box it
gives me the user not in list again. What other ways are there to accomplish
this.

 

[CD Tom]

I'm pulling my hair out (what's left of it). I've tried everythink you've
sent and still can't get this darn thing to work. I keep getting a message
that the record has to be saved when I try and requery the combo box. When I
look into the table it shows the record, of course that's after I've closed
the forms and everything. Why doesnt' it save the record? I've tried the
refresh and the requery but nothing. I don't understand why this is so
difficult. I'm about to give up and try something different. What else do
you have that may help.
Thanks for your continuing help.

 

[John]

I can do you one better

2003 verion
http://office.microsoft.com/en-us/workessentials/TC010184581033.aspx?pid=CL101211891033
if this link doesnt work, goto http://office.microsoft.com/ and in the
search box type "Inventory management database"

1) Download this database
2) Open this database
3) Double Click on the Category combo box and on the "Categories" pop
up form add "Cold Beverages" to the Category name
4) Close the "Categories" pop up form

This does not change the value of the category field on the Products
form. That would require some additional coding, but if you click the
Category drop down the value "Cold Beverages" does appear in the list.
This is the process I was trying to explain.

BTW the category combo box also has a not in list event.

John
MS Access Programmer
'Code Monkey'

<Note>
For an A2k version search Office.Microsoft.com for "Membership
Database".
1) On the main switchboard click "Enter/View Members"
2) At the top Click the Committees Command Button"
3) In the sub form Double Click on the Committee field and follow the
code.
<End Note>

 

[Arvin Meyer [MVP]]

I see you still haven't resolved your issue. Using the Access constant WILL
resolve it. I also have some generic code at my website:

http://www.datastrat.com/Code/NotInListCode.txt

You will notice that the Response argument in the NotInList code uses:

Response = acDataErrAdded

That Response argument is there for a reason. Requerying will not help if
you do not use it.