Solved Form shows data from one table but when edit new data are saved into second table (1 Viewer)

[Tea]

Hello,

so I'm trying to find a way to show data records from one table in my form List, but when the user changes something (e.g. adds or deletes a record) in that form, the changes (records) would be made in a different table and the first table would be without changes.

Data in first table are used as a template. Changes made in this form (List) should not make any changes in the first table. The second table has all the template records also the changes in them - this table is used for later purpose. How to set up data sources?

Any ideas? Is it even possible?

Thank you!

 

[theDBguy]

You could assign the valves from the template table as default values on your form.

 

[Tea]

You could assign the valves from the template table as default values on your form.

The form shows different data (template records) depending on previous selection (e.g. you can pick from more then one template)

 

[theDBguy]

The form shows different data (template records) depending on previous selection (e.g. you can pick from more then one template)

And you can assign DefaultValue using code.

 

[MajP]

can you show a screen shot and explain from that how you would like it to work? I do not really understand what is desired.

 

[Tea]

can you show a screen shot and explain from that how you would like it to work? I do not really understand what is desired.

I've created a simple test version - in the main form i choose from combo box Name and after choosing some fields are automatically added from template tables (Items_template and List_Template).

When I pick the first template : template1 - there are two items in the subform below (8,9) and i would like to e.g. add an item - but if i just went to a new row, the table Items_template would be updated and not Items_toPrint (thats the table i want the changes to show).

Because everytime a user uses this form he just needs to print something, but next time he would like to see the exact same template (like it was in the beginning with item 8,9) even though he changed something the last time (e.g. added item 10). The changes are saved into the table to print, because he uses it only once. Next time he opens the template 1, there will be again only item 8,9.

 

[MajP]

I still do not understand the purpose of this, maybe someone else gets it or more likely you need a better table schema. You can add a second subform to form 1 based on items_Toprint linked to Form1ID. The user can than add additional records to the second subform. If the intent is to print both records 8,9 and new records then you can have a thirdsubform. This is based on the union query of tables Items_template and items_toprint linked by form1ID. For real estate you can have each subform on tabs (template records, new records to print, combined records). Also the template subform should be set to No additions, deletions, or edits.

I am only guessing here, and does not make much sense to me why you would do something like that but should work.

 

[Tea]

I still do not understand the purpose of this, maybe someone else gets it or more likely you need a better table schema. You can add a second subform to form 1 based on items_Toprint linked to Form1ID. The user can than add additional records to the second subform. If the intent is to print both records 8,9 and new records then you can have a thirdsubform. This is based on the union query of tables Items_template and items_toprint linked by form1ID. For real estate you can have each subform on tabs (template records, new records to print, combined records). Also the template subform should be set to No additions, deletions, or edits.

I am only guessing here, and does not make much sense to me why you would do something like that but should work.

