Updating Multiple tables

[Ceriumde58]

Is it possible to have a Field on a form bound to two different tables? I have a form that end users will be filling out on a daily basis. After a user inputs a date for the daily form I would like it to update the DailyFormTable as well as update a LastDailySubmitted field on another table.

I would like to do this so that I can generate daily reports based on specific date ranges, but i would also like to automatically populate fields in the Daily Form based on the LastDailySubmitted date. This is just to hasten the data entry portion for the end user's.

 

[Alansidman]

You can only have a form bound to one table. If there can be a relationship between the two tables, you can create a second form and embed it in the first form. This is referred to as a sub form. That second form is bound to the second table. Look at this link and scroll down to subforms.

http://www.datapigtechnologies.com/AccessMain.htm

 

[Galaxiom]

After a user inputs a date for the daily form I would like it to update the DailyFormTable as well as update a LastDailySubmitted field on another table.

This requirement indicate a normalization anomaly. You need to rethink your data structure.

The LastDailySubmitted value should be calculated from the entries in the DailyFormTable as required.

 

[RainLover]

By using a Query you can Bind a Form to a Dozen Tables if you wish.

You just need to have a properly designed set of tables with the Relationships set.

 

[Galaxiom]

By using a Query you can Bind a Form to a Dozen Tables if you wish.

You just need to have a properly designed set of tables with the Relationships set.

Yes but that is bit simplistic, Rain. There are indeed many legitimate queries that can built on what would be considered "properly designed sets of tables" yet they are not suited as the RecordSource of a form because they are not updateable.

In most real world cases a subform structure is the appropriate measure. That was Alan's main point even though technically he was incorrect about forms only being able to be bound to one table.

Indeed I think it would be quite difficult to write a query against a set of twelve "properly designed tables" and still have it updateable.

 

[Alansidman]

Galaxiom;
Interesting statement about being able to bind a form to more than one table. I have never seen this but would welcome a demo as I have tried and failed in my early days of using Access and never tried again later.

Do you have an example you could share or a link to a demo? You have piqued my interest on this.

Thanks,
Alan

 

[RainLover]

Yes but that is bit simplistic, Rain. There are indeed many legitimate queries that can built on what would be considered "properly designed sets of tables" yet they are not suited as the RecordSource of a form because they are not updateable.

In most real world cases a subform structure is the appropriate measure. That was Alan's main point even though technically he was incorrect about forms only being able to be bound to one table.

Indeed I think it would be quite difficult to write a query against a set of twelve "properly designed tables" and still have it updateable.

I don't understand what you are trying to convey here.

You have agreed with me on every point.

I am sure with you abilities you could create such a query. Why you would wish to do so is another point however it is possible.

The point is that you can use more than One Table and I do so quite often.

 

[RainLover]

Alan

Have a look at this Query and follow it through.

qryfrmTrainingEmployeesSub

 

[Galaxiom]

Galaxiom;
Interesting statement about being able to bind a form to more than one table. I have never seen this but would welcome a demo as I have tried and failed in my early days of using Access and never tried again later.

As Rain pointed out the RecordSource can be a query with as many tables as you want included. I assume you have done this before but the query always ended up non-updateable.

I don't understand what you are trying to convey here.

Your implication is that multiple tables can be used in a RecordScouce query and it is just a matter of "proper table design" for it to work. My point is that many "proper table designs" won't result in an updateable query and as such are not suited to the RecordSource job.

I just don't want new developers who are looking at our advice assuming they have a faulty data structure because their query doesn't work as a RecordSource.

BTW The original question asked if it was possible to bind a single control to fields in more than one table. AFAIK there is no way to bind a control to more that one field in the same table let alone fields in multiple tables.

Anyway the most important point is that the OP has a normalization error.

 

[RainLover]

Is it possible to have a Field on a form bound to two different tables?

I did not read the question properly. Sorry about that. I shall stand in the naughty corner for a while.

 

[Alansidman]

Galaxiom and Rain;

I misunderstood your comment about binding a form to two tables. I had assumed you meant to do it independent of a query. My head must have been somewhere else. For being technically incorrect I will stand with Rain in the corner, but will wear a dunce cap.

 

[Ceriumde58]

Oh geez this has gotten alot of attention. Evidently I have much to learn, time to get cracking. Thank you all for your responses, I will sift through them and try to figure out a solution to my dilemma.

 

[RainLover]

Oh geez this has gotten alot of attention. Evidently I have much to learn, time to get cracking. Thank you all for your responses, I will sift through them and try to figure out a solution to my dilemma.

You really haven't been given the full answer. I would suggest that you post a pic of you Relationship diagram so we can see how correct or not your table designs are.

 

[gemma-the-husky]

i have to say that my feeling is that you should regard a system as only dealing with records in a single table at a time.

any query is in truth based on a single BASE table, with links to other tables - so the record you are editing is the noe in the BASE table.

Now, it is true that you can change a value of a record in a table linked to the BASE table - which will cause a knock on effect to all other records linked to that record.

so - eg, you have a customer file, linked to a US state table. you can certainly set it up, so while editing all the customer's details, you could also change the name of the linked state - but this is not quite the same as editing the state table at the same time - and is probably also not a very desirable feature anyway.

trying to design a system to permit changes to multiple non-related records will quickly lead you to a un-normalised database with loads of attendant problems.

 

[Ceriumde58]

I have included a screenshot of my relationship chart.

I figure I should take a moment to describe what my employer would like to accomplish as having a column dedicated to the last daily report date on my contracts may not be the best way to accomplish what I'd like to do.

Currently the field employees submitting daily reports to my employer by using a template excel document. In order to save time and reduce redundancy they typically will open the last excel report they have complete, change the date to the current date, and then just update a few of the fields that vary from day to day.

In my Access version of the Daily Form it is set up nicely to allow users to submit data on a daily basis. Unfortunately as it stands now, they must start from square 1 everyday. For each new day all of the fields default to blank, my employer would like to have each field default to whatever the last day's data was, so that his field employees only have to change a few select fields.

I have no idea how to add this functionality, so I had assumed that saving a field on the Contracts Table to save the most current date of a daily report would be a good jumping off point. I've made practically no progress on this issue this week.

Any assistance is appreciated.

 

[RainLover]

I have included a screenshot of my relationship chart.

I figure I should take a moment to describe what my employer would like to accomplish as having a column dedicated to the last daily report date on my contracts may not be the best way to accomplish what I'd like to do.

Currently the field employees submitting daily reports to my employer by using a template excel document. In order to save time and reduce redundancy they typically will open the last excel report they have complete, change the date to the current date, and then just update a few of the fields that vary from day to day.

In my Access version of the Daily Form it is set up nicely to allow users to submit data on a daily basis. Unfortunately as it stands now, they must start from square 1 everyday. For each new day all of the fields default to blank, my employer would like to have each field default to whatever the last day's data was, so that his field employees only have to change a few select fields.

I have no idea how to add this functionality, so I had assumed that saving a field on the Contracts Table to save the most current date of a daily report would be a good jumping off point. I've made practically no progress on this issue this week.

Any assistance is appreciated.

It would be best for you to look at Normalisation. There are heaps of articles on the subject if you go to Google. You need to study this thoroughly. It will take time but it is worth it.