checkbox to generate control number

[soulpiercing]

Help!

The work I do requires that I assign control numbers but only in certain circumstances depending on the final resolution of cases. What I want to do is have a checkbox or button that will, once checked, generate a control number.

Issues:

If the box is unchecked after being checked, I do not want other numbers to adjust - either the number should remain or be deleted and have a missing number in the log.

Also, if unchecked then checked again, I do not want another number to be assigned.

Is this possible?

Thanks

 

[rockman]

Hmmm...

Not exactly sure what you are getting at. Give an example of a "control number". Is there a fldControlNumber in each record of your table?

Do the "control numbers" increase sequentially if the checkbox is clicked?

More info please.

 

[soulpiercing]

control number info

there is a controlnumber field in the table and it is referenced on the form (but I plan to protect the field so that the info cannot be manually typed in).

The number should increase each time the box is checked but no duplicates and preferably no unassigned numbers.

Once a number is assigned, it must remain constant - cannot be removed or changed.

Hope this helps.

 

[WayneRyan]

Jason,

I don't have any examples, since I don't have
Access with me. You'll have to look up the
syntax for the DMax function.

You could use the AfterUpdate event on your
checkbox:

If IsNull(Me.ctlNumber) Then
ctlNumber = DMax(...) + 1
End If

That way:

If never been checked, assign number.

If unchecked, the checkbox indicates a
status of not active.

If rechecked, the checkbox indicates a
status of active (again).

The checkbox is a yes/no field in your
table.

hth,
Wayne

 

[rockman]

You best be good with code to venture into this task. There is no quick and easy way to do it without coding it (in my experience). Here's a start. For the checkbox control try this untested code...

Private Sub chkGenerateControlNumber_Click()
Dim rsGetMax as ADODB.recordset

   If txtControlNumber.value & "" <> "" Then Exit Sub  'control number already exists on this record
   Set rsGetMax.OpenRecordset("SELECT TOP 1 fldControlNumber FROM tblData ORDER BY fldControlNumber DESC;")
   txtControlNumber.value = rsGetMax!fldControlNumber + 1
   Set rsGetMax = Nothing

End Sub

HTH,
Jeff