Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17

    Autofilling a form based on Query by form

    I have 3 unbound controls in the header of a form. i have the recordsource property of the form which selects * from the underlying table where each of the 3 controls equals the 3 fields in my table. the SQL is correct and this displays in a dataset. however I need the form to display all the fields on the form. I've got a button on the form which I'd like to click in order to get the fields displayed. I've tried Refresh and Requery etc but nothing works.

    I know this can be done and I think I'm missing something obvious. can someone please give me a line by line plan of action please?

    thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What do you have in the Detail section of the Form?

  3. #3
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17
    they're all bound controls based on the only table I'm using

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So you duplicate them in the Header of the form? The Current Event of the Form can fill the header controls if you want.

  5. #5
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17
    in the header I input firstname lastname and academy. my query retrieves a single record based on these parameters (I know this works because I can see it in a datasheet). my problem is displaying the record in the detail section.

    thanks

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What is the SQL for your query?

  7. #7
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17
    SELECT Main.ULN, Main.fname, Main.lname, Main.academy, Main.startdate, Main.DateRecd, Main.DateOn, Main.RFT, Main.Programme, Main.Compliant, Main.DateCompliant, Main.DaysOverdue, Main.ComplianceTargetDate, Main.DaysToBecomeCompliant, Main.BKSBDate, Main.App, Main.PLR, Main.Certs, Main.BKSB, Main.ELI, Main.[H&S], Main.AppAgr, Main.LrnrAgr, Main.ILP, Main.SelfDec, Main.CovSheet, Main.KeyedBy, Main.TPApp, Main.TPPLR, Main.TPCerts, Main.TPBKSB, Main.TPELI, Main.[TPH&S], Main.TPAppAgr, Main.TPLrnrAgr, Main.TPILP, Main.TPSelfDec, Main.TPCovSheet, Main.TPFunding, Main.ExemptMaths, Main.ExemptEnglish, Main.ExemptIT, Main.TCFunding, Main.[AGE App]
    FROM Main
    WHERE (((Main.fname)=[Forms]![Update Learner]![firstname]) AND ((Main.lname)=[Forms]![Update Learner]![lastname]) AND ((Main.academy)=[Forms]![Update Learner]![acad]));

    The where clause is for the 3 controls in the header

    thanks

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What code do you have under the button? Let's modify it so we can tell what is going on.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Me.Requery should do it so there must be some other issue. Add this after the Me.Requery and it should tell us something:

    MsgBox "Record count is :" & Me.Recordset.recordcount

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is this a split form or a form with a subform control and subform datasheet? If the latter, the wrong form is probably being queried.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17
    it's a single form: header, detail, and footer sections.

    thanks

  12. #12
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17
    just done some messing about. I've amended the onclick property of my control that should contain the me.requery and invoked the populate update learner query (the sql that I sent the other day). i have this displayed in a datasheet and the query works as intended- returning one record. So, when I run the query and display in a datasheet it works, when I run me-requery nothing is returned, and a msgbox with recordcount shows 0

    thanks

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Well it is the RecordCount = 0 that is controlling this situation. Is the query a static query (saved with a name)?

  14. #14
    lancham is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    17
    hi , the record source for the form is saved under the name 'populate update learner form' which calls the SQL that I've already sent. The on click event now contains:
    Public Sub Command122_Click()
    DoCmd.Requery
    Me.Refresh
    MsgBox "Record count is :" & Me.Recordset.RecordCount
    DoCmd.OpenQuery "populate update learner form"
    End Sub

    and returns 0 for record count

    thanks for your continued help

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does the Record Source of your form say "populate update learner form" or is it the SQL you posted?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 06-03-2014, 05:15 AM
  2. Autofilling controls on a form
    By yuvraj.date in forum Forms
    Replies: 13
    Last Post: 09-18-2012, 12:53 PM
  3. Replies: 1
    Last Post: 01-25-2012, 06:46 PM
  4. Autofilling when New Record Created from Form
    By SpdRacerX in forum Forms
    Replies: 6
    Last Post: 01-24-2012, 10:24 AM
  5. Replies: 3
    Last Post: 01-24-2012, 02:44 AM

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