I can't get acNewRec to work in a list box in a subform

[David Wilch]

This is driving me crazy.

First, I'm a novice at access and VBA.
I'm using Access 2003.

I have a form "Athlete_Registration"
This form works great. I can edit old athletes from the table "Athlete"
using a listbox to find them quickly or add a new athlete.

I have subform "Registration_Form".
This subform is linked to the form "Athlete_Registration" with the
Athlete_Number (primary key).
The name and number display correctly on the subform correctly.

Next to the name and noumber, I have a list box "Event_List" of events from
a table "Events".
This appears to display correctly.

Odd note: if the athlete is an old customer, his last event is highlighted
and it also displays the number of his events at the bottom of this subform.
This is not a problem, just a possible clue.

What I'm trying to do:
when I click on an event in the listbox "Events_List", I want the
Athlete_Number and Event_Number to be entered as a new record into the
table "Registration" (auto-numbered).

Code under On Click of the list box:

Private Sub Event_List_Click()
On Error GoTo Err_List_Click

DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_List_Click:
Exit Sub

Err_List_Click:
MsgBox Err.Description
Resume Exit_List_Click
End Sub

Here's what happens:

If there is no previous record of the Athlete_Number in "Registration", it
works fine.

If there is a history, then it edits (does not add new) one of the athletes
events.
I noticed that the record number at the bottom of the sub-form increases,
but nothing was recorded in that location.

I thought the problem might have been with setfocus, so I tried this:

Me.Registration_Form.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

....And I get Compile Error: Method or data member not found.

One thing that does work, I added a button for new_record to the subform and
I have to click it (or the asterik at the bottom of the Form) prior to
clicking in the listbox and it works perfectly.
But I need to intergrate the new_record action into the On_Click procedure
to reduce data entry errors.

TIA,
David

 

[Steve Schapel]

David,

I don't really understand the code you showed. But let me suggest
another approach. The subform is supposed to show the Registrations for
the Athlete shown on the current record of the main form, right? I
would use the subform in Continuous view (this is set in the properties
of the form you are using as the subform). And instead of a listbox, I
would suggest a Combobox would be easier to manage. This combobox would
be bound to the Event_Number field in the Registration table (I assume
the subform is bound to this table?), and it's Row Source would be the
Events table, just like you have already done with the listbox. Ok, so
then it will be obvious to the user where there are existing
Registration records entered for the current Athlete, and it will also
be obvious where to enter a new Registration, whether or not there are
already existing Registration records. So, select the Event in the
combobox, and your desired outcome will happen with no need of any code
at all. Hope that makes sense.

 

[David Wilch]

Steve,

Thanks for the ideas.

"The subform is supposed to show the Registrations for
the Athlete shown on the current record of the main form, right?"

No, the subform does not need to show the past events for the athlete. The
purpose of the subform is to populate a registration table with the
athlete's number and one or two new events from a listbox of predefined
events.

"And instead of a listbox, I
would suggest a Combobox would be easier to manage."

I don't want any changes to the options in the listbox form the user. I
will make those chnages prior to registration. So, I think the list box is
the right...???

The listbox is bound to the events table.

A little more info:
For each competition, an athlete might sign up for 1-3 events on a hard copy.
Data entry people input the info. Prior to the competition, I define the
all of the info for each event and I don't want it accidentally changed.

The registration table only contains an auto numbered registration number,
an event number, and an athlete.

The subform:

1. There is a text box verifing the athlete's name. This is linked from the
main form.

2. There is a list box with 3 events. This box is set up with an On Click
procedure to select events to be populated
into a registration table. This all works perfectly if the user clicks the
new record button on the bottom of the subform prior to clicking an event in
the listbox. If the user forgets to click the new record button, older info
in the registration table will be changed.

Control Source...EventID
Row Source Type...Table/Query
Row Source...SELECT Events.EventID, Events.EventDate, TrainingName.
TrainingName FROM TrainingName INNER JOIN Events ON TrainingName.
TrainingNameID=Events.EventNameID ORDER BY Events.EventID DESC;

3. There is another list that is unbound to a registration query. This is
only to verify the registration was processsed properly.

I tried changing the view source to continuous with no difference.

????????

 

[Steve Schapel]

David,

