Full Version: I can't get acNewRec to work in a list box (using On Click) in a
UtterAccess Forums > Microsoft® Access > Access Forms
slingblade01
This is driving me crazy.
First, I'm a novice at access and VBA.
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. "AthleteID" is the primary key.
I have subform "Registration_Form".
The purpose of this subform is to allow me to input "AthleteID" and "EventID" (from event table) into "Registration_Table". "RegistrationID" is the primary key.
This subform only contains 3 objects(?) in it.
The First is a textbox with the athlete's name. This is showing me that the primary key "AthleteID" carried down to the subform properly. It always does.
The Third (I'll come back to the second) is a list box simply showing the last 5 events in "Registration_Table". I use "Registration _Query" to accomplish this.
The second object is where the trouble lies. I have a listbox of predetermined events. In the properties of that list box under On Click, I have:
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
What I want to happen is for Registration_Table to autonumber a new record "RegistrationID" and record "AthleteID" and "EventID" in the same record.
If the athlete is new (no history in Registration_Table), it works perfectly.
If the athlete has a history in the Registration_Table, it edits the record by changing an old "EventID".
I did noticed that the record number at the bottom of the sub-form increases,
but nothing was recorded in that location.
If I add a New button ( DoCmd.GoToRecord , , acNewRec) outside of the listbox and click it before I click in the listbox, it works perfectly. I need to get rid of this extra step.
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.
Odd note:(before the click) 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.
TIA,
David
tinygiant
It sounds like there's a problem with which recordsource the subform is bound to. It seems the subform is being requeried to search for data based on what you've input on the main form and then makes that record current. Clicking on the listbox will automatically change the data in that record.
) What is the recordsource of your subform?
2) Is there already an item selected in the list when the subform is open or when an athlete is selected?
3) Have you tried opening the form Add Only to force a new record every time?
4) Is there a way you can post this part of your db?
BTW, Welcome to UA!
slingblade01
Tinygiant,
think your right! That makes sense.
>1) What is the recordsource of your subform?
SELECT DISTINCTROW Registration.* FROM Athlete INNER JOIN (Events INNER JOIN Registration ON (Events.EventID=Registration.EventID) AND (Events.EventID=Registration.EventID)) ON Athlete.AthleteID=Registration.AthleteID;
>2) Is there already an item selected in the list when the subform is open or when an athlete is selected?
Yes, the last event.
>3) Have you tried opening the form Add Only to force a new record every time?
Oadded a add_new button to subform outside of the list and that works perfectly if I click it first before I click in the list. However, I want this integrated into the list ( idiot proof ).
>4) Is there a way you can post this part of your db?
Is the above part what you need?
Tinygiant, I understand what you are saying...and it make perfect sense. The subform is linking the athlete with his last event.
But why wouldn't the acNewRec command in the listbox start a new record?
Another thing that I noticed, when I click on the event, and it changes the last event, the bottom of the subform (where the access record numbers are located) will display one more record added for that athlete. But when I verify the Registration table, the additional record is not there and the last event was changed.
So, I think it is trying create a new record, but it just modifies an old one.
Should I consider removing the events table from the subform's recordsource?
Thanks again,
David
slingblade01
Ok, I cleaned up the subform so that the record source is now just Registration (table).
This had no affect on the function of the listbox .
So next I change the control source of the listbox. It was set to EventID of Registration(table). It is now unbound.
Now nothing works in the subform but I think this is the right direction.
Do I next use record set methods in the On Click event to write the new record?
I am clueless here.
TIA,
David
tinygiant
I have an example of what you're trying to do at home. When I get a chance tonight (or early tomorrow morning), I'll thin it out so I can post it up here for you.
Here's how it works in case you want to try to experiment before I can get you the example.
It has two unbound listboxes. One holds the athletes names and one holds the event names. Updating either listbox requeries the form. There are several other bound controls and whenever the form is dirtied, either the record is edited, if it already exists, or a new one is automatically added, if a record does not exist. The dirty event is used to assign the athlete and event id to the appropriate fields, if a new record is added.
Sorry I can't attach it right now, I just don't have access to it and I'm off to a meeting!
tinygiant
Ok, so here's the example I was talking about.
It's the only form in the database. It uses two unbound listboxes to control a third bound listbox. There are two names in the list. Jack Johnson has all his permissions set already. So if you click on his name, then start selecting the components (second listbox), you'll see the bound result in the third. You can edit these results. If you choose the other guy (HaveNo Permissions), when you select a component, there is nothing selected in the third listbox. If you select something, a new record is created in the table tblSecurityPermission.
To show that records are actualy being created, look at tblSecurityPermission before adding any permissions for the second guy. You'll see there are five records which correspond to the five possible permissions in the component (second) listbox. When you add one record for the second guy, open up the table again and you'll one additional record added.
Hope this helps. Let me know if you have any questions about how it works. There is a lot of commented out code to make it work without all of the other db components, you can just ignore 'em.
slingblade01
>If you choose the other guy (HaveNo Permissions), when you select a component, there is nothing selected in the third listbox. If you select something, a new record is created in the table tblSecurityPermission
Tried this with a single click and no new record. Tried it with a double and nothing. Tried it with a single and then changed to a new component and crash...run time error.
Another thing, your listbox is bound to a control source from the same table that your form gets its record source.
My listbox is not bound to the same table.
Any ideas???
tinygiant
Odd. I use this setup all the time and it works great. I'll take a look at it when I get to work tomorrow. I probably forgot to include a component. Sorry for the non-working example. I'll double-check the function before I post the next one.
tinygiant
Ok. Here's a different version that I know works from a current db I'm using (and I tested it this time!) The combo at the top of the form can simulate your athlete selection. And the list on the left can simulate your event. All of the other controls are part of the record. To test this one, choose "Squadron Commander" from the drop down at the top (it has no records in the table right now). Select a component (Left Listbox) and then select the option through the rest of the controls. As you select them, new records will appears in the table tblDatabaseSecurityProfileDetail.
That datasources listboxes get their info from should be irrelevant if they're not part of the data set. If they are part of the data set and you want them to show data that may not necessarily be part of the record, that solution is also included. Take a look at the _Dirty event of the form to see and example of how data is moved to the appropriate controls when required.
slingblade01
got your's working.
on't have a lot of time to study it today or tomorrow.
I will try to find some time on sat. and maybe have a few questions for you after then.
thanks for help so far.
David
tinygiant
No problem. Glad I could help. If I don't respond to this thread, feel free to PM me as sometimes these things get lost in the shuffle.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.