Default numbering on a subform (1 Viewer)

[connie]

This is driving me insaaaaane. I've spent the better part of the day Googling variations of this and nothing I've found has really helped.

I have a subform that is based on another subform inside a main form (so 3 forms total). I want to do a DMax default value in VBA to autonumber the *new* next record on form 3 before it is entered, and have the following:

Run-time error '3075': Syntax error (missing operator in query expression '[pkNumber] ='.

I think this is probably relatively simple and is due to my using the incorrect quotes or syntax for an integer?

My code is:

Dim strParentDocName As String
    Dim x As Integer
    x = DMax("[Inspection_Number]", "[tblInspectionData]", "[pkNumber] =" & [Forms]![frmPermitEntry]![subfrmThirdPartyData]![ctlpkNumber])
    If IsNull(x) Then
        Me.ctlInspectionNumber.DefaultValue = 1
        Else
        Me.ctlInspectionNumber.DefaultValue = x + 1
        End If

And the line the debugger is highlighting is:

x = DMax("[Inspection_Number]", "[tblInspectionData]", "[pkNumber] =" & [Forms]![frmPermitEntry]![subfrmThirdPartyData]![ctlpkNumber])

SOS! Thx...

 

[connie]

I'm still not sure I figured out the answer to what was wrong with that syntax, but I've managed to work-around by using an expression instead (which I was doing initially but then ran into a snafu and had decided to switch it up).

If anyone's curious, here's how I'm now auto-incrementing number fields on my subform (of a subform):

Set default value property on that field to

=NZ(DCount("[Inspection_Number]","tblInspectionData","[pkNumber] = Forms![frmPermitEntry]![subfrmPenetrationData].Form![ctlpkNumber]"),0)+1

 

[connie]

Well, I thought that worked. Unfortunately the more I was working with that subform I found random instances where the default value # is totally off. Like starting off at an 8 when there's no matching child fields.

So I'm back to trying to set it up in VBA, and I've tinkered it to:

Private Sub Form_Current()
Dim x As Integer
Dim IntPK As Integer
    x = Nz(DMax("[Inspection_Number]", "[tblInspectionData]", "[pkNumber]=" & IntPK), 0)
    IntPK = Me.Parent!pkNumber
    If IsNull(x) Then
        Me.ctlInspectionNumber.DefaultValue = 1
        Else
        Me.ctlInspectionNumber.DefaultValue = x + 1
        End If
End Sub

Except that it's giving me the error that it 'can't find the field pkNumber referred to in my expression.'

subfrm2 (which I'm trying to write the code for above) is linked in its Property Sheet by: Source Object: subfrm2, Link Master Fields: [subfrmPenetrationData].Form![pkNumber], Link Child Fields: pkNumber.

Can any one please help? If I'm doing this totally wrong I'm willing to do it any workable way!

 

[JamesMcS]

Just looking at the dmax statement, there's a pknumber field in tblinspectiondata yeah? And I'm not sure but I would imagine the line IntPK=me.parent!pkNumber needs to go before the x=nz(dmax... line.

And I don't think x would ever be null as you're using nz so you'd probably always be adding 1 to x no matter what.

 

[connie]

Just looking at the dmax statement, there's a pknumber field in tblinspectiondata yeah? And I'm not sure but I would imagine the line IntPK=me.parent!pkNumber needs to go before the x=nz(dmax... line.

And I don't think x would ever be null as you're using nz so you'd probably always be adding 1 to x no matter what.

THANKS for answering! Yes, there is a pkNumber field in tblInspectionData. You're right about the IsNull...I modified my code per your advice to:

Private Sub Form_Current()
Dim IntPK As Integer
Dim x As Integer
IntPK = Forms![frmPermitEntry]![subfrmPenetrationData]![ctlpkNumber]
x = Nz(DMax("[Inspection_Number]", "[tblInspectionData]", "[pkNumber]=" & IntPK), 0)
 
    If Me.NewRecord Then
        Me.ctlInspectionNumber.DefaultValue = x + 1
    Else
    End If
End Sub

However I'm getting the error "You entered an expression that has an invalid reference to the property Form/Report" and it highlights the line

IntPK = Forms![frmPermitEntry]![subfrmPenetrationData]![ctlpkNumber]

I've tried writing the def of IntPK a variety of ways, including Me.Parent!ctlpkNumber, Me!Subform1.Form!ctlpkNumber, etc...all the other ways it says "cannot find ctpkNumber." I do have that control hidden on the parent form but I don't think that's the reason?

 

[JamesMcS]

I'm not sure about the hidden thingy to be honest - try unhiding it and see what happens. I think if you're referring to a control on a form you'd need to put

IntPK = Forms![frmPermitEntry]![subfrmPenetrationData].form![ctlpkNumber]

 

[connie]

Thx again...ok so I unhid ctlpkNumber on the Parent subform and changed the def of IntPK to:

IntPK = Forms![frmPermitEntry]![subfrmPenetrationData].Form![ctlpkNumber]

And I still get "Run-time error '2455': You entered an expression that has an invalid reference to the property Form/Report" and highlighting the line I just put above. I don't get why it doesn't want to recognize that control

 

[JamesMcS]

Me neither - I'm clutching at straws here but does it make a difference if you put .value or .text at the end? If it doesn't I'm perplexed.... the error means there's a problem with the syntax but I can't see it.