View Full Version : One form, two tables


Paul Main
02-22-2005, 01:07 PM

I need to create a form that has two tables, not linked in a set. How can I do this? What I want to achieve is data in one table and record version numbers in the second. the information is not linked.



Raymond Lyons
02-22-2005, 07:29 PM
First, I might be better able to help if you explained a bit more. For example, are you saying the "data" in table 1 has no connection whatsover to the "record version numbers" in table 2? If not, why are you trying to show them on the same form? And if not, I doubt there is a way to do what you want.

However, do you mean something like a one record table with data on the company (name, address, phone, etc.) and another table with, say, a mailing list of prospects? Bad example maybe, but if this is what you are after, why not link the 2 tables in a set using two fields, like company name and prospect name, one-to-one "closest matching record"? Thus all records in the mailing list table would be linked to the same single record in the company table. This works.

But I suspect this type of scenario is not what you are talking about. If not, then at the very least I would need more information before I could be of any further help.


Paul Main
02-22-2005, 10:47 PM

All I want to do is be able to have a version reference on each form indicating which version the application is up to. I know I can just put a text field in each time make changes but I was hoping to have a memo field also to record the changes made, a sort of history file of changes.

I hope this is clearer.

Is there a better way to do this?


Tom Cone Jr
02-23-2005, 02:24 AM
Paul, if you kept the last version (revision?) number in a field and kept a description of the changes in the memo field you could "lookup" the last version and display it using a calc field in the form. i.e. base the form on one table, but create a calc field that looks up the last version field value from the other. You could add a button to the same form to pop up another form to display the change/revision description text. This second form would be based on the second table.

-- tom

Raymond Lyons
02-23-2005, 10:05 AM
Ah, now I get it!

Tom's suggestion is good, but if I were doing it I think I would use a global variable instaed of a calc field. That way when the user starts the application, an autoexec could lookup the revison number on the last record in your revisions table and set your global variable to that value. Then you could easily put that variable on all your forms, if desired. And as Tom says, a button somehwere would bring up the entire history with you memo field, etc.


Paul Main
02-23-2005, 11:20 AM
Both ways look good to try. I now need to go away and do it.



Cheryl Lemire
02-24-2005, 12:43 PM

I show the current version and build on the main menu of one application. I do not have the need to show it on all the forms so I did not bother with using my autoexec to populate the variables.

In my main menu design mode, I created two variables. Then I dragged and dropped them onto the form as you would any field. I use the OnInit event in the form properties to populate the variables with the following code:

'Open t_version table, go to the last record
'Get values of vers_num and vers_build fields
'Assign field values to global variables

dim global vcgVersion as C
dim global vcgBuild as C
dim tbl as p

tbl = table.open("t_version")
vcgVersion = tbl.vers_num
vcgBuild = tbl.vers_build

In your case, since you want to display the version on multiple forms, I would suggest using Ray's idea and place your code in the autoexec so that it is available to all forms without having to program the OnInit event in all of them.

I also have a table that I created to store information that I want to have available to any form/report/letter etc without having to tie that table to those forms/reports/letters. I used Dr Wayne's example for initializing global variables at:

A Better Way to Initialize Global Variables

My table to store the information has more fields than in his example, but all that is really needed is:

My_Var, My_Type, and My_Value as he describes.

I then created a function 'company_variables' to initialize the variables:

FUNCTION company_variables AS C ( )
while .not. t.fetch_eof()
evaluate_template("dim global "+trim(t.My_var)+" as "+trim(t.my_type))
evaluate_template(trim(t.my_var)+ "=" + trim(t.my_value))
end while

In my autoexec file I call the above function:

'**** Global Variables to capture Company Information ****

company_variables() 'this will be used on reports, etc

This option works nicely for a many reasons. It allows you to add variables at any given time during development or after the application is live and you are making updates, without having to change your function or your autoexec. All that is needed is to add new records in the table where you are storing the variable information.

With this option I can now place any of these variables on any form, report, letter, etc without having to ever adjust my code or without having to ever tie the table to that form, report, letter, etc.

You could use the above method for your version as well. Like they say, there are many ways to skin a cat. Whichever is easiest for you and achieves the specific results you need is the way to go.

Good luck.