[Ref2335]

I'll start general and if it looks like I can get my question answered I will elaborate.

I'm wondering if I can get a single form to display specific records from multiple tables without using a subform.

I'll elaborate on what I'm doing if this can even be done.

Thanks!

[OBP]

It can be done, but is not recommended.

[Ref2335]

OK, well I've never let that get in my way in the past.

The database I am working with is a supplier information database. it has contact info, location, phone numbers, as well as buy history form each supplier for the past 2 years.

Currently, an analyst chooses a subset of these suppliers to view based upon specific criteria. All of that data is contained in 1 table. I know its not relational, thats something I'm trying to correct but for now, thats just the way it is and it works.

What I am trying to do is get the supplier buy history to show on this form as well but its not part of the main table. To further complicate matters the buy history is in a table that was made from a cross table query.

What is important here is that the proper buy history get displayed based upon the initial search criteria.

I've got the fields created on the form where the data needs to go, I just can't figure for the life of me how to get the proper fields from the crosstab table displayed....

[OBP]

Without seeing the data structure I can't really help, can you post a zipped copy of the database with some dummy data in it?

Have you tried combining the data from the 2 tables in to 1 query, that is normally how it is done.

[Ref2335]

Yes, I considered doing it that way. What I'm struggling with is that there is no query to build the form to begin with. Its coded, not query based.

Its going to take some time to dummy up the records in the database but I'll give it a go.

[Ref2335]

OK, I killed the majority of the records and left 9 with some bogus data in there to play with.

[OBP]

Ref, I am sorry I can't find any forms with Fields sert up with no data sources.
Which Form did you want this to go on, the Tabbed f_Supplier_Form where it says Suppler Spend data?
If so why don't you want it to be a Subform?

[Ref2335]

Sorry, I should have clarified that. The form this would be added to is f_MVS Suppliers.

[OBP]

Ref, there is no f_MVS Suppliers Form in the database that you posted, I think it must be frmMvsSuppliers, I will look at that.

[Ref2335]

Nope, just confirmed. Its there, f_MVS Suppliers. Its listed between f_MR OEM Page and f_NM OEM Page.

[OBP]

ref, sorry, yes you are correct, it is virtually the same as the frmMvsSuppliers which I have updated using a query.
Does it do what you want?

Do you want any recommendations on the Database, the number of queries could probably be reduced quite a bit.

[Ref2335]

I see the query. What I need to do is to make sure on the f_MVS Suppliers form the spend for only the appropriate planner code is listed. The planner codes are the columns titled B0, B1, B2 etc...

The determination for the proper planner code is made as the user clicks on the individual buttons starting from the f_Front page form.

As I said initially there are no queries that set up the data to go into the f_MVS suppliers form, its code driven. As far as the queries go, the majority of those are old and need to be deleted.

[kalam88]

chooses a subset of these suppliers to view based upon specific criteria. All of that data is contained in 1 table. I know its not relational, thats something I'm trying to correct but for now, thats just the way it is and it works.

What I am trying to do is get the supplier buy history to show on this form as well but its not part of the main table. To further complicate matters the buy history is in a table that was made from a cross table query.

[OBP]

Ref, I am not sure about the "there are no queries that set up the data to go into the f_MVS suppliers form, its code driven", the data in the form isn't what I would call code driven, as it is coming directly from the Table, the only code that I can see changes the Form's Caption.

Can you tell me the precise sequence for controlling the Planner Code that you want to display on the Form as I was just showing the year end totals in the "frmMvsSuppliers" form.
It is possible to use a query to make that kind of selection, but difficult. It is also possible to use the Dlookup Function on the Form or VBA to obtain the values.
But I need to know what controls which Planner Code to work with.

[OBP]

Ref, I have gone back to the beginning and I now see how it is working, the form is Code Filtered rather than the code providing the data.
This seems quite a long winded way to do this.
Is the only place that the Planner Code exists in the Annual Spend tables?