Automatically assigning values based on next available

S

Spidey3721

I have a table that includes apartment numbers (AptID) for a building (ex:
201,202,etc...)
Same table has different CaseID numbers, that track service calls to that
apartment (201.001,201.002,etc...)

I want to have a form that automatically assigns the next incremental number
based on the apartment unit I am adding a case to (if 201.002 was the last
caseID entered for apartment 201, I want the form to automatically assign a
CaseID number of 201.003 as soon as I enter unit 201 in the AptID field)

Wondering if anyone has any ideas on how I can accomplish this.
 
T

tina

try calling the following procedure from the AfterUpdate
event of the AptID control on your form:

Private Sub isNextNumber()

Dim strMax As String

strMax = Nz(DMax("CaseID", "TableName", "Left(CaseID,
Len(CaseID) - 4) = '" & Me!AptID & "'"), 0)

If strMax = 0 Then
Me!CaseID = Me!AptID + 0.001
Else
Me!CaseID = strMax + 0.001
End If

End Sub

1. change "TableName" to the name of your table that
records the service calls.
2. if "CaseID" is not the correct name of the field in
that table, correct it in the code above.
3. if Me!CaseID is not the name of the form control you
want the new ID to show in, correct it in the code above.
4. if Me!AptID is not the name of the form control you
enter the apt # in, correct it in the code above.

hth
 
S

Spidey3721

I had this all working as described in earlier posts, but for many other
reasons - I changed my CaseID to a number and got rid of compounded ID
numbers all together (using UnitID=301 & CaseID = 7 instead of
CaseID="301.007"

This simplfied the equation that I need here to calculate the "last used"
CaseID number, but I'm getting an error now (Data Type mismatch in criteria
expression). Both CaseID and UnitID are numbers (Long Integer, General, Auto
Decimal places). The [UnitCombo] Combobox references UnitID's from the
[Service Calls Table] itself....



Private Sub UnitCombo_Change()
Dim strmax As String

'--------------------------
'strmax = Nz(DMax("[CaseID]", "[Service Calls Table]",
"Left([CaseID],Len([CaseID]) - 4) = '" & Me![UnitCombo] & "'"), 0)
'OLD Statement before revision to CaseID
'--------------------------

strmax = Nz(DMax("[CaseID]", "[Service Calls Table]", "[UnitID]" & "='"
& Me![UnitCombo] & "'"), 0)
If strmax = 0 Then
Me!CaseID = 1
Else
Me!CaseID = strmax + 1
End If

End Sub
 
J

John Vinson

This simplfied the equation that I need here to calculate the "last used"
CaseID number, but I'm getting an error now (Data Type mismatch in criteria
expression). Both CaseID and UnitID are numbers (Long Integer, General, Auto
Decimal places). The [UnitCombo] Combobox references UnitID's from the
[Service Calls Table] itself....
Click to expand...

Leave off the quotemarks delimiting the criterion: they are needed for
Text datatype, but Number datatypes should not use any delimiter. You
can also simplify your string concatenation a bit - it's not necessary
to have separate string constants for the fieldname and the equals
sign:

strmax = Nz(DMax("[CaseID]", "[Service Calls Table]", "[UnitID]="
& Me![UnitCombo]), 0)
 
Top