Recordset and Forms - Please HELP

[overkamp]

Hi,

This may sound like a silly question but I've searched and searched but still could not find an answer to my question.

I have a form that is bound to a table (tblOrdersTemp). The purpose of this form is for the user to enter several different orders all at once and then it will update all these orders to the tblOrdersMain table and all the entries in the tblOrdersTemp will have the same main order number, and differentiated with different subOrder numbers.

Eg: Client comes into shop, does 5 orders all at once. These 5 orders will be placed in tblOrdersTemp. Once all details for the 5 orders are completed, these records will be moved out of tblOrdersTemp and placed in tblOrdersMain, with order number as Order001-00, Order001-01, Order001-02, Order001-03, Order001-04

I want to create my own navigation buttons on the form because the ones offered by access is not what I want. I want it so that the form will not show a blank form until I press the "ADD NEW" button. With the buttons offered by access, it will show a blank form if I press "NEXT" (provided I am on the last record). This is why I thought of using recordsets (but I must admit I am a newbie to access and haven't really used recordsets before).

I placed the following code in the Form_Load event:

Dim myConnection as ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordset as New ADODB.Recordset
myRecordSet.activeConnection = myConnection
myRecordset.Open “tblOdersTemp”, , adOpenStatic, adLockOptimistic

However, when I put command button on the form and tried to use "myRecordset.moveNext", it wouldn't work and said "Object variable not found". I am guessing that's because I have not declared myRecordset in the command button sub procedure? But if I declare it again, wouldn't it be different to recordset I've declared in the form load event? Or am I missing something completely?

I have thought of using the DoCmd method, but the moveNext method will still give a blank form, which is not what I want. I want it to show a blank form ONLY if the user presses the "ADD NEW ORDER" button. Furthermore, I want records to be updated in the tblOdersTemp table ONLY if the user presses the "SAVE THIS ORDER" button.

Any help would be greatly appreciated.

Thanks all.

 

[boblarson]

You don't need to use any recordsets for this. Just set the navigation buttons to NO and then you can create your own buttons using

DoCmd.RunCommand acCmdRecordsAddNew

DoCmd.RunCommand acCmdRecordsMoveNext

DoCmd.RunCommand acCmdRecordsMovePrevious

DoCmd.RunCommand acCmdRecordsMoveFirst

DoCmd.RunCommand acCmdRecordsMoveLast


And you will need to trap for the error 2105 that you will get if you are at the first record and press the MovePrevious one and also the same if you are at the last record and hit MoveNext.

 

[overkamp]

Hi Bob,

Thanks for the reply. I have actually tried that before moving on to recordsets. I tried to put an IF statement as follows:

IF BOF = True Then
MsgBox "This is the first record"
Else
DoCmd.RunCommand acCmdRecordsMovePrevious
End If

But the BOF only works with recordsets? How can I check the first and last record?

Also, is there a way so I can make it will only save when I press the "SAVE ORDER" button? But there seems to be no DoCmd for save?

Thanks again.

 

[boblarson]

Again, I will emphasize. You don't check for BOF or EOF. You add an ERROR HANDLER to be able to IGNORE the error that is generated that tells a user they can't go to that record because they are already at the beginning or end of the records.

 

[overkamp]

Thanks Bob, I think I got it to work.

My next question would be is it possible to not automatically save the record to the table until the user presses a "SAVE" button? At the moment, as soon as the fields are completed, it gets saved to the table, but I don't want that. Is this some form properties that I can change?

Thanks once again.

 

[JANR]

You can use the Form_BeforeUpdate event to cancel a save to table.

JR

 

[LPurvis]

FWIW, for an example of disabling buttons (so that Prev/Next aren't available) have a look at the Nav buttons example linked to in my sig below.
It's using recordsets - but those of the form, not as you have been.
You can just drop the nav buttons onto any form as a subform and they'll work - no further coding involved.

Cheers.

 

[boblarson]

You can just drop the nav buttons onto any form as a subform and they'll work - no further coding involved.

Plug and Play - always good

 

[overkamp]

Thanks Leigh! I got the disabling button to work!!

JANR, I did some googling on Form_BeforeUpdate and I think I know how to get it to not save until I press a button. I'll do a condition in the formbeforeupdate procedure to check whether the save button was pressed, if yes then save, if not then cancel = true

Thank you so much for all of you guys help. Really really appreciate it!

 

[LPurvis]

Well, not to come across as "example guy" (as that's really not what I am about - more like "inane lengthy waffling response guy")...
The Cancel Undo example on the same page would give some pointers to checking for save or not.

Cheers.