Access 2010 is giving me an error when I try to open a form

[ro88y09]

hello and good day to you all!

I have an issue with my VBA coding and I must inform you that my VBA is not up to par. I have little understanding of it. I think this example, however, is straight forward. I have form, Contacts Form, that is used to update all my contacts in my table, tblContacts. My VBA coding is suppose to (when I save or close the form) tell me that the First name AND Last name that I entered already exists. Then it is suppose to ask me if I would like to go to that record source.

As you can probably tell, I am trying to reduce duplicate values to a certain degree. Granted there could be 2 DIFFERENT bob smiths and I wouldn't want to eliminate the possibility of not including both. Below is my current code. The code works fine for all the red portion (it prompts me before closing or saving and the correct dialogue box comes up). But as soon as I try to execute the green portion (by selecting "yes"), I get the error: "Run-time error '3021': No current record."

I believe what I really need is a command that will take me to the record that has the same first and last name (there could even be a possibility of more than 1 similar contact I'd assume).

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
ContactNumber = Nz(DFirst("ID", "tblContacts", "[First Name]='" & txtFirstName & "' AND [Last name]='" & txtLastName & "'"))


If ContactNumber <> 0 Then
Response = MsgBox("This name already exists." & vbCrLf & "Do you want to go to that record?", vbYesNo)
If Response = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID] = " & ContactNumber
Me.Bookmark = rs.Bookmark
End If
End If
End If
End Sub

Thank you in advance for you time in reading this and helping

[June7]

Don't know about the DoMenuItem code, never used it. Try:

If Response = vbYes Then
Cancel = True
Me.RecordsetClone.FindFirst "CustID=" & Me.ContactNumber
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

[ro88y09]

It gave me "Compile error: Method or data member not found"

It highlights ".ContactNumber" when the warning says that.

p.s. It's suppose to be ID not CustID. My apologies

[June7]

Sorry, ContactNumber is a declared variable, not a field/control on form - remove the Me. prefix.

Also, the Nz() should return empty string, not 0 because you have not given 0 as alternative.

ContactNumber = Nz(DFirst("ID", "tblContacts", "[First Name]='" & txtFirstName & "' AND [Last name]='" & txtLastName & "'"),0)

[ro88y09]

I get the original error when I use this:
If Response = vbYes Then
Cancel = True
Me.RecordsetClone.FindFirst "ID="
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

im surprised there's not someway that I can use the DoCmd.openform and then navigate it to the ID that has the same entries. Is that not plausible I assume?

[June7]

I haven't really had need for Form_BeforeUpdate so am learning here. This event is triggered when you try to close form or navigate to new record? How are you closing and navigating? The form is already open so need to cancel the close action if the record update fails. I am thinking the RecordsetClone code needs to go in different event. Need to cancel the close action so form remains open then go to the existing record.

[ro88y09]

Yes. When you try to close or save it will trigger the event. It can't however navigate in this same form. When I open this form, there is no navigation function because it is used for adding entries. Maybe that's my issue.

[ro88y09]

Ok so I just made my form open so that my form opened in edit mode and then I would navigate to a new entry. As soon as I did that, it worked smoothly! I guess I'll just have to think of a new way to accomplish this... Thanks for the replies. That brought some insight to me.

[ro88y09]

I'm done on here for the night. If you have some further suggestions, that would be great. Thanks again.

[ItsMe]

Yes. When you try to close or save it will trigger the event. It can't however navigate in this same form. When I open this form, there is no navigation function because it is used for adding entries. Maybe that's my issue.

Navigation being disabled is not the issue. It is disabled by design. Surprisingly, it works with the navigation enabled too. I should just say, "I am surprised it works".

Here is why it does not work for you right now.

rs.FindFirst "[CustID] = " & ContactNumber
needs to be
rs.FindFirst "[ID]" = " & ContactNumber

I don't know what to say about this code. I really am speachless. I want to comment about "this". Then I think about "that". As I start to type I remember the "other thing". I don't klnow weather to congradulate the author or ask how long it took to dream it up. So I will just say, "Wow".

Oh, and I suggest you leave the form properties how they are.

[June7]

You have the form DataEntry property set to Yes? That would prevent moving to previously existing records because existing records are not included in the form RecordSource. I should have recognized that when you said the error was "No current record". It is possible to use the form for adding new record and for editing existing. Don't set DataEntry to Yes and use code to move to new record row with the form opens.

[ItsMe]

You have the form DataEntry property set to Yes? That would prevent moving to previously existing records because existing records are not included in the form RecordSource. I should have recognized that when you said the error was "No current record". It is possible to use the form for adding new record and for editing existing. Don't set DataEntry to Yes and use code to move to new record row with the form opens.

I don't think the code would get that far if there was only one record. I was thinking more along the lines of a custom menu. Maybe the form's properties are not working because VBA can not call the edit menu because it is hidden from the user. But yeah, Data Entry needs to be set to "No".

I think the form's load event is adding the new record and hiding navigation to appear as a data entry form when it really is not. One of my "What The Heck" moments as I was reading the code. None the less, my money is on [CustID] not existing. Then address the MenuBar thing. Then ask what the form's load and current events contain. Goofy stuff.