Results 1 to 12 of 12

Thread: [RESOLVED] Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide * Row

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Resolved [RESOLVED] Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide * Row

    I used MS Access 2007 to create a continuous form with the Allow Additions Property set to Yes. The user selects from a combo box and uses the After Update Properties of it and a "Find As U Type" text box, to filter the form. However, after an entry in the text box returns no records, I don't know another way to have the records (preferably All Records) reappear in the form without using Allow Additions.

    One of my forms has controls with calculated values, so not only is the visible New (empty) Row unattractive, it also has #ERROR Messages in some of its fields. Since these are supposed to be read only forms, I also had to set the Allow Edits to Yes and then include an [Event Procedure] in the Before Insert Property to keep it read only and I was wondering if there is a similar workaround for my Allow Additions property but I just can't see it (maybe I've been staring at it too long!)

    The "Find As U Type" combo and text boxes are from a cheater module I found on the following website. There is actually a lot of very helpful, versatile, free code provided for MS Access, I wish I had found it sooner!

    http://allenbrowne.com/AppFindAsUTypeCode.html

    Any suggestions would be greatly appreciated!

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Hello

    I am sure I have never worked with something like this earlier. However, I am willing to have a look at it if you post the project?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Absolutely! I uploaded the MS Access 2007 file to 4shared.com If you can't open it let me know. The forms to look at would be frmSummaryOutputs, frmDocMaintenanceHistory, frmAnnualReviewHistory

    http://www.4shared.com/file/wiYt3iVi...FinalDemo.html

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Ok, I have got it....

    Take me through the exact steps and tell me the problem that you are facing
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    It started when I added this find as you type text box. If you open frmSummaryOutputs: "Allow Additions" has to be set to "Yes" or when the filter returns no records, it will freeze the detail section empty. So, since it is read only I put an [Event Procedure] in the "Before Insert" to prevent the addition of new records. However, since I have calculated fields, the new (empty) row is giving me a #Error.

    I was wondering if I could use an If...Then statement that would basically If docType is Null Then Visible = False, I know the syntax is wrong, jus tthe basic idea here. Every document is assigned an docType at its creation so the only thing that wouldn't have one is a new record.

    To get rid of the #Error,

    IIf(IsError(Avg(DateDiff("d",DMax("dwfDateComplete","tblDocWorkFlow","dwfTitleNo = 2 And dwfDocID = " & [docID]),[DatePostedSharepoint])));"";Avg(DateDiff("d",DMax("dwfDateComplete","tblDocWorkFlow","dwfTitleNo = 2 And dwfDocID = " & [docID]),[DatePostedSharepoint])))

    could work for Step 10A and something similar for the other affected #Error Steps but I wold still see that empty row.

    Do you have any suggestions Mr. Waters?

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Do you have any suggestions Mr. Waters?
    I would prefer Mr. Syd Barrett

    Well, I am about to finish work in 3 hrs time. Once I go home, I will go through it.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Thank you Mr. Barrett! That is great stuff. My kids love dancing around with me to Love You, it is one of their favorite songs, too cute.

    Take your time, I'm sure when you get off work the last thing you want to do is dive right back into this stuff. I'm not sure what I want to do is even possible. I didn't understand all the complaints at first but I'm starting to see why everybody dislikes Access so much, when it gets beyond the basics there seem to be a couple of bugs that need workarounds. Not sure if one exists for this issue.....

    Thank you so much for your time!

  8. #8

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    One more thought. If there is any record where docType=null it can be hidden, it shouldn't ever happen but I don't think you can hide just one row on a continuous form, so based on this condition (empty value for docType, would be acceptable.

  9. #9

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Ok, I'm having a blonde moment. docID is my primary key for every form control source with this issue. So if I could set up an If docID=null Then Visible=False would make more sense. Sorry.

  10. #10

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    If you can't think of a way to hide a row where there isn't a value for docID, I have eliminated the #Error from steps 8, 9 , and 10A by adding a Nz([docID],0). For example Step 10A's control source now looks like this:

    =DateDiff("d",DMax("dwfDateComplete","tblDocWorkFlow","dwfTitleNo = 2 And dwfDocID = " & Nz([docID],0)),[DatePostedSharepoint])

  11. #11

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: Cont Form: Filter = Allow Additions or Show All if No Records Returned or Hide *

    Ok. I think I am good with the #Error workaround I described above. I am getting another error, ugh I thought I was done. When you select SOP Document Tracking from the SOP Document Management option group on the Main Menu, after you select a document from the popup window, an error message box pops up with the docID on it. Do you have any idea why and how to make it go away?

  12. #12

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: [RESOLVED] Cont Form: Filter = Allow Additions or Show All if No Records Returned

    Figured it out. It was on my frmMasterTabForm, Case Else MsgBox frmType.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width