This is just an example. The purpose of this is that the user can select a template they want, where everything is already filled out (including the items). Each month the user prints the same thing with all the items (including more than 100items - that's why the use of a template - so the user doesnt have to create everything anew if for each month the item data are the same), but there are some months where there are new items registered but are not in the template included. The new item will be included in this month printed report but not in the next one because its not a usuall item but an ocassional item. So i don't want to save the ocassional item into my template because it won't be registered anymore.

Each template has their own theme - just another example
Template Pool - Itmes: cushions, waterballoons, etc...
Template Food - Items: shrimp, apple, ...

I hope it make sense. If i would make another subform with the new records, it will still be there next time, if i open the emplate no?

 

[MajP]

I am still guessing, so I might be off mark.

I assume you have a table of all items
TblItems
--ItemID primary key
--ItemNumber
--ItemDescription
.....
if an item can belong to one and only one template then I assume you have
--TemplateID_FK a foreign key to the template table
if an item can be in more than one template then you need a junction table
Some items do not belong to a template so they are blank

TblTemplates
--TemplateID pk
--TemplateName (Food, pool)

TblOrders
--OrderID primaryKey
-- other fields specific to an order

TblOrder_Items
--OrderID_FK foreign key to tblOrders
--ItemID_FK foreignKey to tbl items


The user selects a template from a pull down and an insert query is run to populate tblOrder_items
this table would have the orderID and all the item ids from one or more templates. Now the user can select from the list of unassigned items and add to the list.

 

[Tea]

I am still guessing, so I might be off mark.

I assume you have a table of all items
TblItems
--ItemID primary key
--ItemNumber
--ItemDescription
.....
if an item can belong to one and only one template then I assume you have
--TemplateID_FK a foreign key to the template table
if an item can be in more than one template then you need a junction table
Some items do not belong to a template so they are blank

TblTemplates
--TemplateID pk
--TemplateName (Food, pool)

TblOrders
--OrderID primaryKey
-- other fields specific to an order

TblOrder_Items
--OrderID_FK foreign key to tblOrders
--ItemID_FK foreignKey to tbl items


The user selects a template from a pull down and an insert query is run to populate tblOrder_items
this table would have the orderID and all the item ids from one or more templates. Now the user can select from the list of unassigned items and add to the list.

Yes to all the mentioned tables above, the problem here is that if the user wants to select an unassigned item, the item will be registered in table TblItems not? That's what I'm trying to prevent. I want to have a subform where the items from TblItems are shown and when the user wants to add a row into the subform the added row would go to the tblOrder_Items. The TblItems would be without changes.

About the union query table, I'm interested if it's still has the relationship with the TblTemlates and TblOrders?

 

[Pat Hartman]

but when the user changes something (e.g. adds or deletes a record) in that form, the changes (records) would be made in a different table and the first table would be without changes.

You are thinking about this backwards. Whenever the user wants to use the template, he should create a new record in the table where you want the data to be saved and using a combo, pick the template to use. Your code then copies the relevant columns and the user can change whatever he wants. OR he can add a row by typing everything from scratch if there is no template record.

 

[MajP]

Yes to all the mentioned tables above, the problem here is that if the user wants to select an unassigned item, the item will be registered in table TblItems not?

Yes, but the question is should it be. If you ever want to reuse that item then it can go into the items table and not assigned to a template. In the future it can be selected again. But if this is truly a one time shot and the details for that item only pertain to a given order then you need another table

TblAdditionalItems
ItemID Primary key
' all necessary item fields
OrderID_FK a foreign key relating to an order

Main form is your order
1st subform is based on a query TblOrderItems joined to Items

You select a template and do your insert query
Your order now will show in the first subform all the items inserted into the TblOrderItems for a template. You can delete from these.

The second subform is TblAdditionalItems where you can add 1 time used additional items.

Using a union query you can join the two queries to show both template items and additional items in one subform or report/query.

 

[Tea]

You are thinking about this backwards. Whenever the user wants to use the template, he should create a new record in the table where you want the data to be saved and using a combo, pick the template to use. Your code then copies the relevant columns and the user can change whatever he wants. OR he can add a row by typing everything from scratch if there is no template record.

He can't change but only add new records (the problem is I don't want the hanges be saved in the table template). If you mean to set the table where i want to save it, then I won't be able to show my template records.

Yes, but the question is should it be. If you ever want to reuse that item then it can go into the items table and not assigned to a template. In the future it can be selected again. But if this is truly a one time shot and the details for that item only pertain to a given order then you need another table

TblAdditionalItems
ItemID Primary key
' all necessary item fields
OrderID_FK a foreign key relating to an order

Main form is your order
1st subform is based on a query TblOrderItems joined to Items

You select a template and do your insert query
Your order now will show in the first subform all the items inserted into the TblOrderItems for a template. You can delete from these.

The second subform is TblAdditionalItems where you can add 1 time used additional items.

Using a union query you can join the two queries to show both template items and additional items in one subform or report/query.

TblOrderItems can't be joined to Items - there's no relationship between those two, only when they are joined on the TblOrder.

If you use a union query and have both of them in one query to show in subform - here's the question again? Where or how will be the new record saved? If one subform shows template items and also additional, when you make changes where is it going? In which table?

Also I need a relationship between those tables. Between Order and OrderItems. I need to export an XML that is nested. With a union query it's not possible i guessing?

 

[Pat Hartman]

