Form and subform (1 Viewer)

[Vulcan1500]

In MS Access 2003 I have a form and subform. The form is a basic invoice and the subform is per record an invoice line (approx 10 lines within the subform window). Each record does calculate the price ex VAT and after adding a record the total invoice price ex and incl VAT is calculated in the form. My problems are:
1. Going to a new line in the subform an after update event recalculates the total invoice price and returns to the first line of the subform. Can I send it to the last (new) line?
2. Once I move to the 11th line I first have to scroll down to get this line in view. How can I keep the last line in view?
Hopefully one of you can help me with these questions. They kept me bussy for the last few days! Thanks in advance!

 

[John Big Booty]

You can use;

docmd.GoToRecord acDataForm, "FRM_Name" ,acLast

To go to the last record on the form, use acNew if you want it to go to a new record. I'm not 100% sure but I suspect if you go to the last record it should be visible in your form.

 

[Vulcan1500]

Thanks for your quick response John Big Footy! Although I understand your advice, I wasn't able to solve the problem and added a couple of screenshots to make it visible.

A - new invoice (nieuwe factuur)
Making a new invoice works as expected. A new line in the subform is entered by moving the cursor to the next line (by mouse or tab) and totaal excl BTW, BTW and totaal incl BTW are updated.

B - update invoice (factuur wijzigen)
Entering a new line as above results in an update of the amounts and a focus on the first line.

I would like to have the focus on the new line. Basicly both forms are the same and execute the same command in the after update when the cursor is moved to the next line.

Private Sub Form_AfterUpdate()
Me.Parent.[TotaalExBTW].Requery
End Sub

I hope this is giving the information that one of you can push me in the right direction.

 

[John Big Booty]

Try putting the following in the main form's on current event

Dim rs As DAO.Recordset
Set rs = FRM_YourFormName.Form.RecordsetClone
rs.MoveLast
FRM_YourFormName.Form.Bookmark = rs.Bookmark
Set rs = Nothing

 

[Vulcan1500]

I got a runtime error on the second line:

Set rs = frmFactuurWijzigen.frmDetail.RecordsetClone

main = frmFactuurWijzigen
sub = frmDetail

 

[John Big Booty]

You need;

Set rs = frmDetail.Form.RecordsetClone

 

[Vulcan1500]

Getting closer now! Following code is now in the on current event of main form.

Dim rs As DAO.Recordset
Set rs = frmDetail.Form.RecordsetClone
rs.MoveLast
frmDetail.Form.Bookmark = rs.Bookmark
Set rs = Nothing

When I enter 'modify invoice' the last and an empty record are displayed in the subform window (see attachment C), which is good. After typing a new invoice line in the empty record and entering it by clicking the newly created empty record then it jumps to the first invoice line again.

 

[John Big Booty]

Try putting the code in the after update event of the sub form.

 

[Vulcan1500]

Sorry, I cann't scream YESSSSS yet!

Due to an error I had to modify the code to:

Dim rs As DAO.Recordset
Set rs = Forms!frmFactuurWijzigen.frmDetail.Form.RecordsetClone
rs.MoveLast
Forms!frmFactuurWijzigen.frmDetail.Form.Bookmark = rs.Bookmark
Set rs = Nothing

Now the first time the focus is on the first invoice line. Scrolling down and after entering an invoice line it jumps to the first again.

 

[John Big Booty]

Try putting the following in the on current event of the main form;

Dim rs As DAO.Recordset
Set rs = frmDetail.Form.RecordsetClone
rs.MoveLast
frmDetail.Form.Bookmark = rs.Bookmark
Set rs = Nothing

then in the After update event of the last field in your invoice line put;

Dim rs As DAO.Recordset
Set rs = Forms!frmFactuurWijzigen.frmDetail.Form.RecordsetClone
rs.MoveLast
Forms!frmFactuurWijzigen.frmDetail.Form.Bookmark = rs.Bookmark
Set rs = Nothing

 

[Vulcan1500]

Sorry, it didn't work. I'm doing something very stupid or I'm giving you wrong information. For that reason I attached a zip-file with the relevant parts of the database and, if you have more time for me, pls have a look at it.

after unzipping
open form 'frmFactuurHerinnerenWijzigenBetaaldAfdracht'
click OK of the inputbox
select by clicking an invoice in the listbox, select Wijzigen-Inhoud wijzigen and the form 'Factuur wijzigen' opens

You can now see what happens. The focus is on the last record, but after entering a new invoice line the focus is on the first line again.

 

[John Big Booty]

Sorry, I cann't scream YESSSSS yet!

Due to an error I had to modify the code to:

Dim rs As DAO.Recordset
Set rs = Forms!frmFactuurWijzigen.frmDetail.Form.RecordsetClone
rs.MoveLast
Forms!frmFactuurWijzigen.frmDetail.Form.Bookmark = rs.Bookmark
Set rs = Nothing

Now the first time the focus is on the first invoice line. Scrolling down and after entering an invoice line it jumps to the first again.

I think part of your problem is that you have no form called frmDetail. I think, but am not sure, that what it should be is frmFactuurDetail, I suspect that if you make that change it should work.

 

[Vulcan1500]

Still I don't get it to work, but I now realise that the names of my form and controll are confusing. The subform is indeed called frmFactuurDetail and the control of the subform on the main form is called frmDetail. Due to the last advice (frmFactuurDetail instead of frmDetail) I modified this twice (is this correct?) in the on current event of the main form and in the after update event of the sub form.

 

[John Big Booty]

The problem seems to lie with the Me.Parent.[TotaalExBTW].Requery portion of the code in your After Update event of the sub form. I suspect I know why but I have yet to figure out how to overcome the behaviour it is causing.

 

[John Big Booty]

What you need to do is to work out a way of delaying the action of this portion of the code

Dim rs As DAO.Recordset
Set rs = Forms!frmFactuurWijzigen.frmDetail.Form.RecordsetClone
rs.MoveLast
Forms!frmFactuurWijzigen.frmDetail.Form.Bookmark = rs.Bookmark
Set rs = Nothing

until the action initiated by Me.Parent.[TotaalExBTW].Requery has finished. At this point I'm afraid I do not have an answer for you.

 

[Vulcan1500]

Thanks for all your time and effort to help me with this problem. I will try to solve it by delaying the 'focus-code'. Once I'm successful I will come back on this thread. Thanks again!

 

[Vulcan1500]

Sorry John, but eventually I found the problem. For testing purposes I had an invisible field with the primairy key of a table on the form, which I forgot to delete.

 

[John Big Booty]

It's always something small that trips you at the last hurdle. Glad you got it working.