Check to see if a record exists then do something

[Mr_Si]

Hi all,

I need to sort out some coding to see if a record exists based on 2 fields...

lngzShowID and lnzgClassID in one form, being equal to that of the form which is issuing the command.

If it doesn't, then I will open a form with the command to add, but otherwise nothing will happen.


I know it needs to be something to do with Record Sets and also I have been reading about DCount? but not really sure what to do and not sure if there's a basic set of code that is needed to set it up and then I customise it?

Would I tell the database to run a query and then based on that, somehow "Get" the records that match from that. But I suppose that's where the recordset comes in.

Not doing this programming frequently enough, I don't know enough about it to even have a go!

So Basically,


On update of "lngzClassID" in Form 1,
run code to check if record on form 2 exists that matches both the showID and Class ID in form 1.
If not, create new record on form 2 with this info.


I appreciate your comments.

 

[MarkK]

I commonly use DCount() to check for the existence of data. If the count is zero, then the item doesn't exist.
Cheers,

 

[Mr_Si]

Something like this...?

Private Sub lngzClassID_AfterUpdate()
    
    Select Case Me.lngzClassID.Column(4)
    
    Case Is = 1
        Dim rs As New ADODB.Recordset
        Dim strSELECT As String
        Dim strWHERE As String
        Dim strSQL As String
        
        MsgBox "Making new entry in Results for Section = " & Me.lngzClassID.Column(4)
        
        strSELECT = "SELECT tblClassShow.lngzShowID, tblClassShow.lngzClassID FROM tblClassShow "
        strWHERE = "WHERE tblClassShow.lngzShowID=" & Me.lngzShowID & " AND tblClassShow.lngzClassID=" & Me.lngzClassID
        strSQL = strSELECT + strWHERE
        MsgBox strSQL
        
        Set rs = db.OpenRecordset(strSQL)
        If DCount(rs) = 0 Then
            MsgBox "Making new entry in Results for Section = " & Me.lngzClassID.Column(4)
        End If
        'DoCmd.Close
        'DoCmd.OpenForm "frmResultsSection1", , , , acFormEdit, , 2
        Set rs = Nothing
    End Select
End Sub

 

[Mr_Si]

Got it working...

Private Sub lngzClassID_AfterUpdate()
    Dim strWHERE As String
    
    Select Case Me.lngzClassID.Column(4)
    
    Case Is = 1
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            MsgBox "Section " & Me.lngzClassID.Column(4)
            DoCmd.OpenForm "frmResultsSection1", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 2
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            MsgBox "Section " & Me.lngzClassID.Column(4)
            DoCmd.OpenForm "frmResultsSection2", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 3
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection3", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 4
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection4", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 5
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection5", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 6
        
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection6", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
        
    Case Is = 7
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection7", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 8
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection8", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
        
    Case Is = 9
        strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID
        
        If DCount("*", "tblResults", [strWHERE]) = 0 Then
            DoCmd.OpenForm "frmResultsSection9", , , , acFormAdd, acDialog, 2
        End If
        strWHERE = ""
    
    End Select
    
End Sub

 

[MarkK]

When you see blocks of code repeated like that bells should go off in your head that there has to be a simpler way. Consider...

Private Sub lngzClassID_AfterUpdate()
   Dim critieria As String
   Dim fn As String
   
   critieria = "lngzShowDate=" & Me.lngzShowID & " AND lngzClassID=" & Me.lngzClassID
   
   If DCount("*", "tblResults", [critieria]) = 0 Then
      fn = "frmResultsSection" & Me.lngzClassID.Column(4)
      DoCmd.OpenForm fn, , , , acFormAdd, acDialog, 2
   End If
End Sub

We can construct the name of the form and parameterize it so we don't need a Select Case.
And there's almost no way you need eight forms with names that differ only by a trailing digit. Surely you can alter the RecordSource property of a single form to handle all these cases.
Cheers,

 

[Mr_Si]

Ooooh that looks nice.

My VBA and Access Experience, as you may expect from the above, is limited. I'm not an access virgin and I understand normalisation etc having done a Computer Science degree with modules on Software Systems Analysis and Design (DFDs etc).

However, I don't know how to alter the record source in the way you suggest.

It's going to be a database used once a year by one person.

If you can give me some pointers, that would be wonderful!



Actually, I've just had an Idea about which form I can use. I was just being a bit dim.