open record from union query

[eshai]

ok this is the sql union 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]

So my suggestion was adding the form:

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

 

[June7]

Ooops, need a comma instead of period after [firstname].

Again, what are the actual names of your forms?

 

[pbaldy]

Thanks; darn fat fingers.

 

[The_Doc_Man]

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]

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?

 

[The_Doc_Man]

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]

tnx i got the point make sense
i hope the post will help other people

best regards:eshai

 

[June7]

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 & "'"

 

[eshai]

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