Table Links
I have a table A that has a one to many relationship with Table B. Most of the time there is no data in B. If I create an update query linking A with B with say 100 records all of the check boxes are greyed out and are not updateable because there is no corresponding data in table B to link the two tables.
What I am trying to do is update all 100 records in the update query. So how do you force the linking field to always have the link field in table B when no data has been entered in it.
Basically I want the link field always created in table B when a record is created in table A.
I access Table B via a pop up form. In this form are check boxes that automatically add data such as today's date.
I do input data into table A via a form and I could force the pop up form for B to be visible/invisible and then just set the link field from the link field in form A. It just seems a crude way to do it.
What I am trying to do is update all 100 records in the update query. So how do you force the linking field to always have the link field in table B when no data has been entered in it.
Basically I want the link field always created in table B when a record is created in table A.
I access Table B via a pop up form. In this form are check boxes that automatically add data such as today's date.
I do input data into table A via a form and I could force the pop up form for B to be visible/invisible and then just set the link field from the link field in form A. It just seems a crude way to do it.
see this link for more detailed explanation
https://support.office.com/en-us/article/Create-a-form-that-contains-a-subform-a-one-to-many-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b?ui=en-US&rs=en-US&ad=US
https://support.office.com/en-us/article/Create-a-form-that-contains-a-subform-a-one-to-many-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b?ui=en-US&rs=en-US&ad=US
Creating dummy records in table A isn't the answer.
In the query that joins the two tables, omit the FK from tblB.
Select tblA.PK, tblA.fld1, tblB.fld2
From tblA Left Join tblB ON tblA.PK = tblB.FK;
In this query, if you type something in tblB.fld2, Access will AUTOMATICALLY populate tblB.FK with the foreign key and insert a row in tblB. This of course, assumes that you have actually defined a relationship using the relationship window that tells Access how tblA and tblB are related. Access will also allow you to Add a row to tblA and to tblB in the same query.
In the query that joins the two tables, omit the FK from tblB.
Select tblA.PK, tblA.fld1, tblB.fld2
From tblA Left Join tblB ON tblA.PK = tblB.FK;
In this query, if you type something in tblB.fld2, Access will AUTOMATICALLY populate tblB.FK with the foreign key and insert a row in tblB. This of course, assumes that you have actually defined a relationship using the relationship window that tells Access how tblA and tblB are related. Access will also allow you to Add a row to tblA and to tblB in the same query.
Upload a sample database with the 2 tables. Include 5 test records.
Explain what result is required.
Form/Subform is a natural way to input data in related tables.
The left join allows showing all records of A .
Explain what result is required.
Form/Subform is a natural way to input data in related tables.
The left join allows showing all records of A .
Yes, a form/subform is the natural way to do this interactively. I believe the OP is looking for a query that will work.
In my first post I omitted the FKToTable1 field but that actually prevents you from adding to both tables in the same query. If you include it, you can add new records to both tables in the same query.
In my first post I omitted the FKToTable1 field but that actually prevents you from adding to both tables in the same query. If you include it, you can add new records to both tables in the same query.
Assume tables:
a (aid, ....)
b (aid, bid, ....) - bid default value 0
UPDATE a LEFT JOIN b ON a.aID = b.aID SET b b.aID = [a].[aid];
This adds new records to table b and populates the linked field.
a (aid, ....)
b (aid, bid, ....) - bid default value 0
UPDATE a LEFT JOIN b ON a.aID = b.aID SET b b.aID = [a].[aid];
This adds new records to table b and populates the linked field.
ASKER
Hi Ray
That would work if I am adding data to the subform and the point here is that I am not. So the subform does not create data in the linked field without data in table B.
Pat
I am trying to add the FK to table B not A without entering data in B. So is this correct?:
Select tblA.PK, tblA.fld1, tblB.fld2
From tblA Left Join tblB ON tblA.PK = tblB.FK;
Can't get that to work.
Thanks Derek
That would work if I am adding data to the subform and the point here is that I am not. So the subform does not create data in the linked field without data in table B.
Pat
I am trying to add the FK to table B not A without entering data in B. So is this correct?:
Select tblA.PK, tblA.fld1, tblB.fld2
From tblA Left Join tblB ON tblA.PK = tblB.FK;
Can't get that to work.
Thanks Derek
Try:
UPDATE tblA LEFT JOIN tblB ON tblA.PK= tblB.PK SET tblB.PK = tblA .PK;
You need to set a default value for tblB.FK in table tblB, say 0, and that will be inserted for each created record with tblB.PK.
UPDATE tblA LEFT JOIN tblB ON tblA.PK= tblB.PK SET tblB.PK = tblA .PK;
You need to set a default value for tblB.FK in table tblB, say 0, and that will be inserted for each created record with tblB.PK.
ASKER
That looks really neat hnasr!
When do I run this query or can it be used with a select statement that provides the data for the form I am using to update the joined table query data?
Would it create an updateable record set?
I am trying it but not sure where it should go
When do I run this query or can it be used with a select statement that provides the data for the form I am using to update the joined table query data?
Would it create an updateable record set?
I am trying it but not sure where it should go
Create a new query. Cancel table selection. Go to SQL view.
Paste the update query. Select Update type.
Use proper names of tables and fields.
Press the run icon.
Paste the update query. Select Update type.
Use proper names of tables and fields.
Press the run icon.
ASKER
That's great and works perfect. Unfortunately it does not answer any of my questions. Basically all we have done is create an update query. So again I can only assume that this query will not have any effect on table B when I add a new record to Table A. Which is what I am trying to do.
I can call that query every time I open my global change form. It just seems an unnatural way to do it.
I can call that query every time I open my global change form. It just seems an unnatural way to do it.
Forcing dummy records to be created in tableB when you add a row to tableA is wrong. I don't know how else to say it. I showed you how to create an updateable query (which is not the same as an update query which is what hnasr posted) which allows you to add rows to tableA AND tableB at the same time. You simply need to fill out data in columns for tableA AND tableB. If you don't know the data that belongs in tableB, DO NOT create dummy records. Wait until you have the data you need.
Typically when working with a 1-many relationship which may be sparse (no rows in the many-side table) you would use a left join - that is what the query I posted is.
Typically when working with a 1-many relationship which may be sparse (no rows in the many-side table) you would use a left join - that is what the query I posted is.
"That's great and works perfect. Unfortunately it does not answer any of my questions."
That's fine and indicates a misunderstanding of the issue.
If non of the other experts work for you, then try to upload a sample database with the 2 tables. Include 5 test records, and explain what result is required.
That's fine and indicates a misunderstanding of the issue.
If non of the other experts work for you, then try to upload a sample database with the 2 tables. Include 5 test records, and explain what result is required.
ASKER
Here it is.
Open Form "Main" as you move through the main form records with the record counter notice that although the subform shows ID number in each record there are no actual records in the subform.
Open the "Update form" and see that again there are no records in the part of the records that come from the Order table. Now click the button "Update Date in all records and change update tick box" and see that nothing happens. The query2 will show records going from query design view and choosing the show data icon, but it will not update the fields because there are no records in the Order table..
So all I need is some way to create a record in the "Order" table every time that the main form moves to a new record.
Hope that helps
db1.mdb
Open Form "Main" as you move through the main form records with the record counter notice that although the subform shows ID number in each record there are no actual records in the subform.
Open the "Update form" and see that again there are no records in the part of the records that come from the Order table. Now click the button "Update Date in all records and change update tick box" and see that nothing happens. The query2 will show records going from query design view and choosing the show data icon, but it will not update the fields because there are no records in the Order table..
So all I need is some way to create a record in the "Order" table every time that the main form moves to a new record.
Hope that helps
db1.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Ray
For some reason I cannot open or copy that file. Don't we normally just double click the file name?
Derek
For some reason I cannot open or copy that file. Don't we normally just double click the file name?
Derek
you just have to click on the file
you just have to click on the file , and save it
ASKER
Hi Rey
Sorry about this. I do not get an option to save, it will only save link or Open. What is actually happening is that when I open the file it saves it automatically without notification or confirmation and then says I cannot open this file. I have it saved 11 times. Crazy.
So I got it to open. If you create a new record in customer form you get error. I fixed the error but then when you add a record it does not update the child table until you move to another record. This may seem petty but if a user is entering a new customer and moves immediately to the update form the record cannot be updated. So I put the code in the form's after update procedure:
Private Sub Form_AfterUpdate()
If IsNull(ID) = False Then
If IsNull([Order].Form![Order
CurrentDb.Execute "insert into [Order] (CustID) values(" & Me.ID & ")"
Me.Order.Form.Requery
End If
End If
End Sub
Sorry about this. I do not get an option to save, it will only save link or Open. What is actually happening is that when I open the file it saves it automatically without notification or confirmation and then says I cannot open this file. I have it saved 11 times. Crazy.
So I got it to open. If you create a new record in customer form you get error. I fixed the error but then when you add a record it does not update the child table until you move to another record. This may seem petty but if a user is entering a new customer and moves immediately to the update form the record cannot be updated. So I put the code in the form's after update procedure:
Private Sub Form_AfterUpdate()
If IsNull(ID) = False Then
If IsNull([Order].Form![Order
CurrentDb.Execute "insert into [Order] (CustID) values(" & Me.ID & ")"
Me.Order.Form.Requery
End If
End If
End Sub
ASKER
Thanks to you all
Derek
Derek
Create a Form based on TableA ( can be a single form ) "FormA"
Then create a Form based on TableB( datasheet or continuous form) "FormB"
Saved both form and close.
in the design view of FormA, select FormB and drag and drop it on the detail section of FormA
then in the property sheet of FormB set
Link Master Fields ID field name in TableA
Link Child Fields ID field name in tableB