Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Field Linking from Report to Form (Access 2003)

    In the after update event of as many fields as you need to satisfy the group by the query, run the query as a recordset to get the number of students that are already enlisted in the class. What you will have to do is to also ensure that the parameters are entered into the querydef.

    Say you query called qry1 is :
    SELECT Count(*) as Cntr FROM <table> WHERE Field1 = forms!formname!formfield1 And Field2 = forms!formname!formfield2 GROUP BY Field1, Field2

    Then in vba after update event of the two form fields, put:

    Dim rs as Dao.Recordset, qdf as Dao.Querydef
    Set qdf = CurrentDb.QueryDefs("qry1")
    qdf.Parameters("forms!formname!formfield1") = forms!formname!formfield1
    qdf.Parameters("forms!formname!formfield2") = forms!formname!formfield2
    Set rs = qdf.OpenRecordset()
    If Not rs.Eof then
    textbox = maxseatcount - rs.Cntr
    Else
    textbox = maxseatcount
    End If

    Where textbox is the name of the control on the form where the number of students is shown.
    Also you need to substitute Field1 and Field2 with the appropriate names.

    This is aircode, so you mayhave to tweak it.

    If you wish you send a cutdown version of your database.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Linking from Report to Form (Access 2003)

    Patt,

    You have gone above and beyond in helping me...I'm just lost on your explanation. It's above my head and I wish I could understand it. All I gather is concept here. Access is a monster sometimes...and it's always been my favorite...til today. Now I'm just exasperated, and want to take a moment out and thank you for all your explanations. It seems I am just not as savvy at Access as I want to be. I know it's working through problems like this that make us the wiser in the program, but this time I am just not getting it...the database cannot be paired down any or I will lose too much in the process of what I am trying to explain as my needs...It's 1.55 mg even after compacting, and 892K zipped up.
    Thanks for your patience...she may just have to use the macro I provided her to look up the class count remainder in the report.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Field Linking from Report to Form (Access 2003)

    Is this basicallly the same question you asked in <post:=654,393>post 654,393</post:>? It's confusing if you start multiple threads about the same subject.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Linking from Report to Form (Access 2003)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>. It only takes a few seconds, and it makes it a lot easier for others)

    Question: I have a textbox with a formula in it on a report. The formula is =[maxseatcount]-[countofclassno] which is the result of a query and a grouping effect on the report...a column in the report shows total no of students signed up for a class, and the formula takes that count and subtracts it from a field that tells the max number of students allowed.
    Is there a way to take the content of that text box formula on the report and put it's end result on in a locked field on a form? When the secretary enters a new student into the student registration form, I want the content of that report field to show on the form, so she knows if there are still seats available in the class as she registeres the student into the form. Right now all I've got is a button she can click on that will open the report and she can look up the class in the report alphabetically and see if there is room left. I really would like it to just have the result of that report formula visible on the form so she wouldn't have to use the button to go look it up in the report. Is there a way to do this. It seems complicated to me, so that is why I came up with the click the report button and go look it up idea...hey, I figure everything can't be completely automated, but I have seen it done.
    I have seen forms that when a class gets a new student, the student seat number drops by one, like the following example at a college I know:

    http://www.alamance.cc.nc.us/distance_ed/c...ine_courses.php and then click on any class name like computer 101

    Thanks for any feedback
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Linking from Report to Form (Access 2003)

    Yes, I suppose it is...Not sure how I did that. Sorry. I will post what I posted there as my last admission:

    This database was created after sitting down with a lead secretary and getting her input. She loved it. It did everything she wanted it to do. Then it was passed down to the secretaries who would actually be inputting data, and they had this last request. It was not an initial request, and came after the database was created, signed sealed, delivered and approved. Without having to completely redesign this whole thing, I thought there may be a way to instill the seats left result in a field "somewhere", without having to refer them to the report I created that shows them the result...I was looking for more convenient for them...something visible on the form as they enter a student into a class, that says...oh another student? You have 4 seats left...without looking that up in the report.

    I've designed a few databases in my time. I've taught databases for 12 years. I know there is so much to learn about this program, and consistantly try to learn it...I am a Master Certified Instructor...yet sometimes out of the blue...here comes a question I can't solve...I do not know it all...I admit that...if I did I'd been bored years ago teaching this application.

    I know the design is not perfect for the last request on this database. I was not asked to implement this into the databases original design...only now, and without disseminating the whole database and starting from scratch I thought there could be a way. It is okay. I can go with what I have. My hat is off to all of you as you are so much smarter in this app then I am, and have tried to help me through this difficult scenario...for that I really do thank you.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Field Linking from Report to Form (Access 2003)

    I think it's best to continue the discussion in the original thread. To avoid duplication, this one is locked. Please post all replies in the thread starting at <post:=654,393>post 654,393</post:>.

Posting Permissions

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