How many lines to my sub-form

[ShaneBrennan]

Woulds you believe I'm having a little technical difficulty using Access?<br><br>background info.<br><br>before I start on a large project I'm ensuring I can do some of the more difficult parts, before I commit my time and get egg on my face when I can;t get it working.<br><br>I've created a very small data base to manage the contents of a cupboard.&nbsp;&nbsp;The contents are stored in boxes.&nbsp;&nbsp;I've created a very simple form with a sub-form.&nbsp;&nbsp;The main form displayes the cupboard information whilst the sub-form informs the user how many boxes are held in the cupboard.&nbsp;&nbsp;All this works fine - no problems at all.<br><br>I've added a text box to the main form which contains the following command in it's Control Source, to tell me how many boxes are in the cupboard:<br><br>=DCount(&quot;[box_id]&quot;,&quot;in cupboard&quot;, &quot;[cupboard_id] = [The_cupboard_id]&quot<br><br>This also works beautifully.&nbsp;&nbsp;The problem however, is when I add/delete records to the sub-form this figure does not update.&nbsp;&nbsp;It will only update when I scroll through the main form (Cupboard details).&nbsp;&nbsp;<br><br>Does anyone out there know of a way that I can easily update this.<br><br>I have tried using:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Forms![Cupboards - mainform]![The_cupboard_id].SetFocus<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.RunCommand acCmdRefresh<br><br>To re-set focus back to the main forms, then refresh.&nbsp;&nbsp;This works, but the focus remains in the main form and the sub-form resets to the beginning.<br><br>I would appreciate any help on this problem.&nbsp;&nbsp;I know other will have this, cause this would solve quite a few invoice, stock control, etc. problems.<br><br>Thanks for any help in advance.<br><br>Shane Brennan<br> <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>

 

[Julio]

Shane,<br><br>I propose the following solution:<br><br>In your subform, create an unbound textbox with its rowsource set to: =Count([The_cupboard_id]). Name it TotLines (or whatever).<br><br>Then, in your main form, create another unbound textbox, and set its rowsource to: =[TheNameOfTheSubform].[Form].[TotLines].<br><br>This should give you an always accurate record count of your subform.<br><br>HTH,<br><br>Julio

 

[ShaneBrennan]

Erm....<br><br>I've had another go and I think I'm getting closer to solving this problem, but not entirely there yet.<br><br>On Form_AfterUpdate() and Form_Delete() I have the following line:<br><br>Forms![Cupboards - mainform].Refresh<br><br>This works and the number is updated correctly and the cursor remains in the sub-form.&nbsp;&nbsp;However, on an add record the records are re-sorted in the sub-form(which is fine!) - but the cursor is left on the last record - which is not fine.<br><br>Is there a way of recording the record number of the record I have entered, doing the refresh, then moving the cursor back to the correct record?<br><br>HELP! <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>

 

[ShaneBrennan]

Thanks Julio<br><br>That worked a treat! <br><br>I'm getting better at access, but somethings really make me want to tear my hair out!<br> <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>