Automatically assigning values based on next available

[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.

 

[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

 

[Spidey3721]

I think that I follow - One thing I am having trouble interpreting though -

Forgive me - I am a little new at this - wondering of you could explain what
the - '" & Me!AptID & "'" - part does; specifically the quotations and the
&'s. I am assuming that the Me! brings the focus to the current form

 

[tina]

no, Me! doesn't do anything to the form's focus.
a domain aggregate function such as DMax() takes strings
for its' arguments. when you are using the value from a
form control (Me refers to the form that the class
module "belongs" to, AptID is the name of the form
control) rather than an "hard-coded" value, you have to
concatenate (by using & or +) the control reference into
the string argument in the function. the string

"Left(CaseID, Len(CaseID) - 4) = Me!AptID"

could not be executed by the function.

fyi, i wrote this making the assumption that the CaseID
field in your table is a text field. if you made it a
number field, suggest you consider changing it to text if
you can do so safely. as a Double number field, it will
use more memory than a text field holding the values you
describe. also, in a text field you will not have to
contend with "dropped" zeroes, either at the beginning or
end of the value. as a text field, you can have apt
numbers from 1 to...anything (left side of the decimal
point), and up to 999 service records for each apt (based
on the example you used in your original post).

hth

 

[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

 

[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....

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)