open record from union query

[eshai]

hi:
i build an union query from 6 tables For that matter we call them' tbl1,tbl2 etc'
the have fields called "table name" "id" "first name" "last name" and some other fields
now what i need is to add a field Called open record that well open the record
in the form of the table
i made a form from the query In datasheet view
any ideas

best regards:eshai

 

[CJ_London]

fields do not open records, events on forms/controls do.

Not enough information provided to do anything more than speculate on your requirement. The fact you have multiple tables implies an issue with your db design.

but suggest you need to look at using something like docmd.openform or perhaps build sql for a query and then open the query

 

[eshai]

hi:
that why i build a form based on the quiry
the commend in the field should be "on click event"
and in the vba I thought to use "if" statement
like "if table name = tbl1 then docmd.open form1 when form1[id]=me.id"

 

[pbaldy]

You could use this, with similar code deciding which form to open:

http://www.baldyweb.com/wherecondition.htm

Edit: in fact, your union query could include the name of the form instead of or in addition to the name of the table.

 

[Galaxiom]

i build an union query from 6 tables For that matter we call them' tbl1,tbl2 etc'
the have fields called "table name" "id" "first name" "last name" and some other fields

Just a suspicion but I think your table structure is probably not what it should be.

 

[eshai]

Just a suspicion but I think your table structure is probably not what it should be.

the table structure is fine tbl1 is main school other tables are sub schools
each table have a unique fields
the first reason i did union query is to find where is the record of the student
instead of writing a loop code that well take a long time

 

[Galaxiom]

the table structure is fine tbl1 is main school other tables are sub schools
each table have a unique fields

Just as I suspected. The records should all be in one table with a field to record the SchoolID.

There are a number of ways to deal with the unique fields.

 

[eshai]

Just as I suspected. The records should all be in one table with a field to record the SchoolID.

There are a number of ways to deal with the unique fields.

no.
every table have a unique structure and the gets there data by an excel file and some of them have unique codes and for the final there is another db that sanding data to this db. it's a very complected db that have 7 end users that dells with is department

now for my Q' i build a form based on the union query i add field that called open record
onclick event i need to open the form that contain the record
code: if table name = tbl then docmd open form frm1 when frm1.id=me.id
the problem is that the code should be bound to the current record

 

[Galaxiom]

no.
every table have a unique structure and the gets there data by an excel file and some of them have unique codes and for the final there is another db that sanding data to this db. it's a very complected db that have 7 end users that dells with is department

You have let the Excel files dictate your data structure so you have ended up replicating spreadsheets instead of building using database principles.

now for my Q' i build a form based on the union query i add field that called open record
onclick event i need to open the form that contain the record
code: if table name = tbl then docmd open form frm1 when frm1.id=me.id
the problem is that the code should be bound to the current record

You are having to do workarounds like this because of the way you have structured the data. Problems like this tend to get more frequent and intractable as the database becomes more complex.

Imagine what you would have to do to add another sub-school. In a well structured database it would simply require adding a few records to existing tables.

 

[June7]

You have a form based on UNION query? You select record on that form and want to open another form based on UNION form record?

A field could be built in the UNION query that provides the table/form number, and then that is available for use in VBA. Something like:

DoCmd.OpenForm "frm" & Me!TableID, , , "SchoolID = " & Me!SchoolID

If you need more help, post the UNION sql.

I agree that you will be faced with having to do more 'workarounds' with your db structure.

 

[The_Doc_Man]

Got to side with Galaxiom and June7 on this one.

From your description, you have "allowed the tail to wag the dog" and thus failed to completely analyze the data from a database viewpoint. Excel is incredibly powerful but it is not capable of doing everything you want (obviously) because otherwise you would not have attempted to unify everything through Access. But just putting things into Access tables and then trying to treat them like Excel-structured data is, long-term, a waste of time.

This statement is not intended to be a scathing rebuke but rather is a suggestion that you really need to consider. We have seen this a thousand times or more. Excel does things one way; Access does them another. You have transferred data but didn't change the structure and now you face difficulties.

Enough with the platitudes. Your contention that the tables have fields in common (hence the ability to make a UNION query) but that a lot of fields are different? That doesn't matter. From the viewpoint of what they represent, these tables of sub-schools should be ONE TABLE. If there are a few differences that cannot be mapped to one another, then it is OK for you that you have extra fields and that some of them are either blank or have "N/A" (meaning not applicable).

