Errors galore!

[Wavona]

Good morning Access Programmers,
I need some help with a form and its underlying select query.

I have a form that returns the details of a class, its tutors, how many places are available on the class, how many are taken, and how many are available in total.

Initially, the underlying query was an uneditable recordset, which prevented me from editing or adding data while in Form mode. However, I managed to alter the query to make it an editable recordset. The problem now though is that the query returns duplicate records of the same courses! Selecting "Unique records" in the Properties of the query makes no difference, and selection "Unique values" makes the query uneditable again.

Please could you help solve this for me? I attach the spreadsheet of the query, which hopefully demonstrates what the problem is!

Thanks,
Anna

 

[gemma-the-husky]

the question is, I would think, why the query returns duplicates, when you do not set unique records.

what IS the query designed to do?

 

[Wavona]

the question is, I would think, why the query returns duplicates, when you do not set unique records.

I don't know what you mean by 'set unique records'. If you mean does the query hold unique records the answer is yes (Class ID).

what IS the query designed to do?

The query is designed to bring together data from 2 different tables: Classes and Students & Classes, which I then display in a form.

When the data from these two tables is merged together in a query, everything is fine. The query returns the correct number of records, and the query is editable.

The problems start when I want to add a third query to this table. The third query (qryResults) returns how many places we have on each course, how many are taken up by students, and how many are left available.

When I add qryResults to my existing query (qryTestAvailability), the latter becomes uneditable, probably because qryResults has calculations, and so does the Form which is linked to it, hence my problem.

Is this any clearer?

Thanks,
Anna

 

[JHB]

Use a recordset to run and get the result from the third query and show the result in unbound control(s) on the form.
The code for the recordset could be placed under the form's "On Current" event or even in a module.
Another way is to have a subform only showing the result from the third query.

 

[Wavona]

Use a recordset to run and get the result from the third query and show the result in unbound control(s) on the form.
The code for the recordset could be placed under the form's "On Current" event or even in a module.

Many thanks JHB for your response, which is much appreciated

I am not a programmer so I wouldn't know where to start with writing the code. Could you give me an example please?

Thanks again,
Anna

 

[JHB]

Place the below code under the form's "On Current" event:
Create an unbound/or more control(s) on the form.

Remember to change the query name, your unbound control name and the result fieldname from the query, to what your call them.

  Dim dbs As Database, rst As Recordset
  
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("YourQueryName")

  Me.YourControlName = rst![YourFieldNameInQuery]

Else post a striped version of your database with some sample data, (zip it), + information about which form the result should be showed.