Form opened with acFormAdd change to edit if productID entered already exists

[illicited]

Here's my current dilemma:

I have a button on a form that when clicked opens another form in data entry mode via DoCmd.OpenForm and the acFormAdd mode. However what I would like to happen after update of the productID field of the newly opened data entry form is that if a productID entered already exists I would like that same form to change data entry mode to false and display the record of the existing productID and allow it to be edited or saved.

Currently when try to simply set the data entry mode to false and use OpenRecordset to display the record associated with the productID entered I get 3022 duplicate entry errors.

Here is the AfterUpdate code for the productID field, its not finished so I'm sure there are probably syntax issues etc.:

Dim product As StringDim crit As String
Dim msg As String
Dim db As Database
Dim rst As Recordset
Dim SQLstr As String


Set db = CurrentDb




product = Me.prodID.Value
crit = "[prodID] = '" & product & "'"


If Me.prodID = DLookup("[prodID]", "tblProducts", crit) Then
    msg = MsgBox("This record already exists, would you like to view it?", vbYesNo)
    
    If msg = vbYes Then


    SQLstr = "SELECT tblProducts.prodID, tblProducts.prodTypeID, tblProducts.prodDescription FROM tblProducts " & _
             "WHERE (((tblProducts.prodID)= '" & product & "'));"
             
    Set rst = db.OpenRecordset(SQLstr, dbOpenDynaset)
    rst.MoveFirst
    
    Me.prodID = rst.Fields("prodID").Value
    Me.prodDescription = rst.Fields("prodDescription").Value
    Me.prodType = rst.Fields("prodTypeID").Value
    Me.prodID.SetFocus
    
    Else
    
    Resume Next
    
    End If
    
End If


Set rst = Nothing
Set db = Nothing


End Sub

[June7]

Determine if the productID already exists before opening the form, then open the form in appropriate mode.

[ssanfu]

My thoughts........

Dim product As String
product = Me.prodID.Value

Is "prodID" as String or a Long?

I don't think your code would even execute if the prodID exists because the 3022 error would occur first.

The AfterUpdate event is the wrong event to use (IMO). You should be using the BeforeUpdate event to check if the prodID exists. If the prodID exists, you can cancel the update - the BeforeUpdate event can be canceled, the AfterUpdate event cannot be canceled.

opens another form in dataentry mode via DoCmd.OpenForm

It looks like the form unbound?

[illicited]

prodID is a string

I should give more context of what I have going on. I have a form displaying a list of all the products and a button for adding a new one. The button being clicked opens a blank form for the product specifics, but in effort to make things simple for the user if the user inputs a product ID that already exists in the blank form (product ID's are alphanumeric with several different schemes involved in their choosing) then I would like the form to load that record. There may be a better way of doing this that I haven't considered.

[illicited]

Steve,

Thank you, you got me on the right track. My data entry form wasn't unbound which was what was causing duplication issue. I removed all bound references and its working as intended now. Thanks again.

[June7]

So if user clicks the 'New Product' button, form opens to new record and user enters productID. But if they goof by entering an existing productID, you want to retrieve that record instead of just telling them to try a different input?

[illicited]

Yes exactly

[June7]

I guess you have satisfactory solution. I've never use UNBOUND forms for data entry/edit.

[illicited]

What would you suggest in a situation like this? It's opening the record now, but doesn't appear to be saving any changes.

[June7]

Would need code to write data back to the record. That's why I don't use UNBOUND forms.

Could open bound form, move to New Record row, code in BeforeUpdate event validates user input and if ProdID exists, cancel the entry and move to existing record.

[illicited]

I will look at doing that instead. I am wary of the unbound because of all the coding. Why not let Access do what it already has built-in procedures for doing.

[June7]

My sentiments.

For a start, consider code in the New Product button:

DoCmd.OpenForm "Products"
DoCmd.GoToRecord acDataForm, "Products", acNewRec

[ssanfu]

Then you could use code in the BEFORE UPDATE event to check for duplicate entry.