open record from union query (1 Viewer)

eshai

Registered User.
Local time
Today, 11:09
Joined
Jul 14, 2015
Messages
193
ok this is the sql union code
Code:
SELECT "tbl1" as tablename, [id], tbl1.[lastname], tbl1.[firstname]
FROM tbl1
union
SELECT "tbl2" as tablename, [id], tbl2.[lastname], tbl2.[firstname]
FROM tbl2
Union
SELECT "tbl3" as tablename, [id], tbl3.[lastname], tbl3.[firstname]
FROM tbl3
Union
SELECT "tbl4" as tablename, [id], tbl4.[lastname], tbl4.[firstname]
FROM tbl4
Union
SELECT "tbl5" as tablename, [id], tbl5.[lastname], tbl5.[firstname]
FROM tbl5
Union
SELECT "tbl6" as tablename, [id], tbl6.[lastname], tbl6.[firstname]
FROM tbl6 ;

the names of the tables and forms are different in my db
i used tbl1,tbl2 for exemple
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:09
Joined
Aug 30, 2003
Messages
36,118
So my suggestion was adding the form:

SELECT "tbl1" as tablename, [id], tbl1.[lastname], tbl1.[firstname], "FormName" As FormToOpen
 
Last edited:

June7

AWF VIP
Local time
Today, 01:09
Joined
Mar 9, 2014
Messages
5,423
Ooops, need a comma instead of period after [firstname].

Again, what are the actual names of your forms?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:09
Joined
Aug 30, 2003
Messages
36,118
Thanks; darn fat fingers.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:09
Joined
Feb 28, 2001
Messages
26,996
eshai:

If you look at the normal syntax of the DoCmd.OpenForm, the first argument is a quoted string for the normal case. BUT... if your field contains the actual name of the form, what happens is that instead of using the quoted literal to return a string, you use a form reference to return a string. Of course, the CORRECT name of the form must be in that field, but... it can be that way if you set it up that way.
 

eshai

Registered User.
Local time
Today, 11:09
Joined
Jul 14, 2015
Messages
193
So my suggestion was adding the form:

SELECT "tbl1" as tablename, [id], tbl1.[lastname], tbl1.[firstname], "FormName" As FormToOpen

this code add a field formname
the event procedure have to be on the field "on click"
so it will be
docmd.openform
then it have to be which form to open on specific record
so
DoCmd.OpenForm Me.FormToOpen, acNormal, "", "[fieldname]=" & "'" & me.FieldName & "'", , acNormal
ok so you can do it this way and you can do it in my way
any different?
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:09
Joined
Feb 28, 2001
Messages
26,996
ok so you can do it this way and you can do it in my way
any different?

If you add a seventh school or drop one of the six schools, doing it by indirection does not require a code change. Doing it hard-coded requires reprogramming for a seventh school and suggests (but doesn't mandate) reprogramming for loss of a school.
 

eshai

Registered User.
Local time
Today, 11:09
Joined
Jul 14, 2015
Messages
193
tnx i got the point make sense
i hope the post will help other people

best regards:eshai
 

June7

AWF VIP
Local time
Today, 01:09
Joined
Mar 9, 2014
Messages
5,423
Actually, the field is named FormToOpen. The text between quote marks is the value. This needs to be the actual name of your form, just as you did with table name. Is ID a text type field that uniquely identifies each student across all tables so it will be unique in the UNION?

SELECT ID, lastname, firstname, "tbl1" AS tablename, "frm1" AS FormToOpen FROM tbl1
UNION SELECT ID, lastname, firstname, "tbl2", "frm2" FROM tbl2
UNION SELECT ID, lastname, firstname, "tbl3", "frm3" FROM tbl3
UNION SELECT ID, lastname, firstname, "tbl4", "frm4" FROM tbl4
UNION SELECT ID, lastname, firstname, "tbl5", "frm5" FROM tbl5
UNION SELECT ID, lastname, firstname, "tbl6", "frm6" FROM tbl6;

DoCmd.OpenForm Me.FormToOpen, , , "ID='" & Me.ID & "'"
 
Last edited:

eshai

Registered User.
Local time
Today, 11:09
Joined
Jul 14, 2015
Messages
193
id is the students id number or passport so its a short text field
your sql Looks shorter and better
anyway i'm already working on a new db
i'll check your code when i'll have time

What the heart desires time expire
it sounds better in my language
 

Users who are viewing this thread

Top Bottom