Scroll to bottom of table in a form 2

[ronphx]

I have an invoice form with a subform that contains the detail lines of parts ordered (from table tbldetail). In the main form I add a new part number to the invoice, which is then subjected to edit tests, then added to tbldetail. The problem I have is that I would like to have the subform automatically scroll down to view the last part entered instead of staying at the beginning of the table. That way the user could see that the part was added to the table. I really don't want the user to have to manually scroll down. I've tried the MOVELAST command but that didn't work. I can get this to work by using the SENDKEYS command to set focus to the subform, then do a CTRL-Down. Is there a better way to do this without having to set focus to the subform?

Thanks.

 

[lamarw]

Hi,
Since you are using a subform you have access to that forms 'record source'. Instead of using the table name that it is connecting to, try using a 'select' statement selecting all the records from the table including a 'KEY' that is unique to the table and sort descending on the key value. This will put the last item entered at the top of the form instead of at the bottom and is always visible. In effect the last item entered is the first in the list, other items appear in their descending KEY order. You don't have to display the key value in the subform in order to have this work.
Does this do what you want it to do?

 

[lamarw]

Hi,
Since you are using a subform you have access to that forms 'record source'. Instead of using the table name that it is connecting to, try using a 'select' statement selecting all the records from the table including a 'KEY' that is unique to the table and sort descending on the key value. This will put the last item entered at the top of the form instead of at the bottom and is always visible. In effect the last item entered is the first in the list, other items appear in their descending KEY order. You don't have to display the key value in the subform in order to have this work. One more thing, the sort descending is accomplished with 'ORDER BY ' the name of the value and then 'DESC':'Select * from [tablename] ORDER BY [tablename.keyname] DESC'
Does this do what you want it to do?

 

[ronphx]

Thanks for the idea. That's a good approach that will work with some other forms I'm using. However, in this form, there are some "package" part numbers which consist of several lines of parts that need to be displayed in an ascending order from top to bottom, so I don't think that method will work here.

 

[lamarw]

So, then, you have a BOM that is represented on the form as a packge number? If so, do all the items in the BOM show on the form? If not, then are we talking about grouping items together?

 

[PHV]

I've tried the MOVELAST command but that didn't work
Can you please post the code you tried ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ronphx]

All of the parts in the package are listed on the detail form, and the users would like to see parts displayed in the order entered:

When I tried the movelast command I simply used:

rstempdet.MoveLast

I also tried setting the focus to the subform, then movelast:

Me.frminvoicedetailsubform.SetFocus
rstempdet.movelast

but that didn't work either.

 

[Zion7]

ronphx, i'm also curious why the MoveLast didn't work.

Is it because, you refresh the form after the Insert into tblDetails, & the order is not by primary key but by Package? ...it is not, Last record you want.

Can you open a recordset, use find, then match bookmarks?

Dim rec As New ADODB.Recordset
rec.Open "SELECT * FROM tblDetails WHERE fkDetailID =" & Me.InvoiceID, CurrentProject.Connection, adOpenStatic, adLockPessimistic (if SubForm is Not linked, no need for WHERE statement.

rec.Find "txtDetails ='" & Me.txtDetails & "'"

Me.subDetails.Form.Bookmark = rec.Bookmark

or, Can you use..
Me.subdetails.Form.setFocus
Me.SubDetails.Form.txtInvoiceNumber.setFocus
DoCmd.FindRecord Me.InvoiceNo

...but you didn't want to set focus to subform?

Just some other thoughts, good luck!

 

[PHV]

Take a look at the Bookmark property:
Me.frminvoicedetailsubform.SetFocus
Set rs = Me.frminvoicedetailsubform.Form.RecordsetClone
rs.MoveLast
Me.frminvoicedetailsubform.Form.Bookmark = rs.Bookmark
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ronphx]

Thank you (all). I did get the bookmark to work, and I didn't have to use setfocus

Me.frminvoicedetailsubform.SetFocus

so the focus never did go to the subform.

I did notice though, that the first field (quantity) of the last record in the subform was selected. Is there a way to unselect that text?

 

[Zion7]

Actually, your subform does have the focus, and I assume your tab order begins with [quantity]. I don't believe, you can avoid this. As a workaround, I always put a command button on a readonly form, so small barely visible, tight in the corner, and have it 1st in my tab order list.

Hope this helps, good luck!

 

[ronphx]

Thanks - I like that idea. That's a good way to avoid the quantity field.