Query with SUBFORMS

[ShaneBrennan]

Okay It's me again, abusing this lovely forum.<br><br>Okay.&nbsp;&nbsp;Same problem that I've been having all day, but I'm trying something different.<br><br>With a sub-form - with view set to Continuous Forms<br><br>When you press ANY key in the last record.&nbsp;&nbsp;The record selector bar changes to a pencil to indicate the record is being changed and a new record appears at the bottom with an asterisk in the record selector.<br><br>Is there a way of disabling the automatic creation of this new blank record?<br><br>e.g.&nbsp;&nbsp;Set allowadditions to true, add a new blank record with some hidden field defaulting to some fictional default value, then switch the allowadditions to false.<br><br>Or even trap this asterisk record before it is created some how.<br><br>Thanks for your help<br><br>PS.&nbsp;&nbsp;Any help with the earlier questions will also be appreciated.<br><br>PPS. I love access.<br><br>PPS. Honestly (NOT!) <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>

 

[DougP]

yes, set the End of page behavior <br>this applies to All forms though.<br><br>close your form<br>click &quot;tools menu &quot;Options&quot;<br>Click the Keyboard TAB<br>put a Check in the &quot;Cursor Stops in First/Last field&quot;<br>It;s on the lower right.<br><br>then click OK on the dialog box to save changes<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[ShaneBrennan]

Thanks Doug.<br><br>I tried it, and the astrist stayed there - blooming thing!<br><br>By the way I'm using office 97<br> <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>

 

[DougP]

Well thats the way Access behaves.<br>It won't actually get a new record unless you key something in another field.<br><br>you could also try setting the &quot;TAB order&quot; so the last field goes to a command button or some other none text items.<br>In design view of the form<br>Click &quot;View&quot; menu then &quot;TAB order&quot;<br><br>click in the left of a field and then click and drag them up or down.<br>You can also click one then hold the Shift key down and click some more, then move a group up or down. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Guest_imported]

can you try something like this in your before update event<br><br>Dim db As Database, rst As Recordset<br>Dim recnum As Integer<br>Set db = CurrentDb<br>Set rst = db.OpenRecordset(&quot;Your table&quot<br>rst.MoveLast<br>recnum = rst.RecordCount<br>If recnum &gt; 20 Then<br>Me.AllowAdditions = False<br>Else<br>Me.AllowAdditions = True<br>rst.Close<br>db.Close

 

[Guest_imported]

this works to limit the records <br>had to put in the before insert event<br>as soon as you start to type the entry field goes away<br><br>Private Sub Form_BeforeInsert(Cancel As Integer)<br>Dim db As Database, rst As Recordset<br>Dim recnum As Integer<br>Set db = CurrentDb<br>Set rst = db.OpenRecordset(&quot;table1&quot<br>rst.MoveLast<br>recnum = rst.RecordCount<br>If (recnum &lt;= 20) Then<br>Me.AllowAdditions = True<br>Else<br>Me.AllowAdditions = False<br>End If<br>rst.Close<br>db.Close<br><br><br>End Sub

 

[ShaneBrennan]

NEARLY THERE!!!&nbsp;&nbsp;Okay I said that yesterday - but I really feel confident about this.<br><br>before I witter on thank you for all your help Glo4 and DougP - I've taken both your suggestions and I have come up with the following code:<br><br>Private Sub Form_BeforeInsert(Cancel As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim recnum As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;'Set rst = db.OpenRecordset(&quot;Boxes in cupboards - all information&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = Me.RecordsetClone<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;recnum = rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;If (recnum &lt; Me![MaxAllowed]) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.AllowAdditions = True<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;cannot add anymore records&quot;, vbExclamation, &quot;No more&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.AllowAdditions = False<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Close<br>End Sub<br><br><br>This DOES WORK, apart from one thing (That I know about).<br><br>I get a run-time error '3021':&nbsp;&nbsp;No current record when I try to type into a blank sub-form, e.g. When I start a new record, in the main form I have a blank sub-form.&nbsp;&nbsp;When I potition the cursor in the first fields of the sub-form and press a key I get the above error.&nbsp;&nbsp;On Debug the line rst.MoveLast&nbsp;&nbsp;is highlighted.&nbsp;&nbsp;I guess because there are no records for it to move to.&nbsp;&nbsp;Do you know a way of trapping this, so I can get round this problem!<br> <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>

 

[ShaneBrennan]

BOY AM I THICK!<br><br>I changed the lines:<br><br>rst.MoveLast<br>recnum = rst.RecordCount<br><br>to:<br><br>recnum = rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>If recnum &lt;&gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveLast<br>End If<br><br>And It works..... YEE HAR! <p> Shane Brennan<br><a href=mailto:brennans@tcat.ac.uk>brennans@tcat.ac.uk</a><br><a href= > </a><br>