Help with IIF statement

[Guest]

I need help with an iff statement on my form. Basically I have a new record
when the "Insert" key is pressed. But I want to stay with the current
record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours.
Otherwise a new record is called for. I think I'm close here but a little
help would be appreciated. Thanks, Randy

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrRoutine

Select Case KeyCode
Case vbKeyInsert
If [TotalHrs] > 0 Then
If IDRb_subform.Form!SumHours <> [TotalHrs] Then
If MsgBox("Total Hours for Applicants to not agree with Total
Hours", vbYes + vbDefaultButton2) <> vbYes Then
Me.Form![TotalHrs].SetFocus

'Case Else
'End Select

Else
End If
End If
End If

Select Case KeyCode
Case vbKeyInsert
DoCmd.GoToRecord , , acNewRec
Case Else
End Select
ErrRoutine:
If Err.Number = 2105 Then
Resume Next
End If
End Select
End Sub

 

[banem2]

I need help with an iff statement on my form. Basically I have a new record
when the "Insert" key is pressed. But I want to stay with the current
record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours.
Otherwise a new record is called for. I think I'm close here but a little
help would be appreciated. Thanks, Randy

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrRoutine

Select Case KeyCode
Case vbKeyInsert
If [TotalHrs] > 0 Then
If IDRb_subform.Form!SumHours <> [TotalHrs] Then
If MsgBox("Total Hours for Applicants to not agree with Total
Hours", vbYes + vbDefaultButton2) <> vbYes Then
Me.Form![TotalHrs].SetFocus

'Case Else
'End Select

Else
End If
End If
End If

Select Case KeyCode
Case vbKeyInsert
DoCmd.GoToRecord , , acNewRec
Case Else
End Select
ErrRoutine:
If Err.Number = 2105 Then
Resume Next
End If
End Select
End Sub

Hi Randy,

I think you need to check if the record is dirty and If [TotalHrs] >
0

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty and IDRb_subform.Form!SumHours > 0 Then
Cancel = True
MsgBox "Total Hours for Applicants to not agree with Total Hours",
_
vbOKOnly, "Cannot Update"
End If
End Sub

In case you add record in subform only, then you can use something
similar on BeforeUpdate of subform.

Regards,
Branislav Mihaljev

 

[Guest]

welllll....not entirely clear what you mean by an Insert 'key'.... button?

Guessing that you have a button that, via wizard, is set up to go to next
record/new record....

So to wrap an if/then around this button, to make it conditional, then you
have to select that button's OnClick property and open the VB code for that
button. ... look for the code line with DoCmd.FindNext (or New depending on
which it is set up to do...)

in the line above this command put:
If Me.[TotalHrs] = Me![IDRb_subform].Form!SumHours Then

and in the line below the DoCmd put:
End If

This essentially makes the change record command conditional to the values
you define.

hope this helps

 

[Guest]

The Insert Key is not a button, it is the actual "Insert" key on the
keyboard. Hence: Case vbKeyInsert. When I press this key a new record
appears.

welllll....not entirely clear what you mean by an Insert 'key'....
button?

Guessing that you have a button that, via wizard, is set up to go to next
record/new record....

So to wrap an if/then around this button, to make it conditional, then you
have to select that button's OnClick property and open the VB code for
that
button. ... look for the code line with DoCmd.FindNext (or New depending
on
which it is set up to do...)

in the line above this command put:
If Me.[TotalHrs] = Me![IDRb_subform].Form!SumHours Then

and in the line below the DoCmd put:
End If

This essentially makes the change record command conditional to the values
you define.

hope this helps
--
NTC

I need help with an iff statement on my form. Basically I have a new
record
when the "Insert" key is pressed. But I want to stay with the current
record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours.
Otherwise a new record is called for. I think I'm close here but a
little
help would be appreciated. Thanks, Randy

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrRoutine

Select Case KeyCode
Case vbKeyInsert
If [TotalHrs] > 0 Then
If IDRb_subform.Form!SumHours <> [TotalHrs] Then
If MsgBox("Total Hours for Applicants to not agree with Total
Hours", vbYes + vbDefaultButton2) <> vbYes Then
Me.Form![TotalHrs].SetFocus

'Case Else
'End Select

Else
End If
End If
End If

Select Case KeyCode
Case vbKeyInsert
DoCmd.GoToRecord , , acNewRec
Case Else
End Select
ErrRoutine:
If Err.Number = 2105 Then
Resume Next
End If
End Select
End Sub