Sort Data In Query and OPening a Form (2002 sp2)

Topic

New Reply

Hello all,
I am here again! 2 Problems this time (1) I need to sort a Column for Name by surname ascending .Name is like this Bert Smith my database sorts by the first name
Will I have to create 2 columns 1 for first First name and one for Surname or can this be done another way.
(2) Is there away of opening a form to specific criteria I need to open a form to a specific date (Ask Date) On Open to give me a choice of which date to use to open form
Any help,

Reply | Quote

Replies

(1) It would be best to have separate columns for first name and surname in the table from the start, and use these columns for data entry. If you already have existing data with the full name in one field, it is worthwhile to split it and use separate first name and last name fields from now on. If that is not feasible, you will have to create a calculated column for surname in a query. If all names are of the form “Bert Smith” and not “Bert L. Smith” or “Bert Lawrence Smith”, it is relatively easy: if your full name field is imaginatively named FullName, you can use

Surname: Mid([FullName], InStr([FullName], " ")+1)

to get the surname. You can then sort on this calculated column.

(2) You can use a parameter query for this. If you put [Ask Date] in the criteria line under a date field in a query, Access will prompt you to enter the date when you open the query, or a form based on the query. A more elegant approach is to use a form in which the user can enter the date:

• Create an unbound form (no record source) in design view.
• Put a text box txtDate on the form, and a command button cmdOpenForm.
• Set the Caption of the label belonging to the text box to “Enter the date”, or some such text.
• Set the Format property of txtDate to one of the date formats.
• Create an On Click event handler for the command button to open the form:

  Private Sub cmdOpenForm_Click()
  DoCmd.OpenForm “frmMyForm”
  End Sub

  where frmMyForm is the name of the form to be opened.

• Save the form as frmEnterDate.
• Change the parameter [Ask Date] in the query to [Forms]![frmEnterDate]![txtDate]
• Save the query (this query should be the record source of “frmMyForm”).
  [/list]

Reply | Quote

Hans,
Thanks for reply, tried both and got them both to work.
With regards to sort on surname gone to 2 columns.
Open form with date did not produce just what I wanted. I have a data entry form based on a Query and Tables, i need to enter data before the Horse show to produce the Class Sheets which you have sorted, then go back into the data entry form to enter the results after the show ie Place Points and championship. With just one date it is relatively easy but I have 3 dates to work with, I need a way of using a date to retrieve all the data input for that date so I can update the records. I have tried basing on a query only but when the form comes up it does not allow me to update the records and I also loose all my automatic input of data. Help Required Zip Attached

Reply | Quote

Hi Les,

It is not clear to me what you are asking. Do you want to know why the query [Entries All] is not updateable? That’s because the Unique Values property of the query has been set to Yes. Unique Values queries are not updateable. The form in the database you posted has nothing to do with this query, so I don’t know what you mean by “when the form comes up it does not allow me to update the records”. It is also not clear to me where the three dates you mention come into this.

I don’t understand the structure of your tables and the relationships between them. The tables Members, Entries and [Horse or Pony Name] each contain fields Member(s), [Horse or Pony Name] and CombinationID. You have a direct relationship between Entries and Members, but also an indirect one via [Horse or Pony Name].

So, frankly, I don’t understand your database at all. Can you try to explain what it should do?

Reply | Quote

Hans,
Sorry for misleading you Form based on various tables.
We run 3 Horse shows a year hence the 3 dates.
We have input data (Entries to the Show) to the entries table and this is queried by (Query Entries All )for each date prior to the show, and produce Class Sheets
see prev posting (re: 268506 from esac How to print fixed number of lines on a report). after the show has taken place I have to update the data with the place an entrant finished etc. This process has to be done for each show date and I need some way to open with a Show Date and to see all the data for that date only.
At present if I open the Entries form it will show all the data entered. (See updated Zip) As you can imagine with 60 different classes and 200+ entries per Show,
It would take a long time to scroll through the form to find the correct date and the correct class and the correct Entry to update. I have added a couple more entries to the Zip file on different dates. Hope this explains what its supposed to do. The full database does what i want except for sorting data via the form by date. hope you can help

Reply | Quote

Les,

You could place an unbound combo box in the form header. Its row source is the Show Date table. When the user selects a date, the form is filtered to show only records for that date. Name this combo box cboSelectDate. We use the After Update event of the combo box for this:

Private Sub cboSelectDate_AfterUpdate()
Me.Filter = “[Show Date] = Forms!Entries!cboSelectDate”
Me.FilterOn = True
End Sub

BTW, I still don’t understand the structure of your database. Several fields are duplicated between tables, and as I wrote in my previous reply, the relationships are confusing. I have attached a picture of a possible simpler structure. In which the Entries table is the central table. Its primary key is on Member/Horse or Pony/Show Date.

Reply | Quote

Les,

I have attached a zipped database that illustrates the modified table structure I mentioned in my previous reply.

Reply | Quote

Hans,
Thanks for Zip and updated Version much neater than mine I have not yet tried it out as its to near our next show, I will carry on with mine for this year which will give me time to look at yours and probably put it into practice also i will have more knowledge on Access as I said before I am new to this! Will the relationships you show
also refer the Horses name to a Member as a combination as i member could have many horses or 1 member could have a horse with the same name as another Member this is vital for my purposes. Still having problems filtering data don’t know what I am doing wrong sure I am following your instructions correctly. ( Is it Possible to filter data by date and by class No) I will leave you alone now and thanks for all your assistance

