The following query generates the numbers 0 to 32767 from thin air.
SELECT n0+2*n1+4*n2+8*n3+16*n4+32*n5+64*n6+128*n7+256*n8+512*n9+1024*n10+2048*n11+4096*n12+8192*n13+16384*n14 AS n FROM (Select 0 as n0 from tbl1 UNION Select 1 from tbl1) AS t0, (Select 0 as n1 from tbl1 UNION Select 1 from tbl1) AS t1, (Select 0 as n2 from tbl1 UNION Select 1 from tbl1) AS t2, (Select 0 as n3 from tbl1 UNION Select 1 from tbl1) AS t3, (Select 0 as n4 from tbl1 UNION Select 1 from tbl1) AS t4, (Select 0 as n5 from tbl1 UNION Select 1 from tbl1) AS t5, (Select 0 as n6 from tbl1 UNION Select 1 from tbl1) AS t6, (Select 0 as n7 from tbl1 UNION Select 1 from tbl1) AS t7, (Select 0 as n8 from tbl1 UNION Select 1 from tbl1) AS t8, (Select 0 as n9 from tbl1 UNION Select 1 from tbl1) AS t9, (Select 0 as n10 from tbl1 UNION Select 1 from tbl1) AS t10, (Select 0 as n11 from tbl1 UNION Select 1 from tbl1) AS t11, (Select 0 as n12 from tbl1 UNION Select 1 from tbl1) AS t12, (Select 0 as n13 from tbl1 UNION Select 1 from tbl1) AS t13, (Select 0 as n14 from tbl1 UNION Select 1 from tbl1) AS t14
Without including tbl1 in the query Access will complain that there is no table or query for input, even though the select statements create virtual tables. But tbl1 can be any table, for example a table with one field called x of type byte. It is important though that the table contains at least one record, i.e. it must not be empty. So have one record where x is 0, for example.
This kind of number generating query can be good for various things, for example when you want to make a query that lists dates.
Use the following event coding in the form:
Private Sub Form_AfterUpdate() Me.AllowAdditions = False End Sub Private Sub Form_Current() Me.AllowAdditions = Me.NewRecord End Sub
The problem with a subform is that if the Allowadditions has been set to false, then the current event never happens in the subform when you select a record in the main form for which there is not yet a record in the subform.
To solve this, add the following event coding in the main form, as a complement to the above coding in the subform.
Private Sub Form_Current()
With Me.sbfContainer.Form
.AllowAdditions = True
.Requery
End With
End Sub
This forces the subform current event to trigger. The Requery is needed because normally the subform current event happens before the current event of the main form, so the Requery forces it to happen now when the AllowAdditions allows it to happen.
Note that in both cases, for both the "simple form" and the "form/subform" scenario, there is no need to set the Cycle Property to "Current Record". It can have its default value of "All Records".
Sometimes the subform is linked to a control or many controls or a control and a main form, so one would have to duplicate the code
With Me.sbfContainer.Form
.AllowAdditions = True
.Requery
End With
for the AfterUpdate event of each control. This can get difficult to maintain, and controls might change programmatically without the AfterUpdate procedure being called.
For such scenarios one can instead add the following code to the subform container control:
Private Sub sbfContainer_Enter()
With Me.sbfContainer.Form
.AllowAdditions = .NewRecord
End With
End Sub
Private Sub sbfContainer_Exit(Cancel As Integer)
Me.sbfContainer.Form.AllowAdditions = True
End Sub
Thus when the user leaves the subform to do something elsewhere which affects the record selection of the subform, the subform will allow additions, which makes sure it will not go blank if no record exists.
When the user then enters the subform again, the decision is made whether to allow additions or not, depending on whether there is a record or not.
Note that the Me.AllowAdditions = False in the Form_AfterUpdate event is still a necessary part of this solution.