Tabs with related tables

[muffinimal]

Hi,

I have a database with a couple of tables. All of which have a 1:1 relationship to another table, patient:
- patient (id (auto_increment), name, address, etc)
- nutrition (patient_id, some, stuff, etc)
- sleep (patient_id, foo, bar)

The patient_id in nutrition and sleep is a primary key and corresponds with the id of a patient (my 1:1 relationships)

I have a couple of forms. One for each table actually. In this form the user should be able to add/modify a patient's data. These forms are not to be used by themselves.

I have another form(the main form), which has a tab-control element on it. There are 3 tabs on this and on these tabs the 3 different forms are displayed.

Now for the problem:
- when I add a new record to the database using the main form, there is no problem when adding a new patient. Woohoo! When I switch to another tab, , however, the problems start: I can enter new information, but when I leave the tab I get an error: "Index or Primary key cannot contain a null value." Blech.

I want Access to realise that it should use the id that is given to the patient in the first tab, but it seems it cannot get it automatically. (I am not sure, but I think the problem didn't occur at first, so I think I may have done something which is causing this problem.)

Any help here is appreciated.


PS I was unable to find any posts on this forum that can help me with this. The closest I could find was: http://www.access-programmers.co.uk/forums/showthread.php?t=39933

 

[RuralGuy]

You must explicitly set the key of your other tables each time you create a new record. Only your Patient table with the AutoNumber field will create the key automatically. I would use the BeforeInsert event of your SubForms to locate and set the PrimaryKey field of their respective tables. It would look something like:

[Patient_id] = Me.Parent.[ID]

 

[muffinimal]

Thanks for the great & speedy reply, Ruralguy!

I thought it wasn't necessary to use VB to get this to work, but it works perfectly. Now I have only figured out get it to work like this:

[patient_id] = Forms![mainForm]![patient].Form![id]

Would it be possible to use something like your parent-thingy here? I would like to use the same form in other forms (other main-forms with another layout) as well.

I guess you can use parent for subforms? Is there a similar code for tabs? I guess not? Can I refer to the textbox with the id in it? Hmm... I need to experiment...

 

[RuralGuy]

Hi muffinimal,

I'm not sure I understand your questions. The Tab object does not affect the addressing of controls in any manner. Post any other questions you have and I'm certain someone will be able to answer it.

 

[muffinimal]

Thanks again, Ruralguy. I am just curious to these things and do not really need the answers right now. If the stuff I want to comes up again, I will get back to it.

And as I am writing this and rereading what you and I have read, I now think I understand it anyway ;-)


Thanks again!