Union Query

[Guest]

I have a union query that is pulling data from 2 tables. The fields are the
same in each table but they are different locations. There is one field that
I am pulling that is a "Yes/No" field. When I open my form that runs the
query I want to be able to put a check in that "Yes/No" field. When I try it
I get a message that says the record is not updateable. Any idea if this can
be done through a union query or am I missing something?

 

[Rick B]

I don't believe you can through a Union query. My question is why you have
two tables? Why not just add a "location" field to the records and put them
all in one table? That would be much more normalized.

If you do so, the data will be easier to work with and your queries would be
updatable.

You can still limit access to the data by location using the new location
field and filtering the data appropriately.

Most of the time (but NOT always) when you find yourself using Union
queries, you are likely to have a normalization issue.

 

[Guest]

Well I have to keep them in different tables because they generate different
ID's for each plant. I agree putting them in the same table would be easier
but because of the ID #'s they have to be separate. So how can I link these
two tables in a query and be able to update fields from one form? Is there
another way around the Union Query?

 

[Pieter Wijnen]

no, a union quey will never be updatable
I use "tabbed" forms myself ro deal with these kind of problems, allthough
there are different approaches

Pieter

 

[Guest]

I like that idea about using a tabbed approach. Can I set each tab to a
different query?

 

[Pieter Wijnen]

yupp, Use one subform for each tab, you can even use the same Form twice
with different Recordsource for each subform control, so you can have the
same logic without needing Xerox Engineering.
Remember that when you refer to a SubForm, you're actually referencing the
Control, and through it, the underlying Form

As an Example
Form: MyTabForm
SubForm Control: MySubForm1, SourceObject: MySubForm (located on TabPage1)
SubForm Control: MySubForm2, SourceObject: MySubForm (located on TabPage2)

Private Sub Form_Open (Cancel As Integer)

Dim cSF As Access.Control
Dim SF As Access.Form

'Set cSF = Me.Controls("MySubForm1")
Set cSF = Me.SubForm1 ' Faster & Better to use
Set SF = cSF.Form
SF.RecordSource = "SELECT * FROM MyTable1"
Set cSF = Me.SubForm2
Set SF = cSF.Form
SF.RecordSource = "SELECT * FROM MyTable2"
Set SF = Nothing
Set cSF = Nothing ' Allways destroy handles, Never Rely on the Program to do
it - even if it's supposed to/usually does it
End Sub

HTH

Pieter

 

[Guest]

Thanks Pieter. I will try this out!

yupp, Use one subform for each tab, you can even use the same Form twice
with different Recordsource for each subform control, so you can have the
same logic without needing Xerox Engineering.
Remember that when you refer to a SubForm, you're actually referencing the
Control, and through it, the underlying Form

As an Example
Form: MyTabForm
SubForm Control: MySubForm1, SourceObject: MySubForm (located on TabPage1)
SubForm Control: MySubForm2, SourceObject: MySubForm (located on TabPage2)

Private Sub Form_Open (Cancel As Integer)

Dim cSF As Access.Control
Dim SF As Access.Form

'Set cSF = Me.Controls("MySubForm1")
Set cSF = Me.SubForm1 ' Faster & Better to use
Set SF = cSF.Form
SF.RecordSource = "SELECT * FROM MyTable1"
Set cSF = Me.SubForm2
Set SF = cSF.Form
SF.RecordSource = "SELECT * FROM MyTable2"
Set SF = Nothing
Set cSF = Nothing ' Allways destroy handles, Never Rely on the Program to do
it - even if it's supposed to/usually does it
End Sub

HTH

Pieter



--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!