H!

I'm setting up a database to log details about incoming complaints, loging problem, fault code (in three level), supplier etc.
I have the following tables
Main
Supplier
Staff
Fault code lvl1
Fault code lvl2 (sub category to lvl1)
Fault code lvl3 (sub category to lvl2)

What causes me problems is bring everything together in a form:

With Supplier and Staff I have set a combobox with a list showing all relevant values (when entering data) and storing the PK. But I also want to show some related data - simply put, I want to see the name of people and not only their PK, so how do I define a text field to show data from a related field?

With the Fault Codes the Idea is to input data in three steps from a value list, each step only showing relevant data (in relation to the supercategory/previous selection). For the first level this is simple enough as i always show the entire list, but I can't make the correct restrictions when showing the second and third lists.

Any help is greatly appreciated!!

Anders

Hi Anders,

"so how do I define a text field to show data from a related field?"

use a combobox

read the combobox example in this document:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1220772
30-page Word document on Access Basics (for Programming) -- it doesn't cover VBA, but prepares you for it because it covers essentials in Access

here is another good link:

FAQ: Displaying Data from related tables on a form - Scott Gem
http://www.utteraccess.com/forums/showflat...;Number=1265454

Thank you Crystal!

As you can see I'm new to access , but have beeen working with FileMaker for a couple of years resulting in quite a bit of confusion with really simple things.
Thanks again

Anders

you're welcome, Anders happy to help

Ok...

After a bit of reading and messing about with my database I have solved the first problem (showing related fields).

I get the impression that I always have to make a query including all fields I want in a form, and that I have to make the query complete before writing the form (I tried adding fields to a query, but they were not available in the form I had already started based on that query).
Is my assumption correct? If not, how do i update the list of available fields in the Controll Source?

Regarding my second question (Fault Codes), do I have to make a series of queries for each step? Or how do I fix that?

Thanks
Anders

Hi Anders,

"I get the impression that I always have to make a query including all fields I want in a form"

I don't do that -- I based forms directly on tables and that is what I recommend to folks starting out. As you get better with Access and NEED to use queries (for instance, if you have a huge amount of records and need to limit them for speedy retrieval)

Create Mainform and Subform

In my opinion, each form/subform should be based on just one table*. If you wish to manipulate information in more than one table, it is best to use a main form to for the "parent" table and subforms to display and edit information in each of the related tables.

*Note: This is a general guideline that helps when you are beginning to use Access. As you get better and realize that certain records must be created before related records, you can bend this rule.

Create the main form and the form(s) that will be used as subform(s) -- make sure to put the linking key fields on them (usually ID fields).

to put a subform on a main form:

Create a subform control on your main form using the subform/subreport tool in the toolbox (Cancel the wizard if it pops up and fill properties manually)

Then, from the design view of the main form:

1. turn on the properties window – Right-Click anywhere and choose Properties from the shortcut menu

2. Click ONE time on the subform control if it is not already selected

3. Click on the DATA tab of the Properties window

SourceObject --> drop list and choose the name of the form you will use as a subform (You can also Drag a form object from the database window and drop it on the main form. This automatically sets the SourceObject property.)

LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;Fieldname_main
LinkChildFields --> MainID;Fieldname_child

WHERE:
-- MainID is replaced with your field name holding an AutoNumber field (usually) in the parent table and a Long Integer field in the child table. Fieldname_main is the fieldname in the main RecordSet – and it is best to actually put the field on the main form. Fieldname_child is the name of a field in the child RecordSet – and, once again, it is best that this field actually be ON the related subform.

Even though the Help for Access says that the linking fields do not have to be ON the forms, I find this not be to be the case. It is best that you reference fields that are ON each of the respective forms. If a control is bound, I usually make the Name of the control the same as the ControlSource (what is in it). This does not follow standards but I find that it eases confusion. There are those who disagree and insist that controls should be named according to convention such as:

Naming Conventions for Microsoft Access - Leszynski/Reddick Guidelines for Microsoft Access
http://msdn.microsoft.com/archive/default....dn_20naming.asp

Reddick VBA (RVBA) Naming Conventions
http://www.xoc.net/standards/default.asp

It is common to set the Visible property to No for the control(s) containing the field(s) used in LinkChildFields

4. while still on the subform control, Click the ALL tab in the Properties window -- change the Name property to match the SourceObject property (minus Form. in the beginning if Access puts it there).

Difference between Subform Control and Subform

The first Click on a subform control puts handles* around the subform control.
*black squares in the corners and the middle of each side -- resizing handles

The subform control has properties such as
Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

The subform control is the container for the subform or subreport used as the SourceObject.

The second Click on a subform control gets you INTO the subform that is contained by the subform control …then when you first Click on the contained form, you will see a black square where the rulers intersect in the upper left of the "form" you are "in"

me.subform_controlname --> the subform control
me.subform_controlname.form --> the form inside the subform control
me.subform_controlname.form.controlname --> a control on the form contained by the subform control

