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

Form Doesn't Display Existing Data

P: 12
Hi,

I am trying to create a data entry form for a large database. There is a parent form with 6 forms linked with a one-to-one relationship (due to a large number of fields) and several tables linked with a one-to-many. All have auto update/delete checked. The date entry form uses tab control (across 11 tabs) and subforms for some of the tables. I have two problems...

Firstly, if I enter part of the data for a new record in the form (eg fill in tabs 1-3) and then close it, the data appears in the tables, but when I re-open the form the record does not appear, so it is impossible to complete the data entry.

Secondly, ** Edit How do I Navigate to Next Tab Automatically **

I am fairly new to access and not familiar with all the code etc behind it, so I would be grateful for any advice!

Thanks
Dec 15 '11 #1
Share this Question
Share on Google+
16 Replies


NeoPa
Expert Mod 15k+
P: 31,186
This sounds very much like you have the Data Entry property set for the form. This ensures no existing data can be seen. Only new records can be entered. Check it out in Context-Sensitive Help.

NB. Remember only one question per thread please.
Dec 15 '11 #2

P: 12
Thanks for the response. The Data Entry property on the form is set to 'No', and I can see all the records displayed that were previously entered on the last form, which I have had to redesign to add more fields. When I open the form there are 417 entries. If I start to add entry 418 (e.g. enter ID and another field) the data displays in the table, but when I open the form again it still shows only 417 entries.
Dec 15 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Is this a form of your own creation or something you have inherited?

What version of access?

What the recordsource of the main form?

Are tab 1-3 part of the main form or a subform?

When you look at the newly created record in the table is the key/id field filled out?
Dec 15 '11 #4

P: 12
Sadly its a form of my own creation... Its in Access 2007, and based on another simpler version I had made before and worked fine, but the very large number of fields seems to be making things very complicated!

The record source for the main form is below. I'm guessing the fact that this is ridiculously long is not a good sign! I tried first putting all the fields from the one-to-one tables in as bound fields but had to replace many of them with subforms as there were too many fields.

Tabs 1-3 are part of the main form and subforms, and when the record is created in the table the ID is filled in.

