Create new record if no record exists

[Gkirkup]

I have a situation where the user makes a selection of an item from a combo box control on a form. If a record for that item exists, it is displayed on the form. If no item exists, a new empty record needs to be created and that is then displayed on the form.
How would I do the 'if no record found, then create a new record'?

Robert

 

[John Big Booty]

Perhaps the sample posted here will give you some ideas.

Another method is to use the following code in your Not In List event;

    Dim MsgText As String
    Dim lngcombo As Long
    Dim stDocName As String
    
    MsgText = Me.Combo1.Text & " is not in the list. Would you like to add it to the list now?"
    stDocName = "FRM_DataEntryForm"
   
    If MsgBox(MsgText, vbYesNo) = vbYes Then
        
    DoCmd.OpenForm stDocName, , , , , acDialog, "*" & Me.Combo1.Text
    
    If lngcombo <> 0 Then Me![Combo1] = lngcombo
    Else
        MsgBox "You can double click this field to add this item latter"
    End If
    
    If IsNull(Me![ComboName]) Then
        Me![ComboName].Text = ""
    Else
        lngcombo = Me![ComboName]
        Me![ComboName] = Null
    End If
    Me![ComboName].Requery
    
    Response = acDataErrContinue

With the following code appearing in the FRM_DataEntryForm's On Load Event;

    If Left(OpenArgs, 1) = "*" Then
        DoCmd.GoToRecord acDataForm, "FRM_Brewer", acNewRec
        Me.Brewer.SetFocus
        Me.Brewer = Right(OpenArgs, Len(OpenArgs) - 1)
    End If

 

[Gkirkup]

John: The item is always in the combo box list. That is not the problem. The problem is that another record may exist (in which case, display it) or may not exist (in which case create a new record and display that).
I am thinking that maybe an append query may work, with a criteria of the record being looked for. If that criteria is not met then append a new record. How does that sound?

Robert

 

[missinglinq]

Assuming that the Data to search on is Text and you have Data in three columns of the cbo that you want to populate the new record, if needed:

Private Sub ComboBoxName_AfterUpdate()
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Field1] = '" & Me![ComboboxName] & "'"
    If rs.NoMatch Then
     DoCmd.GoToRecord , , acNewRec
     Me.Field1 = Me.ComboboxName
     Me.Field2 = Me.ComboboxName.Column(1)
     Me.Field2 = Me.ComboboxName.Column(2)
    Else
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
End Sub

Linq ;0)>

 

[bparkinson]

I just had to solve that problem last night, although in my case I have to create n rows based on the existence of several rows in a lookup table. Here's my code. I'm using SQL Server so I call a stored procedure to do the actual insert, which I'll include below for completeness. For Access, just put the INSERT SQL statement under the CASE 0 section. I think this code is efficient, because instead of populating a dao recordset with n rows, which in my case has to come from the SQL Server across the network, it always returns just the one row with the record count. Oh yeah, it needs proper error handling too.

Public Sub InsertExerciseDefaults()

On Error Resume Next

If iExerciseID = 0 Then Exit Sub

Dim sSQL As String
Dim rs As dao.Recordset
Dim p1 As New ADODB.Parameter

Set cmd = New ADODB.Command

InitConnection

sSQL = "SELECT COUNT(*) AS NumberOfRecords FROM ExerciseDefaults WHERE ExerciseID = " & iExerciseID

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, [dbSeeChanges])

Select Case rs!NumberOfRecords

Case 0

cmd.ActiveConnection = cn

p1.Direction = adParamInput
p1.Type = adInteger
p1.value = iExerciseID

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spInsertExerciseDefaults"
cmd.Parameters.Append p1

cmd.Execute

Case Else

GoTo CleanUp


End Select

GoTo CleanUp

CleanUp:
Set rs = Nothing
Set cn = Nothing
Set cmd = Nothing
Set p1 = Nothing
Exit Sub

End Sub


Stored Procedure that creates one row in the ExerciseDefaults table for each row in the ExerciseGoals table, each with the same ExerciseID passed in as a parameter.


USE [EliteTeamsSQL]
GO
/****** Object: StoredProcedure [dbo].[spInsertExerciseDefaults] Script Date: 07/06/2011 15:34:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spInsertExerciseDefaults] @ExerciseID int

AS
BEGIN
SET NOCOUNT ON;

INSERT INTO ExerciseDefaults
([ExerciseID]
,[ExerciseGoalID]
,[Name])

SELECT @ExerciseID, ID, Name FROM ExerciseGoals


END