Save referenced record and goto duplicate record?
- Thread starter Kila
- Start date
OK...this is the AfterUpdate code you created:
Private Sub cmbSSN_AfterUpdate()
If IsNull(Me.cmbSSN) Then
Me.Filter = ""
Exit Sub
End If
If DCount("CustomerID", "Customers", "[SSN] = [Forms]![frmCustomersPOP]![cmbSSN]") < 1 Then
MsgBox "No entry for this SSN number - please complete a new record"
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.SSN = Me.cmbSSN
Else
DoCmd.ApplyFilter , "[SSN] = Forms!frmCustomersPOP!cmbSSN"
End If
End Sub
However, I got it to work once the first time I went in, and then I experimented with it, making mistakes the user would (and will when we get on the floor), like putting in the wrong number and trying again. After that, when I put in a new number, it flickered past, updating the record, then the thing went straight to a new, empty record (I can tell b/c the field you added had AutoNum in it). If I hit "back", I could get to the new SSN. What might cause this that it does not work the same way every time? Why is it creating a new record AFTER holding the SSN?
Private Sub cmbSSN_AfterUpdate()
If IsNull(Me.cmbSSN) Then
Me.Filter = ""
Exit Sub
End If
If DCount("CustomerID", "Customers", "[SSN] = [Forms]![frmCustomersPOP]![cmbSSN]") < 1 Then
MsgBox "No entry for this SSN number - please complete a new record"
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.SSN = Me.cmbSSN
Else
DoCmd.ApplyFilter , "[SSN] = Forms!frmCustomersPOP!cmbSSN"
End If
End Sub
However, I got it to work once the first time I went in, and then I experimented with it, making mistakes the user would (and will when we get on the floor), like putting in the wrong number and trying again. After that, when I put in a new number, it flickered past, updating the record, then the thing went straight to a new, empty record (I can tell b/c the field you added had AutoNum in it). If I hit "back", I could get to the new SSN. What might cause this that it does not work the same way every time? Why is it creating a new record AFTER holding the SSN?
I think I have located the problem, but not fixed it.
I am thinking that I have located the problem, but I am struggling with the syntax. Here is the OnCurrent code for the ActiveQuestions sub-subform. I am trying to get it to requery the combo box on the ActiveAnswers subform (I disabled the AfterInsert command there), but I keep getting various errors.
Sub Form_Current()
Dim ParentDocName As String
Dim ctlActiveAnswer As Control
'Set ctlActiveAnswer = Forms("frmSurvey").Form_
.Controls("subActiveAnswer").Form.Controls("cboAnswerID")
On Error Resume Next
ParentDocName = Me.Parent.Name
If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
'DoCmd.Requery ([ctlActiveAnswer])
'Me.Parent.Parent.Form.Controls("frmActiveAnswer")_
.Form!ctlActiveAnswer.Requery
Me.Parent.Parent.Form!subActiveAnswer.Form_
!ctlActiveAnswer.Requery
Rem ctlActiveAnswer.Requery
Rem Me.Parent![frmActiveAnswer].Requery
End If
Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub
I am thinking that I have located the problem, but I am struggling with the syntax. Here is the OnCurrent code for the ActiveQuestions sub-subform. I am trying to get it to requery the combo box on the ActiveAnswers subform (I disabled the AfterInsert command there), but I keep getting various errors.
Sub Form_Current()
Dim ParentDocName As String
Dim ctlActiveAnswer As Control
'Set ctlActiveAnswer = Forms("frmSurvey").Form_
.Controls("subActiveAnswer").Form.Controls("cboAnswerID")
On Error Resume Next
ParentDocName = Me.Parent.Name
If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
'DoCmd.Requery ([ctlActiveAnswer])
'Me.Parent.Parent.Form.Controls("frmActiveAnswer")_
.Form!ctlActiveAnswer.Requery
Me.Parent.Parent.Form!subActiveAnswer.Form_
!ctlActiveAnswer.Requery
Rem ctlActiveAnswer.Requery
Rem Me.Parent![frmActiveAnswer].Requery
End If
Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub
