Full Version: Sub Form not displaying data
UtterAccess Forums > MicrosoftŪ Access > Access Forms
mraetrudeaujr
HELP!!!
Here are the suspects;
TABLE
tbl_ShiftBids
ShiftBidID - Auto - PK
AgentID - Number - FK to the 'tbl_Agents'
ShiftBidID - Number - Fk to the 'tbl_ShiftBids'
ChoiceID - Number - FK to the 'tbl_Shifts'
FinalResult - Number - FK to the 'tbl_Shifts'
ChoiceNumber - Number - Integer
BidDate - Text
Bumped - Yes/No
Submitted - Yes/No
FORMS
frm_ShiftBids
frm_Shift Bids_sub
frm_Shift Bid Results_sub
The frm_ShiftBids form has the main table tbl_Agents as its' Record Source.
On this form I have two subforms;
frm_Shift Bids_sub
frm_Shift Bid Results_sub
...I split the information GUI gathering for this tbl_ShiftBids into two subforms because I did not want redundant or duplicate information for each "Shift Bid" record. Note that there WILL be two choices for each Agent, but only one (1) condition (or outcome) of 'Final Results', 'Submitted', and 'Bumped'.
THERE IS THE PROBLEM:
First I input the data into the frm_Shift Bids_sub, then I go to the frm_Shift Bid Results_sub.
When I input information into this subform (frm_Shift Bid Results_sub) and then go to the next Agent (Employee), by 'mousewheel' scrolling (for example), then back to this Agent, the information is not there.
However, if I "re-input" the information (data) and scroll forward to the next Agent, and then back...the information IS there.
I'm thinking that it may be something with the 'Relationships' but I need to have someone check the forms, first. I have attached a 'sanitized' version of my DB.
Al T.
trapperalexander
Took me a minute to recreate your problem, but I figured it out after showing the navigation buttons on your subform. If there is only going to be 1 record for each frm_shift Bid Results_sub ( 1 result for each record in tblAgents ) then add those Three(3) fields (Submitted, bumped, final results) to your Agents table and delete them from tbl_shiftBids. After doing this, you will need to set your subform RecordSource to tbl_Agents, or just delete the subform and put those Three(3) fields on your form.
Trapper
mraetrudeaujr
Yes, that would be a quick 'fix', but then this shift bid process is Bi-Annual event. So, I need to make sure that each 'Final Result', 'Submitted', and Bumped' results can be tracked back to a particular date; in this case, the date is held as 'BidDate' in the 'tbl_ShiftBids' table.
esides, each process will have at least five hundred (500) 'ID' records added --- twice a year! That will bloat this 'tbl_Agents' table dramatically, and this table contains the most sensitive and difficult employee records to maintain.
Without this connection, I will not have any way to accurately display who submitted or who got 'bumped', and what the final outcome was for THAT process event.
That's also why I put in the 'ShiftBidID' FK with the hope of tying this part to the tblShiftBids table.
I'm using the AgentID FK to tie the form's Source to the subforms.
No, I don't think that I've got this worked out yet. This is the last MAJOR module to work out for this database, and it is proving more challenging than I've experienced so far.
Al T.
trapperalexander
Al,
orru I did not see the whole picture from your first post, and I am not sure if I do now. From what I understand it would make sense to create a new table for your Final Results. insert it into your relationships table like this...
tblAgents:
AgentID - PK - auto
....
tblFinalResults:
ResultID - PK - auto
AgentID - FK to tblAgents
Final Result
Submitted
Bumped
Date
tblShiftBids:
ShiftBidID - PK -auto
ResultID - FK to tblFinalResults
...
your date field will be in your new tblFinalResults. this will allow you to track a history of final results for each Agent(each final result having a date). And each FinalResult can have several ShiftBids.
HAs I said before, not sure if I see the entire picture yet so let me know if this will accomplish what you are wanting.
thanks
-Trapper
mraetrudeaujr
Ho boy....
ow, I'm back to square one. By doing this;
The records displayed in the form frm_Shift Bids_sub subform within the frm_ShiftBids stay in place, even though I go to another record! (e.g. scrolling to another Agent 'record').
Furthermore, I receive the following error:
"You cannot add or change a record because a related record is required in table 'tbl_Agents'."
I am so frustrated! I have torn down these tables, rebuilt them, added records, deleted records, tore down forms, rebuilt them, deleted relationships, re-established links, deleted linked tables, re-linked them...I think I'm becoming more confused than learning anything!
I AM ABOUT READY TO BLOW A GASKET!!! crazy.gif
I wish that it was as easy as our UA Moderators, VIP's, Gurus, etc. make it seem. I'll bet that I could sit down with any one of them with my database and they would have it 'fixed' in less than thirty (30) minutes! uarulez2.gif
NORMALIZING your tables is MUCH harder in practice than in theory!
Yes, I could do it the 'old' way and just throw everything in a table and let'er rip! But I'm trying to do this the 'right' way. I sure wish other UA users would chip in here!
Sorry Trapper, I'm not venting on you, I'm just frustrated. I have less than three weeks to get this working, AND input last April's data, before the October Shift Bid Process kicks off confused.gif
Thanks Trapper...we'll keep trying. thanks.gif
(BTW, did you try what you suggested with my attachement? If you get it working, just re-attach it and I download and dissect what you did to get this part working.)
Al T.
trapperalexander
Look at the form "TEST" in the attachment...see if that will get you anywhere! Ill keep trying to help as long as noone else has any advice...
Trapper
mraetrudeaujr
Trapper,
It looks like the only thing that you did different was to put the 'frm_Shift Bids_sub' as a subform of the subform 'tbl_Shift Bid Results_sub', right?
...though you used a 'test' form and a 'test' table... wink.gif
Al Trudeau
trapperalexander
No, I created a new table as I suggested earlier tblFinalResults. And I added this into your relationships window. I did use your frm_shiftBids to start off. I deleted both subforms. Added a new subform which i created from the new tblFinalResults, and a sub-subform which i just copied from your original frm_shiftBids.
id that help you?
-Trapper
mraetrudeaujr
Well, actually yes and no.
Y actually yes and no.
Your 'new' table equates to my tbl_ShiftBidResults, without the "ShiftBidID" field, and you added a "Date" field. The reason for a lack of "Date" field, here, is because I have it in the tbl_ShiftBids table. So this may or may not have an effect on the final results. Unless my tbl_ShiftBidResults is corrupted, it should work with taking out the 'ShiftBidID' field; I'll play with it with some data and see what happens.
If the problem is with the 'Date' (BidDate) not being in the tbl_ShiftBidResults, then I'll just move MY date field ("BidDate") to the tbl_ShiftBidResults. Then I'll put this frm_Shift Bids_sub into my frm_Shift Bid Results_sub;
And, yes, from your newly created subform (tblFinalResults subform --- created from your table, 'tblFinalResults'), you inserted my subform frm_Shift Bids_sub.
Obviously I'll have to check the relationships. But, this will effectively give me what you have set up. I'll post back either way (positive or negative results). Thanks
Al Trudeau
trapperalexander
Al,
think you have got it figured out! Sorry for any confusion, just trying to help!
good luck and I look forward to hearing your feedback.
-Trapper
mraetrudeaujr
Well, I still get that same problem, even with the changes.
o I investigated your 'Sources' on the forms and I discovered that the 'Source' for your first subform
TEST was using an SQL statement???
I did you build this or did you use the 'Wizard' and then go into "design" mode and add the other subform to it?
In other words, it looks like your TEST subform is a 'recordset' --- not the actual table.
Al T.
trapperalexander
Al,
It works either way. If you change the RecordSource of my first subform to the table I created - tblFinalResults than you get the same results. I just am in the habit of using forms based on querys, but since you are using a continuous form with all records than it does not make any difference.
Have you taken a look at the relationship window and the changes I made to it. I also changed the Agent_ID in your tbl_shiftbids to ResultID - linking to tblFinalResults.
-Trapper
mraetrudeaujr
Okay, Okay blush.gif
figured it out. After creating these 'subforms' using the Wizard, I got halfway there. Still a few errors. Further exploring and I discovered that I hadn't made all of the changes needed to all of the forms dazed.gif
After clearing this up, it appears like it's working. I will know after I put in several more records.
Thanks Trapper! thanks.gif
Al T.
mraetrudeaujr
You're too fast! laugh.gif
tried to get my reply in before you saw the last post...
Thanks!
Al T.
trapperalexander
Welcome! Good Luck with your project!
et me know how it goes.
-Trapper
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.