Expand|Select|Wrap|Line Numbers
  1. SELECT [1 Background].*, [2 Reef use].ID AS [ID_2 Reef use], [2 Reef use].SeaImportant_YN, [2 Reef use].SeaImportance, [2 Reef use].ReefImportant_YN, [2 Reef use].ReefsImportance, [2 Reef use].UseOfSea_comments, [2 Reef use].PastUse, [2 Reef use].WorkSuccess, [2 Reef use].ReefsWorkSuccess, [2 Reef use].ReefsWorkSuccess_comment, [2 Reef use].FishingType, [2 Reef use].OtherSeasonalInfo, [2 Reef use].TrapFisher_Soak, [2 Reef use].Trap_HaulReset, [2 Reef use].Trap_Comments, [2 Reef use].SellCatch_CommunityMarket, [2 Reef use].SellCatch_OtherMarkets, [2 Reef use].SellCatch_Exporters, [2 Reef use].SellCatch_Middlemen, [2 Reef use].SellCatch_Cooperatives, [2 Reef use].SellCatch_LocalStores, [2 Reef use].SellCatch_LocalPeople, [2 Reef use].SellCatch_NoSale, [2 Reef use].SellCatch_Restaurants, [2 Reef use].SellCatch_Hotels, [2 Reef use].SellCatch_Comments, [2 Reef use].BoatOwnership, [2 Reef use].VesselName, [2 Reef use].OwnerName, [2 Reef use].BoatKept, [2 Reef use].NumberCrew_Midpoint, [2 Reef use].NumberCrew_Comments, [2 Reef use].ReefFishHome_Self, [2 Reef use].ReefFishHome_Crew, [2 Reef use].ReefFishHome_Comments, [2 Reef use].ShareSystem, [3 Environment & reef health].ID AS [ID_3 Environment & reef health], [3 Environment & reef health].WeatherChanges_LT, [3 Environment & reef health].WeatherChanges_Extreme, [3 Environment & reef health].ImpactOnSea, [3 Environment & reef health].ClimateChange, [3 Environment & reef health].CC_Effects, [3 Environment & reef health].CC_Reefs_YN, [3 Environment & reef health].CC_Reefs, [3 Environment & reef health].HealthyReef, [3 Environment & reef health].ReefHealth, [3 Environment & reef health].ReefHealth_Comment, [3 Environment & reef health].ReefFishHealth, [3 Environment & reef health].ReefFishHealth_Comment, [3 Environment & reef health].AffectsReef, [3 Environment & reef health].ChangeReefs_Comment, [3 Environment & reef health].EffectOfReefChange, [3 Environment & reef health].[FutureChange Reefs_YN], [3 Environment & reef health].FutureChangeReefs, [3 Environment & reef health].ImproveReefHealth, [3 Environment & reef health].WorkedInPast, [4 Reef management].ID AS [ID_4 Reef management], [4 Reef management].ResponsibleReefs, [4 Reef management].Rules_YN, [4 Reef management].RulesComment, [4 Reef management].RulesAffectLivelihood, [4 Reef management].RulesEnforced, [4 Reef management].UnderstandWho, [4 Reef management].UnderstandWho_Comment, [4 Reef management].InfoOnDecisions, [4 Reef management].InfoOnDecisions_Comment, [4 Reef management].GoodJob, [4 Reef management].GoodJob_Comment, [4 Reef management].ChallengeRules, [4 Reef management].ChallengeRules_Comment, [4 Reef management].Participate, [4 Reef management].Participate_Comment, [4 Reef management].FairEnforcement, [4 Reef management].FairEnforcement_Comment, [4 Reef management].GroupsCooperate, [4 Reef management].GroupsCooperate_Comment, [4 Reef management].Resources, [4 Reef management].Resources_Comment, [4 Reef management].FuturePlans, [4 Reef management].FuturePlans_Comment, [4 Reef management].InfoReefs, [4 Reef management].ChallengeWho, [4 Reef management].Suggestions, [5 Occupations & fish consumption].ID AS [ID_5 Occupations & fish consumption], [5 Occupations & fish consumption].Family, [5 Occupations & fish consumption].YearsResident, [5 Occupations & fish consumption].WhyMove, [5 Occupations & fish consumption].OtherFamily, [5 Occupations & fish consumption].HH_NoPeople, [5 Occupations & fish consumption].HH_M, [5 Occupations & fish consumption].HH_F, [5 Occupations & fish consumption].PeopleWorking, [5 Occupations & fish consumption].PeopleWorking_M, [5 Occupations & fish consumption].PeopleWorking_F, [5 Occupations & fish consumption].Contribute_Outside, [5 Occupations & fish consumption].Contribute_Outside_Number, [5 Occupations & fish consumption].Contribute_Outside_Comment, [5 Occupations & fish consumption].Depend_Outside, [5 Occupations & fish consumption].Depend_Outside_Number, [5 Occupations & fish consumption].Depend_Outside_Comment, [5 Occupations & fish consumption].PercentIncomeReefFishing, [5 Occupations & fish consumption].MainProvider, [5 Occupations & fish consumption].SeasonalChangeIncome, [5 Occupations & fish consumption].SeasonalChangeIncome_Comment, [5 Occupations & fish consumption].CopeChangeIncome, [5 Occupations & fish consumption].OccupationsChanged, [5 Occupations & fish consumption].OccupationsChanged_Comment, [5 Occupations & fish consumption].HH_Changes, [5 Occupations & fish consumption].HH_ChangesFuture, [5 Occupations & fish consumption].Jobs_Options, [5 Occupations & fish consumption].OtherFishing, [5 Occupations & fish consumption].OtherFishing_which, [5 Occupations & fish consumption].NotFishing_other, [5 Occupations & fish consumption].FishingTimeChanged, [5 Occupations & fish consumption].FishingTimeChanged_Comment, [5 Occupations & fish consumption].FishingGearChanged, [5 Occupations & fish consumption].FishingGearChanged_Comment, [5 Occupations & fish consumption].ChangeGasPrice, [5 Occupations & fish consumption].ChangeGasPrice_Comment, [5 Occupations & fish consumption].LikeFishing_Easy, [5 Occupations & fish consumption].LikeFishing_Income, [5 Occupations & fish consumption].LikeFishing_Food, [5 Occupations & fish consumption].LikeFishing_Enjoy, [5 Occupations & fish consumption].LikeFishing_NoBoss, [5 Occupations & fish consumption].LikeFishing_Other, [5 Occupations & fish consumption].LikeFishing_Comment, [5 Occupations & fish consumption].DislikeFishing_Strenuous, [5 Occupations & fish consumption].DislikeFishing_Income, [5 Occupations & fish consumption].DislikeFishing_Nothing, [5 Occupations & fish consumption].DislikeFishing_Other, [5 Occupations & fish consumption].DislikeFishing_Comment, [5 Occupations & fish consumption].ChildrenFishing, [5 Occupations & fish consumption].ChildrenFishing_Comment, [5 Occupations & fish consumption].CompensateFishing, [5 Occupations & fish consumption].[CompensateFishing$], [5 Occupations & fish consumption].CompensateFishing_Comment, [5 Occupations & fish consumption].NoChange, [5 Occupations & fish consumption].[25%more], [5 Occupations & fish consumption].[25%fewer], [5 Occupations & fish consumption].[50%fewer], [5 Occupations & fish consumption].[25%larger], [5 Occupations & fish consumption].[25%smaller], [5 Occupations & fish consumption].[50%smaller], [5 Occupations & fish consumption].ChangesComments, [5 Occupations & fish consumption].EatReefFish_Freq, [5 Occupations & fish consumption].EatConchLobster_Freq, [5 Occupations & fish consumption].EatReefFood_Freq_Comment, [5 Occupations & fish consumption].EatReefFood_Type, [5 Occupations & fish consumption].EatOtherSeafood, [5 Occupations & fish consumption].EatSeafoodWhy, [5 Occupations & fish consumption].EatReefFoodPast, [5 Occupations & fish consumption].EatReefFoodPast_Comment, [5 Occupations & fish consumption].EatReefFoodFuture, [5 Occupations & fish consumption].EatReefFoodFuture_Comment, [5 Occupations & fish consumption].ReefFishChanges_Price, [5 Occupations & fish consumption].ReefFishChanges_Avail, [5 Occupations & fish consumption].ReefFishChanges_Size, [5 Occupations & fish consumption].ReefFishChanges, [6 Community & social capital].ID AS [ID_6 Community & social capital], [6 Community & social capital].GroupsLikeToJoin_YN, [6 Community & social capital].GroupsLikeToJoin, [6 Community & social capital].ReefRelatedGroups, [6 Community & social capital].ReefRelatedGroups_Represent, [6 Community & social capital].ReefRelatedGroups_AchieveAims, [6 Community & social capital].ReefRelatedGroups_MoreEffective, [6 Community & social capital].ReefRelatedGroups_WhyNot, [6 Community & social capital].CommunityHonest, [6 Community & social capital].CommunityHonest_Comment, [6 Community & social capital].OwnWelfare, [6 Community & social capital].OwnWelfare_Comment, [6 Community & social capital].CommunityRelations, [6 Community & social capital].CommunityRelations_Comment, [6 Community & social capital].ResponsibilityGov, [6 Community & social capital].ResponsibilityGov_Comment, [6 Community & social capital].CommunityTrustworthy, [6 Community & social capital].CommunityTrustworthy_Comment, [6 Community & social capital].Acceptance, [6 Community & social capital].Acceptance_Comment, [6 Community & social capital].Conflicts, [6 Community & social capital].Conflicts_Comment, [6 Community & social capital].SolveProblems, [6 Community & social capital].SolveProblems_Comment, [6 Community & social capital].DecisionMaking, [6 Community & social capital].DecisionMaking_Comment, [7 Demographics & contact info].ID AS [ID_7 Demographics & contact info], [7 Demographics & contact info].Nationality, [7 Demographics & contact info].Religion, [7 Demographics & contact info].Ethnicity, [7 Demographics & contact info].GroupAssociate, [7 Demographics & contact info].AgeYrs, [7 Demographics & contact info].School, [7 Demographics & contact info].Courses, [7 Demographics & contact info].Courses_Comment, [7 Demographics & contact info].MaritalStatus, [7 Demographics & contact info].OwnLand, [7 Demographics & contact info].House_Ownership, [7 Demographics & contact info].House_Characteristics, [7 Demographics & contact info].Electricity, [7 Demographics & contact info].TV, [7 Demographics & contact info].[VCR/DVD], [7 Demographics & contact info].Telephone, [7 Demographics & contact info].Internet, [7 Demographics & contact info].Computer, [7 Demographics & contact info].[Washing machine], [7 Demographics & contact info].Refrigerator, [7 Demographics & contact info].Stove, [7 Demographics & contact info].PipedWater, [7 Demographics & contact info].Sewage, [7 Demographics & contact info].Vehicle, [7 Demographics & contact info].Walls, [7 Demographics & contact info].Roof, [7 Demographics & contact info].Floor, [7 Demographics & contact info].ImprovingMaintaining, [7 Demographics & contact info].KeptUpToDate, [7 Demographics & contact info].Phone, [7
Dec 15 '11 #5

NeoPa
Expert Mod 15k+
P: 31,186
Rachel:
Thanks for the response. The Data Entry property on the form is set to 'No', and I can see all the records displayed that were previously entered on the last form, which I have had to redesign to add more fields. When I open the form there are 417 entries. If I start to add entry 418 (e.g. enter ID and another field) the data displays in the table, but when I open the form again it still shows only 417 entries.
You keep referring to forms but it seems there is more than one of them. If so, it's hard to follow your meaning as you never specify which you're referring to.

An almost random guess would be to ensure you run a Requery on any form that was already open when any separate form added data to the table that the first form is using as a record source.

Otherwise, please explain your situation again more clearly.
Dec 15 '11 #6

P: 12
Sorry, I got confused between tables and forms in that last reply. There is one main form and several subforms - none of the subforms have tabs.
Dec 15 '11 #7

NeoPa
Expert Mod 15k+
P: 31,186
Rachel:
The record source for the main form is below. I'm guessing the fact that this is ridiculously long is not a good sign!
Indeed. Not only did it (you?) fail to copy all the SQL, thereby making it 100% useless, but even if it had it would have been a horrendous mess to ask people to work with. Currently, it is cut off part-way through the SELECT clause.
Dec 15 '11 #8

NeoPa
Expert Mod 15k+
P: 31,186
Rachel:
Sorry, I got confused between tables and forms in that last reply. There is one main form and several subforms - none of the subforms have tabs.
And when you looked into the Requery option you found ...?
Dec 15 '11 #9

P: 12
It, I think, since I havent manually copied any SQL. I take it I might be better off starting from scratch. If thats the case, do you have any advice on how I might design it better, given the huge number of fields I have?
Dec 15 '11 #10

P: 12
I havent looked into the Requery option yet - will take some time as I have no idea what that means. Apologies if I am asking stupid questions but I am a beginner...
Dec 15 '11 #11

NeoPa
Expert Mod 15k+
P: 31,186
Stupid questions are never a problem. Only avoiding asking something you need to know is a problem. That can lead you into avoidable trouble.

Recordsets (and by extension bound forms) have a method called Requery. This reruns the query. Until the query is rerun, it will ony show data that was available at the time it was last run. Even the Refresh method doesn't include any newly added records (or even remove newly deleted ones) but only shows updates to pre-existing records.

I think I'm confused by some of your earlier comments as I seem to recall you saying the tabs contained bound fields rather than subforms. Maybe I'm confusing your two threads. I don't understand why you would have subforms within this question though. It makes no sense to me (The effect would only be to make you life more complicated and introduce problems for you to handle).

At this stage I believe you're still suffering from various problems which aren't really associated with your specific question, but are due to the complexity you still have in your design. Until you start to simplify, then build up from your known positions, you will struggle, and we (I guess - I can only speak directly for myself) will be inclined to see the struggle as self-inflicted.
Dec 15 '11 #12

NeoPa
Expert Mod 15k+
P: 31,186
Rachel:
do you have any advice on how I might design it better, given the huge number of fields I have?
I'm not clear exactly on your current design. If you are using subforms, then you probably would do better to lose them (I'm guessing they're causing most of your problems). I don't know all the field/control limitations so it's hard to make many helpful recommendations. Also, without knowing the basic structure of your setup, it's hard to advise where to split the fields to (which tables), but I seem to see the questions included with the answers. If so, then they should not be stored together. Questions should be one-to-many with the answers. Comments may be stored in a separate (one-to-one) table if that proves necessary.

If you haven't already done so, checking Database Normalisation and Table Structures should prove very helpful.
Dec 15 '11 #13

TheSmileyCoder
Expert Mod 100+
P: 2,321
Could you maybe do a few screenshots of your current form in design mode? And one of your relationships. That might prove helpful.
Dec 15 '11 #14

P: 12
The form contains both bound fields and subforms. All the one-to-many tables are included as subforms. I tried to include all the other fields as bound controls, but I dont know if there is a limit on the number of bound fields on a form, as I reached a point where I couldnt add any in. I have some screenshots of the form design and the relationships - how do I post them here? (the image icon asks for a URL?) I have checked the data normalisation and table structures, and it makes sense, but I dont see how I can apply it here other than to use two one-to-one tables rather than six...

As I mentioned in the other thread, I have re-built this form after having to add several fields to the main table, which took it over the limit of 255 fields and meant I had to split the main table, and include some fields as subforms in the main form. Before that I did not have this problem, so I guess that is my last "known position"!
Dec 15 '11 #15

NeoPa
Expert Mod 15k+
P: 31,186
I'm still hearing clues here that you have a humungously complicated setup that you're trying to debug. Short of repeating that you are approaching this the wrong way again, I can do little. I certainly don't want to waste more time dealing with issues that are not central to your problem, because you're refusing to follow the advice I have already given.

If/when you get to the stage where you realise my advice makes sense, then let me know and I can get involved again.
Dec 16 '11 #16

P: 12
Thanks - I have resolved the problem.
Dec 22 '11 #17

Post your reply

Sign in to post your reply or Sign up for a free account.