vbYesNo issue to move to record

[Shift838]

I am wanting to do a find command on the database on a before update event. I have the part that it will find the record if it already exist or not.

Next I want to have a vbYesNo Message box to ask the user if they want to continue and create a duplicate record, if so it will continue if "yes" is clicked. If "no" is clicked it will move to the first record of the database and in effect cancel before it updates and saves the record to the database. I am getting an error stating it cannot move to the first record. below is my code:

Private Sub UserID_BeforeUpdate(Cancel As Integer)
MsgBox "searching before updating"

Dim Criteria As String
Dim IDName As String

Dim MyRS As DAO.Recordset ' Recordset used to search.

Set MyRS = Me.RecordsetClone

IDName = Chr$(34) & UserID.Text & Chr$(34)
Criteria = "[IDNAME]=" & IDName

MyRS.FindFirst Criteria

If MyRS.NoMatch Then
MsgBox "User ID: " & UserID.Text & " not in database, Click OK to continue to save."
Else
Dim msgstring As String
Dim message

msgstring = "ID already exist in database, do you want to continue to save ID in database and make a duplicate record?"
message = MsgBox(msgstring, vbYesNo)

If message = vbNo Then

DoCmd.GoToRecord acDataForm, "frmNSAP", acGoTo, 1

End If

End If
MyRS.Close
End Sub

 

[hneal98]

I think you are confusing the Input box with the message box. If you use the message box, then you need to test for VbMsgBoxResult. You can not just assign the messagebox results to a variable.

Look in Access help to get more info on VbMsgBoxResult.

 

[Shift838]

i have tested the message box results so I know when I click on the appropriate yes or no button I get the correct message. I want to cancel the save of the record and move back to the first record.

 

[Remou]

I think you need another two lines:

<...>
   If message = vbNo Then
   [blue]Cancel = True
   Me.Undo[/blue]
   DoCmd.GoToRecord acDataForm, "sdf", acGoTo, 1

 

[Shift838]

Remou,

That worked, however when it goes to the first record I get an error stating:

"You must save the field before executing the GoToControl Action, GotoControl Method, or the SetFocus Method.

 

[Remou]

I used all your code in the test, and did not get this error. Have you got an On Current event?

 

[Shift838]

sorry. Yes I do have an on current event as :

Me.TabCtl0.Pages(0).SetFocus

That is what is throwing the error.

It works fine if I remove the docmd.gotorecord and just leaves a blank form.

 

[Remou]

Are you happy with that?

 

[Shift838]

I really wanted it to move back to the first record.

 

[Remou]

As far as I know, this is due to using the Before Update event. Try this as a workaround:

'At module level
Dim blnFudge

Private Sub Form_Current()
If Not blnFudge Then
    Me.TabCtl0.Pages(0).SetFocus
End If
blnFudge = False
End Sub

Private Sub UserID_BeforeUpdate(Cancel As Integer)
<...>    
   If message = vbNo Then
   Cancel = True
   Me.Undo
   [blue]blnFudge = True[/blue]
   DoCmd.GoToRecord acDataForm, "frmNSAP", acGoTo, 1
      
   End If
   
End If
MyRS.Close
End Sub

 

[Shift838]

you said "At Module Level" what do you mean by that? Do I need to create a module or something?

 

[Remou]

No, I just mean at the top of the code window for the form, like so:

[tt]Option Compare Database
Option Explicit
Dim blnFudge[/tt]

 

[Shift838]

This works however when it goes back to the first record it clears out ID Name field which it is focused on.

 

[Remou]

Can you post your code as modified to suit you application?

 

[Shift838]

Here is my code:

Option Compare Database
Option Explicit
Dim blnFudge

---------------------
Private Sub Form_Current()
If Not blnFudge Then
Me.TabCtl0.Pages(0).SetFocus
End If
blnFudge = False

'Me.TabCtl0.Pages(0).SetFocus ' Sets focus back to Tab 0 main tab when navigating through records
calendar.Value = Date