Thanks for the further explanation. I did misunderstand some of what
you wanted.

Since the subform is simply for the purpose of entering new
registrations, you can set the Data Entry property of the form you use
for the subform to Yes. This means it will always open at a new record,
and all you have to do is enter select the event.

When you say that "the listbox is bound to the events table", I assume
you mean the listbox is bound to the Event_Number field in the
Registration table? And the Events table is its Row Source?

I would definitely recommend using a combobox instead of a listbox in
this context. It is the use of the listbox that is the main ingredient
in the confusion over existing records being accidentally altered.

If you don't mind me saying so, it seems to me that a very simple
process is being made unnecessarily complicated.

 

[David Wilch]

Since the subform is simply for the purpose of entering new registrations,
you can set the Data Entry property of the form you use for the subform to
Yes.

I tried this and it failed. It still edited an older registration.

When you say that "the listbox is bound to the events table", I assume you

mean the listbox is bound to the Event_Number field in the Registration
table? And the Events table is its Row Source?

You are correct on every account. I'm still lacking on my understanding of
access.

I would definitely recommend using a combobox instead of a listbox in this

context. It is the use of the listbox that is the main ingredient in the
confusion over existing records being accidentally altered.

I chose the list box for 2 reasons.
1. I don't want users accidentally creating new events.
2. I don't want to click a dropdown each time I add an event to the
registration...an athlete might sign up for 1-3 events per competition.
Also, we might register 200 athletes in 2 hrs, so efficiency is critical.

It is the use of the listbox that is the main ingredient

in the confusion over existing records being accidentally altered.

I tried changing to a combobox and when I added a new event it failed again.
The use of a combobox did not correct the problem.

If you don't mind me saying so, it seems to me that a very simple process

is being made unnecessarily complicated.

I'm open to any suggestions. Please understand, there are 5 totally
different reports that need to be generated from the registration table
based on events and the athlete's clubs. Once I get past this bug, I'm
looking to create 2 very complex queries/reports.

I am beginning to believe that the problem stems from an improper dependency
somewhere.

 

[Steve Schapel]

David,

I chose the list box for 2 reasons.
1. I don't want users accidentally creating new events.

This would not be possible.

2. I don't want to click a dropdown each time I add an event to the
registration...an athlete might sign up for 1-3 events per competition.

You wouldn't have to.

Also, we might register 200 athletes in 2 hrs, so efficiency is critical.

Combobox would be more efficient than listbox

I am beginning to believe that the problem stems from an improper dependency
somewhere.

Maybe you could email me your .mdb file and I can have a look at it. If
you want to, send to:
steves at mvps dot org

 

[Steve Schapel]

David,

I have looked at the database you sent me.

I see that you have a MasterEvent table, and an Events table, to reflect
that each MasterEvent can have more than one Event, and it is the events
in the Events table that the Athletes register for. So the Registration
table is set up correctly.

So the question is, how to manage this via your forms. Well, first of
all, my suspicions were right - listboxes are not the right tool for
what you are trying to do. You also are trying to run code on the Click
event of the EventID listbox, which is not appropriate. Possibly the
After Update event would be applicable. On looking back to your
original post, I see that you mentioned this, so I'm sorry I didn't pick
it up at the time.

So, with forms, there is normally a data viewing requirement, and a data
entry/editing requirement. When you are entering your new
registrations, I assume it makes most sense to enter the Athlete
details, and then enter the Event(s) he is registering for. Is that
right? Well, if it was mine, I would make these changes:
1. Make the Event_Registration subform Continuous view.
2. Change EventID to a combobox.

That way, you will see each Event that the current Athlete is registered
for, and it will be virtually impossible for the data entry person to
make an error. Hope you understand what I mean.

If applicable, you could also have another combobox, to select the
MasterEvent currently being registered for, so that the EventID combobox
only lists events from the relevant MasterEvent.

I do not understand the unbound Registration_Confirmation listbox, but
to do the above steps, you would have to remove it from the subform, and
try to get the same functionality some other way. At the moment, it
looks pretty dicey - seems to have a macro that will delete your
Registration table if you don't watch out!

From the data viewing point of view, I would imagine that it would be
good to have a subform on the Events form, so you can look at an event
and see a list of all the Athletes registered.

 

[David Wilch]

Steve,

When you are entering your new

registrations, I assume it makes most sense to enter the Athlete details,
and then enter the Event(s) he is
registering for. Is that right?

This is correct.

I would make these changes:

1. Make the Event_Registration subform Continuous view.
2. Change EventID to a combobox.

I did this and it had no improvement on the form. I was still editing
registration and not adding. I think this is due to bad code in the On
Click event.

That way, you will see each Event that the current Athlete is registered

for, and it will be virtually impossible for the data entry person to make
an error. Hope you understand what I mean.

I don't care about seeing the athlete's past events i this listbox/combobox.
I only want to see my event choices and click on them to add them to
registration. Also, edits are still happpening. I believe this is due to
the list box being bound to registration.

If applicable, you could also have another combobox, to select the

MasterEvent currently being registered for, so that the EventID combobox
only lists events from the relevant MasterEvent.

This would start taking up screen real estate. I don't want another Combox
for this. The EventID listbox is set to show the newest three events. Your
idea would make the db run slightly more efficient but I the user to be
efficient and the form to be as simple as possible.

I do not understand the unbound Registration_Confirmation listbox, but to

do the above steps, you would have to remove it from the subform, and try to
get the same functionality some other way.

No function was desired here. The listbox is from a query of registration so
that I can instantly see that the registration took affect.

I do not understand the unbound Registration_Confirmation listbox, but to

do the above steps, you would have to remove it from the subform, and try to
get the same functionality some other way.

GOOD CATCH! You are right that was old garbage code that attempted to
delete mistakes. I have now removed it. Thanks.

From the data viewing point of view, I would imagine that it would be good

to have a subform on the Events form, so you can look at an event and see a
list of all the Athletes registered.

That's what the Event confirmation listbox does.


After doing some reading on the net last night, it would appear that could
probably ditch the subform alltogether, and move the eventid listbox to the
main form as an unbound listbox. Then the hard part for me be to come up
with the code for the On Click event to add the data to registration. It
looks something like:

______________________________
Dim ???????
Set rst = Dbs.OpenRecordset("Registration", dbOpendynaset)
With rst
..Addnew
!AthleteID=??????????
!EventID=????????????
..Update

End rst
________________________

Does all of this sound correct?

 

[Steve Schapel]

David,

1. Make the Event_Registration subform Continuous view.
2. Change EventID to a combobox.

I did this and it had no improvement on the form. I was still editing
registration and not adding. I think this is due to bad code in the On
Click event.

Yes, I think you should remove the code. Using code in this scenario,
to do something that is much more easily achieved without code, is bound
to cause many headaches.

... I don't care about seeing the athlete's past events i this listbox/combobox.
I only want to see my event choices and click on them to add them to
registration.

Exactly what I am proposing. And then, if you select it/them in a
combobox, you will have them there on the screen, instead of
disappearing into some repository behind the scenes, which creates the
danger of the operator not knowing where they are in the process.

Also, edits are still happpening. I believe this is due to
the list box being bound to registration.

Replace the listbox with a combobox. It is quite correct that this be
bound to the EventID field. Make the subform continuous view, so that
it is easy to see whether you are on an existing or new registration
record. This will avoid the mistake you are seeing.

This would start taking up screen real estate.

No. I'm sorry, I don't seem to be able to make this clear. It would
reduce the clutter you have on the screen at the moment.

I don't want another Combox
for this. The EventID listbox is set to show the newest three events. Your
idea would make the db run slightly more efficient but I the user to be
efficient and the form to be as simple as possible.

My whole purpose here is to simplify this for you.

No function was desired here. The listbox is from a query of registration so
that I can instantly see that the registration took affect.

Ok, so it can be removed, as if you directly enter the registration,
there will be no need for an additional place to "see that the
registration took affect", it will already be obvious.

After doing some reading on the net last night, it would appear that could
probably ditch the subform alltogether, and move the eventid listbox to the
main form as an unbound listbox. Then the hard part for me be to come up
with the code for the On Click event to add the data to registration. It
looks something like:
______________________________
Dim ???????
Set rst = Dbs.OpenRecordset("Registration", dbOpendynaset)
With rst
.Addnew
!AthleteID=??????????
!EventID=????????????
.Update