Reply | Quote

Hi Les,

In the setup I proposed you can have horses with the same name. The Entries table has a primary key on the combination of show date, member and horse/pony. These combinations must be unique, but each individual field can be repeated.

It should be possible to filter the data any way you like by creating queries.

Good luck with the further development of your database. Don’t hesitate to ask specific questions if you want more assistance.

Have a good time in Longdendale.

Reply | Quote

Hi Hans’
Your comment about Longdendale took me by surprise, The Lake District of THe Peak District also UFO alley.
Have you been checking up

Reply | Quote

Hi Les,

Ten years ago, I spent a long weekend in Manchester. I had a few hours to spare, and since the weather was fine, I took a train to Hadfield and walked along the reservoir. I still have some nice pictures from that walk.

Reply | Quote

Hi Hans,
That solves it, I live not far away from Hadfield Station, The Longdendale trail up and running now its the old railway line to Sheffield via the Woodhead Tunnel its about a 5mile walk to the Tunnel, from Hadfield Station it passes all the 4 Reservoirs, The wife and I often take the dogs for a walk, been around all 4 resovoirs.
Zip attached

Reply | Quote

Hello Les,

Thanks for the pictures. One of mine was taken at almost the same spot as your first one. There were fewer (or smaller) trees then.

Reply | Quote

Hans,
Yes I am back again! Trees Have Probably grown a lot but they have planted more. Attached standard vba script for not in list I have altered to check horse or pony name not in list but it doesn’t check against a combination of member and a specific horse name it only checks against the horse name and adds it.
I have to manually change my horse and pony name table,is there a way to alter vba (Not really got my head round vba yet I am awaiting a book) to query against combination Id without altering all my database if the horse is not in the list it advises accordingly Hope you can help

Reply | Quote

Hi Les,

I’m afraid that I don’t understand the problem. Why should you check for a combination of member and horse/pony if the user enters a new horse/pony name? The horse/pony name doesn’t occur, so no combination of that horse/pony name with a member occurs either. Or am I completely off the mark?

Reply | Quote

Hans,
Sorry did not explain correctly . Say
member 1 has a horse called Fred as a combination , member 2 has a horse called Bert as a combination when horse name entered no tigger, if mem 1 gets a new horse called Harry and i enter name it triggers the (not in list), but if member1 gets a new horse called Bert it is a new combination but the (not in list )does not trigger as the horse is already in the list, In the last instance I need the (not in list) to trigger to add a new combination Hope this makes sense

Reply | Quote

Les,

I am afraid you can’t make the Not In List event trigger if the user enters a name that is already in the list – it’s contradictory.

If you want to keep the present structure, you should change the row source of the combo box to show only horses/ponies for the current member. The row source could look like this:

SELECT [Horse or Pony Name].[Combination ID], [Horse or Pony Name].[Horse or Pony Name] FROM [Horse or Pony Name] WHERE [Horse or Pony Name].Members=Forms!Entries!Member;

You must update this row source when you move to a different record and when you select a different member:

Private Sub Form_Current()
Me.[Horse or Pony].Requery
End Sub

Private Sub Name_AfterUpdate()
Me.[Horse or Pony].Requery
End Sub

Since the “horse or pony” combo box now only displays animals for the current member, entering a name that is already listed for another member will still trigger the Not In List event.

Reply | Quote

Hans,
Sorry not replied sooner been busy with Horse Show this weekend, your solution re the not in list worked great, just in time for the show. Now I have another I need some advice on. A bit back I asked you how to filter dates on a form your suggestion was an unbound combo box (see post 271713) got this working but when form opens showing all records for all dates they are in Class No order, when I select a date it shows the records out of order. At moment I have 2 dates 18/05/03 and 13/07/03. When I open form it shows all dates and all records in Class order Ascending, When I select The 13/07/03 from the combo box the records come up in a random order, then If reselect 18/05/03 from the combo box it retrieves the records in class no ascending order
Any Ideas, Sorry still waiting for books to assist me Attachment showing code you gave me

Reply | Quote

Hi Les,

This kind of thing usually occurs if the record source of the form doesn’t specify the sort order explicitly. So open the record source of the form, and set it to be ordered by Class No.

Reply | Quote

Hello Hans,
Sorry not got back sooner all codes work I have even created one of my own, amending one you sent me, Been busy with horse show printing classes sheet etc,
I have one more area i cannot resolve I have a Query which Gives me everyones points gained by GROUPED BY SUM, but i dont want the sum 0 to show. I have tried to put in Criteria, like Not”0″ not Sum of group=0 and all variations i can think of but cant get query not to show sum The Sum 0.
Any Ideas

Reply | Quote

Hi Les,

Since the sum is a number, you shouldn’t use quotes around the value – quotes are for text values. Try

0

in the criteria line. Note: that is 0 but without a space between

Reply | Quote

Hi Hans,
Thanks for reply couldn’t get yours to work then checked the relevant tables etc and found i had set to text instead of number, works fine now with>0
Dum or wot!
Sorry to bother you i have 2mths before next show will try your suggestions for the database and let you now

Reply | Quote