Without more information we cannot tell you how to perform that cross-field mapping. BUT here is the truth about database entity analysis.

Things that are to be treated similarly belong in a single table. The fact that you need to use a UNION query to treat the sub-schools together means that they belong together no matter how superficially different they might appear.

Things that are to be treated differently belong in different tables. That way you get the interference out of the way so that you CAN unify what you are doing across all the things you have to process.

Where you face a hybrid of similarities and differences, you face a case where your database is not normalized. Normalizing a database allows you to keep like things together in one table for treatment according to uniform rules. And it allows you to split out things that are different into child tables where you can isolate the differences.

Therefore, I see it as imperative that you try to study normalization techniques. If you do a web search, look for "database normalization" because by itself, "normalization" would give you articles with mathematical, political, and chemical subject matter.

I am not chastising you. As I said, you made this change because you thought it would help you, and it probably will. But probably under pressure to get something done, you have proceeded too early to write code based on the differences when you should still be isolating the similarities. We've seen it before. It is NOT a character flaw. It is just that you have a goal and a deadline and the deadline was not realistic. Been there, done that - no fun. I really do sympathize and understand. But there is no excuse for skimping on the analysis phase of a project. You only do yourself AND your users a grave disservice.

 

[eshai]

hi:
tanx you all
i build this db when the other (schools) worked like stone age (the still do) so the send me students list in excel file to solved this problem i build a software that get the data from the network and insert it to my tables without losing my stricture
i well past my code i managed to solve it and its working fine. and i will explain the benefits of working with union query

best regards:eshai

 

[eshai]

first this is my code

If FieldName = "txtvalew" Then
        DoCmd.OpenForm "formname", acNormal, "", "[fieldname]=" & "'" & .FieldName & "'", , acNormal
    
   ElseIf FieldName = "txtvalew" Then
    DoCmd.OpenForm "formname", acNormal, "", "[fieldname]=" & "'" & .FieldName & "'", , acNormal
    ' in my case i add more Else If 
    End If

now for my explanation
i have 6 schools that working in the stone age in my case it was or building a 6 db or built one that combine all 6 .it happened because my department got the job to manage the all schools if it's money classroom rooms etc'
the are sending my the students list by excel I couldn't built a single table for all the data because its a mass to start inserting date from 6 excel doc
so i builded 6 tables one for Each school then i builded Third party software that get the excel file by the network and insert it
to my table without Override any information
But still how to find a student in thousands of records. This is where a union query Which becomes effective
I built an union query only for the "id,last name, first name,table name" on that i built another query with linked
table. now Each of the employees has unique form( Data Sheet) Filtered by his work one by date and one by test etc' and when the have to get to the The student's card the simple click on my code

And sorry in advance for spelling mistakes
Too long but maybe it would help someone
best regards: eshai

 

[pbaldy]

I assume that's not actually your code, as it makes no sense to test for the same value in the ElseIf. If you include the form name in your UNION query, you can simply do:

DoCmd.OpenForm Me.FieldName...

without the If/ElseIf code.

 

[eshai]

I assume that's not actually your code, as it makes no sense to test for the same value in the ElseIf. If you include the form name in your UNION query, you can simply do:

DoCmd.OpenForm Me.FieldName...

without the If/ElseIf code.

if the union query is build from 6 table that have queries and 6 different forms
how can you put docmd.openform in datasheet view or even in form view without if and else if. the code goes behind a field in a query that build based the union query. it open a form not a table

 

[pbaldy]

You're already adding the school name in the query, right? I'm saying to add the form name also/instead.

 

[eshai]

i cant add the form name to the query because i will need to do it manually
this where the code do the job to apply which form to which table

 

[June7]

Agree with Paul, the posted code makes no sense.

Suggestion is to include formname as a constructed field in UNION query, then the formname will be displayed in form bound to UNION query and therefore available for reference in code.

As requested in post 10, post your UNION sql statement and can advise how to modify.

 

[eshai]

you suggest to insert the form name to union query for tables?
can you union forms and tables in union query in sql? (no)
i will have to combine 2 union query that is to mass my code do the job perfect
and i'm using it also for search record that is more quick for me instead of do loop

 

[June7]

Not suggesting to UNION forms and tables. Suggesting constructing a field in UNION that has form name as a value.

Since you do not understand the suggestion, post the SQL statement of UNION query and will advise how to modify. Exactly how are the forms named - "Form1", "Form2", "Form3", "Form4", "Form5", "Form6"?