Enter/Change field data into a table in Union Query (1 Viewer)

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
hey All,
I have a union query that includes 2 tables TableA and TableB
And I have a field called Comments in the 2 tables
And I have a form where the records source is the union query that lists the data as datasheet
Is there a way to add the Comments (in TableA or B) thru the unit queries ?

Thanks
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
A Union query is non-updatable. You will have to use another form which you can call to update the appropriate field in the individual table.
 

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
I tried that BOB, but sometimes the record is in TableA and sometimes in TableB!! And you can’t populate 2 tables into one form
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
I tried that BOB, but sometimes the record is in TableA and sometimes in TableB!! And you can’t populate 2 tables into one form
Yes, and you would need to know which it is so you can do that by adding a field to the Union Query which also tells you which recordsource for the form to use. So like this in the union query:

Select tblA.*, "TableA" As TableUsed
UNION
Select tblB.*, "TableB" As TableUsed

And then you could use the double click event of one of the fields in your form which uses that and then you can open the correct recordsource like:
Code:
DoCmd.OpenForm "FormNameHere"
Forms!FormnameHere.Recordsource = "SELECT * FROM " & Me![TableUsed] & " WHERE [fieldNameHere] = " & Me.YourKeyFieldOnTheForm
And if the field is a text field you would use

Code:
DoCmd.OpenForm "FormNameHere"
Forms!FormnameHere.Recordsource = "SELECT * FROM " & Me![TableUsed] & " WHERE [fieldNameHere] = "  & Chr(34) & Me.YourKeyFieldOnTheForm & Chr(34)
 

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
keep getting an error (Query input must contain at least one table or query.)!!
 

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
Select tblA.*, "SO Master" As TableUsed
UNION
Select tblB.*, "SO_Master_Shipped" As TableUsed
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
Sorry - I missed something:

Select tblA.*, "SO Master" As TableUsed
FROM tblA
UNION
Select tblB.*, "SO_Master_Shipped" As TableUsed
FROM tblB
 

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
hey Bob,
I getting a vb error! @
Code:
Form!Sales_Order_Review.RecordSource = "SELECT * FROM " & Me.[TableUsed] & " WHERE [SO_ID] = " & Chr(34) & Me.SO_ID & Chr(34)

here is the union query (UnionProduction_SUM) i'm runing
Select [SO Master].*, "SO Master" As TableUsed
From [SO Master]
UNION Select [SO_Master_Shipped].*, "SO_Master_Shipped" As TableUsed
from [SO_Master_Shipped];


and i have a main form called "main Form" and Subform named "SO Master subform" that lists records from a union query.
when I DblClick a SO_ID in the subform, another form named "Sales Order Review" is popup and show that SO_ID detail where i can change or add comments ..

Thanks a lot
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
Form!Sales_Order_Review.RecordSource = "SELECT * FROM [" & Me.[TableUsed] & "] WHERE [SO_ID] = " & Chr(34) & Me.SO_ID & Chr(34)

But is SO_ID text or numeric. If numeric, leave off the Chr(34)'s.
 

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
Ms access can't find the field 'Sales_Order_Review' referred to in your expression
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
Ms access can't find the field 'Sales_Order_Review' referred to in your expression
Two things -

first it is Forms!.......

with the S

and second, are you sure your form is named

Sales_Order_Review

with the underscores and not spaces? If it has spaces then you must use one of these:

Forms![Sales Order Review].RecordSource =


or

Forms("Sales Order Review").RecordSource =
 

guinness

Registered User
Joined
Mar 15, 2011
Messages
249
I think I might be able to help but what I'm going to suggest is probably bad db design.

On table a create a new field named exactly the same name as the comments field in table b with exactly the same properties (text etc). In table B create a field that exactly mirrors the comments field from table A. (The two comments fields need to be named slightly differently like comments A and Comments B. You can hide these new fields as data won't require to be entered in either. They just need to be there.

Now create a query from table A with the details that you want and the empty field at the end. Do the same for table B. (the layout of both queries must be identical).

Create a new query and go to the sql tab. Copy the details from the sql tab of query a into the sql tab of the new query. Remove the ; at the end of the sql statement. Type Union on the line below and press enter to go to a new line. Now paste the sql details from query B. Run it. Hopefully you will get what you'r after.

This explanation made sense when I tried it but email back if it doesn't work. Don't be too surprised if a flurry of people tell you this is a bad idea. It saved my sanity.

Cheers
 

Pelerin13

Registered User
Joined
Mar 23, 2011
Messages
56
I think I might be able to help but what I'm going to suggest is probably bad db design.

On table a create a new field named exactly the same name as the comments field in table b with exactly the same properties (text etc). In table B create a field that exactly mirrors the comments field from table A. (The two comments fields need to be named slightly differently like comments A and Comments B. You can hide these new fields as data won't require to be entered in either. They just need to be there.

Now create a query from table A with the details that you want and the empty field at the end. Do the same for table B. (the layout of both queries must be identical).

Create a new query and go to the sql tab. Copy the details from the sql tab of query a into the sql tab of the new query. Remove the ; at the end of the sql statement. Type Union on the line below and press enter to go to a new line. Now paste the sql details from query B. Run it. Hopefully you will get what you'r after.

This explanation made sense when I tried it but email back if it doesn't work. Don't be too surprised if a flurry of people tell you this is a bad idea. It saved my sanity.

Cheers
the only problem though is that the main Tables are on the server side and i have no control over it :(

sorry but i think it doesn't look like a bad db design!! :D
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom