Form and query (1 Viewer)

darth sidious · Apr 29, 2014

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.
:banghead:
Darth

TimW · Apr 29, 2014

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

darth sidious · Apr 30, 2014

TimW said:
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 · Apr 30, 2014

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 :banghead::banghead::banghead:

Galaxiom · Apr 30, 2014

Make sure the Bound Column of the combo holds the data required for the query.

darth sidious · Apr 30, 2014

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.

darth sidious · Apr 30, 2014

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

darth sidious · May 1, 2014

Please help re above.

bob fitz · May 1, 2014

Perhaps something like the attached db

darth sidious · May 1, 2014

bob fitz said:
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?

bob fitz · May 1, 2014

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.

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.

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.

darth sidious · May 2, 2014

bob fitz said:
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

Galaxiom · May 2, 2014

darth sidious said:
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.

darth sidious · May 2, 2014

How would I achieve this.

bob fitz · May 3, 2014

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.

darth sidious · May 3, 2014

bob fitz said:
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.

Hi

The database holds details of productions like wizard of oz and charlie and the chocolate factory. Students can be assigned to an actor role or a production job role.

Production jobs have up to 10 vacancies and actor roles if they are main characters can have 1 vacancy.

what i want the database to do is select a student and see what his/her preferred job is. Then assign that job to the selected student and store the character job in the assigned characters table and if it is a production job store the job in assigned jobs table. The jobs needed would need to be deducted by 1 and the job assigned would need to be incremented by 1. And the got jobinproduction for that student would be checked to yes.

Does this make it any clearer.

thanks

darth

ps bob can you list the steps amd the options you selected for the subform as i cant see how you did this.

thanks

darth

bob fitz · May 3, 2014

ps bob can you list the steps amd the options you selected for the subform as i cant see how you did this.

Use the wizard to create a sub form.
Select first option "Use existing tables and queries"
Next, Select your query "qryFindCharacter" from the drop down list of tables and queries.
Select all field to be used.
Then just select next and finish.
I will post again when I have had time to review your tables.

bob fitz · May 3, 2014

Can you clarify the following details:

I assume that each student can only be assigned to one character part and that same student can not be assigned a job in the same production but can that student have either an acting role or production job in both productions.

darth sidious · May 3, 2014

bob fitz said:
Can you clarify the following details:

I assume that each student can only be assigned to one character part and that same student can not be assigned a job in the same production but can that student have either an acting role or production job in both productions.

Yes that is correct

bob fitz · May 3, 2014

I have just been looking at your tables. If the details in table of students is not fictitious you may want to remove the db attachments you have made in this thread. Let me know if I need to do the same.

Form and query (1 Viewer)

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Attachments

Registered User.

AWF VIP

Attachments

Registered User.

AWF VIP

Attachments

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread