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

VBA Novice looking for help

P: n/a
I know a little about VBA, but everything I know I've learned on my
own through trial and error (and cut & paste). Now I'm actually
taking a class, but it's online twice a week.

I'm running through the chapters on my own and I'm coming up with some
questions that I'm afraid might be too stupid to post here (don't want
to ruin my image, hahaha)

Does anyone know where I might go to find answers to such simple
questions, via posting or researching, when my classes are not in
session?

Ok, the question I have right now is in this statement:
Private Sub cboCategoryName_NotInList(NewData As String, Response As
Integer)

Is "NewData" a set "function" (right term?) that Access recognizes?
Because I notice I'm not declaring anywhere what NewData will be when
this code runs... Same with "Response".

And later in the code I set Response = acDataErrContinue, so I'm
assuming that acDataErrContinue must be one of those functions that is
either 0 or 1? Like True or False?

Just in case, here's the whole procedure:

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

MsgBox "The value that you entered, " & NewData & ", is not in the
list."

Response = acDataErrContinue

End Sub
Thanks!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
This group is fine for clarifying your understanding.

The line you are asking about declares the procedure that handles the
NotInList event for a combo box named "cboCategoryName". Inside the brackets
are 2 things that Access provides to the procedure whenever it runs.

The first is named "NewData", and it is a string variable. It is the value
that has been typed into the box which was not found in the RowSource of the
combo. If you wish, you can write code that adds the NewData to the table
that supplies the list of values for the combo - the table named in its
RowSource property. That solves the problem: the data is now in the list.
For an example of how to do that, see:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

The 2nd argument Access provides is named "Response", and it is an Integer
variable. You can set the value of Response to tell Access what it should do
from here. If you do add the NewData to the lookup table, set Response to 2.
Access reads the number, reloads the combo, and is then happy the value *is*
now in the list. Instead of having to remember that 2 is the magic number,
use the constant "acDataErrAdded", i.e.:
Response = acDataErrAdded

Some other event procedures have an argument named Cancel. If so, you can
cancel the event by setting this to True. For example, if you put this code
into the Open event procedure of a form, it would never open. Every time you
tried to open it, the Form_Open event would be cancelled, and so Access
would never open the form:

Private Sub Form_Open(Cancel As Integer)
Cancel = True
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Donna Sabol" <ds********@yahoo.com> wrote in message
news:e8**************************@posting.google.c om...
I know a little about VBA, but everything I know I've learned on my
own through trial and error (and cut & paste). Now I'm actually
taking a class, but it's online twice a week.

I'm running through the chapters on my own and I'm coming up with some
questions that I'm afraid might be too stupid to post here (don't want
to ruin my image, hahaha)

Does anyone know where I might go to find answers to such simple
questions, via posting or researching, when my classes are not in
session?

Ok, the question I have right now is in this statement:
Private Sub cboCategoryName_NotInList(NewData As String, Response As
Integer)

Is "NewData" a set "function" (right term?) that Access recognizes?
Because I notice I'm not declaring anywhere what NewData will be when
this code runs... Same with "Response".

And later in the code I set Response = acDataErrContinue, so I'm
assuming that acDataErrContinue must be one of those functions that is
either 0 or 1? Like True or False?

Just in case, here's the whole procedure:

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

MsgBox "The value that you entered, " & NewData & ", is not in the
list."

Response = acDataErrContinue

End Sub
Thanks!

Nov 12 '05 #2

P: n/a
NewData and Response are parameters that are passed to the function when it
is called. In the case, it is called by Access when the NotInList event is
triggered. NewData will contain the value of the data just typed into the
combo box (i.e. the data that triggered the NotInList event). Response is
expecting you to modify it with the values you mention to tell Access how to
handle what you just did in the event. The values you give to Response are
built in constants. If you want to see their value, you can open the
immediate window (Ctrl+G) and type

?acDataErrContinue

Then press Enter. The immediate window will display the value of this
constant. "?" is shorthand in the immediate window for "Debug.Print".

The value Response is being passed to the function "By Reference" (ByRef).
This is the default for visual basic. This passes the memory address of the
value to the function instead of passing the value itself. This is what
allows you to modify the value of Response and Access picks up on what you
did, because you had the actual memory location of Response, when you
changed it the calling procedure was able to access this change. The
alternative is to pass the item "By Value". This will generate a "copy" of
the value for the called function. You will see this frequently for API
calls. Look for ByVal in the function call to indicate that an item was
passed this way. I don't know if NewData is being passed ByRef or ByVal,
I've never tried to modify it to see what happens, but I suspect it is
ByVal.

--
Wayne Morgan
Microsoft Access MVP
"Donna Sabol" <ds********@yahoo.com> wrote in message
news:e8**************************@posting.google.c om...
I know a little about VBA, but everything I know I've learned on my
own through trial and error (and cut & paste). Now I'm actually
taking a class, but it's online twice a week.

I'm running through the chapters on my own and I'm coming up with some
questions that I'm afraid might be too stupid to post here (don't want
to ruin my image, hahaha)

Does anyone know where I might go to find answers to such simple
questions, via posting or researching, when my classes are not in
session?

Ok, the question I have right now is in this statement:
Private Sub cboCategoryName_NotInList(NewData As String, Response As
Integer)

Is "NewData" a set "function" (right term?) that Access recognizes?
Because I notice I'm not declaring anywhere what NewData will be when
this code runs... Same with "Response".

And later in the code I set Response = acDataErrContinue, so I'm
assuming that acDataErrContinue must be one of those functions that is
either 0 or 1? Like True or False?

Just in case, here's the whole procedure:

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

MsgBox "The value that you entered, " & NewData & ", is not in the
list."

Response = acDataErrContinue

End Sub
Thanks!

Nov 12 '05 #3

P: n/a
Comments between sections...

"Donna Sabol" <ds********@yahoo.com> wrote in message
news:e8**************************@posting.google.c om...
I know a little about VBA, but everything I know I've learned on my
own through trial and error (and cut & paste). Now I'm actually
taking a class, but it's online twice a week.

I'm running through the chapters on my own and I'm coming up with some
questions that I'm afraid might be too stupid to post here (don't want
to ruin my image, hahaha)

Does anyone know where I might go to find answers to such simple
questions, via posting or researching, when my classes are not in
session?
For the most part, the Access help file is a good 'first' place to look
for help. If you have installed the VBA help files during Access setup, then
most of the concepts and details of VBA are addressed there as well.
Although I find the 2002 help files to be extremely lacking!

Ok, the question I have right now is in this statement:
Private Sub cboCategoryName_NotInList(NewData As String, Response As
Integer)

Is "NewData" a set "function" (right term?) that Access recognizes?
Because I notice I'm not declaring anywhere what NewData will be when
this code runs... Same with "Response".
The "things" listed inside the brackets of a function are called
Arguments. Sometimes the event will pre-assign these a value for you to use
in the event code. Other arguments are for you to provide information back
to the event handler. Check the help file for the NotInList event, and it
will explain how to use these arguments.

And later in the code I set Response = acDataErrContinue, so I'm
assuming that acDataErrContinue must be one of those functions that is
either 0 or 1? Like True or False?

These are not always equal to a True/False value - they can be any Long
number. This is usually because many of the constants you use in Access are
stored in what is called an Enum within the library the function is
contained in. Enums can only be longs.

Just in case, here's the whole procedure:

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

MsgBox "The value that you entered, " & NewData & ", is not in the
list."

Response = acDataErrContinue

End Sub
Thanks!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.