Multi Level Form

I need a what might be called a three leveled form but cannot get it to come out right. The goal is a form that operates as follows:

First, the user opens a club selection combo box to pick a single golf club, each of which can have multiple courses.
Second, the user opens a course selection combo box to pick a single course in order to edit hole information for that course.
Third the user enters or edits hole data. There will generally be 9 or 18 records of hole data for each course.

I created a hole edit form based on a club select query having the club name and index. The form only shows the club name. A combo box limited to only field names shows the club names and works fine.

Then a course form is created based on a query that shows courses and is dragged and dropped into the first form. The problem is that the combo box for the courses shows all the courses and is not limited to the courses for the club that was selected.

How can the course select combo box be limited to those available for the selected club?

This might direct the reader to something I am doing wrong. I create the second form for courses (a sub form) and add the combo box to select a course name. When I show the form and try to pick a course, there is a VBA error. Selecting debug shows:

Code: Private Sub Combo12_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Course_Name_And_Tee] = '" & Me![Combo12] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub The debugger highlight this line:
Code: rs.FindFirst "[Course_Name_And_Tee] = '" & Me![Combo12] & "'" This code was created by Access when I used the Wizard to create the combo box. I do not know enough yet about SQL to detect an error.

But that is just additional info.
How do I make a form with two levels of selection, each from a separate table, then have fields to edit from a third table?

Thanks for your time,
Bryan


Post your answer or comment

comments powered by Disqus
I have used Access for some time but only today decided to try a Multi-item form. I am creating a simple form the will list inventory items and allow the user to update the inventroy levels. I can't update any of the fields in the form. I tryied to create unbound fields and code to update in the backround but then when I changed the fist field it updated all the like unbound fields on the form. My question is "Is a multi Item form just a fancy way to show a non editable datasheet?"

Any one got any sugestions on the best way to view a multi level database in form view. The only way I can think of is a series of subform but it is not very satisfactory as the dispaly is very cluttered and not easy to understand if you are just an inputer rather than someone who understands database structure?

OS = Windows 2000, 5.00.2195, Service Pack 3
Access = 2000, 9.0.4402 sr-1

I have a table of weekly transactions of dollar amounts. Next, I created a query #1 to also show the year for the transaction using the YEAR function and show the last share price for the the year using a subselect (subquery). Query #1 showed the right data. This is the query:

SELECT year(t.date) AS TheYear, t.Date,
iif(t.transaction like "*employee*","Paid",iif(t.transaction like "*fee*","Expense","Appreciation")) AS TransactionType,
t.Transaction, t.Dollar, t.SharePrice, t.Shares, t.TotalShares,
(
SELECT Last(t2.SharePrice)
FROM FranklinTempleton AS t2
WHERE (((Year([t2].[date]))=year(t.date)) AND ((t2.Transaction) Not Like "*fee*"))
) AS LastSharePrice

FROM FranklinTempleton AS t;

A report using query #1 complained with this error message:
Multi-level GROUP BY clause is not allowed in a subquery.

I then created a query #2 which just did a "Select *" from query #1.

I changed the Data Record Source for the report to query #2 and then the report worked, sort of. It didn't show the results of the LastSharePrice field.

Further experimenting showed:

If I changed the report so that group headers and group footers were not shown, then LastShareprice did show up.

If I took query #2 and created a table from it, and used that table as the input for the report then, even with group header/footers turned on, the report worked fine.

I'll try some more experimenting and then see if I want to install the latest Office service pack.

This is a similar problem: http://www.access-programmers.co.uk/...light=subquery

I think there is an Access/Jet bug/feature so I'm really posting this to share my experience. But if any of you folks can think of something I'm doing wrong, let me know.

I am trying to have a multi page form on "sheet 1" but am having trouble getting started. How can i accomplish this?

Hi all,
I am trying to use Access to calculate bonus payments in a mult-level structure (network marketing). I.E. Person 1 recruits person 2, p2 recruits p3, p3 recruits p4 and so on, the linked levels can go down forever. Each record (person) is linked to the one above (by sponsor ID). For each person, I need to calculate down only 3 level below then stop. When anyone in the 3 levels of people sells a product, they trigger off a payment to the guy above (3 levels up). I can do it on a single level (obviously) but not going down to pre-determined multi-levels. I also need to calculate down to infinity when needed as well. How the hell do you do it, are there any geniuses out there that can help? Many thanks ....

Hi -

I have a form that is multi-page. On the third page of that form, I tried to set up a tab control that allows me to natvigate among three subforms. The problem is that the moment I click a tab (which is on the third page of the multi-page form), the form automatically jumps back to the first page.

