Form with 2 parts (top fields are static, bottom fields permit new records) (1 Viewer)

gbuckton · Aug 26, 2008

Hi there,

I have a form that consists of 2 parts:

1. Survey Program Info: Top part is relatively static - that is, only needs to be filled out once in a while

2. Species Sightings: Bottom section will require entry of many records, which will relate to the top section (Survey Program)

How do you recommend I set this up?

One idea is to have a button in the Species Sightings section which would insert a new set of fields into the form. I'm not sure if this is possible, and if it is, how the new fields would be best named. For example, if I had a field 'Location' and then inserted a new row of fields (with a new 'Location' field), would I need a script that would put an automatic '_location2' at the end of the field value?

Another idea is to set up Survey Program Info as a separate form, and so the new Species Sightings form will display the Survey Program Info at the top, but it will be locked, and pulling in the data from a separate table.

My apologies if this is confusing!
thanks for any help

CraigDolphin · Aug 27, 2008

It's a classic 1 to many situation. That indicates two tables to handle that relationship, and another to use as a lookup:

tblSurvey
SurveyID (autonumber, pk)
SurveyDate
SurveyLocation
etc etc

and another table that contains a list of species
tblSpecies
SpeciesID (autonumber, pk)
Genus
Species

And a third table to contain species ids associated with a particular survey.

tblSpeciesSightings
SpeciesSightingID (autonumber, pk)
SurveyID (fk)
SpeciesID (fk)

You have a main form, in single form view, which is bound to tblSurveys.

On that main form, you have another form, claled a subform, in continuous form view. This subform is bound to tblSpeciesSightings.

The main form and subform are connected using the SurveyID fields as master/child keys.

On the subform, you have a text box to contain the surveyID (usually left invisible), and a combobox that displays the list of species names, but stores the species id.

You may want to do some reading on the topic of database normalization before you proceed any further. As a biologist myself, I know how ingrained spreadsheet-thinking is for biology types, and how detrimental it is to correct use of a database like Access.

Hope it helps.

gbuckton · Aug 29, 2008

Thanks very much,

I have the tables and forms built, however, when I attempt to change the record in my subform (speciesSightings) of my Survey Form, I get the following error message:

"You cannot add or change a record because a related record is required in table 'tblSpecies'."

I'm not sure if I set the Survey form up properly. I created the relationships using the Relationship wizard so that tblSpeciesSightings has it's SurveyID and SpeciesID fields related to their respective tables.

Instead, should I be editing a specific Parent/Child properties field? Could you tell me where this should be done, or are there any other issues that might be occurring?

thanks for your help!

CraigDolphin · Aug 29, 2008

On the subform control, open the properties and look at the data tab.
You should see the following in the frist three properties

Source Object: speciesSightings
Link Child Fields: SurveyID
Link Master Fields: SurveyID

Make sure that on you main form, you have a textbox bound to the SurveyID field in tblSurvey. And on your subform, make sure you have a textbox bound to SurveyID in tblSpeciesSightings (you can make them invisible if you like, but they must be present on both the form and subform).

Now, when you enter data, you should be entering the information in your main form first, and in your subform second. When you do, any records you add to the subform, should get the correct SurveyID automatically added.

If you try this, and still can;t get it to work, then attach a zipped copy of the db to your next post and I'll have a look at it for you.

gbuckton · Sep 2, 2008

Hi CraigDolphin,

I've double-checked the items you've listed, and everything seems to be there, so I'm not sure what's gone wrong.

I've attached the DB and if you get a chance to take a look it would be greatly appreciated!

The main form name is 'Survey'...

cheers

CraigDolphin · Sep 3, 2008

Ah, found the problem. Go to design view of tblSpeciesSightings. Look at the default values for the SurveyID and SpeciesID fields. You have them being populated with a 0 by default. You have referential integrity enabled, preventing records with a 0 value being allowed because there are no pk values of 0 in the linked tables.

Change the default values for both fields from 0 to Null, and the problem should go away.

gbuckton · Sep 3, 2008

Yes that worked like a charm - thanks again!!

gbuckton · Sep 10, 2008

Hi again,

I need a slight tweak to my original form.

I'd like my subform to behave so that SpeciesSightingsID (autonumber) will 'refresh' back to the value 1 every time a new record is entered on the main Survey form.

Is there a way to accomplish this?

thanks!
G

CraigDolphin · Sep 10, 2008

No. An autonumber field cannot have duplicates, and you should NOT be using an autonumber field to represent some kind of running total. An autonumber field is intentionally meaningless except to act as a way for the db to uniquely identify a record.

Instead, for what you want to achieve, you should use an unbound control on the subform, and generate/calculate the running sum using domain agregate functions, like DCount, in the control source to determine if the current record is x of y.

There are some good examples of this in the sample database section. For instance, here's one by GHudson
http://www.access-programmers.co.uk/forums/showthread.php?t=97789&highlight=record

That should show you the correct way to proceed.

gbuckton · Sep 12, 2008

thanks I'll give this a shot!

gbuckton · Sep 12, 2008

arghhh!!!

not sure what happened - but now my form is not writing to my table. When I close and re-open the form, it is starting at Record 1 with all fields blank, even though Data was entered previously, and is still in the table.

any ideas?
thx

CraigDolphin · Sep 12, 2008

You probably set the form's DataEntry property to true somehow...? If not, check to make sure your form's recordsource is still set appropriately.

gbuckton · Sep 12, 2008

Yes that fixed the issue for the main form, but the subform (I also set the DataEntry to 'no') is still not displaying any values even though they are in the table...

any suggestions?

CraigDolphin · Sep 12, 2008

Is there any code somewhere that is opening up the form in dataentry mode?
Is the subform's record source set correctly?
Are the master-child link field settings still correctly in the properties of the subform control?

Otherwise, I'd prolly have to look again at it to find the glitch.

gbuckton · Sep 15, 2008

Hi CraigDolphin,

I have checked your three possible causes, but they all seem to be fine in my DB.

One thing I've noticed is when I open the form initially, all fields are blank, however, when I go into Design mode, and then back to Form mode, the fields are populating properly from the table.

I will send the DB to you by PM as it has some confidential info.

thanks again,
G

gbuckton · Sep 15, 2008

Back again as I've noticed one other thing.

When I open the Form directly from the Database 'Forms' Window, the form opens properly with all data populating the fields.

But when I open the form through the Swithboard (set to open form in Add mode) it is not displaying the data in the fields as it should.

any ideas?
thanks!

missinglinq · Sep 15, 2008

Opening a form from a Switchboard in Add mode is the same as having Data Entry set to Yes, you can only add new records. You need to go back and switch it to Edit mode. You can still add records, but you will also be able to see existing records.

gbuckton · Sep 15, 2008

thanks Missinglinq,

that fixed my issue.

thanks everyone for your help!
G

missinglinq · Sep 15, 2008

That's why we're here!

Form with 2 parts (top fields are static, bottom fields permit new records) (1 Viewer)

Registered User.

GrumpyOldMan in Training

Registered User.

GrumpyOldMan in Training

Registered User.

GrumpyOldMan in Training

Registered User.

Registered User.

GrumpyOldMan in Training

Registered User.

Registered User.

GrumpyOldMan in Training

Registered User.

GrumpyOldMan in Training

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread