incremental values when there is a composite index

[uscitizen]

in a form i call 'Concomitant Medications' (whose record source is the called the same), i have a compsite primary index involvind 'Patient Number' and 'Med Number'. 'Med Number' is 'just' a way to be able to enter more than one medication per patient and has no intrinsic meaning. there is a drop down combo box on Med Number with values from 1 to 12.

in the after update event of 'Patient Number' i have placed the following vba code:


Private Sub Patient_Number_AfterUpdate()
[Med Number] = IIf(IsNull("[Med Number]&quot, 1, 1 + DMax("[Med Number]", "Concomitant Medications", "[Patient Number] = " & Me![Patient Number]))
If Me![Med Number] > 12 Then
RetValue = MsgBox("Delete any records exceeding the upper limit", vbInformation)
End If
End Sub

the idea being Med Number will be incrementally added to the Med Number field until you try to create a record when the value 12 exists already for that Patient Number.

well, what's happening is that ms a2k is giving me a 'Run Time 3314' along with the following narrative: "The field 'Concomitant Medications.Med Number' cannot contain a Null value because the Required property for this field is True. Enter a value in this field'.

well i guess i sort of have to agree with the good doctor's prescription but i wonder whether there's not some way to bypass this error message and draft one of my own which does not allow the user the opportunity to 'debug' my program?

anyone?

 

[MichaelRed]

It is generally refered to as error trapping, although the more general process is also called Verification and Validation. Examples and explinations abound in the literature, starting with the ubiquitous {F1}, and continuing through these fora on into third party manuals/tutorials.

In general terms, the process should be done BEFORE submitting the information for actual update of the data.


If you have not reviewed and purchased at least one third party manual/tutorial, I would suggest a visit to your local bookstore,





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[uscitizen]

i have one somewhere of limited value which was the reason prompting me to write to this 'list'. this phenomenon occurs, btw, only occurs when the value of 'Med Number' is blank -- which is true only when no other records for the 'Patient Number' whatever have been entered.