Two tables, one form

[Remster]

Folks



The attached database contains two tables: DocumentTable and TaskTable. Their common field is TaskID, which is the primary key in TaskTable. The database also contains a form: MainForm. The form contains all the fields from both DocumentTable and TaskTable, so it contains two occurrences of TaskID, which Access has automatically named 'DocumentTable_TaskID' and 'TaskTable_TaskID'.

Now, here's my problem. I want my form to contain only one TaskID field, or at least only one visible TaskID field. And I want it to be such that a) if a value entered already exists in TaskTable, Access doesn't try to create a new record in that table (which wouldn't be allowed, since TaskID is the primary key) but places the value in the TaskID field in DocumentTable; and b) if a value entered doesn't already exist in TaskTable, Access both creates a new record in TaskTable and places the value in the TaskID field in DocumentTable.

How can I achieve this? The only idea I've had is to make TaskTable_TaskID invisible and have the AfterUpdate event for DocumentTable_TaskID say 'If the value entered doesn't already exist under TaskTable.TaskID, add a new record to TaskTable'. But I haven't the faintest idea what the coding would be for this (the few things I've tried have generated error messages). Please help!

Thanks

Remster

PS There can be more than one document per task but only one task per document, hence the one-many relationship between the two tables. The reason why I haven't created a TaskForm with a DocumentSubform is that the document information is what the database is all about, with the task information playing a secondary role. I've found that navigation between records and between fields within records goes pear-shaped if I have a form and a subform, which is why I have one form for two tables.

[RuralGuy]

Start by saving the RecordSource of your form as a Static Query and then modify that query to suit your needs. Then set it as the RecordSource of your form. It puts you more in control of what you want. You do not need both TaskID fields in the query. One of the things you will discover is that the Form/SubForm will maintain the ForeignKey for you if you let it. Unless there is a strong reason for creating your own TaskID, I would strongly recommend using an AutoNumber for that fields as well.

[Remster]

Hi RuralGuy

Thanks for your reply.

Each task needs to have a unique task number with a particular format: '10-01' to '10-n' for tasks in 2010, '11-01' to '11-n' for tasks in 2011, and so on. I'd be happy to have autonumbering running alongside this, but I wasn't sure whether it would be necessary or even advisable.

I think I understand what you're proposing: a) I create a query that contains fields from both DocumentTable and TaskTable; b) I base my form on the query rather than directly on the tables. Is that right? If so, which TaskID field do I use in the query: the one from DocumentTable (the 'many' side) or the one from TaskTable (the 'one' side)? (I don't have Access at the same location as I have the Web at the moment, which is why I can't just try it out!)

Remster

[RuralGuy]

I am then going to suggest you use a Text Field for the PK field of the Task table rather than a LongInteger. Are you prepared to do all of this work yourself with no assistance from Access? It could involve some VBA to quite an extent.

[Remster]

Any assistance I can get from Access will be very welcome! Can I get Access to do it for me? I feel as if I'm missing what you're driving at.

[RuralGuy]

As I said in post #2, If you can use a Form/SubForm with the 1 side on the Form and the Many side on the SubForm then Access will maintain the ForeignKey field for you in the SubForm by using the LinkMaster/ChildFields properties of the SubFormControl.

[Remster]

I'd love to do that, but I gave a reason in the postscript of my initial post for not doing so:

The reason why I haven't created a TaskForm with a DocumentSubform is that the document information is what the database is all about, with the task information playing a secondary role. I've found that navigation between records and between fields within records goes pear-shaped if I have a form and a subform, which is why I have one form for two tables.

Perhaps I can explain what I mean with reference to the attached database (though of course I'm speaking from memory here). Suppose I create TaskForm and DocumentSubform. DocumentSubform will appear at the top of TaskForm, and the tab order will be set as follows:

DocumentSubform (DocumentID, DocumentName), TaskID, Notes

Now, suppose I'm tabbing through the record for Document 2 (which I believe is the second of two documents for task 10-01). I want the next stop after DocumentName to be TaskID, but instead I'm taken to a blank subform record. And even if I were taken to TaskID and entered '10-01', wouldn't Access think I was trying to enter a duplicate value?

[RuralGuy]

I have to go help a neighbor get their truck to the repair shop now. I'll check back in when I get back. Sorry.

[Remster]

Tut, you rural guys.

[RuralGuy]

DocumentSubform will appear at the top of TaskForm, and the tab order will be set as follows:

DocumentSubform (DocumentID, DocumentName), TaskID, Notes

Now, suppose I'm tabbing through the record for Document 2 (which I believe is the second of two documents for task 10-01). I want the next stop after DocumentName to be TaskID, but instead I'm taken to a blank subform record. And even if I were taken to TaskID and entered '10-01', wouldn't Access think I was trying to enter a duplicate value?

I'm not sure why you want to put the Document SubForm at the top of the TaskForm but you certainly can do that.

[Remster]

I'm not sure why you want to put the Document SubForm at the top of the TaskForm ... .

Because the document information is the main subject-matter of the database; the task information is secondary. But then I have the navigation problem that I explained in my last post.

However! The moment I woke up this morning (sad) an adequate 'workaround' (eugh) dawned on me. Unless someone comes up with a better suggestion, I'm going to do this:

1. As you suggested, make TaskTable.TaskID an autonumber field and add 'TaskNumber' as an additional field within TaskTable.

2. Prefill the values 10-01 to 10-99, 11-01 to 11-99 and 12-01 to 12-99 under TaskTable.TaskNumber. (I forgot to mention that there won't be more than 99 tasks in any given year.)

3. Delete TaskTable_TaskID from MainForm.

4. Change DocumentTable_TaskID to a combo box with 'SELECT TaskID, TaskNumber FROM TaskTable' as its row source.

5. Add the field 'InUse' to TaskTable and a hidden checkbox to MainForm that sends the value 'TRUE' to TaskTable.InUse whenever DocumentTable_TaskID isn't null.

See the attachment for a demo. Any feedback would be appreciated.

[RuralGuy]

Any solution that achieves your goal would be fine. I am usually against pre-filling records that may never be used as a waste of time and space. Perhaps the issues come up because you are trying to achieve too much with one form. Viewing the documents and then selecting one for an additional task could simply open another form for the addition. Just a thought.

[Remster]

Yep, that's a useful thought. I have to weigh considerations of wasted space against ease of inputting. Many thanks for your help.

[RuralGuy]

In the end it is your db and you can and should do anything you want. Have fun.

[Remster]

Thank you.

Having thought about it some more, I suppose one thing that would be helpful is if I could have a button on my form that

a) added a new record to TaskTable

b) added the highest task number + 1 to the TaskNumber field

Does that sound feasible? (Even better would be if the series changed automatically from 10## to 11## on new year's day!)