How to test for EOF on Query driven form

[dickhob]

I have a single sheet form driven by a select query. As the user completes information I control the advance to the next sheet by setting the form’s cycle property.

When I come to the end of the file, the form hangs. I have EOF processing to do but can find no way to test for EOF.

Is there a way to test for EOF on a query driven form?
Thanks

 

[Eupher]

How about -

If Me.Recordset.EOF Then

HTH

Ken S.

 

[dickhob]

The ME.Recordset.EOF does not appear to be working, but it may be my placement. I put it in the form ONCurrent event.

In debug I can't display a true/false value, but if I test for FALSE my eof code executes.

I know I am getting to the end of the recordset because it contains 130 records and current record counter is 131.

Where is the best place to set this code?
Thanks
Dick

 

[Eupher]

Hi, Dick,
I'm still a little fuzzy on how your form works. Could you provide a little more detail? Maybe post some code?

Ken S.

 

[dickhob]

I hope I can explain this clearly and still be brief. THINK PLYWOOD.

We are assembling laminated parts of varying complexities. The order and placement of the various parts are critical. A series of drawings have been made to visually show what part goes where. Each lamination has a corresponding drawing that may show placement for zero to X parts (usually a 1 to 4). Each part has a barcoded number. Therefore lamination A contains part 1. Lamination B contains parts 2,3,4. Lamination C contains part 5,6 etc. A lamination with zero parts is considered an instruction, and is coded as an instruction in the lamination record.

Using a select query, the form reads a lamination table containing links to the drawings, which are displayed as images on the form. I then programmatically load the corresponding parts for the lamination to a grid on the form. (Think main form and subform, but I create the subform). As the parts are assembled, the barcode is scanned. The scanning updates the grid by changing font to bold and background color. This indicates the part was used and was used in the proper sequence. When all of the parts associated with a lamination have been scanned, the query loads the next lamination, I reload the grid and the process is repeated.

I cannot use a subform because I need to update INDIVIDUAL cells (row and field) on the grid. With a subform if you update FIELDx you update all instances of FIELDx, not FIELDx, row2. I have asked on several Access boards if this can be done using a subform, but have never received an answer.

The form is loaded from a select query on the laminations table and the parts grid loaded in VBA in the OnCurrent event.
I use the forms Cycle property to manually control page movement from lamination to lamination.
I control the form with a single input field that updates when a part number is scanned.
The AfterUpdate event triggers the checking/updating of the grid cells.
When all cells have been bolded, I reset the Cycle property to load the next lamination.
If the lamination is an instruction (has zero parts) any scan will advance to the next lamination.
The process repeats itself.

That is the bare bones gist of the program. There are other factors, but they are not relevant here.

The particular assembly I am testing has 130 laminations and 236 parts. The lamination drawing at the end of the file are instructions and have no parts. In other words I cannot use the parts table to test for EOF because I have several instructional drawings to display before I can close the form.

I am trying to find the end of the lamination table so I programmatically can close this form and load a follow-on process.

The ME.Recordset.EOF APPEARS to work, but I don’t believe I am placing the code in the correct place. I presently have it at the beginning of the forms OnCurrent event. I say appears because when I test for EOF false, I execute the close form and follow on process, but I’m not sure this is a valid assumption.

I am considering simply placing a command button on the form to allow the user to close it. Then I have to deal with premature closings- a new problem.

Sorry for the long post. I hope this helps and states the problem clearly enough.

Another thought. Is there a way to open the lamination table in SQL and KEEP IT OPEN for the entire life of the program? I only seem to be able to open a table for a cycle, close it and then open it again for the next cycle- which starts from the beginning of the file. If I could open the lamination table and keep it open, I could move through the table and test for EOF in code that way.

Thanks for your help