Not In List Event

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

andreainpanama - 10 Aug 2006 23:46 GMT

Greetings from the backpackers hostel in Panama.

I have gotten for the most part my forms and tables OK, and now I am just
suffering with unnecessary repetition of data entry which will be the subject
of further posts, I am sure.

For now, can someone walk me through in a detailed fashion, how to do a "not
in list" event so I can simply enter a new guest name, and if it does not
appear in my existing list of guests, allow me to enter the new client?

I don't even really understand who to add an event in the first place!

Reply to this Message

RuralGuy - 11 Aug 2006 00:21 GMT

>Greetings from the backpackers hostel in Panama.
>
[quoted text clipped - 7 lines]
>
>I don't even really understand who to add an event in the first place!

Maybe this link will help.
http://www.fontstuff.com/access/acctut20.htm

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

Reply to this Message

Klatuu - 11 Aug 2006 14:47 GMT

Here is an example from one of my apps. Note that to use the Not In List
event, the Limit To List property for the combo must be set to Yes. In the
example below, if the user clicks yes on the message box, a new record is
created with just the primary key value and the record source is positioned
on the new record ready for data entry.

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

> Greetings from the backpackers hostel in Panama.
>
[quoted text clipped - 7 lines]
>
> I don't even really understand who to add an event in the first place!

Reply to this Message

andreainpanama - 11 Aug 2006 17:17 GMT

Thank you so much for your responses, but I am afraid I need even more hand
holding. Can I get a very specific instruction as to what to do if:

The form is called MainGuestInfo. The table is MainGuestInfoTable and it
holds ALL my client data. (Is this OK, or should I have another table or
query that will only hold the last names?) The combo field is
MainGuestLastName which is NOT the primary key. The property limit to list
IS set to YES.

Is the only thing that I have to replace in the code that you have given me
is the word ACTIVITY for MainGuestLastName? Other than than I just cut and
paste what you sent?

All help is appreciated. What I am doing now is if I think I recognize a
name, I do a search, lots of error potential considering that I have
thousands of clients. (but I am only on number 356 in terms of entering the
data!)

Thanks so much!!!

> Here is an example from one of my apps. Note that to use the Not In List
> event, the Limit To List property for the combo must be set to Yes. In the
[quoted text clipped - 35 lines]
> >
> > I don't even really understand who to add an event in the first place!

Reply to this Message

Klatuu - 11 Aug 2006 18:07 GMT

Change this line:
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) "
To:
CurrentDb.Execute ("INSERT INTO MainGuestInfoTable (MainGuestLastName)
"

And this line:
rst.FindFirst "[Activity] = '" & NewData & "'"
To:
rst.FindFirst "[MainGuestLastName)] = '" & NewData & "'"
And This needs to be change from cboActivity to the name of your combo box:
Me.cboActivity.Undo
and txtDescription needs to be eiter omitted or you can change
txtDescription to the control you want to have the focus after the new record
has been added.
Me.txtDescription.SetFocus

The problem here is that if you have thousands of records, it is almost a
certainty you will have multiple last names. The FindFirst may not retrieve
the record you just created. You may have to add an additional qualifer to
get the correct record. Without knowing enough about your data, my first
guess is you have a MainGuestClientFirstName. Since your existing records
probably have a first name filled in and the new one doesn't, you may need
something like this in your FindFirst:

rst.FindFirst "[MainGuestLastName)] = '" & NewData & "' AND
MainGuestFirstName IS NULL"

> Thank you so much for your responses, but I am afraid I need even more hand
> holding. Can I get a very specific instruction as to what to do if:
[quoted text clipped - 55 lines]
> > >
> > > I don't even really understand who to add an event in the first place!

Reply to this Message

andreainpanama - 12 Aug 2006 01:51 GMT

I tried but I am still doing something wrong. The limit to list is set to
YES, so I do get the standard error message when I try to enter data, but it
is as if this code does not exist.

This is what I have, and I am pasting it into the code module of visual
basic in the "not in list event" procedure. Still not firing.

Private Sub cboMainGuestLastName_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In MainGuestInfo " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MainGuestInfo (MainGuestLastName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtMainGuestGender.SetFocus
Response = acDataErrAdded
Else
Me.cboMainGuestLastName.Undo
Response = acDataErrContinue
End If

End Sub

I am sooo newbie at this I can't even believe that I can actually write this
message!

> Change this line:
> CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) "
[quoted text clipped - 83 lines]
> > > >
> > > > I don't even really understand who to add an event in the first place!