NO, I'm not saying that the user can change the template. I'm saying that he copies the template record into the "new" table and then changes the copy.

I don't know why you are using a union query at all. Forms based on union queries are NOT updateable. A union query stacks one set of data with other sets. Think of it like tearing pages out of a phone book and laying them out top to bottom of the previous page. In the multiple selects, ALL of the embedded queries have EXACTLY the same column list. The columns don't need to have the same name but they should have the same context. So if you created a union and the first query selected custname, custaddress, orderID and the second select selected custaddress, custname, orderID, the union would be technically correct since name and address are both text but it sure wouldn't make any sense when you looked at the data. A query with a join is totally different and that is what you would do to export data from the Order and OrderItems in the same recordset. An inner join selects only records from tbl1 that have dependent records in tbl2 so if you wanted Orders regardless of whether or not they had related OrderItems, you would use a left join.

When you add OrderItems, the relationship between Order and OrderItems is managed with a FK in OrderItems that points to the PK of Order. I don't know how this is relevant.

 

[Tea]

You are thinking about this backwards. Whenever the user wants to use the template, he should create a new record in the table where you want the data to be saved and using a combo, pick the template to use. Your code then copies the relevant columns and the user can change whatever he wants. OR he can add a row by typing everything from scratch if there is no template record.

Well how would it be possible to copy the template record into the new table just by choosing from combo box? How yould the code look like?

 

[MajP]

Here is a demo. Easier to show then explain

Main form is an Order.
You pick a template from the combo and it does an insert query. I picked both.
The second subform allows you to one time add additional items
subform 3 is the union of the two for that order and is read only for viewing the combined order. It can be used in reporting.

 

[MajP]

FYI. The above works as described, just not sure that is correct. Can you better describe the process for adding additional items. I still would think the user is choosing that item from some table and not adding it for a one time use. Where would the user get the Item name and number from and additional information for that item?

 

[Pat Hartman]

The simplest code would be an append query. Or you could just copy each column from the combo's RowSource query. You would need to include all the columns in the RowSource of the combo and copy the fields that way.

In the AfterUpdate event of the combo

        Me.FK = Me.MyCombo
        Me.ExportID = Me.MyCombo.Column(1)
        Me.BillDT = Me.MyCombo.Column(2)
        Me.BillAmt = Me.MyCombo.Column(3)
        Me.nvNum = Me.MyCombo.Column(4)
        Me.ChangeBy = Forms!frmLogin!txtEmpID
        Me.ChangeDT = Now()

Obviously, you need to use your own actual control names. The .Column() collection of the combo is a zero based array. So Me.MyCombo or Me.MyCombo.Column(0) = the first column, Me.MyColumn.Column(1) = the second column, etc. I added a couple of other fields also so you can log who changed the record and when it happened.

 

[MajP]

Well how would it be possible to copy the template record into the new table just by choosing from combo box? How yould the code look like?

This is how it works in my demo.
Each item in TblItems has a template ID.
TblTemplateItems TblTemplateItems

ItemID	TemplateID	itemName	numberItem
7​	Food	Shrimp	123​
8​	Food	Chips	456​
9​	Food	Dip	789​
10​	Pool	Cushion	321​
11​	Pool	Water Balloon	543​
12​	Pool	Floaties	643​

From the combo box if you Pick "Food"
It inserts into the table TblOrders_Item the Order ID and each of the ItemIDs where the template = "Food"

Private Sub cmdAdd_Click()
  If Not IsNull(Me.cmbotemplate) Then
    Dim strSql As String
    strSql = "insert into tblOrders_Items (OrderID_FK, ItemID_FK) Select " & Me.OrderID & " As ORDERID_FK, ItemID from TblTemplateItems WHERE TemplateID = '" & Me.cmbotemplate & "'"
    CurrentDb.Execute strSql
  End If
  Me.frmOrderItems.Form.Requery
End Sub

I also created a unique index in TblOrders_Items so that you cannot add the same item twice for the same order. If you try to add a template twice no additional records get created.

 

[bastanu]

Similar demo based on your test sample uploaded in post # 6.
Cheers,