End rst
________________________

Does all of this sound correct?

No, it not correct. Forget using code. Entering data directly into
bound controls on a bound form is one of the simplest, most basic things
you can do in Access, and this is exactly what your requirements
dictate. No code.

 

[David Wilch]

Yes, I think you should remove the code.

Did that.

Replace the listbox with a combobox.

Did that.

It is quite correct that this be bound to the EventID field.

Did that.

Make the subform continuous view, so that it is easy to see whether you are

on an existing or new registration record.

Did that.

And it's still not working. It still edits and does not add.

???????????

 

[Steve Schapel]

David,

Care to send me the updated version?

 

[Steve Schapel]

David,

You need to make the vertical height of the subform smaller, so that you
can see more than one record, and scroll to the new record. That's the
whole point. I will do a quick tweak of how I would do it, and send the
database file back to you, but it will be later in the day.

 

[Steve Schapel]

David,

I have done a very quick and rough tweak to your database, principally
to the subform, to illustrate the type of approach I have been trying to
describe. Hope you can make sense of it. There is an unbound combobox
in the Header of the subform, where you can select the MasterEvent you
are registering for, after which the EventID combobox in the subform
will only list those events associated with the selected MasterEvent.
So you can quickly enter the event(s) the Athlete is registering for,
and then move on to the next Athlete, but the registration process is
much simpler and more transparent, and therefore less prone to error or
confusion. Well, that's my theory anyway, so hope you will agree.

Another thing I would change to make things simpler - I couldn't
understand the purpose of the TrainingNameID field in the TrainingName
table. Surely each entry in the TrainingName field will be unique, so
there is no purpose served by an Autonumber field. I would change the
EventNameID field in the Events table to Text instead of Number data
type, and then the lookup to the TrainingName table can directly enter
from the TrainingName field. One result of this is that the
TrainingName table then won't need to be included in queries just so the
TrainingName can be retrieved based on the useless
TrainingNameID/EventNameID. Hope that makes sense. Mind you, making
this change would involve a few compensatory changes to some existing
stuff, including part of what I have done.

 

[David Wilch]

Another thing I would change to make things simpler - I couldn't understand
the purpose of the TrainingNameID field in the TrainingName table. Surely
each entry in the TrainingName field will be unique, so there is no purpose
served by an Autonumber field.

You are correct and I understand. I will change this as soon as I get
everything else working.

...after which the EventID combobox in the subform

will only list those events associated with the selected MasterEvent.

This does not work. When I select an athlete and then a master event, no
events show up in the drop down boxes.

I like your approach with the master event combobox. I never knew that was
possible. I like your approach with the multiply event boxes. I just
wished it would work.

Am I doing something wrong.
Any ideas?

Why is this thing 6MB when I email it and only 2MB (before zip) when you
email it?

 

[Steve Schapel]

David,

This does not work. When I select an athlete and then a master event, no
events show up in the drop down boxes.

Some of the MasterEvents don't have Events associated with them, and of
course some athletes are not yet registered for any events. Try
MasterEvent "McDonough: 4/03/2006" the Event combobox should list
"ScrimmageAM: 4/03/2006", "ScrimmagePM: 4/03/2006"; or MasterEvent
"Jacksonville: 11/06/2005" should list "Scrimmage: 12/06/2005", "Lee:
11/06/2005", "Scrimmage: 11/06/2005" - at least it does for me!

There will always be one blank Event combobox for each Athlete... that's
where you enter the new registration

Let me know if you still can't get it right.

Why is this thing 6MB when I email it and only 2MB (before zip) when you
email it?

Tools=>Database Utilities=>Compact & Repair

 

[David Wilch]

Steve,

It's not doing that for me.

Is there something that I could have setup wrong in access?
Maybe the db is fine and my access is setup wrong.

Any ideas.

I can't get past the Master combobox. It has all the options. It is always
starting at 3/4/06 no matter which athlete I select.
Then, there are no events. I'm checking only the last two master events
because they are associated to 23,24,26.

I get one blank event combox box no matter what.
The bottom of the subform always shows 1 record.

?????????

 

[Steve Schapel]

David,

I can't really see how anything with the Access setup could affect it.
You and I use a different date format, but I don't see how that would
cause a problem either.