Is it possible I can fix it so that when I use the tab control on the third page on a multi-page form, I will not get bounced back to the first page? Although this sounds simple, I cannot figure it out.

Thanks

tm

I created a multi-tab form the other day for my project that works just fine. Today, I needed to add a button to each page, and to my surprise, I only needed to add the button to the first page and all the rest had it. I figured, "why mess with success?"

However, today I am trying to create another multi-tab form and I ran across a very odd problem. I was able to create the first tab easily, but now anything I copy from an older form gets pasted on both existing tabs of the new form. What have I done wrong?!?

I checked all the properties of both the working form and the new form and they seem identical. I have also tried selecting different areas on the new tab, with no different results.

Any help is most appreciated.

TIA

oldgnome

I have a multi-item form for adding for entering data in to the db. I have 2 combo boxes. One of the comboboxes is dependent on the 1st combobox. Example: You are choosing from all 50 States with one combobox, but you add a first combobox (Region) to limit the amount of states in the list. When I do this it updates all the dropdowns on the multi-item form. I want to be able to add several locations into the form and make changes to each. Access wants to change all comboboxes to the what the current region is. My current SELECT statement is below. I have tried it in the OnFocus event of the states combobox and it updates the current combobox, but blanks out the others on the page.

SELECT Statestbl.stateID, statestbl.stateName, statestbl.RegionID FROM statestbl Where ((statestbl.RegionID) = [cmbStatesRegionID]);

thanks
Josh

Hi everyone,

Does anybody knows how to address a Fifth level Form? Up to the fourth level, for me worked the following statement:

Forms!frmClass!frmGroup!frmAccount!frmSubAccount!f rmAuxiliar.Form.Name

...But if a I insert a new form and want to address that one deeper level Form; ACCESS says "It can not find the Field frmClass" (observe this is the first Form name)

(N.B as far as I know ACCESS can support 7 levels deep Forms structure. Is that true??)

Any help would be apprecciated.
Thanks in advance.
O. Montes

I have a multi-record form which opens and is populated from a query Q_JobsToPass which looks like:

SELECT T_CallDetails.*
FROM T_customers INNER JOIN T_CallDetails ON T_customers.custkey = T_CallDetails.custkey
WHERE (((T_CallDetails.Job_Passed)=False))
ORDER BY T_customers.customer, T_CallDetails.Job_Type;

So this pulls out all jobs which have yet to be passed out to the on-call engineer. I then have a button on the form which, as it's multi-record, appears against each record and allows me to quickly save that record after updating some information. All this is working fine. But what I also want to do is to provide the user with 2 combo boxes which allows them to filter the results further. So assume we have 20 jobs, with 4 different customers and within customer A there are 5 jobs of the same type. I need ComboBox A to be populated with the NAMES of all the unique customer names already being displayed (as opposed to all customers in the customer table) and ComboBox B to be populated with all the unique Job_Type values currently being displayed.

So the user selects a customer and job type from these two combo boxes and presses a button to filter the results.

I can't get past my first problem of how to display the available customer NAMES in the combo box. I can pick up all the unique custkey values using

SELECT distinct [Q_JobsToPass].[T_CallDetails].[custkey] FROM [Q_JobsToPass] ;

But I just can't find the right syntax to join that to the T_customers table to display the list of customer names rather than custkey values. This has been stalling me for days now so I haven't even looked at the next step of how to apply the filter when the user presses the button - hopefully that will be straight forward!

Any help gratefully received. Here are the structures for the two tables.

Customers Table - T_Customers

custkey - AutoNumber
customer - Text


Call Table - T_CallDetails

Reference Number - AutoNumber
custkey - Number - Links to custkey in Customer
Job_Type - Text
Job_Passed - Yes/No
More fields, not relevant to problem.


Cheers
David

how do i make access print out all of the 'tabs' in a multi tab form? i've tried using the command button wizard to print current record but it only prints the tab that the command button is on.

I have pictures in the file system and an Access database containing the file names in a database field. How can I design a multi-record form that shows the pictures given the link in the field?

My first trial with a picture control and an Form_Current event procedure:

Me.Image11.Picture = Me.FileNamevoll

changes the pictures of all records to the same picture. With the bound control it seems not to be able to show jpg's without the link to a displaying program.

Any ideas for a simple solution?
Cheers
Markus

I have multiple tables in my database. I have tblEmployees with employee data. tblCourses lists course information. tblDates lists all the available dates for each course listed in tblCourses. Finally, I have tblTraining that combines those tables and lists what training is scheduled/completed by each employee. I am trying to create a multi-item form based on the tblDates information. I want to be able to select a specific course (combobox?) and have the form filtered to list only that course. Then, I want to be able to click on a specific class/date/time and add that record to an employee in tblTraining. Suggestions on how to do that?

