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


Sponsored Links:



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]));