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

Pelerin13

Registered User.
Local time
Today, 09:00
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
Local time
Today, 09:00
Joined
Jan 12, 2001
Messages
32,059
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.
Local time
Today, 09:00
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
Local time
Today, 09:00
Joined
Jan 12, 2001
Messages
32,059
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.
Local time
Today, 09:00
Joined
Mar 23, 2011
Messages
56
keep getting an error (Query input must contain at least one table or query.)!!
 

Pelerin13

Registered User.
Local time
Today, 09:00
Joined
Mar 23, 2011
Messages
56
Select tblA.*, "SO Master" As TableUsed
UNION
Select tblB.*, "SO_Master_Shipped" As TableUsed
 

boblarson

Smeghead
Local time
Today, 09:00
Joined
Jan 12, 2001
Messages
32,059
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.
Local time
Today, 09:00
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
Local time
Today, 09:00
Joined
Jan 12, 2001
Messages
32,059
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.
Local time
Today, 09:00
Joined
Mar 23, 2011
Messages
56
Ms access can't find the field 'Sales_Order_Review' referred to in your expression
 

boblarson

Smeghead
Local time
Today, 09:00
Joined
Jan 12, 2001
Messages
32,059
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.
Local time
Today, 09:00
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.
Local time
Today, 09:00
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

Top Bottom