I hope I can get some help here. I have a Multi-Item form and would like to do some math and change formatting on some fields depending on content of data in each row. From what I can tell, the on Load even only lets you do something with the first row and does not execute the VBA code for any following row. I don't see any way to trigger the VBA code on a row-by-row basis as the form is being painted.
Does anyone have a technique to do this or do I have to go to some other form type to do some dynamic formatting? Thanks for any help...

I need to generate a CSV file structure like below with MS Access. I assume multi-level would be a good term for this type of structure. Anyway, no clue as to how I create this for say hundreds of records daily. What is this called and where do I start?

(...... periods represent indents for each line)
Message Header Record1
......Message Detail Record1
............Package Detail Record1
......Message Detail Record2
............Package Detail Record2
............Package Detail Record3
......Message Detail Record3 (cancel)

thanks!

I've been tasked by my organization to develop a Calling Tree application. I am a fairly well versed Access and VBA user but am having some difficulty grasping how I should set up the DB for reporting. Here's my problem...

My orgainzation (about 900 people) is set up into multiple groups (as most are). Some groups are fairly straight forward. They go from Director to Group Manager to Staff. However, some groups go from Director to Group Manager to several "lesser" managers to staff. Some other groups go even deeper than that (up to 6 levels down from the Director). To make matters worse, some of these groups are multi-tiered. Here's a crude visual interpretation...

Director
Group A Manager
Group A Sub-Manager 1
Staff
Group A Sub-Manager 2
Staff
Group A Sub-Sub-Manager 1
Staff
Group B Manager
Staff

The reporting difficulty stems from not knowing how many groupings to set up in a report and at what level the Detail section will appear.

Some Groups will go down five levels of managers before you hit the staff (Detail section) while others only go one level down.

It is a simple one table setup. The table has basic contact info for each employee along with the Group they belong to and a field denoting which other employee calls them in the call tree.

Any thoughts...?

Good day all,

Knowledge Level Novice

I've been trying to build a Multi-Select form that a user can select names from based on a search to narrow down their list of choices.

With some help yesterday my search is working well (the list box needed to refresh after each new search). However I have a missing object in my code that I can narrow down.

I have included a sample.

The user will open the from > search for some names > begin a selection > those names are added to a temp table > continue to search for names and building towards a final list.

The After Update in the List box is kicking out an error. Can anyone help me?

Hi all

Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form.
I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this

Thanks Jackie

Hi guys

I havn't been using access for quite a while - but jumping back into it.

Hopefully someone can help - I'm not too familiar with SQL or VBA at all..

I have a form with two list box's

first one:
Access Type
Value List, "Direct Freight Web"; "Tikitour"

second one:
Status
Value List, "Active"; "In-active"; "Pending"

I have both these set to Multi - Simple

then the form has a run report button on it with opens a report from a query - query is "Customeruserinput"

What I'm wanting to do is have one or more selected in each list box, then run the report, but when I do this - it returns no results.

The query criteria I have is:

for Access Type: [forms]![Access Type and Status]![Access Type]
for Status: [forms]![Access Type and Status]![Status]


Would really appreciate anyones help on this - I feel like i'm going around in circles with it!

so far my SQL looks like this:

SELECT Contacts.[Customer Code], Contacts.[Customer Name], Contacts.City, Contacts.[Access Type], Contacts.Status
FROM Contacts
WHERE (((Contacts.[Access Type])=[forms]![Access Type and Status]![Access Type]) AND ((Contacts.Status)=[forms]![Access Type and Status]![Status]));

Hello all.

I have a customer-level form, with invoices showing in a continuous subform. I want to be able to prepopulate a number of reports (that duplicate current manually-filled paper documents) with customer-level information AND invoice-level information.

I was thinking about putting a single button on the subform, at the invoice level, that when clicked would bring up a subform allowing the selection of the specific report (there are 9 in all) that is desired from a set of buttons. Then, if any ad hoc info is needed, a small secondary form would open and, with a final button click, information would be gathered from the customer info and the invoice info to populate the already-existing info into the report, with the ad hoc just typed in.

There's got to be a better way of doing this... and even if I do it this way, I need some help on button code to capture this multi-level info. I need some way of selecting the appropriate invoice, so I thought of the "button on the invoice level" idea. But then, the button would appear on every record. Is this the way to do it, or would it be better... and again, I'm not sure I know how to do this... to have a checkbox on each record so that multiples could be selected prior to selecting a report for population?

THANKS for any help!!

Tom

Hi All,

