Solved VBC to add 1 on null (1 Viewer)

[rondon]

Hi, while responding to another issue one of the forums members suggested for good practice I also limit user impute into the RecNum field by inserting the following code into RecNum field so it self populates with the next number

Private Sub RecNum_BeforeUpdate(Cancel As Integer)
If IsNull(Me.RecNum) Then
RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
End If
End Sub

I understand the code to some extent in that it is say if tblRecommendations , field RecNum is null then before update a new record add 1 to the pervious number in the field in the tblRecommendations into the RecNum field for the user. I have inserted the code but nothing happens. I have checked all the naming conventions and they are correct
(RecNum and it is stored in the tblRecommendations).

1) My only thoughts are as it is a number field when the new record is created the RecNum field is self populated with a 0
2) The RecNum is not the primary Key the primary key for each RecNum is RecID
3)The frmRecommendations is a subform of frmReportID

Any help greatly appreciated Ron

 

[Gasman]

Are you sure it was not meant to be in the form's BeforeUpdate event?

 

[Gasman]

I've just checked and Pat did indeed state Form's BeforeUpdate event

https://www.access-programmers.co.uk/forums/posts/1699606/

 

[rondon]

Are you sure it was not meant to be in the form's BeforeUpdate event?

it is in the
Private Sub RecNum_BeforeUpdate(Cancel As Integer)

 

[Gasman]

it is in the
Private Sub RecNum_BeforeUpdate(Cancel As Integer)

Yes, I know, I can see that from the code. Pat said the Form's BeforeUpdate event. Put it in there.

 

[rondon]

Yes, I know, I can see that from the code. Pat said the Form's BeforeUpdate event. Put it in there.

I have tried that sorry I ,missed what you said

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.RecNum) Then
RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
End If
End Sub

 

[rondon]

I have tried that sorry I ,missed what you said

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.RecNum) Then
RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
End If
End Sub

still not working

 

[cheekybuddha]

You seem to have a Default value of zero set for RecNum (either at the table level, or in the control on your form)

So you can either:

1. Remove the default in the table, or set the Default = Null

Or:

2. Change your code to:

' ...
  If IsNull(Me.RecNum) Or Me.RecNum = 0 Then
    Me.RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
  End If
' ...

Or:

3. Set the default value for RecNum in your table to the expression:
=Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
(Untested)

hth,

d

 

[arnelgp]

you only need to do this on New record, so move the code on BeforeInsert event of the Form:

private sub form_beforeupdate()
Me.RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
end sub

 

[rondon]

you only need to do this on New record, so move the code on BeforeInsert event of the Form:

private sub form_beforeupdate()
Me.RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
end sub

I have set RecNum default value in the table tblRecommendations to Null. I then typed the code into the frmRecommendations BeforeInsert event

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = " & Me.ReportID), 0) + 1
End Sub

When I open frmReportID which contains the sub form frmRecommendations and created a new record ReportID 2030_1. I then completed the fields in frmReportID and tabed into the subform frmRecommendations the following error message occurred

3075
Syntax error in query expression (ReportID =2030-1)

It appears to be calling for the ReportID number which is what links the two form
Thanks Ron

 

[arnelgp]

Report id is obviously a Text, so change:

Private Sub Form_BeforeInsert(Cancel As Integer) Me.RecNum = Nz(DMax("RecNum", "tblRecommendations", "ReportID = '" & Me.ReportID & "'"), 0) + 1
End Sub

 

[rondon]

Thank you arnelgp

yes that worked. For my learning does this indicate it is a text string as opposed to a number

'" & Me.ReportID & "'"

Thanks Ron