Hi I am new here at using Access (1 month only so please be patient with me, I really do appreciate your time).
I have a button that when clicked moves a piece of data to a subform.
I have put the whole sequence below.
The trouble I am having is :
- The event will not happen until the form is saved. I think this is because fkTaskID is a new record (auto) number which hasn't generated yet.
- If I force the form to save it does work but then I get an error on the INSERT command when not all required fields of the form are complete (see sample in second part below).
Is there a way to save the record and maintain the integrity of the form input - and still have this code work? or any other ideas.....I hope this makes sense
Thank you
Rhonda
BUTTON CODE
==========
Private Sub BTNAddReasonRw_Click()
Dim dIndex As Long
DoCmd.SetWarnings False
For dIndex = 0 To Me.LISTReworkReasonsUnselected.ListCount - 1
If Me.LISTReworkReasonsUnselected.Selected(dIndex) Then
DoCmd.RunSQL "INSERT INTO ReworkT(fkTaskID, fkReworkReasons) VALUES(" & Me.pkTASKID & ", " & Me.LISTReworkReasonsUnselected.ItemData(dIndex) & ")"
End If
Next
DoCmd.SetWarnings True
RequeryListBoxes
End Sub
CHECKING INTEGRITY OF DATA INPUT
========================
Private Sub Form_BeforeUpdate(Cancel As Integer)
'PROTECT INTEGRITY OF DATA INPUT
'-------------------------------------------
Dim dField As String
Dim dControl As Control
If IsNull(Me.fkDebtorCode) Then
dField = "customer"
Set dControl = Me.TXTBOXDebtorCode
ElseIf IsNull(Me.fkTaskType) Then
dField = "task type"
Set dControl = Me.COMBOTaskType
'Check if Task Type is Application
'---------------------------------
ElseIf Me.fkTaskType = 1 Then
If IsNull(Me.fkAppform) Then
dField = "application format"
Set dControl = Me.COMBOApplicationFormat
'ElseIf ... Then
End If
End If
If dField > "" Then
MsgBox "Please select a " & dField, vbOK, "Required field"
dControl.SetFocus
Cancel = True
End If
End Sub
I have a button that when clicked moves a piece of data to a subform.
I have put the whole sequence below.
The trouble I am having is :
- The event will not happen until the form is saved. I think this is because fkTaskID is a new record (auto) number which hasn't generated yet.
- If I force the form to save it does work but then I get an error on the INSERT command when not all required fields of the form are complete (see sample in second part below).
Is there a way to save the record and maintain the integrity of the form input - and still have this code work? or any other ideas.....I hope this makes sense
Thank you
Rhonda
BUTTON CODE
==========
Private Sub BTNAddReasonRw_Click()
Dim dIndex As Long
DoCmd.SetWarnings False
For dIndex = 0 To Me.LISTReworkReasonsUnselected.ListCount - 1
If Me.LISTReworkReasonsUnselected.Selected(dIndex) Then
DoCmd.RunSQL "INSERT INTO ReworkT(fkTaskID, fkReworkReasons) VALUES(" & Me.pkTASKID & ", " & Me.LISTReworkReasonsUnselected.ItemData(dIndex) & ")"
End If
Next
DoCmd.SetWarnings True
RequeryListBoxes
End Sub
CHECKING INTEGRITY OF DATA INPUT
========================
Private Sub Form_BeforeUpdate(Cancel As Integer)
'PROTECT INTEGRITY OF DATA INPUT
'-------------------------------------------
Dim dField As String
Dim dControl As Control
If IsNull(Me.fkDebtorCode) Then
dField = "customer"
Set dControl = Me.TXTBOXDebtorCode
ElseIf IsNull(Me.fkTaskType) Then
dField = "task type"
Set dControl = Me.COMBOTaskType
'Check if Task Type is Application
'---------------------------------
ElseIf Me.fkTaskType = 1 Then
If IsNull(Me.fkAppform) Then
dField = "application format"
Set dControl = Me.COMBOApplicationFormat
'ElseIf ... Then
End If
End If
If dField > "" Then
MsgBox "Please select a " & dField, vbOK, "Required field"
dControl.SetFocus
Cancel = True
End If
End Sub
