Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    (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<pre>Surname: Mid([FullName], InStr([FullName], " ")+1)</pre>

    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:
    <UL><LI>Create an unbound form (no record source) in design view.
    <LI>Put a text box txtDate on the form, and a command button cmdOpenForm.
    <LI>Set the Caption of the label belonging to the text box to "Enter the date", or some such text.
    <LI>Set the Format property of txtDate to one of the date formats.
    <LI>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.
    <LI>Save the form as frmEnterDate.
    <LI>Change the parameter [Ask Date] in the query to [Forms]![frmEnterDate]![txtDate]
    <LI>Save the query (this query should be the record source of "frmMyForm").[/list]

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  5. #5
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.
    Attached Images Attached Images
    • File Type: png x.png (11.5 KB, 0 views)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Les,

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

  8. #8
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  10. #10
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  12. #12
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  14. #14
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •