Form Doesn't Display Existing Data

[Rachel Turner]

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

[NeoPa]

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.

[Rachel Turner]

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.

[TheSmileyCoder]

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?

[Rachel Turner]

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.

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

[NeoPa]

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.

[Rachel Turner]

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.

[NeoPa]

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.

[NeoPa]

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 ...?

[Rachel Turner]

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?

[Rachel Turner]

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...

[NeoPa]

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.

[NeoPa]

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.

[TheSmileyCoder]

Could you maybe do a few screenshots of your current form in design mode? And one of your relationships. That might prove helpful.

[Rachel Turner]

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"!

[NeoPa]

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.

[Rachel Turner]

Thanks - I have resolved the problem.