form - record source - select a table

[johnseito]

Form - record source - select a table that when the user enter a value in the field of the form
it gets place into the designated table (select a table).



my problem is that I have two fields in the form, one field from the data entry is suppose to go to the train table,
and another field the data entry is suppose to go to the station table.

How can this be done?

if I have the form's record source have a designated table for the data entry.
It doesn't have two tables for the data entry. Thanks!!! :-)

[JoeM]

How about joining the two tables together in a query, and then using the query as the source of your Form?
Then you should be able to update both tables at once.

[June7]

That might work depending on table relationships but only if records already exist in both tables. Won't allow creating records in the tables.

Basically, a form should be entering/editing data for only one table. Perhaps you really should be using a form/subform arrangement for this.

Describe table relationships.

[JoeM]

That might work depending on table relationships but only if records already exist in both tables. Won't allow creating records in the tables.

Yes, I was a bit hasty, and should have said that it will only work under certain conditions.

However, there are certain conditions in which you can add new records to both tables at the same time. For example, if the two tables are joined on an Indexed fields that does not allow duplicates, it will work.

[johnseito]

How about joining the two tables together in a query, and then using the query as the source of your Form?
Then you should be able to update both tables at once.

This would update the query, not the original table right? That is what I think will happen.

[June7]

Query is based on table(s). If the query is editable then the edits will pass to table.

[johnseito]

Describe table relationships.

booking, bookingLeg, and linestation.

bookingID is FK to passenger by cid PM
bookingLEG is bookingID is FK to booking PM bookingID, and it's startID and endID is FK to the PM stationID station table.
linestation lineID is FK to linein line table PM and linestation stationID is FK to stationID line table PM.
trainid from train table is join with bookingleg tid

my bookingleg trainID combo box is suppose to update the trainID in the train table, and the start station and end station from the bookingleg form in the combo box is suppose to update the
bookingleg table's startID and endID.

so with this relation you think the join table, and query could be updated to the train table and the bookingleg table?


Here is a picture!! Thx

[June7]

Think I see circular reference relationships. Could be an issue. Review http://www.codeproject.com/Articles/...atabase-Design

and https://www.accessforums.net/forms/a...ect-39272.html

[JoeM]

This would update the query, not the original table right? That is what I think will happen.

To follow up on what June said, it is important to understand what queries are, they are not separate entities from the tables. They are just different "views" into your tables.
So, when you update data in a query, you are really updated the underlying data in a table.
By definition, it would be physically impossible to update something in a query, and not have it update the underlying table.

[johnseito]

Think I see circular reference relationships. Could be an issue.

Ok thanks for pointing that out. Didn't know much about circular reference, and I am not a modeling expert (wish I am) but from what
I have gather, I think circular reference means when you have an entity that is reference to two other entities and one of the referenced
entity is also reference to the other entity or reference to another entity that is reference to the other entity.

example: entity A, entity B, entity C, entity D

entity D is reference to entity A and C
entity C is reference B who is reference to A and this is a circular reference.

Thanks for pointing that out, I made a change and got rid of the line table so now I have this.

[johnseito]

To follow up on what June said, it is important to understand what queries are, they are not separate entities from the tables. They are just different "views" into your tables.
So, when you update data in a query, you are really updated the underlying data in a table.
By definition, it would be physically impossible to update something in a query, and not have it update the underlying table.

Ok thanks for letting me know of this.
So I created a query, I called it trainANDbookingleg

and this is the query:

SELECT tid, startid, endid
FROM trains INNER JOIN bookingleg ON trains.trainid = bookingleg.tid;

I am going to use this as the row source, but I am still not sure how I can update two table (the train and bookingLeg) by this?

[June7]

Still suggest you limit a form to one table for data entry/edit. Use form/subform arrangements.

[johnseito]

Still suggest you limit a form to one table for data entry/edit. Use form/subform arrangements.

Ok I will try that, never done a form and subform arrangement yet. too many things to try and test out.
Thanks!!!

[June7]

Review http://office.microsoft.com/en-us/ac...010098674.aspx