Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-29-2014, 04:21 AM   #1
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Form and query

Hi

The databse I am trying to develop is based on a theatre showing two productions (wizard of oz & charlie and the chocolate factory).

I am having problems with a form(Activity 2). The last three combo boxes on the form select a production, Production job and character name. What I also have is a qryFindCharacters which should return the list of characters based on what is selected in the combo boxes. If the correct selections are made from the combo boxes(WIZ001, Act, Dorothy) the the qryFindCharacters should diplay all details for this character.

When I run this query it returns no results. Also, I would like the results of this query to be displayed on the Activity 2 form.

Any assistance with this will be greatly appreciated.

Many thanks.

Darth
Attached Files
File Type: mdb Theatre Database Prototype.mdb (704.0 KB, 42 views)

darth sidious is offline   Reply With Quote
Old 04-29-2014, 07:55 AM   #2
TimW
Newly Registered User
 
Join Date: Feb 2007
Location: Crowland, Peterborough UK
Posts: 91
Thanks: 3
Thanked 11 Times in 10 Posts
TimW is on a distinguished road
Re: Form and query

Hi Darth
I think you need to look at your query. if you remove the parameters there are no results.
Therefore, if I were you, I would rewrite the query without the parameters to show all possible results and then add the parameters later.

I hope this helps
__________________
TimW

[Currently looking for a position...]
TimW is offline   Reply With Quote
Old 04-29-2014, 10:00 PM   #3
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

Quote:
Originally Posted by TimW View Post
Hi Darth
I think you need to look at your query. if you remove the parameters there are no results.
Therefore, if I were you, I would rewrite the query without the parameters to show all possible results and then add the parameters later.

I hope this helps

Ok will try this.

darth sidious is offline   Reply With Quote
Old 04-29-2014, 10:18 PM   #4
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

Hi

I have a problem with a query not finding a character from the character table based on the 'Production' and 'character' selected from the respective combo boxes on the form Activity2.

When I manually type this criteria in to the qryFindCharacter the query works. But it returns no results when I select the criteria from the combo boxes from the Form Activity 2.

For the life of me I can't figure out why. Any help would be greatly appreciated.

Thanks

Darth
darth sidious is offline   Reply With Quote
Old 04-29-2014, 10:23 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,653
Thanks: 98
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Form and query

Make sure the Bound Column of the combo holds the data required for the query.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
darth sidious (04-30-2014)
Old 04-29-2014, 11:15 PM   #6
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

The two combo boxes are unbound. I have used unbound combos on previous databases and it has worked fine but this time round its been so problematic.


Also, if use text boxes it works but not when I use a combo box. Weird.

Last edited by darth sidious; 04-29-2014 at 11:22 PM.
darth sidious is offline   Reply With Quote
Old 04-30-2014, 01:35 AM   #7
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

Hi

I have a query which takes criteria from the frmActivity2. What I would like to do is return the results of this query on the same form underneath the combo boxes from which the criteria is selected.

I have tried everything but cannot get this to work. Am I being really stupid about this??? As it stands I cant get it to work at all. I have attached the Database for info.

Any help would be greatly appreciated.

Many Thanks

Darth
Attached Files
File Type: mdb Theatre Database Prototype1.mdb (704.0 KB, 29 views)


Last edited by darth sidious; 04-30-2014 at 10:26 PM.
darth sidious is offline   Reply With Quote
Old 04-30-2014, 10:27 PM   #8
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

Please help re above.
darth sidious is offline   Reply With Quote
Old 04-30-2014, 11:16 PM   #9
bob fitz
AWF VIP
 
Join Date: May 2011
Location: Essex England
Posts: 3,528
Thanks: 52
Thanked 683 Times in 665 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Form and query

Perhaps something like the attached db
Attached Files
File Type: mdb Theatre Database Prototype1Bob01.mdb (932.0 KB, 37 views)
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
The Following User Says Thank You to bob fitz For This Useful Post:
darth sidious (05-01-2014)
Old 05-01-2014, 04:10 AM   #10
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

Quote:
Originally Posted by bob fitz View Post
Perhaps something like the attached db

Thanks that's great but I would need to see the field headings in the box below. How did you do this? and is the data in this box updateable?
darth sidious is offline   Reply With Quote
Old 05-01-2014, 05:08 AM   #11
bob fitz
AWF VIP
 
