Compare 2 fields of current record on continuous form

[Wolfen02]

I'm trying to compare two fields (date1, date2) on the current record of a continuous form. If the user attempts to enter information into Date1 when Date2 is not null, an error message is produced. The code below works fine for single form, but does not distinguish between records on a continuous form. The txtDateChoice shown in code refers to MS Date and Time Picker Control object. Looking for any suggestions!

If Me.txtDateChoice.Value > 0 Then
If Me.ActiveControl.Value = Me.txtDateChoice.Value Then
Me.ActiveControl.Value = ""
Else
Me.ActiveControl.Value = Me.txtDateChoice.Value
DoCmd.Requery
If Forms![frmDates]![Date2].Value > 0 Then
MsgBox "Conflicting records"
Forms![frmDates]![Date1].Value = ""
End If
End If
Else
Me.ActiveControl.Value = ""
End If

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[payback]

It looks to me as if you are getting a little complex to achieve a simple result or else I am reading you question incorrectly :-(

Why are you using ActiveControl and why a requery?

In a form (single, continuous, datasheet) you can refer to each control by name (without the .value also) and it generally relates to the current record only - therefore in the BeforeUpdate event of a control you can put code to check other controls in the same record and trap accordingly.

Does this help?

 

[jimlee]

hi wolfen, did you ever find out how to do this, i have exactly the same problem?

jimlad

 

[Wolfen02]

Jimlee,
Actually Payback's remark about the requery was the trick. Leaving off the requery command was all it took to make the code work like a charm.

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[jimlee]

of course! so bloody obvious when you think about it! thanks alot, you just saved some unfortunate bugger getting a flying coffee mug in the head as a result of my frustration!!

jimlad