How to make an editable/updatable query on multiple tables of same type

[Praveen]

https://www.accessforums.net/newthread.php?do=newthread&f=10


Hi:

I have multiple tables in my access db. The structure of all the tables (3 in number) is exactly the same but the data in all of them is for different years. These fields are not related to each other. Following are field names in all the tables:

S. No.:
Customer Name:
Date of Purchase:
Balance:


Followup Date:
Remarks:

Following are the table names:
Aging 2004-2006 (contains data for date of purchase lying in years 2004-2006)
Aging 2007-2008 (contains data for date of purchase lying in years 2007-2008)
Aging 2009-2010 (contains data for date of purchase lying in years 2009-2010)

As the data is huge in all these individual tables, I cannot merge all the tables in one.

I have 4 queries for all the tables which triggers the data where follow-up date is "Today." A team member follows the customer and updates "Remarks" field, which should be updated in the individual table to which that row belongs.

I can combine all the 4 queries using a union query but that's not updatable.

Could you please help me in making an editable/updatable query, which triggers the data from all the four tables, where the follow-up data is "Today" and add on a field name giving us the name of the table from which that row has come from?

I sincerely appreciate your help on this.


Praveen

[Praveen]

Thanks for all your help.

[maximus]

After Reading your thread I understand you have the following Requirements:
1) Given Tables Should be Combined.
2) Remarks should be updateable.

This is What I have done:
I have three tables with the same fields as you had specified:

Following are the table names:
Aging_2004_2006
Aging_2007_2008
Aging_2009_2010

The following are the fields:
Srl_No.:
Customer_Name:
Date_of_Purchase:
Balance:
Followup_Date:
Remarks:

I have a form which opens at startup. Click on Load data. The Combined data from all the tables with the Followup_Date=Type_Date Text Box on the Form will load. Note: I have set the default value of the Type date_Text box to #04/08/2010#. You can change it to Date() to display current date.

To enter Remark select an entry on the form. type Remark in the type_remarks text box on the form footer. then Click on post_remarks button and see the comments will appear on the form.

Requirements taken care off:
1) Data from all the three tables combined.
2) Table Name is from which data belongs indicated by the Table_Name Field.
3) Remarks can be entered/Edited.

Note: To delete an entered remark Select an entry. Keep Type_Remark TextBox blank and click on post_remark button.
Note: To change entered remark,Select entry type new remark and click post_remark.

Code used:

Load data Button:

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click
Dim strSQL As String
strSQL = "Select Srl_No,Customer_Name,Date_purchase,Balance,Followu p_Date,Remarks," & """2004-2006""" & " As Table_Name From Aging_2004_2006 Where Followup_Date=#" & Me.Text17 & "#"
strSQL = strSQL & " Union Select Srl_No,Customer_Name,Date_purchase,Balance,Followu p_Date,Remarks," & """2007-2008""" & " As Table_Name From Aging_2007_2008 Where Followup_Date=#" & Me.Text17 & "#"
strSQL = strSQL & " Union Select Srl_No,Customer_Name,Date_purchase,Balance,Followu p_Date,Remarks," & """2009-2010""" & " As Table_Name From Aging_2009_2010 Where Followup_Date=#" & Me.Text17 & "#"

Me.RecordSource = strSQL
Me.Srl_No.ControlSource = "Srl_No"
Me.Customer_Name.ControlSource = "Customer_Name"
Me.Date_Purchase.ControlSource = "Date_Purchase"
Me.Balance.ControlSource = "Balance"
Me.Followup_Date.ControlSource = "Followup_Date"
Me.Remarks.ControlSource = "Remarks"
Me.Text15.ControlSource = "Table_Name"
Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub



Post_Remarks:

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click
Dim strTableName As String
Dim intSrl_No As Integer
Dim strSQL As String
Dim strRemarks
strTableName = Me.Text15
intSrl_No = Me.Srl_No
strRemarks = IIf(IsNull(Me.Text24), " ", Me.Text24)
Select Case strTableName

Case Is = "2004-2006"
strSQL = "UPDATE Aging_2004_2006 SET Remarks=" & "'" & strRemarks & "'" & " Where Srl_No=" & Me.Srl_No
Case Is = "2007-2008"
strSQL = "UPDATE Aging_2007_2008 SET Remarks=" & "'" & strRemarks & "'" & " Where Srl_No=" & Me.Srl_No
Case Is = "2009-2010"
strSQL = "UPDATE Aging_2004_2006 SET Remarks=" & "'" & strRemarks & "'" & " Where Srl_No=" & Me.Srl_No
End Select

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Requery
Me.Text24 = Null
Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click

End Sub

Please refer attached sample mdb.
if this solves your problem please mark this thread solved.

[Praveen]

Thanks a lot Maximus for all the efforts that you have put in for the reply.

I could not open the file attached to your reply. I would appreciate if you could forward me the file at praveengupta1979@gmail.com. It would help me better understand the code.

Thanks a ton,


Praveen

[Praveen]

I appreciate your support Maximus and I appreciate Accessforums.net for enabling people like me to get help from terrific people like you.

Thanks & regards,


Praveen