Join Date: May 2011
Location: Essex England
Posts: 3,528
Thanks: 52
Thanked 683 Times in 665 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Form and query

Quote:
Thanks that's great but I would need to see the field headings in the box below.
The "box below" is a listbox control. To see the column headings just change it's Column Heads property to Yes.
Quote:
How did you do this?
I created the listbox with the wizard and used the SQL of the query called qryFindCharacter as it's Row Source. I added one line of code to the After Update event of the three combo boxes.
Quote:
is the data in this box updateable?
No. I have attached another db which has a subform showing the same data as the listbox. This may updateable, I don't know because I haven't tried.
Attached Files
File Type: mdb Theatre Database Prototype1Bob02.mdb (644.0 KB, 28 views)
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
The Following User Says Thank You to bob fitz For This Useful Post:
darth sidious (05-01-2014)
Old 05-01-2014, 10:38 PM   #12
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

Quote:
Originally Posted by bob fitz View Post
The "box below" is a listbox control. To see the column headings just change it's Column Heads property to Yes.
I created the listbox with the wizard and used the SQL of the query called qryFindCharacter as it's Row Source. I added one line of code to the After Update event of the three combo boxes.No. I have attached another db which has a subform showing the same data as the listbox. This may updateable, I don't know because I haven't tried.

Thank you Bob!

I've been trying a subform for ages but could not get it to work. I used the wizard but to no avail. Could you tell me the steps you went through to get this working please.

Also, I want to ammend the qryFindCharacters to do the following:

IF the character gender matches the student gender on the same form (Activity 2) Then I would like 1 to be deducted from NumberNeeded(Characters table) and 1 to be added to the NumberAssigned(Charcters table). In the "Assigned Characters" table the ProductionID, StudentNumber and CharacterID would need appending and the GotJobInProduction in "Student" table would need to be ticked.

Therfore the button on the form would read "Assign Job" and in the subform rather than it showing 1 job needed and 0 jobs assigned it would show 0 jobs needed and 1 job assigned.

Mnay thanks for your assistance.

Darth

Last edited by darth sidious; 05-01-2014 at 10:45 PM.
darth sidious is offline   Reply With Quote
Old 05-01-2014, 10:45 PM   #13
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,653
Thanks: 98
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Form and query

Quote:
Originally Posted by darth sidious View Post
IF the character gender matches the student gender on the same form (Activity 2) Then I would like 1 to be deducted from NumberNeeded(Characters table) and 1 to be added to the NumberAssigned.
I have not looked at the detail of your requirement but generally incrementing and decrementing fields indicates you are storing derived values that aught to be calculated from the raw data.

This is not usually a good practice. Store the data and calculate the derived value each time it is required.

Don't worry that this can involve processing thousands of records to get the answer each time. Database engines are astonishingly fast at such tasks if the data is correctly structured and appropriately indexed.
Galaxiom is offline   Reply With Quote
Old 05-02-2014, 12:04 AM   #14
darth sidious
Newly Registered User
 
Join Date: Feb 2013
Posts: 86
Thanks: 17
Thanked 0 Times in 0 Posts
darth sidious is on a distinguished road
Re: Form and query

How would I achieve this.
darth sidious is offline   Reply With Quote
Old 05-03-2014, 10:16 AM   #15
bob fitz
AWF VIP
 
Join Date: May 2011
Location: Essex England
Posts: 3,528
Thanks: 52
Thanked 683 Times in 665 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Form and query

I would agree whole-heartedly with Dave's advice against storing calculated fields. I haven't studied your tables but it could be that you would need to restructure them in some way. Can you tell us in plain English about the need and purpose of your db.

__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query In Form Fails When Form Is Called Through Navigation Form guyuz Forms 2 01-28-2016 03:38 PM
(2007) easiest way to include query criteria in a query form weeblebiker Forms 4 02-24-2014 11:07 AM
Query by form, Query explodes after being closed for the first time, any help appre New to Access Queries 1 04-22-2011 03:35 AM
Combining 3 Queries to Form One Query With Columns from each query by month springa Queries 3 03-17-2009 01:13 AM
Search form will run query but will not open form based on same query mitchem1 Forms 3 06-27-2002 12:07 PM




All times are GMT -8. The time now is 03:51 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World