move form to specific record in VBA

[buratti]

I am having trouble with a very simple task. I have a form that is opened in "add mode". The user enters the customers account number (same is the customer ID primary field) and I want it to lookup if the account number already exists in the underlying query and if so then navigate the form to that record, and if not jsut continue in add mode. This is what I have so far but cant get to work right...

Private Sub CustomerID_AfterUpdate()
'Check if the customer being entered is currently in the list...
If DLookup("[Charge ID]", "[Recurring Charges]", "[Customer ID] =" & Me.CustomerID) > 0 Then
    If MsgBox("The selected Custome is already set up for recurring charges.  Would you like to view their current info?" & Chr(13) _
    & "Click Yes to view existing or No to enter a new recurring charge for this customer.", vbYesNo) = vbYes Then
Dim rs As Recordset
Set rs = Me.RecordsetClone
 
rs.FindFirst "[Customer ID] =" & Me.[Customer ID]
If rs.NoMatch Then MsgBox "error" 'random error msg used here for debugging 
 
'Undo the entry of the customer...
        Me.Undo
'navigate to the record that matches
        Me.Bookmark = rs.Bookmark
    Else
        Exit Sub
    End If
End If
End Sub

With the above code rs.nomatch always returns true when in fact there should be a match in my testing. What could I be missing. Can it have something to do with the form being opened in "add mode"? Can the Set rs = Me.RecordsetClone be setting the recordset to an empty recordset?

 

[Mr. B]

Try using the DCount function to test for an existing record. If DCount returns anything > 0 then the record exists and you can cancel your adding of a new record.

To move to the existing record after canceling the adding of the new record, set focus to the field that is holding the record ID field and use the value entered by the user as the value to find:

DoCmd.FindRecord Me.NameOfControlWhereUserEnteredValue

Hope this helps.

 

[buratti]

Thanks. I already use the Dlookup function to determine if there is an existing record which is not too different form the dcount function you suggested. Once it is determined if there is an existing record I use the me.undo command. Is this the correct method to use? This essentially erases the customer ID the user entered from the control field rendering it useless to use as a reference to search from does it not?

However for kicks and giggles I tried your suggestion of docmd.findrecord me.Customer_ID before running the undo command and I gor the error "You cant use find or replace now". What can be wrong or any different suggestion?

 

[Mr. B]

As long as you are in the Add mode you cannot do the FindRecord.

What you need to do is define a variable that will hold the value typed by the users so when you do the me.undo you will have the value stored in the variable and be able to use that variable to supply the value for the FindRecord.

 

[buratti]

Ok seems easy enough, but how do I go about "switching" to edit mode after the form has been opened in add mode? i can say about 95% of the time the user will be adding a record which is why I need to originally open the form in add mode.

 

[Mr. B]

When you use the Me.Undo that will stop the add mode. Then you can set the focus to the control that holds the record ID and do the FindRecord. It should then be able to find the record.

 

[buratti]

Hmmm. I must be missing something. I simplified everything to the bare bones just to get it working first. This is what I have in the after update of the CustomerID field but I am still getting the message "You cant use find or replace now".

Dim temp As Integer
temp = Me.CustomerID
Me.Undo
Me.CustomerID.SetFocus
DoCmd.FindRecord temp

Also, you said that me.undo will stop the add mode, but after that line is ran the form is still showing "Record 1 of 1". When its opened in regular (edit) mode it shows somewhere along the lines of record "x of 356". Me.Undo doesn't seem to be switching the form back to edit mode and making all of the records available to navigate through. What am I doing wrong?