Form AfterUpdate and setting the current record

[AlexLMillington]

Hi all,

I have a form with a subform in it. In the afterupdate event of the subform I have a macro set to run which checks whether the value in a certain control is a particular number. If it is then I want to open up another form to enter some additional detail about that record.

The problem I have is that while I can check whether the control contains that number and if it does, I can open the new form, the record in the original subform has moved to a new record or the next record as a result of the user pressing 'enter' when the original data was changed.

What this means is that all the data in the new form, which relies on the current record of the original form still being the record that requires the additional data, #REFs out.

I have tried all the ways I can think of to stop the record in the original subform from moving to a new record or the next record but to no avail. Is there anyone out there who can help with this?

Thanks
Alex

 

[June7]

Perhaps use the AfterUpdate event of the control instead form. Or the BeforeUpdate event of the form. Why another form - is this a dependent related table? If so, the record must be committed to table before dependent record can be created. How are you passing the record ID to dependent record?

Use form BeforeUpdate event to save record ID to a global variable or UNBOUND textbox (can be hidden) or TempVar. Use that variable to open relevant record in AfterUpdate event.

 

[AlexLMillington]

Thanks for the response.

So the problem is that a user might update the record leaving it via any control rather than just the control that contains the trigger for opening the additional form. I did think I probably wasn’t being clear enough in the initial post but wanted to keep it short... here is the full detail:

What I’m doing is building a simple accounting program and this form is the bank form. The user opens up a form called ‘fbank’ which contains a continuous form subform called ‘fbank_detail’ that is linked to a table called ‘bank’. The user then enters the detail exactly from the bank statement in the subform and allocates the transaction to a general ledger account code. Of course, some transactions apply to more than one P&L account (for example an expense claim) and for these transactions in order for the bank statement to still contain the total amount of the expense claim, the user selects a ‘GL allocation’ account code. When this is done and the user presses ‘enter’ on the record then the AfterUpdate event triggers and an Access macro runs which:

1) does some error checking
2) creates a batch number for transaction processing and enters the detail of the batch in the GL batch table.
3) processes the bank statement transaction to the general ledger table.
4) if the GL allocation account is selected opens up a new form called ‘GLAllocation’ for allocating the other side of the bank transaction to the correct places in the general ledger.

The GL allocation table is supposed to capture details from the bank form which are generally applicable to the transaction such as the GL auto number key, the transaction date, transaction amount etc. These are all unbound controls that get information from the current record in the loaded bank_detail subform. It then contains a subform which is used for processing the detailed elements of the allocation.

What is happeneing is that the afterupdate event is triggering on the bank_detail subform, the error checking completes, the GL transactions are processed and the GLAllocation form is opened up... but the bank_detail subform is now on a new record and not on the record that I want to analyse the cost out for and as a result the header data for that record is not being passed to the GLAllocation form.

I’ve tried putting a public function in which sets focus to the bank_detail subform and then runs DoCmd.GotoRecord ,,acLast ... that doesn’t throw out errors but doesn’t work. Using acPrevious throws out errors but I can’t remember which one off the top of my head. I’ve tried capturing the record with bookmark and then finding it. None of it works and the bank_detail subform still goes to a new record when the enter key is pressed.

Any ideas how to get it to stay on the record I want to capture the details from?

Thanks

 

[June7]

Record data validation should take place in form BeforeUpdate event. This is a common topic and many examples exist.

Then if you want to open another form that needs that record, either pass the record ID to form and open a recordset object of that record or use a method to move back to the record. This might involve RecordsetClone and Bookmarks. In either case, save the record ID to a variable that can be referenced regardless of what record has focus.

Re-inventing the wheel - QuickBooks would probably be cheaper and easier to implement than trying to build an accounting program from scratch.

 

[AlexLMillington]

Thanks june7 but not entirely helpful - if Quickbooks worked for him I’d have implemented that instead... unfortunately it doesn’t hence the bespoking nature.

Error checking is not on the bank record but on whether the data is all correct for the GL transactions to be processed and works fine so no issues there.

I’ve tried bookmark but can’t get it to work - perhaps you have some ideas on that? Alternatively suggest how I could do the second solution you indicated?

Thanks

 

[June7]

Example of RecordsetClone code:

Me.RecordsetClone.FindFirst "RecID=" & intRecID
Me.Bookmark = Me.RecordsetClone.Bookmark

Second solution - passing record ID? I use OpenArgs property of OpenForm.

DoCmd.OpenForm "formname", , , , , acDialog, intRecID

Then code in the second form grabs the value

If Not IsNull(Me.OpenArgs) Then
'do something
Else
'do this
End If

 

[lpapad]

Check the Cycle property of the form. It might provide some solution in helping you staying at the current record.

 

[June7]

Oh yeah! Forgot about Cycle property. Can prevent advancing to next record then code will move to NewRecord only if current record is okay.

 

[AlexLMillington]

Thanks both,

Cycle property didn't work as it only controls the 'tab' event and not the 'enter' keypress so didn't change the issue.

I ended up writing a query that identified the index of the last Bank transaction posted and then a query that picked up the full detail of the transaction based on this last index number. Then used DLookup on the unbound controls in the BankAllocation form to pick up the necessary info. Seems to work OK...

 

[June7]

Works with Enter key if form is Continuous or Single, not Datasheet.