Value of Field

  • Thread starter Thread starter B
  • Start date Start date
B

B

Hello all I am having some troubles figuring out how to accomplish this one
particular thing.. I want the txtEmpid text box to display 7000 if 7000 does
not exist in the UPR00100 table in the EMPLOYID field. If 7000 does already
exist then take whatever is the highest 7000 number and add one too it.
There are two different divisions so it would actually be 7000 for one
division and 8000 for the other. By the Employid is a string as well so I
think I may need to convert somewhere as well not sure if that is the
problem..

Here is my none working code as of now.

Private Sub cmdExe_Click()
Dim EmpId As String
Dim test As String
test = 0

If fraEmp.Value = 1 And IsEmpty(txtEmpid.Value) Then
EmpId = DMax("EMPLOYID", "UPR00100", "EMPLOYID like'7???'")
txtEmpid.Value = EmpId + 1
ElseIf fraEmp.Value = 2 And IsEmpty(txtEmpid.Value) Then
EmpId = DMax("EMPLOYID", "UPR00100", "EMPLOYID like'8???'")
txtEmpid.Value = EmpId + 1
End If

If DCount("[EMPLOYID]", "UPR00100", "[EMPLOYID]='" & test & "'") Then
txtEmpid.Value = "7000"
End If
End Sub
 
B said:
Hello all I am having some troubles figuring out how to accomplish this one
particular thing.. I want the txtEmpid text box to display 7000 if 7000 does
not exist in the UPR00100 table in the EMPLOYID field. If 7000 does already
exist then take whatever is the highest 7000 number and add one too it.
There are two different divisions so it would actually be 7000 for one
division and 8000 for the other. By the Employid is a string as well so I
think I may need to convert somewhere as well not sure if that is the
problem..

Here is my none working code as of now.

Private Sub cmdExe_Click()
Dim EmpId As String
Dim test As String
test = 0

If fraEmp.Value = 1 And IsEmpty(txtEmpid.Value) Then
EmpId = DMax("EMPLOYID", "UPR00100", "EMPLOYID like'7???'")
txtEmpid.Value = EmpId + 1
ElseIf fraEmp.Value = 2 And IsEmpty(txtEmpid.Value) Then
EmpId = DMax("EMPLOYID", "UPR00100", "EMPLOYID like'8???'")
txtEmpid.Value = EmpId + 1
End If

If DCount("[EMPLOYID]", "UPR00100", "[EMPLOYID]='" & test & "'") Then
txtEmpid.Value = "7000"
End If
End Sub
Click to expand...


Here's some air code that should demonstrate the idea:

Private Sub cmdExe_Click()
Dim EmpId As String

If fraEmp.Value = 1 And IsNull(txtEmpid.Value) Then
EmpId = Nz(DMax("EMPLOYID", "UPR00100", _
"EMPLOYID Between '7000' And '7999'), '7000')
txtEmpid.Value = CStr(CLng(EmpId) + 1)
ElseIf fraEmp.Value = 2 And IsNull(txtEmpid.Value) Then
EmpId = Nz(DMax("EMPLOYID", "UPR00100", _
"EMPLOYID Between '8000' And '8999'), '8000')
txtEmpid.Value = CStr(CLng(EmpId) + 1)
End If

End Sub
 
Back
Top