Moving the Record Pointer

[rossco]

I have the following code within the BeforeInsert event of a subform designed to increment the next page (record) number. All is good except the record pointer remains within the same record and doesn't move on.

Code: Select all

       Dim db As Database, rsData As Recordset
       Set db = CurrentDb
       Dim sSQL As String
       Dim PagingLastID As String
            
       PagingLastID = DMax("[PageNo]", "tblSectionF", "[CarID] = " & [CarID] & " AND [TypistCode] = '" & [TypistCode] & "'")
            
       Set rsData = db.OpenRecordset("Select * from tblSectionF", dbOpenDynaset)
               
       PagingF = PagingLastID + 1

       rsData.Close
       Set rsData = Nothing

What am i missing here, please?

Thanks

[HansV]

You open a recordset and close it again without doing anything with it.

What is PagingF?

[rossco]

Ah yeah re recordset - I guess you're right - oops! I also only need the last variable.

PagingF is the page number display field.

I took out all the superfluous rubbish but the cursor still doesn't move to the next record.

[HansV]

The Before Insert event of a form occurs when the user starts entering data in a new record. So when the user moves to a new record, nothing happens yet, but the moment the user types or selects anything in the new record, PagingF will be filled in. The code itself will not cause the subform to move to another record.

I guess I don't understand what you want to accomplish.

[rossco]

I want each subform to have sequential record numbering even though the Access generated autonumber most likely will not be sequential eg

Autonumber ... other fields ... Unique No.
2 ... other fields ... 400
6 ... other fields ... 401
18 ... other fields ... 402
23 ... other fields ... 403

[HansV]

You're retrieving the highest value of the PageNo field for the current CarID and TypistCode. Are CarID and TypistCode the fields on which the subform is linked to the main form? If not, they will probably be empty in the new record, so you won't get the value you want.

And is the PagingF control bound to the PageNo field? If not, I don't see how your setup could work.

[rossco]

The CarID and TypistCode are linked correctly in order to find the previous high value and PagingF is correctly bound to the PageNo field. For the database design purposes both the CarID and TypistCode links are needed but that is not the problem area. That line of code is producing the correct results and is not causing any locks or freezing. The allocation of the next page number all works perfectly.

The only problem is that once the record is filled with data by a typist and the next paging number generated by code, the pointer stays on the same record and does not move the next one for the next new data entry line.

[HansV]

Thanks for the clarification, it's very useful.

Can the user move to a new record by
- Clicking the New Record button on the toolbar (or ribbon), or in the navigation buttons?
- Clicking in the blank record?

If so, a property of the form may not be set the way you want. Try the following:
- Open the subform in design view.
- Activate the Other tab of the Properties window/Property sheet.
- Make sure that the Cycle property is set to All Records.