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:
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
The debugger highlight this line:
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?
1. Multi-Record forms - can you edit data in them?
2. Multi level for structure
3. Multi-level GROUP BY clause is not allowed in a subquery
4. Multi Page form help
5. Multi Level Marketing
6. Using tab control in a multi-page form
7. Multi-Tab Form Creation
8. Multi-item form with comboboxes
9. Deepest Level Form Addressing
10. Combo Box Filter for Multi-record Form
11. multi tab forms
12. Pictures from file to multi-record forms
13. select record on multi item form and add that data as a new record in another table
14. Multi-Item Form, VBA editing on Load?
15. generate multi-level csv
16. Multi-Level Reporting
17. Help with missing object in my Multi-Select Code.
18. multi criteria Form Search?
19. Multi Listbox form to query / report
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
t.Transaction, t.Dollar, t.SharePrice, t.Shares, t.TotalShares,
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 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 ....
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.
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
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
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.
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) =
I have a multi-record form which opens and is populated from a query Q_JobsToPass which looks like:
FROM T_customers INNER JOIN T_CallDetails ON T_customers.custkey = T_CallDetails.custkey
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
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.
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
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
(...... 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)
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...
Group A Manager
Group A Sub-Manager 1
Group A Sub-Manager 2
Group A Sub-Sub-Manager 1
Group B Manager
The reporting difficulty stems from not knowing how many groupings to set up in a report and at what level the Detail section
Some Groups will go down five levels of managers before you hit the staff (Detail section) while others only go one level
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.
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
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
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
Value List, "Direct Freight Web"; "Tikitour"
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
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
WHERE (((Contacts.[Access Type])=[forms]![Access Type and Status]![Access Type]) AND ((Contacts.Status)=[forms]![Access Type