Loading form starting at bottom

[brett429]

I'm converting an Excel sheet over to a database, so for ease-of-use, I'm trying to mimic the style of a spreadsheet as closely as I can. In Excel when you're working on a sheet and you save it, when you re-open it, it starts wherever you left off (so if you're scrolled half-way down the sheet when you close it, it starts off half-way down the sheet when you open it again).

Is there any way I can do this with a continuous form? I tried telling it to set focus on the new record when it loads, but then it displays ONLY the new record and you can't see the exisisting records unless you scroll up (which I don't like). I just want this thing to load starting at the bottom of the records. Any thoughts?

 

[sbenj69]

what happens if you use this in the on load event?

docmd.gotorecord , , acLast

Or are you trying to get some sort of locator, like if you were on record 201 of 456, open back up to 201? If that's the case, then it will be a little more complex

 

[brett429]

what happens if you use this in the on load event?

docmd.gotorecord , , acLast

Or are you trying to get some sort of locator, like if you were on record 201 of 456, open back up to 201? If that's the case, then it will be a little more complex

The code you gave me is exactly what I want... but what it does when you load the form is... it only displays the last record and then under it are the new record fields. All the records above the last record are hidden until you scroll up. That may just be how Access does it... in which case, perhaps I should have it go to the 20th record from the bottom when it loads... is that possible?

 

[Mike375]

Here is a crude way to get to the record you were last on

Add another field to the table (and add it to the query).

Pick a field to ClickOn which will close the form. Thus remove the control box from the form so that option to close is removed. However, before it closes the form it sets the value of the new field (for that record) with an X.

When you reopen you include Find Record and the record will be the record that has X. At the end of the code or macro you use to find the record have it set the value of the new field as Null.

You would also include some type of sort either in a field on the query or on the form open event before the FindRecord.

 

[brett429]

Here is a crude way to get to the record you were last on

Add another field to the table (and add it to the query).

Pick a field to ClickOn which will close the form. Thus remove the control box from the form so that option to close is removed. However, before it closes the form it sets the value of the new field (for that record) with an X.

When you reopen you include Find Record and the record will be the record that has X. At the end of the code or macro you use to find the record have it set the value of the new field as Null.

You would also include some type of sort either in a field on the query or on the form open event before the FindRecord.

Thanks for the idea! I think the other method of going straight to the last record is ideal, though... I'm just trying to find a way around the way Access has it show up (it likes to display the record you're jumping to at the top of the screen - I'd like it to show up at the bottom of the screen). Any ideas there?

 

[Mike375]

Thanks for the idea! I think the other method of going straight to the last record is ideal, though... I'm just trying to find a way around the way Access has it show up (it likes to display the record you're jumping to at the top of the screen - I'd like it to show up at the bottom of the screen). Any ideas there?

Do as I suggester but add to the find record a gotonext or go to previous.

Make a simple macro that just has GoTo Record and select previous and another macro with a RunMacro action (running the first macro) for whatever number times you decide. In fact count how many records the screen displays, lets say 20, then run the macro 10 times and your previous record should be in the middle of the screen when you open the form

 

[sbenj69]

well, if you were going to do something like that, then you could do something like this:

Say your screen shows 20 records at a time......

Dim X as Integer
 
Docmd.gotorecord, , acLast
 
'Go Halfway Up the Screen (20 records displayed, go up 10)
For X = 1 to 10
Docmd.gotorecord, , acPrevious
Next X
 
Docmd.gotorecord, , acNewRec

That's simple and doesn't get too heavy into code..... you could, of course do a dcount, and then figure out where to go from there, but that's a little more coding than I have time to do right now, as I'm at work

Edit: Here is a sample

 

[brett429]

well, if you were going to do something like that, then you could do something like this:

Say your screen shows 20 records at a time......

Dim X as Integer
 
Docmd.gotorecord, , acLast
 
'Go Halfway Up the Screen (20 records displayed, go up 10)
For X = 1 to 10
Docmd.gotorecord, , acPrevious
Next X
 
Docmd.gotorecord, , acNewRec

That's simple and doesn't get too heavy into code..... you could, of course do a dcount, and then figure out where to go from there, but that's a little more coding than I have time to do right now, as I'm at work

Edit: Here is a sample

I have no idea what you did, but it worked PERFECTLY. Thank you!!!

 

[sbenj69]

I have no idea what you did, but it worked PERFECTLY. Thank you!!!

Pretty simple really.....

Went to the last record, moved up 10 records, and then went to the new record.

If you don't have 10 records you might get an error msg..... which is why I said something about using dcount.

Anyhow, glad I could help.

 

[brett429]

Pretty simple really.....

Went to the last record, moved up 10 records, and then went to the new record.

If you don't have 10 records you might get an error msg..... which is why I said something about using dcount.

Anyhow, glad I could help.

Oh that won't be a problem, we'll always have enough records