Form Based on Query Skipping Records

[PKW57]

Greetings,

I have a form that is behaving very strangely.

The form is based on a query that has only one criteria, that the DateClosed field in the underlying table "Is Null".

My test data consists of 24 records of which the first 5 have a DateClosed Entry. The query properly omits those records from displaying in the form.

My form has navigation buttons in the header created using the Next Record and Previous Record functions built into Access. They function normally until I get to record #8 when I have to click the button twice to advance to the next record. The same happens at record #10 and then at record #11 it gets really crazy.

When I try to advance to the next record from #11 (which of course should be #12) it jumps to record #20 and then proceeds normally to the end of my data set.

This same odd behavior also occurs going backwards using the Previous Record button. Using the standard navigation buttons at the bottom of the Form window also produces the same result.

Any suggestions as to what is causing this behavior would be greatly appreciated.

Thanks in advance for your assistance,


~ Phil

[pbaldy]

That is odd behavior. Can you attach the db here? I can't think offhand of what might cause that.

[Missinglinq]

What happens if you use the native navigation buttons to move through the Records?

Although we usually think of Forms, or entire Databases, when we speak of corruption, individual Controls, such as CommandButtons, can and do become corrupted. The test and the fix are one and the same; deleting the Control then re-creating it! If it was corrupt, you've verified the fact and solved the problem! If not, you've only lost a minute or two!

Linq ;0)>

[PKW57]

Paul,

Thanks for the rapid response. My db is slightly larger than the allowed maximum size.

I have placed a copy in my Google Drive at the below location.

https://drive.google.com/file/d/0B78...ew?usp=sharing

[PKW57]

Linq,

The native navigation buttons behave the same way.

[Missinglinq]

Your nav buttons are working correctly! If you look at the Query the Form is based on, you'll see that there are two duplicate Records with the IssueID of 0008...hence when you click 'Next,' it goes to the next Record, which also has an IssueID of 0008! Same thing for Records with IssueID of 0010...there are two duplicate Records! And there are no Records with IssueIDs of 0012-0019...hence the jump from 0011 to 0020!

With any Relational Database, it all begins with the data...and that's where you need to look to solve your problem. You're using Is Null as the Criteria to include Records...is it possible that you actually have Zero-Length Strings ("") in this Field for some Records, instead of Null?

Linq ;0)>

[PKW57]

Lesson learned! I never bothered to look at the query in datasheet view.

I went to the data table, selected each blank DateClosed entry and hit delete to clear out anything that might have been there. I don't know if this is an effective way to clean any stray zero length strings or not.

I also set the default value for DateClosed in the table to "=Null", but i believe that will only have an affect on new records.

In any event, the query is still returning the same results.

What would be the proper way to clear out zero length strings from the existing records, and is setting the default to "=Null" the correct way to guard against this in the future?

Instead of "Is Null" in the query criteria, is there a way to specify that there is no date?

I last programmed in Access back in 97/98 - a lot has changed since then and I've forgotten a lot as well, so I'm having to relearn many things.

Thanks for your help!

[Missinglinq]

I had a little more time to work on your file today, and while, when actively developing Databases, I seldom used multi-table Queries (I'm a firm believer in the One Table/One Form approach, utilizing Main Forms/Subforms for data entry into multiple Tables,) I looked at the SQL for yours and found that you'd used an Inner Join. You probably need to go into Help and search on 'Inner Join,' but here is the most pertinent part, I think:

Inner joins combine records from two tables whenever there are matching values in a field common to both tables.

Records with IssueIDs of 0012-0019 are missing, in your Query, and hence your Form, because they don't exist in the Table named IssueActions, so their are no matches for them!

As I suggested, before, you really need to go back to the beginning, and get the data straight, before proceeding.

As a start, go into Query Design View, right-click on the join line between the two Tables, click on Join Properties, then select the second option, and see if that gives you the desired results.

Linq ;0)>

[PKW57]

Ah-ha. I see what you mean.

I had done something like this once in the past but there was only 1 record for each subform. The way I have it set up now, the query will never have the records I want in the way I want to display them, regardless of the join type.

I'm not certain what you meant when you said "I'm a firm believer in the One Table/One Form approach, utilizing Main Forms/Subforms for data entry into multiple Tables". I have two tables and two forms and I'm trying to use mainform/subform for data entry.

Surely there must be a way to use a mainform/subform when there are multiple records in the subform without creating duplicates of the mainform.

If you've got any more suggestions I'd appreciate it. In the meantime I'm going to keep plugging away. If I can't finesse it maybe I can beat it into submission using brute force.

I appreciate your taking the time to look at my problem and you have my most heartfelt thanks.

~ Phil

[PKW57]

All,

Thanks for your time and paitence.

I have solved my problem by creating a subform query with the Unique Values property set to Yes.

[Missinglinq]

...Surely there must be a way to use a mainform/subform when there are multiple records in the subform without creating duplicates of the mainform...

Sorry for the delay in responding, but we got eighteen inches of snow over the weekend and was busy yesterday trying to dig out!

A Main Form/Subform configuration, typically used for a one-to-many relationship, consists of one Record in the Main Form and one or more Records in the Subform.

Glad you got it figured out!

Good luck with your project!

Linq ;0)>