Results 1 to 8 of 8
  1. #1
    jonny dexter is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2010
    Posts
    7

    datasheets and record selection

    HI - hope this is in the right place didnt know whether to put in forms or here.



    Heres my problem- i currently have a main form which holds a tab control with 5 tabs.

    the main form runs a query on opening to retrive a bunch of records. no problem here.

    each tab then displays a subform which filters the records into respective months and shows them as a datasheet. each tab shows one months records only. also no problems.

    i have a button on the tab sheet which will allow the subform's records to be filtered again, based on a name in one of the columns. this works.

    once i have only the records i need, i want to be able to click on the record selector on the data sheet and get then display the selected record it a more readable view - either above/below the tabbed area or even in a new form.

    what is the best way to extract the information from the datasheet. it does not have to be editable but it would be useful ( i can edit on the datasheet if really necessary).

    many thanks in advance

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I often use this technique to open a form with detail on a given record:

    BaldyWeb wherecondition

    I typically put it in the double click event of a textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jonny dexter is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2010
    Posts
    7
    thanks for the fast reply. i took a look at the link and can see what is happening with the code.

    having looked a having a form pop up i have decided it would be better to have a second subform under the first which shows the full record information.

    how could i get this to move to the record that i click on in the datasheet, i think the fact it has been filtered twice, is causing my some confusion as to how to make sure i have the actual record rather that the index of the filtered one ?

    thanks

    EDIT.

    Having totally rewritten what i had initially, i have decided to use a new form to show the details. using the code from the above link to open the form, i find it does not show the selected record but moves to a new record ??
    I have checked that the correct record number has been passed into the 'docmd' but i dont get the record showing in the form. any suggestions ?
    Last edited by jonny dexter; 07-28-2010 at 03:49 PM.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is your exact code? What is the data type of the field being used? To clarify, you're passing a value for the key field, not a "record number" if by that you mean "the 7th record" or something along those lines.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jonny dexter is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2010
    Posts
    7
    the event code is as per your link

    Code:
     
    Private Sub Form_Click()
     
    DoCmd.OpenForm "Bookings", , , "Booking_ID = " & Me.Booking_ID
    End Sub
    where 'booking_ID' is an autonumber field for the 'bookings' table
    i have tried adding the 'acFormEdit' in as well but to no avail.


    as my form is currently populated with all records ( i am sorting the filtering at them moment) i suppose it is logical that the number i am seeing passed through would match that of the record even if it were not the record number and is in fact the key field as you are suggesting.

    how do i get the new form that is opening to show the record from the row on the datasheet that the user clicked. i thoughtr i may have to use a 'select' as the records have been filtered twice by the time the user clicks on it. Would this be right ?

    sorry if i am not explaining to well

    EDIT

    i have just sorted the filtering out for the first tab, and it does indeed pass the correct Booking_ID to the event to open the form but the form moves to a new record entry instead of the existing one.
    i have also examined the form properties in design view and the filter property shows the setting "Booking_ID=1839" which is the record number i selected.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is there any code running in the form being opened, or is its Data Entry property set to Yes?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jonny dexter is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2010
    Posts
    7
    ok i have solved the problem with the form jumping to a new record.
    it was a mistake on my part with the query that was running on form open.
    all of the records were prepopulated with some information that was requested, the query only returned records that i have played about with and had the full data required to match the query. So if a record was not complete it was trying to create a new entry in the table. i modified the join in the query to return all records and it appears to be ok know.

    Thanks for you help with the form issue that now works a treat, and has saved me a lot of time.

    many thanks

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change to selection in combo box
    By corystemp in forum Access
    Replies: 7
    Last Post: 06-28-2010, 10:20 AM
  2. Replies: 1
    Last Post: 05-31-2010, 05:18 PM
  3. Bring up record according to list box selection
    By dlewicki in forum Programming
    Replies: 4
    Last Post: 03-01-2010, 03:54 PM
  4. Subform Selection
    By doug adams in forum Forms
    Replies: 2
    Last Post: 06-28-2009, 10:27 PM
  5. Data selection
    By cesander in forum Forms
    Replies: 0
    Last Post: 09-18-2008, 02:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums