Dmax code issue - trying to find highest number and add 1

[andy_25]

Hi all,

I'm sure one of you guys can spot the problem here? I have been banging my head against a brick wall for 2 hours

I am trying to find the highest Application_No and add 1 to it and then pass it to a text box. Easy right???

Private Sub Form_BeforeInsert(Cancel As Integer)
'curAppNo - Finds current highest Application_No
Dim curAppNo As Byte
curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = [txtContractNo]"), 0)
'Adds 1 to the current highest
curAppNo = curAppNo + 1
'sets the value of next highest application
txtApplicationNo.Value = curAppNo
End Sub

 

[vbaInet]

Here:

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Me.txtContractNo & "), 0) + 1

Bring out txtContractNo. It was being read as text.

And you can add the one on that line too as I've done.

 

[missinglinq]

Why is curAppNo being dimmed as Datatype Byte? I would think that if numeric, depending on the scope needed, it would be dimmed as an Integer or Long (Integer).

These types of 'numbers' are actually usually defined as Text, since math operations are not typically done with them.

What exactly happened when you tried the above code?

Also note that if this is a Multi-users database, this type of code should be moved to the Form_BeforeUpdate event, which fires at the last possible moment before the record is saved, as opposed to the BeforeInsert event, which fires pretty much as soon as the first character is entered into a new record. Using the BeforeInsert event, in a multi-user scenario, increases the chance of two users starting a record at or near the same time and having the same number assigned to both records.

 

[andy_25]

Here:

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Me.txtContractNo & "), 0) + 1

Bring out txtContractNo. It was being read as text.

And you can add the one on that line too as I've done.

Compile error, Expected: list seperator or )

Apologies, I can;t figure out what's missing

 

[Galaxiom]

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = [txtContractNo]")

When controls are referred to like this they need the full reference:
"Contract_No = Forms!formname.controlname"

Otherwise use:

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No =" & Me.[txtContractNo])

VbaInet's code has an error:

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Me.txtContractNo [COLOR=red]& "[/COLOR]), 0) + 1

 

[vbaInet]

Oops... my bad:

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Me.txtContractNo), 0)

 

[andy_25]

Why is curAppNo being dimmed as Datatype Byte? I would think that if numeric, depending on the scope needed, it would be dimmed as an Integer or Long (Integer).

Yes I agree, it was Long but I put it as Byte when I was messing around with it.

What exactly happened when you tried the above code?

I get an error

Also note that if this is a Multi-users database, this type of code should be moved to the Form_BeforeUpdate event, which fires at the last possible moment before the record is saved, as opposed to the BeforeInsert event, which fires pretty much as soon as the first character is entered into a new record. Using the BeforeInsert event, in a multi-user scenario, increases the chance of two users starting a record at or near the same time and having the same number assigned to both records.

Thanks for the input; once I got the code working I was going to think about the best place to put it, now I know

 

[andy_25]

Oops... my bad:

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Me.txtContractNo), 0)

Error
Missing operator in Contract_No =

 

[vbaInet]

Is that the line that's being highlighted?

 

[andy_25]

Is that the line that's being highlighted?

Yes. I can't get Galaxiom's code to work either even with referencing the form i.e.

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No =" & Forms!frmAddApplication.[txtContractNo]), 0) + 1

 

[vbaInet]

Is Contract_No a Text field or Number field?

 

[andy_25]

Is Contract_No a Text field or Number field?

Number - Long Integer

 

[vbaInet]

Oh sorry, I should have spotted it. You need Nz()

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Nz(Me.txtContractNo, 0)), 0)

 

[andy_25]

Oh sorry, I should have spotted it. You need Nz()

curAppNo = Nz(DMax("Application_No", "APPLICATIONS", "Contract_No = " & Nz(Me.txtContractNo, 0)), 0)

No error now but it is giving out 0 as the Application_No everytime. When I add +1 to the end of the code it is giving out all 1s i.e. not seeign that 1 is already there.

I'm confused

 

[vbaInet]

txtContractNo is Null.

 

[andy_25]

txtContractNo is Null.

for testing purposes on form load I set txtContractNo to the value 2 so it should increment each time but it isn't

 

[vbaInet]

So you're saying during the test curAppNo returned 1? Did you use an msgbox to test curAppno?

 

[andy_25]

So you're saying during the test curAppNo returned 1? Did you use an msgbox to test curAppno?

Yes it keeps inserting 1 into Application_No. I added a messagebox and it confirms the variable is sticking at 1

 

[vbaInet]

In that case, there's no Contract_no with a value of 2.

 

[andy_25]

In that case, there's no Contract_no with a value of 2.

In the Applications table there are several entries of Contract_No with the value of 2 and one in the contracts table.