Reply to this Message

Klatuu - 14 Aug 2006 13:29 GMT

Look at the Properties Dialog for your combo box.
Select the Events tab.
Check the Limit To List property. It should say [Event Procedure].
If it does not, then you did not add the code correctly. I have seen cases
where going directly into VBA, selecting the event from the drop down, and
pasting the code doesn't work. Somehow the control doesn't recognize the
code.

> I tried but I am still doing something wrong. The limit to list is set to
> YES, so I do get the standard error message when I try to enter data, but it
[quoted text clipped - 117 lines]
> > > > >
> > > > > I don't even really understand who to add an event in the first place!

Reply to this Message

andreainpanama - 14 Aug 2006 16:30 GMT

Hi, I kinda figured out that I must have entered the code wrong. The
property is set to Yes, and the line does say Event Procedure. Since I know
ABSOLUTELY nothing about code, could you walk me through this? Does the
above code look correct to you? How can I clear everything and start from
scratch? Do you think that my code is affected because my software package
is Spanish Language?
Should I post this in the code forum?

> Look at the Properties Dialog for your combo box.
> Select the Events tab.
[quoted text clipped - 125 lines]
> > > > > >
> > > > > > I don't even really understand who to add an event in the first place!

Reply to this Message

Klatuu - 14 Aug 2006 16:36 GMT

Here is what you can do to make sure the code is attached to the combo.

In the Not In List property of the Properties Dialog, click on the small
command button to the right of the text box with the dots on it ...

If it opens the VB Editor to your code, then there is another problem. If
it opens to an empty sub, then it doesn't know about your code. Copy your
code into this event procedure, and delete the sub it is in now.

> Hi, I kinda figured out that I must have entered the code wrong. The
> property is set to Yes, and the line does say Event Procedure. Since I know
[quoted text clipped - 133 lines]
> > > > > > >
> > > > > > > I don't even really understand who to add an event in the first place!

Reply to this Message

andreainpanama - 14 Aug 2006 17:26 GMT

Sorry, but I am still not clear. The following is exactly what I am doing.

I go into the properties of MainGuestLastName.
I click the 3 dots next to the Not in List event.
It automatically opens a visual basic window that already has a lot of code
in it.
I delete all that code.
I go to the little list in the upper left window and choose my field,
MainGuestLastName. It automatically registers something in the little window
on the upper right that says, Before Update and it puts a line of code in the
box. I change the option from Before Update to Not in List and again, it
puts a line of code in, but this time what looks to be the first line of code
that you gave me. I then cut and paste the remaining portion of the code
that I have from you. This line appears in red highlignt.
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null,

and save it all, and close all the vb stuff. Then when I go back to my form
entry mode, and i enter something in the field that is not in list...as soon
as I tab to the next field...it automatically opens up (from my data entry
form), a new visual basic code window. With an error message (rough
translation from Spanish....compilation error, syntax error.

And now this line appears in yellow highlight.
Private Sub MainGuestLastName_NotInList(NewData As String, Response As
Integer)

Can you help? (going out for a while, check back later.)

> Here is what you can do to make sure the code is attached to the combo.
>
[quoted text clipped - 142 lines]
> > > > > > > >
> > > > > > > > I don't even really understand who to add an event in the first place!

Reply to this Message