Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Unanswered: Problem with insert if not exists query

    Hi guys,

    I am spending far too much time on dealing with this. I am new to Access and don`t really have a clue about what could be wrong.
    I have a form in access and on that form I have comboboxes and text boxes.
    I have a button at the bottom of the form.
    What I want to achieve is on click of that button I want all currently selected values in the combos to be inserted in a table only if the record as a whole doesn`t already exists.
    I started up creating the sql statement fisrts. I also don`t know how to exactly do the code behind that button so that when he record exist to return pou up message Record exists, if the record is inserted to say Record inserted sucessfully.
    Here is my access query that doesn`t work.it says It will append 0 records:

    INSERT INTO Table( A, B, C, D, E, F, G )
    SELECT [Forms]![frmName]![combo7] AS Expr1, [Forms]![frmName]![combo9] AS Expr2, [Forms]![frmName]![combo49] AS Expr3, [Forms]![frmName]![combo15] AS Expr4, [Forms]![frmName]![text47] AS Expr5, [Forms]![frmName]![cboReportingPeriod] AS Expr6, [Forms]![frmName]![text21] AS Expr7
    FROM Table1 WHERE
    (((Exists (Select [A], [B], [C], [D], [E], [F], [G]
    from Table))=False));
    None of the combos is bound . They are all unbound and the values they display are based on select statement.


    I am not sure how to reference all the values that are currently selected in the combos if I want to creat a function in the code behind eiher.
    For value selected in combo 9?What the syntax for that could be ?how could I declare it?

    Please help.
    I am in trouble and I don`t get anything from access.

    Thanks in advance
    Last edited by funky07; 03-24-09 at 14:52.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First of all, you need a mean to uniquely identify the records in Table1 (it's called a unique key). You cannot test for something existing or not without knowing and, more important, without "explaining to Access" what you are looking for.

    If you really want to write a function that tests for the existence of a record using the values of different fields as criteria, though it is possible it will be very slow and counterproductive to do so. If you still want to try, you can proceed like this:

    1) You'll need to open a recordset on table1, test in each column of each record to see if the value already exists etc...
    2) What I would do would be create a kind of "key" from the concerned columns and test it against the values you try to insert. However this is only valid for those values placed in a certain fixed sequence. Such a function could be something like this:

    Code:
    Function MakeKey(A As Variant, B As Variant, C As Variant, D As Variant, E As Variant) As String
    
        MakeKey = CStr(Nz(A, " ")) & "-" & CStr(Nz(B, " ")) & "-" & CStr(Nz(C, " ")) & CStr(Nz(D, " ")) & "-" & CStr(Nz(E, " "))
        
    End Function
    Then we shall create a function comparing the values from your form with those already present into the table, like this:

    Code:
    Public Function RecordExists(A As Variant, B As Variant, C As Variant, D As Variant, E As Variant) As Boolean
    
        Dim rst As DAO.Recordset
        Dim strKeyFromTable As String
        Dim strKeyFromParameters As String
        
        strKeyFromParameters = MakeKey(A, B, C, D, E)
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
        With rst
            Do Until .EOF
                strKeyFromTable = MakeKey(!A, !B, !C, !D, !E)
                If strKeyFromTable = strKeyFromParameters Then
                    RecordExists = True
                    Exit Do
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Now we can write this code for the OnClick event of the button used to store the values into Table1:

    Code:
    Private Sub Button_Save_Click()
    
        Dim strSQL As String
        
        If RecordExists(Me.A.Value, Me.B.Value, Me.C.Value, Me.D.Value, Me.E.Value) = False Then
            ' execute the query to save the values
            strSQL = "INSERT INTO Table1 ( A, B, C, D, E ) " & _
                    "VALUES (Me.A.Value, Me.B.Value, Me.C.Value, Me.D.Value, Me.E.Value)"
            CurrentDb.Execute strSQL
        Else
            ' Warning message
            MsgBox "A record with those values already exists!"
        End If
        
    End Sub
    There are sevel other method of reaching that goal but this one is the simplest I can find now. We could, for instance, use the FindFirst method of a recordet (but the criteria would be rather complex) or we could write a complex query with a part of it using NOT IN operators. I'm not sure that it would be significantly faster and I'm certain that it would be more difficult to read, to understand and to maintain.

    A totally different approach would be to create a unique index combining the columns of the table but you would be limited to a maximum of ten fields.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    7
    Thank you very very much Sinndho,

    I will try doing it following your instructions. I will definatelly let you know how I get along.

  4. #4
    Join Date
    Mar 2009
    Posts
    7
    Hey sinndho,

    I run the code you gave me . It makes sense now. I am getting a weird error Too few parameters .Expected 5.
    Did a search online on that error and found out that possible cuases are if I have a column name same as the control name , If I am missing a parameter and so on.
    Here is my full code behind. I am not sure where I should fill in values !A I should use Field name or the path to the control. I am thinking it should be the path to the control, like A=CedantID in my case, so it`s gonna be [forms]![frmName]![Combo9] or do I get it wrong .

    Here is the code if you can see soemthing that doesn`t seem right please let me know.
    Code:
    I will keep looking if I can figure it out on my own.
    Thanks a million
    Have a good day
    
    Option Compare Database
    Function MakeKey(CedantID As Variant, CompanyType As Variant, CompanyName As Variant, TypeOfFile As Variant, ReportingPeriod As Variant) As String
    
        MakeKey = CStr(Nz(CedantID, " ")) & "-" & CStr(Nz(CompanyType, " ")) & "-" & CStr(Nz(CompanyName, " ")) & CStr(Nz(TypeOfFile, " ")) & "-" & CStr(Nz(ReportingPeriod, " "))
        
    End Function
    
    Public Function RecordExists(CedantID As Variant, CompanyType As Variant, CompanyName As Variant, TypeOfFile As Variant, ReportingPeriod As Variant) As Boolean
    
        Dim rst As DAO.Recordset
        Dim strKeyFromTable As String
        Dim strKeyFromParameters As String
        
        strKeyFromParameters = MakeKey(CedanID, CompanyType, CompanyName, TypeOfFile, ReportingPeriod)
        Set rst = CurrentDb.OpenRecordset("DataStatus", dbOpenSnapshot)
        With rst
            Do Until .EOF
                strKeyFromTable = MakeKey(!CedantID, !CompanyType, !CompanyName, !TypeOfFile, !ReportingPeriod)
                If strKeyFromTable = strKeyFromParameters Then
                    RecordExists = True
                    Exit Do
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Private Sub Command25_Click()
    On Error GoTo Err_Command25_Click
    
    If Me.Dirty Then Me.Dirty = False
        DoCmd.Close
    
    Exit_Command25_Click:
        Exit Sub
    
    Err_Command25_Click:
        MsgBox Err.Description
        Resume Exit_Command25_Click
        
    End Sub
    
    
    Private Sub Command26_Click()
    
    End Sub
    
    Private Sub cboReportingPeriod_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ReportingPeriodCalendar.Visible = True
    ReportingPeriodCalendar.SetFocus
    If Not IsNull(cboReportingPeriod) Then
       ReportingPeriodCalendar.Value = cboReportingPeriod.Value
    Else
       ReportingPeriodCalendar.Value = Date
    End If
    
    End Sub
    
    
    
    
    
    Private Sub Command28_Click()
    On Error GoTo Err_Command28_Click
    
    
        If Me.Dirty Then Me.Dirty = False
        DoCmd.Close
    
    Exit_Command28_Click:
        Exit Sub
    
    Err_Command28_Click:
        MsgBox Err.Description
        Resume Exit_Command28_Click
        
    End Sub
    
    Private Sub ReportingPeriod_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ReportingPeriodCalendar.Visible = True
    ReportingPeriodCalendar.SetFocus
    If Not IsNull(cboReportingPeriod) Then
       ReportingPeriodCalendar.Value = cboReportingPeriod.Value
    Else
       ReportingPeriodCalendar.Value = Date
    End If
    
    
    End Sub
    
    Private Sub Command44_Click()
    
    End Sub
    
    Private Sub Command59_Click()
    Dim strSQL As String
        
        If RecordExists(Me.CedantID.Value, Me.CompanyType.Value, Me.CompanyName.Value, Me.TypeOfFile.Value, Me.ReportingPeriod.Value) = False Then
            ' execute the query to save the values
            strSQL = "INSERT INTO DataStatus ( CedantID, CompanyType, CompanyName, TypeOfFile, ReportingPeriod ) " & _
                    "VALUES (Me.CedantID.Value, Me.CompanyType.Value, Me.CompanyName.Value, Me.TypeOfFile.Value, Me.ReportingPeriod.Value)"
            CurrentDb.Execute strSQL
        Else
            ' Warning message
            MsgBox "A record with those values already exists!"
        End If
    
    End Sub
    
    
    
    
    
    
    
    
    
    
       
        
    
    
    
    Private Sub ReportingPeriodCalendar_Click()
    cboReportingPeriod.Value = ReportingPeriodCalendar.Value
    cboReportingPeriod.SetFocus
    ReportingPeriodCalendar.Visible = False
    
    End Sub
    
    Private Sub ReportingPeriodCalendar_Updated(Code As Integer)
    
    End Sub
    
    Private Sub Text21_Click()
    End Sub

  5. #5
    Join Date
    Mar 2009
    Posts
    7
    Hi Sinndho,

    just to let you know the syntax you gave me works perfectly. Thanks soo much. Saved me lots of time.
    The statement didn`t work when I executed it with the real field names bacause of syntax error, had CedanID instead of CedantID.
    Glad I spotted it.

    Thanks again for your very cool explanation and great code.
    Have a good day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •