Problem inserting new record when checking for dups

[BJS]

Hello,

I have a form "frmMain" and a subform "frmSubMain".

On frmMain, I have a "Insert Button that will insert a new record into the subform "frmSubMain". When a new record is inserted, it takes the value of the field named "txtDistTo" from the previous record and populates the "txtDistFrom" field with that value in the new record. This code works great, but as soon as I added the "Before Update Event" for the "txtSAMPLE_ID" on the subform, it no longer works.

Here is the code for the "Insert Button" on frmMain:

Forms!frmMain.SetFocus

If Me.AllowEdits = False Then
Me.AllowEdits = True
[txtEditMode] = "Edit ON"
txtEditMode.ForeColor = vbRed
End If

Forms!frmMain!frmSubMain.SetFocus

If Me.AllowEdits = False Then
Me.AllowEdits = True
End If

Dim PrevTo As Variant

DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "txtDistTo"
PrevTo = Forms!frmMain!frmSubMain![txtDistTo]

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "txtDistFrom"
Forms!frmMain!frmSubMain![txtDistFrom] = PrevTo

On the "frmSubMain" I have a field named, "txtSAMPLE_ID".
I have written code for the "Before Update Event" for this field so that it first checks if the user has entered a duplicated Sample No. If so, it sends a message to the user and cancels the event. That part works fine, with only that piece of the code.

The next part of this Before Update code is the same as I have behind the "Insert Button" on the main form. As soon as I added this part of the code, I get an error message when clicking the insert button.

Here is the code for the "Before Update" event on the subform:

Forms!frmMain!frmSubMain.SetFocus

If DCount("[SAMPLE_ID]", "DETAILS", "[SAMPLE_ID]= '" & Nz(Me![txtSAMPLE_ID].Text) & "'") = 1 Then
MsgBox "You have entered a duplicate Number! Try again!", vbOKOnly, "Duplicate Entry"

Cancel = True
Me.Undo

Dim PrevTo As Variant

DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "txtDistTo"
PrevTo = Forms!frmMain!frmSubMain![txtDistTo]

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "txtDistFrom"
Forms!frmMain!frmSubMain![txtDistFrom] = PrevTo

I get runtime error 2108 indicating that the field must first be saved. It seems to hang up on the line "Docmd.GoToControl "txtDistTo"

I'm in a real bind. I hope someone can help!
Many thanks in advance.

BJS
"

 

[BJS]

Hi Pat,

I have modified my code, to reflect changes you have recommended. I am still having the same problem.
This code is occuring in the sub form, not the main form.
The error message I am getting is:

"Run-time error '2108': You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method."

Here is the modified code:

Private Sub txtSAMPLE_ID_BeforeUpdate(Cancel As Integer)

If DCount("[SAMPLE_ID]", "DETAILS", "[SAMPLE_ID]= '" & Nz(Me.[txtSAMPLE_ID]) & "'") = 1 Then
MsgBox "You have entered a duplicate Number! Try again!", vbOKOnly, "Duplicate Entry"

Cancel = True
Me.Undo

End If

Dim PrevTo2 As Variant

DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "txtDistTo" 'HERE IS WHERE IT HANGS UP!
PrevTo2 = [txtDistTo]
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "txtDistFrom"
[txtDistFrom] = PrevTo2


I have been at this all day today. It is the last thing I need resolved to get this database into production.

Your help is greatly appreciated!!!

BJS

 

[BJS]

Pat, here is what is suppose to happen:

The 3 fields in the record that are of interest are:

SAMPLE_ID: text field, no duplicates allowed (e.g. 1045A, 33789)
txtDistTo: number field (e.g. 138.00, 79.00)
txtDistFrom: number field (must be same value as txtDistTo in previous record)

1. When inserting a new record into the subform, I need the value of the txtDistFrom field in the new record to be the same value as the txtDistTo field of the previous record.
The code worked great (before I added the "Before Update" event) when using the insert button I have on the main form.

2. At the same time, when a new record is entered, the SAMPLE_ID field is not allowed to contain a duplicate value. This field is not a typical id field, it is just a text field, but no duplicates are allowed.

If the text entered in the SAMPLE_ID field of the new record is a duplicate, I want the user to be notified that it is a duplicate value and then force the user to enter a different value in the SAMPLE_ID field.

3. I've been using "GoToControl" to pick up the value of the txtDistTo field and populate the txtDistFrom field with it in the new record.

I hope I have explained this more clearly.

Thank you for looking at this for me.

BJS

 

[BJS]

O.K. I finally have it figured out!

In the before update event of the SAMPLE_ID field, I have the following code:

Private Sub txtSAMPLE_ID_BeforeUpdate(Cancel As Integer)

If DCount("[SAMPLE_ID]", "DETAILS", "[SAMPLE_ID]= '" & Nz(Me.[txtSAMPLE_ID]) & "'") = 1 Then
MsgBox "You have entered a duplicate Number! Try again!", vbOKOnly, "Duplicate Entry"

Cancel = True
Me.Undo

End If

End Sub


In the after update event of the SAMPLE_ID field, I have the following code:

Private Sub txtSAMPLE_ID_AfterUpdate()

Dim PrevTo As Variant

DoCmd.GoToRecord , , acPrevious
PrevTo = [txtDistTo]

DoCmd.GoToRecord , , acLast
[txtDistFrom] = PrevTo
DoCmd.GoToControl "txtDistTo"

End Sub


In the on-click event of the insert button on the main form, I have the following code:

Private Sub cmdInsertSample_Click()
On Error GoTo Err_cmdInsertSample_Click

Forms!frmMain.SetFocus

If Me.AllowEdits = False Then
Me.AllowEdits = True
[txtEditMode] = "Edit ON"
txtEditMode.ForeColor = vbRed
End If

Forms!frmMain!frmSubMain.SetFocus

If Me.AllowEdits = False Then
Me.AllowEdits = True
End If

Dim PrevTo As Variant

DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "txtDistTo"
PrevTo = Forms!frmMain!frmSubMain![txtDistTo]

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "txtDistFrom"
Forms!frmMain!frmSubMain![txtDistFrom] = PrevTo
DoCmd.GoToControl "txtSAMPLE_ID"

End Sub


BOY AM I HAPPY! Thanks for your time and tips Pat!
BJS