Control Source problem

[mraetrudeaujr]

I am building an Access 2000 application with a 'tabbed' form (GUI). The first tab is labeled 'Quick Look', the second 'General Info', the third 'Arrest Info', and the fourth 'OR Info'.

It is slowly coming together, but the curiosity of the users on the floor have prompted them to put in their ideas. That's okay, though...they will be the ones inputting the data. However, it has required me to import the tables from two other databases.

After importing these tables I 'linked' them by their common field -- "LOG". Then I chose the join property option #3 "Include ALL records from '2005 OR's' and only those records from '2005 Log' where the joined fields are equal". These three tables have many common fields, so this is why I wanted to link them. There are too many records to try and import them into one table, and I don't know if that would be a good idea either. A better way would be to continue using the tables as is, but then 'pull' the information into this tab. I don't want to overwrite any previous information. How would I pull in the fields and their respective data into one table?

 

[mraetrudeaujr]

To be more specific, I thought that I could just hit the drop down arrow for the "control source" property and see the appropriate table, but it still shows the original table. How can I build the 'controls' on these pages to reflect the data that is in different tables of the same database?

 

[PHV]

Base your form on a query instead of only one table.

http://r937.com/relational.html

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mraetrudeaujr]

PHV,

Okay, I think I understand what you are saying. Let me try and rough out what I understand to be the process:

1. Create a select query
2. Pull in all three tables
3. Pull the fields down from each table to the columns below
4. Run the query
5. Save and name the query

What will happen where there are fields that have the same name as the other tables?

 

[mraetrudeaujr]

PHV,

I tried it but the text box fields now show "#Name?" and only 269 records out of 7257 records show up on this form.

I changed the 'control source' to what is now generated by the query as "2005 Log.LAST NAME" etc. and it will pull in some records, but far fewer than what is contained in the table.

Also, while I'm on the subject of 'fields', should I use a "connected" syntax or can I make it a plain English syntax; e.g. "LastName" or "Last Name"?

 

[PHV]

Have you read the content of the 3 links I gave you ?

Avoid space and special characters for the name of any object (field, control, ...) you create if you want to minimize the headaches.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mraetrudeaujr]

Yes, a lot of it. But everybody on my end just states that you should stay away from special characters...not necessarily avoiding the spaces. They state that Access will put your field names in quotes whenever you look at the background code, and that I should stick to macros whenever possible.

Anyways, I need some help with joining these tables and then being able to pull this information into the main tabbed form. Most of the information is contained in the 2005 Log table, but my predecessors thought it would be easier (or something?) to split all of this information into three separate tables: 2005 Log, 2005 OR's, 2005 Crimlog. I thought about just taking the fields from the 2005 OR's and 2005 Crimlog and putting them in the design of the 2005 log table. But then, how would I get the information out of those tables (and only the fields that are not currently in the main 2005 Log table) into this main table 2005 log?

I'm the only person that can build and maintain their (simple) databases, and they aren't giving me any help. I'm on my own, so that is why I use this forum as much as I can. I'm not trying to reinvent the wheel (database) but I just want a database that will finally return statistical reports that management can have confidence in.

 

[PHV]

The "must absolutely be read and understood before starting any database design stuff" :

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mraetrudeaujr]

Thanks PHV.

I copied the article to MSWord and then "pdf'd" it. I will digest this information slowly, over time. Much of what this article addresses I have read at one time or another. I decided to take your suggestion and redress the basics through this article, again.

However, I still have a database with approximately 12,000 records that I have to rebuild (without losing the data) into an Access Application from where the endusers will be able to smoothly input ongoing records. So, given the above explanation, could you please give me some step-by-step instructions that I could follow? Your help is greatly appreciated, thank you.

Albert Trudeau