Is the the table relationship the problem?

[kate10123]

Hi There

I have a one to many relationship between two tables:

tbl_Tutor and tbl_Tutorial - one tutor can give many tutorials

but this logic doesn't seem to work when it comes to data entry forms

I have created a form using the wizard in Access to allow a tutor to enter information about each tutorial.

This is made up of fields from tbl_tutor and tbl_tutorial (the fields from tbl_tutorial are shown as a subform on the main form)

When I go to enter a new record via this form it brings up a 'null' error.

Here are my table details if any of you can see anything wrong with the fields:

tbl_tutor

TutorID - NUMBER
TutorName - text
Telephone - text
Email - text

tbl_tutorial

TutorialNo - AUTONUMBER
TutorID - NUMBER - linked to tbl_tutor by this field
StudentID - NUMBER - linked to tbl_student
Date - date/time
Notes - text
TutorName - text

TutorID seems to be the problem field from my limited knowledge of databases but perhaps there is a better way of trying to achieve the outcome I need.

Any help appreciated!

 

[stopher]

Welcome to the forum.

Can't see anything wrong with what you've done. Perhaps you can post your database.

Sometimes, the form/subform connection loses the plot. You can view the link by looking at the subform properties but I find it's better to do it again from scratch.

Note that I'm pretty sure you must have the TutorialNo (autonumber) field on your subform (although I think you can make it invisible)

hth
Chris

 

[kate10123]

Thanks for replying

I just tried to create a new form using the wizard and it created the subform as well but I guess I think I know where the problem is now.

The first 3 fields on the form are tutorID, TutorName, Email

TutorName is a dropdown so the user can select the tutor's name, ideally this should put the relevant tutorID number in the tutorID textbox but it doesn't...

so when I go to put in details about the tutorial, the key that links to the two tables together, tutorID does not contain a value.

Any ideas how I can set this to be automatically filled in when the user selects the corresponding tutorname?

So for example in tbl_tutor I have the following:

TutorID TutorName
37 Katie
38 Kevin
39 Zoe

 

[stopher]

The first 3 fields on the form are tutorID, TutorName, Email

TutorName is a dropdown so the user can select the tutor's name, ideally this should put the relevant tutorID number in the tutorID textbox but it doesn't...

so when I go to put in details about the tutorial, the key that links to the two tables together, tutorID does not contain a value.

I'm getting slightly worried here. Are you saying your main form is bound to your Tutor table? In which case you can't just use a dropdrop to navigate to the right tutor. You must use the search function or navigate using the buttons at the bottom of the form (or you can write some code to create a customer search tool). Simply changing the Tutor name/ID will mean you will be changing the value for that record which I guess you don't want to do.

Anyway, that doesn't exactly answer your problem. First of all, ensure you have not set up the field as a lookup at table level. This will give you a real headache.

Then, your dropdown should be a combobox with the source being the Tutor table. You will have two columns as the source TutorID, TutorName. Then in the combobox properties you can set the bound column as colum 1 and set the column widths to 0cm;2cm (for example). This will enures the ID is used as the saved value but actually display the name.

But as I say, I'm curious to why you are selecting Tutor in any case.

hth
Chris

 

[kate10123]

Thanks, I changed the table design for this field to a textbox and followed your advice and it worked great.

 

[Pat Hartman]

TutorName should be removed from tbl_Tutorial. It duplicates the value in tbl_tutor and may be causing part of your confusion.

The fields from tbl_tutor should be on the main form and the fields from tbl_tutorial should be on the subform EXCEPT that you do not need and should not have TutorID on the subform. By setting the master/child links correctly, Access will automatically populate TutorID in the subform record so that the two are properly linked.

On the main form, add a new combo using the wizard. It will give you three options. You want the one that says look up record in current form. You will use this combo for searching. On the main form, TutorID should be unlocked in the Current event if this is a new record and it should be locked if it is not. TutorID should NOT be changeable for an existing record.