There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Business Applications
Tag Cloud
audio blue blue screen boot bsod compaq computer cpu crash dell drivers dvd error excel firefox format freeze freezing hard drive hardware install internet internet explorer kb951748 lan laptop loss of internet malware memory motherboard network networking outlook outlook 2007 problem restart screen security slow sound trojan update virus vista windows windows xp winxp wireless zonealarm zone alarm
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Business Applications >
Access 2003 Form question


Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members. Enjoy!

 
Thread Tools
Ref2335's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
11-Sep-2008, 03:56 PM #1
Access 2003 Form question
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's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
11-Sep-2008, 04:10 PM #2
It can be done, but is not recommended.
Ref2335's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
11-Sep-2008, 04:20 PM #3
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's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
11-Sep-2008, 04:27 PM #4
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's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
11-Sep-2008, 04:39 PM #5
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's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
11-Sep-2008, 04:52 PM #6
OK, I killed the majority of the records and left 9 with some bogus data in there to play with.
Attached Files
File Type: zip tsg.zip (208.8 KB, 4 views)
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
12-Sep-2008, 06:24 AM #7
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's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
12-Sep-2008, 09:14 AM #8
Sorry, I should have clarified that. The form this would be added to is f_MVS Suppliers.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
12-Sep-2008, 09:34 AM #9
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's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
12-Sep-2008, 09:37 AM #10
Nope, just confirmed. Its there, f_MVS Suppliers. Its listed between f_MR OEM Page and f_NM OEM Page.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
12-Sep-2008, 10:03 AM #11
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.
Attached Files
File Type: zip tsg.zip (451.8 KB, 1 views)
Ref2335's Avatar
Computer Specs
Member with 87 posts.
 
Join Date: Jun 2006
12-Sep-2008, 10:13 AM #12
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's Avatar
Computer Specs
Junior Member with 3 posts.
 
Join Date: Sep 2008
Experience: Intermediate
12-Sep-2008, 11:30 AM #13
supplier buy history
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's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
12-Sep-2008, 12:02 PM #14
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
I do not give up easily
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,863 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
12-Sep-2008, 12:20 PM #15
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?
__________________
.
.
OBP
I do not give up easily
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.



Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 03:28 PM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.