Use data entry form to scroll through existing records

[dizzydangler]

Hi, all! I'm a new user, looking to get some help on a form problem in MS Access 2007 I haven't been able to crack.

I've built a form that prompts users to enter new record data, then click "Update Database" to save the record to the table. Each entry creates a new record in the original table (all data goes to the same table).

I've used the button wizard to add a "previous record" and a "next record" button, which work in the sense that they allow the user to scroll forward and backward through all the records they've input in a single "session" (one session = whatever happens between the time the file is opened and the file is closed) However, when a user arrives at the first record they entered and clicks "previous record" again, the message "You can't go to the specified record" is displayed. I thought this might have something to do with the macro, so I went into the "GoToRecord" action in both the "previous record" and the "next record" macros and added the following arguments:

OBJ Type: Table
OBJ Name: Table1
Offset: 1

Now, when a user goes to the first record entered in the session and clicks "previous record," the error message "The object 'Table1' isn't open."

So I went back to the navigation pane, opened up Table 1, then went back to the form and tried to click "previous record" again. Back to the original "You can't go to the specified record" message again.

I tried closing the database after entering records, then opening it up again, and when I clicked the "previous record" button when the form opened with all blank fields in the control boxes, the error message "You can't go to the specified record" displayed.

I'm about at the end of what I know to do and try--if anyone has any suggestions/hints, I'd appreciate it! Thanks!

[missinglinq]

You need to go into the Properties sheet for the form (don't know exactly how you do that in 2007) and set Data Entry to No.

The name for this property is very misleading! Having it set to Yes is not necessary in order to enter new records! Having it set to Yes means that you can only enter new records! You cannot view pre-existing records, which is where your problem lies!

Remember to remove the things you added to the macros.

Linq ;0)>

[dizzydangler]

OK, that worked! (you're right, the terminology was misleading--never would have guessed that)

Only problem now is that the form opens with the data from the very first record displayed--I could add a "go to last record" button, but I'd rather eliminate this step (and the possibility that someone could change the first record inadvertently) by having the form open up with blank fields in the controls, ready to accept a new record. Is there a way to do this while maintaining the ability to scroll back through previously entered records?

[NeoPa]

Either :

Call DoCmd.GoToRecord(Record:=acLast)

or :

Call DoCmd.GoToRecord(Record:=acNewRec)

depending on your requirement.

[dizzydangler]

NeoPa--that's great! The form looks exactly like I want it to when it opens. BUT, now the "previous record"/"next record" buttons don't work--I get the "can't go to specified record" error message. It seems like the command to go to a new record continues to execute, rather than being something that only executes once, when the form is opened. Code is currently:

Private Sub Form_Current()
Call DoCmd.GoToRecord (Record:=acNewRec)
End Sub

[NeoPa]

That's because you have it in the Form_Current() event procedure. This runs every time you go to any record. Consider what happens when you go to the first record for instance? It hits this code and says "Right - back off to the end again".

I would expect something like the Form_Open() event procedure might be more workable.

[dizzydangler]

OK, Form_Open() gave me a "procedure declaration does not match description of event" error, but I got it to do exactly what I want (open w/blank controls ready to enter a new record, but allow user to scroll backwards/forwards to view and edit previous records) with the Form_Activate() event procedure. Thanks!

Joe

[NeoPa]

You could be getting yourself into unexpected problems there Joe.

Besides, your Form_Open() problem needn't be an issue I expect. I suspect you've copied the whole procedure across and just made changes to the name. What you should do, until you're more familiar with the correct format for each event procedure (some, but not all, have specific requirements as to how they're defined), is simply to create the event procedure template automatically from the Access Form Properties page. When that is there - it will have a single empty line as contents - replace this line with the code contained in your previous procedure. This should leave you with a properly defined and fully working event procedure.

[dizzydangler]

OK, working through the form properties, I built the command under the Form_Open() sub--still no luck. Access help seems pretty worthless when it comes to figuring out definitions for commands/functions/etc, so I just went down the list trying each one. Form_Load() seemed to give me the same results as Form_Activate(). Would this be a better event procedure to use?

[nsbecker]

I believe that the Form_Load event is the one you'll want to use - the Form_Open event is the first event that fires - before the form is even populated with data. Thus, attempting to move to a new record when you have yet to bind the form to the data is probably what is giving you the error.

My rule of thumb: setup routines (including dynamically setting the RecordSource) for the form can get called from Form_Open, but anything dealing with data/records in the form are better called from Form_Load.

The problem with Form_Activate is that this event fires when the form regains focus; so, as NeoPa warns, you might run into unexpected behavior if the user navigates away from the form in the midst of editing a record, then returns to it and, wha-lah, are taken to a brand new record.

All the best--

[NeoPa]

I couldn't have put it better :)

[dizzydangler]

Great explanation, nsbecker--helps me not only to understand why I was getting the error, also helps me visualize what the procedures themselves mean/are doing, so I can select the ones I need in the future. Thanks, all!