NotInList Command

A

AndreaSctlnd

I am so very confused. I am building a database to track engineers and their
hour/job. I have a combo box that looks to a query from a seperate table to
list current jobs being done. I want the engineers to be able to add a new
job to this list if at all possible without leaving the main form. I have
been looking at the other strings on this website but I just get so confused.

My main form is the engineers name and the primary key.
On this main form I have the sub form which contains the date worked,
ProjectID (which is what I want to add to), Hours per particular job etc.
Because I can not figure out haow to do this NotInList command, I set up a
macro to open a pop up form so they can add it there, the one big draw back
to that is, you have to leave the form and come back in for it to show in
the list. I thank everyone in advance for any help I can get.

I wish there was somewhere I could get training on how do work with this part
of access. I have no trouble in the actual set up, just the darn VBA.

Thanks again
 
E

e.mel.net

AndreaSctlnd said:
I am so very confused. I am building a database to track engineers and their
hour/job. I have a combo box that looks to a query from a seperate table to
list current jobs being done. I want the engineers to be able to add a new
job to this list if at all possible without leaving the main form. I have
been looking at the other strings on this website but I just get so confused.

My main form is the engineers name and the primary key.
On this main form I have the sub form which contains the date worked,
ProjectID (which is what I want to add to), Hours per particular job etc.
Because I can not figure out haow to do this NotInList command, I set up a
macro to open a pop up form so they can add it there, the one big draw back
to that is, you have to leave the form and come back in for it to show in
the list. I thank everyone in advance for any help I can get.

I wish there was somewhere I could get training on how do work with this part
of access. I have no trouble in the actual set up, just the darn VBA.

Thanks again
Click to expand...



"the one big draw back to that is, you have to leave the form and come
back in for it to show in the list."

This can be fixed with adding cbo_name.Requery to the Form_Activate
event. This will refresh the list when the form comes back into focus
after leaving the popup.

As for the rest that gets a little more complex, if your just storing
the one field in the table I would question the need for it. What I
ususally do is make a combobox with a row source something like "Select
[field] From
Group by [field] Order by [field]" This will
fill the list with the entries that are in all the previous field. and
if you put .Requery in _AfterUpdate() it will also list added entries.
(thats my preference anyway)

If your storing more than one field of info for each job, a popup form
is probably a better way to go. - Just remember .Requery is your
friend :)
 
E

Eric Blitzer

On the NotInList event you could have it go to add new record on your form.

Requery the combo box after you add a new record
 
A

Albert D.Kallal

The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

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

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.


However, there
is one thing we should do, and that is that then the frmAddClient loads, we
should put in the NewData value into the correct field so the user does not
have to re-type it. (and it helps the user "see" things a lot better". So,in
our forms on-load event, we will take the value of NewData, and set the
correct field. The code for this is:

if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

That is all you need. However, to make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

Further, turn off the forms ability to "add new" records. Yes, you read this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record at
a time, then put the follwing in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.
 
A

Albert D.Kallal

On the NotInList event you could have it go to add new record on your
form.

Requery the combo box after you add a new record
Click to expand...

you don't have to....

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.
if you set the response as above....
 
K

KLe

Albert D.Kallal said:
The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

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

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.
Click to expand...
Hello Albert,

I have done this in my own app but I have something else.

The main form also has unbound textboxes which through the use of an
"Update" sub will get the CompanyName value of the combo box, create a recset
of the CompanyData table and set the values of the Address, PhoneNo, Contact,
etc in the unbound textboxes for display only to the user.

The "Update" sub works fine when the main form first loads or when the value
in the combo box is changed, but not when the popup form for adding another
company with the NotInList sub closes.

I've tried puting my "Update" sub in the main form's Form_Activate and
Form_Current events but still no update. The combo box does however show the
new company name just added with the popup form.

I'm obviously not putting something in the correct place.
Any clues ?
Kevin.
 
A

Albert D.Kallal

Hello Albert,
I have done this in my own app but I have something else.

The main form also has unbound textboxes which through the use of an
"Update" sub will get the CompanyName value of the combo box, create a
recset
of the CompanyData table and set the values of the Address, PhoneNo,
Contact,
etc in the unbound textboxes for display only to the user.
Click to expand...

You do relaize that you could do the above witout any code, and simply use a
sub-form...right? I
explain how you could do this here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html
I've tried puting my "Update" sub in the main form's Form_Activate and
Form_Current events but still no update. The combo box does however show
the
new company name just added with the popup form.

I'm obviously not putting something in the correct place.
Any clues ?
Click to expand...

Well, I assume since you have code to display the information after you
select a value in the combo box, then obviously your code that displays the
data is in the after update event of the combo box now..right? That is
where the code belongs.

I would also suspect that perhaps when the form loads, you also likely want
the information to display. So, likely you could call the same routines the
after update event calls to display this data. And, further, if you allow
navigation, then again you will have to put code in the on-current event.

(we are up to 3+ events here) If you form used a bound combo box..and a
sub-form, you could do this whole thing without having to write one line of
code to display that information when the combo box is selected.!!).

If you are using the not-in list correctly as per my example, then placing
your code in the after update of the combo should work (if you allow reocrd
navigation in the form (not clear if you do), then you will also need to use
the on-current event. In fact, likely the on-current event, and the after
update event of the combo boxes would be the only two places you need to run
that display code.

If the combo box is bound, and you use a sub-form as per suggested in the
example, then zero code is needed to display that data...
 
A

AndreaSctlnd via AccessMonster.com

I guess I need to get myself a better book, lol. I don't understand where I
need to put that code. I know it needs to go in to VBA but I don't
understand how. I am sorry I am being such a problem.

Thank you for your help.
 
Top