[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!

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?

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

Ok, I have got it....

Take me through the exact steps and tell me the problem that you are facing :)

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? :)

Quote:

---

Do you have any suggestions Mr. Waters?

---

I would prefer Mr. Syd Barrett :D

Well, I am about to finish work in 3 hrs time. Once I go home, I will go through it.

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!

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.

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.

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])

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?

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