I have expanded the previous version of my conditional formatted form that allows more than the usual 3 options. It works as a main form sub form routine. Just have a look at it. Any comments or suggestions would be much appreciated. If you like it why not up my reputation. If you don't well that's fine.

CodeMaster:

Hello boys and girls,
I've never really used Access before (although I have some SQL/DB experience) and therefore don't know my posterior from my elbow with regards to "the Access way of doing front ends" but I'd like to avoid doing everything in vba and DoCmd.RunSQL..
The DB's purpouse is to log information divided in four main categories; contacts, locations, events and organizations (each a table). Then there are log entries that have a many-to-many with all the categories. A log entry is just date, who entered and a rich text memo for the actual entry; one log entry might apply to two contacts, one location and three organizations for example. Furthermore there are a few other relations (such as a contact has a location, can be a member of one or several organizations, be reponsible for an event etc etc). The actual DB design is not an issue (just a few junction tables).

So, there are a number of form design issues that keep popping up.

MANAGING MANY TO MANY RELATIONS
What's the Access interface way of selecting to which records a log entry applies? I am thinking a popup form with 4 multi-select listviews; for each main category there's a listview and a "create new whatever" button linked to a small dialog (to create a new record in the category, the dialog will just take the bare minimum for the record, like first and last name for a contact). How do I set that up to avoid populating and updating everything with vba? Is there a way to use the "multi-value"-style combo box instead of the list views (without using multi-value fields)?
The actual db design is for this bit is 5 base tables:
contacts
locations
events
organizations
logs
And 4 junction tables:
contact-logs
location-logs
events-logs
organizations-logs

JUNCTION TABLE UPKEEP
What's the Access way of keeping the junction and base tables in sync? I.e. once there are no more relations to a logs record the record is deleted? Right now I'm looking at doing a clean up query in the main form OnClose, is there a "better way"?

CONTINUOUS VIEW SUBFORM DISPLAY
So, obviously I want to pull up the record for say a contact and see all the log entries which concerns him; so far I'm using a continous view subform for that. Ideally I would like each record to expand/shrink in size so that all info (specifically the memo) is displayed without scroll bars; i.e. each record has a different displayed height. Furthermore I'd like for the subform not to have any scrollbars but that the mainform is resized appropriately depending on the subform size (multi level scroll bars are a pet peeve of mine). What's the Access way of doing this? Right now I'm using the .../forums/showthread.php?t=174690 way, but I need to implement for resizing when a log entry is added and removed, and I can't seem to get the main form to shrink when displaying a record with less log entries (although the subform is appropriately displayed)..

FILLING CONTROLS
Another issue having a control display the result of a query based on the current record; for example, on the form displaying a contact I would like to show the organizations he's a member off, to keep the form compact I was thinking of a label and a "edit" button, the label is basically displaying "SELECT organizations.org_name FROM organizations INNER JOIN [contacts-organizations] ON organizations.org_id = [contacts-organizations].org_id WHERE [contacts-organizations].contact_id = XXXXXXX;" where XXXXXXX is the current record id, all concatenated with comas, and the edit button will pop up a dialog with a multi select list view. What's the Access of achieving this?


Thanks
j

Hi,

I am creating a form which will be used to input data into 3 newly created related tables in my backend database.

Table1 is related to Table2 (1 to 1 relationship) via Table1.PK and Table2.FK
Table2 is related to Table3 (1 to many relationship) via Table1.PK and Table3.FK

I have established these relationships in the backend file and set the integrity level and cascading deletes etc.

In the front end client, I am using the form wizard to create my form. I first add all the fields from Table1, then all from Table2 and then all from Table3. I then click Next and see the following error

Quote: You have chosen fields from these tables: Table1, Table2, Table3; One or more of the tables isn't related to the others. Click ok to edit system relationships. You'll need to restart the wizard. Click Cancel to return to the wizard and remove some fields. This then opens up the relationships view in the front end client. There are no relationships present. So I guess when linking tables in the front end to the back end, the relationships are not imported?
It seems strange that Access would expect me to have to duplicate all the relationships again in the front end? What if you have many and make a mistake? Also all the options for cascading are greyed out - I have read that all of that is enforced in the back end so that makes sense - which makes the whole idea of duplicated the relationships even more senseless to me.

So my question.. do I just recreate the relationships established in the back end, or have I not linked my tables correctly or something?

Any help would be much appreciated! Thanks!

hi,

I am about to design a form that has a four-level Master-Child
relationship. I did this a few years ago.

My question is:

Should I have one main form and three subforms?

or

Should I have one main form with a subform, the subform has a subform,
and that subform has a subform?

Help
Wayne


Not finding an answer? Try a Google search.