Solved

How check for existing record and edit if exists otherwise create new record

Posted on 2011-03-10
2
291 Views
Last Modified: 2012-05-11
I want to open a form to edit a record if it already exists OR create a new record if it does not exist.  The form is bound to a table.  But two field criterias have to be met in determining if the record exists already or not.  How can I do this?
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
TinTombStone earned 250 total points
ID: 35094860
The following uses the DLookup() function to check for a particular CustomerID before opening the form in Add (not found) or Edit (found) mode

Sub OpenFormInAddOrEditMode()

Dim custID As String

    custID = "CUST1"

    If Not IsNull(DLookup("[CustomerID]", "[tblCustomers]", "[tblCustomers].[CustomerID]='" & custID & "'")) Then
    'or for a numeric field
    'If Not IsNull(DLookup("[CustomerID]", "[tblCustomers]", "[tblCustomers].[CustomerID]=" & custID)) Then
        DoCmd.OpenForm "frmCustomers", acNormal, , "[CustomerID]='" & custID & "'", acFormEdit, acWindowNormal
    Else
        DoCmd.OpenForm "frmCustomers", acNormal, , , acFormAdd, acWindowNormal
    End If

End Sub
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 35094925
i suggest that you add an openargs option when opening the form in add mode

DoCmd.OpenForm "frmCustomers", acNormal, , , acFormAdd, acWindowNormal,openargs:=custID

then in the open event of the form, use this codes to set the value of the field automatically

private sub form_load()

if me.openargs & ""<> "" then
  me.custID=me.openargs
end if

end sub
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question