The way I set it up, when you first open the application, the Master
Event combobox should show "- All -" by default, and selecting (or
navigating) to any Athlete should show all their registrations in all
events they have registered for. If you change the entry in the Master
Event combobox, then navigating to any Athlete should only show their
registrations for Events within the selected Master Event. The Master
Event should stay the same unless you change it, even when you move from
Athlete to Athlete. And the Events listed in the combobox in the
subform should only show the Events for the selected Master Event. All
this only happens on the After Update event of the Master Event
combobox, so when you say "it is always starting at 3/4/06" well, I
don't know why this would be the case, but you won't get the desired
result until you actually make a selection in the Master Event combobox.
So if you actually drop down the Master Event combobox list, and
select "McDonough: 4/03/2006", or for you probably says "McDonough:
3/04/2006", and then after that, use the navigation buttons at the very
bottom to scroll through the 6 athletes... don't you see Record 2 (Jane
Smith) as registered for "ScrimmagePM: 4/03/2006", and Record 3 (Jose
Gonzales) is registered for both "ScrimmageAM: 4/03/2006" and
"ScrimmagePM: 4/03/2006", and Records 5 and 6 (Cindy Brady and John Kim)
both registered for "ScrimmagePM: 4/03/2006", and the other 2 (records 1
and 4) not registered for anything? If not, what do you actually see?

 

[David Wilch]

Steve,
I got it!!!

Macro security was set to medium level.
Why would this have mattered?


Know the learning/teaching begins.
I want to start here:

SELECT Events.EventID, TrainingName.TrainingName & ": " & Events.EventDate &
IIf(Forms!Athlete_Registration!Registration_Form!MasterSel=0," (" &
[MasterLocationName] & ")","") FROM MasterEvent INNER JOIN (TrainingName
INNER JOIN Events ON TrainingName.TrainingNameID=Events.EventNameID) ON
(MasterEvent.MasterEventID=Events.MasterEventID) AND (MasterEvent.
MasterEventID=Events.MasterEventID) WHERE (((Events.MasterEventID)=Forms!
Athlete_Registration!Registration_Form!MasterSel)) Or (((Forms!
Athlete_Registration!Registration_Form!MasterSel)=0)) ORDER BY Events.
EventID DESC;

First, MasterSel=0 refers to the combobox in the header and zero is a null
row choice?

This
SELECT Events.EventID, TrainingName.TrainingName...
associates all the events with the athlete through the registration table?

IIf is a condition?

I'm lost here:
IIf(Forms!Athlete_Registration!Registration_Form!MasterSel=0," (" &
[MasterLocationName] & ")","")

After I digest this, I may have one or two more questions.

Thanks,

David

 

[Steve Schapel]

David,

I got it!!!
Excellent!

Macro security was set to medium level.
Why would this have mattered?

I don't know!

First, MasterSel=0 refers to the combobox in the header and zero is a null
row choice?

0 is the " - All -" row choice. See the Row Source of the MasterSel
combobox.

I'm lost here:
IIf(Forms!Athlete_Registration!Registration_Form!MasterSel=0," (" &
[MasterLocationName] & ")","")

I wanted the registration entries in the subform to show the name of the
Master Event if "All" Master Events were being included, but if a
specific Master Event is selected in the MasterSel combobox, don't show
it in the subform (duplicated information => clutter) . So, to
translate the expression...
If the MasterSel is set to "All", put the MasterLocationName, enclosed
in ()s, after the name of the Event, otherwise nothing (i.e. "") after
the name of the Event.

 

[David Wilch]

Steve,

I like the changes that you made.

In the MasterEvent Sel combobox it is sorting by ASC by default. I want to
change this to DESC.

Presently:
SELECT 0, " - All -" FROM MasterEvent UNION SELECT MasterEventID,
[MasterLocationName] & ": " & [MasterEventDate] FROM MasterEvent;

I tried this and it didn't work:
SELECT 0, " - All -" FROM MasterEvent UNION (SELECT MasterEventID,
[MasterLocationName] & ": " & [MasterEventDate] FROM MasterEvent ORDER BY
MasterEvent.MasterEventID DESC);

Why not?
How can I do it properly?

Thanks,
David