Check if data already exist in table (1 Viewer)

[sargon]

I have two textboxs (txtYear and txtMonth) and one button (cmdBtn) in Form1

I need to check if the data typed the txtYear and txtMonth already exist in Table1 (Year , Month) when I click the button.

If Year And Month exist in Table1
Then MsgBox "Data already exist"
Else Open Form2
EndIf


Pivate Sub cmdBtn_Click()
.
.
.
End Sub

 

[MarkK]

Check out the DCount() function, which returns a count of records that satisfy criteria that you provide.

 

[bparkinson]

You can do it in SQL by making a recordset in VBA and populating it with this SQL:

SELECT COUNT(*) AS NumberOf Rows FROM Table1 WHERE Month = " & "'" & txtMonth & "'" AND Year = " & "'" & txtYear & "'"

Then test if rs!NumberOfRows is 0 or not.

 

[sargon]

Thanks,

This is my working code:

Private Sub cmdBtn_Click()
On Error GoTo Err_cmdBtn_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    Dim I As Integer
    Dim Y As Integer
    
    I = DCount("[Year]", "Table1", "[Year] = " & Me.[txtYear] & "")
    Y = DCount("[Month]", "Table1", "[Month] = " & Me.[txtMonth] & "")
    
If I > 0 And Y > 0 Then
MsgBox "Already exist!"
Else
 stDocName = "FormX"
 DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_cmdBtn_Click:
    Exit Sub