Dim cgroup As Integer
cgroup = 0

If blnallowidcheckout Then
blnbstseai.Enabled = True
blnbstsmts.Enabled = True
blnbstsdb.Enabled = True
blnbstsests.Enabled = True
blnbsmo.Enabled = True
blnbsstaff.Enabled = True
blnbsabap.Enabled = True
blnbschangesup.Enabled = True
blnbsfinance.Enabled = True
blnbscommkt.Enabled = True
blnbssupply.Enabled = True
blnsstools.Enabled = True
blnsseim.Enabled = True
blnbsqrrc.Enabled = True
blnbsqrrc.Enabled = True
blnioiptel.Enabled = True
blniopcm.Enabled = True
blniocts.Enabled = True
blnionts.Enabled = True
blnioeurope.Enabled = True
blnioasia.Enabled = True
blniobrazil.Enabled = True
Else
blnbstseai.Enabled = False
blnbstsmts.Enabled = False
blnbstsdb.Enabled = False
blnbstsests.Enabled = False
blnbsmo.Enabled = False
blnbsstaff.Enabled = False
blnbsabap.Enabled = False
blnbschangesup.Enabled = False
blnbsfinance.Enabled = False
blnbscommkt.Enabled = False
blnbssupply.Enabled = False
blnsstools.Enabled = False
blnsseim.Enabled = False
blnbsqrrc.Enabled = False
blnioiptel.Enabled = False
blniopcm.Enabled = False
blniocts.Enabled = False
blnionts.Enabled = False
blnioeurope.Enabled = False
blnioasia.Enabled = False
blniobrazil.Enabled = False

End If
If blnbstseai.Value Or _
blnbstsmts.Value Or _
blnbstsdb.Value Or _
blnbstsests.Value Or _
blnbsmo.Value Or _
blnbsstaff.Value Or _
blnbsabap.Value Or _
blnbschangesup.Value Or _
blnbsfinance.Value Or _
blnbscommkt.Value Or _
blnbssupply.Value Or _
blnsstools.Value Or _
blnsseim.Value Or _
blnbsqrrc.Value Or _
blnbsqrrc.Value Or _
blnioiptel.Value Or _
blniopcm.Value Or _
blniocts.Value Or _
blnionts.Value Or _
blnioeurope.Value Or _
blnioasia.Value Or _
blniobrazil.Value Then
cgroup = cgroup + 1
End If

If cgroup > 0 Then
blnallowidcheckout.Value = True
Else
blnallowidcheckout.Value = False
End If

End Sub

-------------------------

Private Sub UserID_BeforeUpdate(Cancel As Integer)
'MsgBox "searching before updating"

Dim Criteria As String
Dim IDName As String

Dim MyRS As DAO.Recordset ' Recordset used to search.

Set MyRS = Me.RecordsetClone

IDName = Chr$(34) & UserID.Text & Chr$(34)
Criteria = "[IDNAME]=" & IDName

MyRS.FindFirst Criteria

If MyRS.NoMatch Then
MsgBox "User ID: " & UserID.Text & " not in database, Click OK to continue to save."
Else
Dim msgstring As String
Dim message

msgstring = "ID already exist in database, do you want to continue to save ID in database and make a duplicate record?"
message = MsgBox(msgstring, vbYesNo)

If message = vbNo Then
Cancel = True
Me.Undo
blnFudge = True
DoCmd.GoToRecord acDataForm, "frmNSAP", acGoTo, 1
End If

End If
MyRS.Close
End Sub

 

[Remou]

I tried your code making only this change:
DoCmd.GoToRecord , , acGoTo, 1
The field on which it is focused is not cleared. I am not sure why the focus would change to User Name from UserID. Is there a sequence of events I can try to duplicate this error?

I notice that your Before Update code is set to run regardless of whether the user is in a new record or not, which leads to some odd effects.