The form that is contained by the subform control is an independent form -- you can open it directly from the database window, just as you can with any other form. It is often referred to as a "subform" because of the way it is being used.

It is advisable to edit forms used as subforms directly, instead of within the main form. Since I often do not disable Name AutoCorrupt, oh-uh, I mean Name AutoCorrect, I have had trouble with Access putting property changes in the wrong place for RowSource and RecordSource. Since it occasionally happens there, for major changes, I go to the design view of the "sub" form directly from the database window when the main form is closed.

Failures caused by Name AutoCorrect
http://allenbrowne.com/bug-03.html

~~~~~~~~~~~~~~
"Regarding my second question (Fault Codes), do I have to make a series of queries for each step? Or how do I fix that?"

ask that one again, I don't want to overwhelm you (if I haven't already )




Edited by: strive4peace2007 on Thu Dec 6 4:51:45 EST 2007.

Hmmm....

Thanks, I think I need to meditate on this for a while.

Anders

Hi Anders,

you're welcome ... thought that might be necessary

post back when you are ready to assimilate more (like after a good night of sleep <smile>)

Ok, I think I have digested. And I have come a bit further playing around with my database.

But I am still helplessly lost with my seemingly simple problem of displaying related fields in a value list.

Hope to get this sorted Monday

Have a good weekend

Anders

Hi Anders,

take a look at the link I gave you in the first post

"displaying related fields in a value list"

You would not use a value list, you would store data in a table and set a RowSource to a query or an SQL statement (don't like using tables for this). The RowSourceType is --> Table/Query

OK!

I think I got it right! It's working as i want it to anyway, with just one small anyoing exception.
The subcategory list will not let me include the descriptive text only the key, when I rewrite the SQL to include the text it will reset after the first time the value in the field is changed

The SQL i've used is

SELECT DISTINCT CodesB.Blvlno, CodesB.Blvltxt ****This latter reference will be automatically deleted***
FROM CodesB
WHERE (((CodesB.AlvlID)=[Main]![ProbCode1]));

to return relevant subcategory entries with PK (Blvlno) and description (Blvltxt)

Thanks! heaps of it!

Anders

Never mind, I figured it out!

Thanks again for your help!

Anders

you're welcome, Anders glad you got it !

additional note: I want to acknowledge MVPs Brent Spaulding (datAdrenaline) and Tom Wickerath (TomWickerath), who both suggested a number of valuable changes to the Mainform/Subform discussion above, which came from the new version of my Access Basics document, which is currently being edited.




Edited by: strive4peace2007 on Thu Dec 13 18:58:52 EST 2007.

Hi Anders,

Crystal invited me to join in, so I figured this was as good a message as any to reply to.

> Is my assumption correct?
No. It is very easy to go back and add additional fields to a query at some later point (I think this much you have discovered). When in form design view, for a form based on the query that you just added one or more fields to, all you need to do is click on View > Field List to display the listing of available fields. There is also a toolbar button to do the same thing. From the field list, you can use the drag and drop technique to add the new field to your form. Just select the new field (or fields--multiselect is also possible), hold down the left mouse button, and drag them onto your form. (This assumes that you have not switched the default mouse buttons using Control Panel).

As an alternative to using the drag and drop technique, you can:
1.) Copy and existing control and paste it to your form. Then change the Control Source property so that the control is bound to the new field that you just added to the query or

2.) Display the toolbox (View > Toolbox in form design view, or use the Toolbox button to toggle it's display). Select the desired control, then paste it to your form and set the control source. The first method is probably easier, since you will get any formats that you've already applied included in the copied control.

Since you are new to Access, I encourage you to have a look at my "Access Links.doc" Word document. You can download a zipped copy from here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

The first four pages include lots of useful information for everyone. Make sure to check out the two hyperlinks on page 4 that are shown in red font. These will help you configure your system to help prevent common errors. Also, make sure that your system is fully updated with service packs, so that you avoid the headaches of encountering problems that have been fixed with updates to Microsoft Access. See the link at the bottom of page 1 of Access Links.doc:

Best Practices
How to keep a Jet 4.0 database in top working condition

Within this article, follow the three links that read:

Verify that the latest operating system service pack is installed
Verify that the latest Microsoft Jet service pack is installed
Verify that the latest service pack for your version of Office is installed

I also recommend making sure you have the latest MDAC (Microsoft Data Access Components) files installed:

http://www.microsoft.com/downloads/details...37-185d0506396c

Good Luck with your Access adventures!


Tom

Great stuff, Tom -- as always! Thanks for adding your valuable wisdom

Thanks

Anders

In addition to Tom's post I'd recommend that you download Microsoft's MDAC Component Checker.

http://www.microsoft.com/downloads/details...;displaylang=en

It can alert you to a corrupted installation and will verify your MDAC installation.