Validation (1 Viewer)

[Zurich98]

I'm using Access 2003 form as front end connecting to sql server 2000 backend via DSN to enter data to one table tblActivity. I need to validate four fields on the form to ensure that the user does not enter duplicate info into tblActivity. the columns are Community, Building, Unit, and Task. Once the user fill in the Task, I would like to search tblActivity to see if a record with the combination of these fields already exists. If so, tell the user 'Activity has already been entered' else add it as a new record. This is the code I have behind the Add button to add the record without the validation:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
Dim strSQLAppend As String

strSQLAppend = "INSERT INTO dbo_tblActivity ([Community],[Building], [Unit],[Task],[Vendor],[Score1],[Score2],[Score3],[InspDate] )" & _
"SELECT [Forms]![frmDataEntry]![TxtCommunity],[Forms]![frmDataEntry]![TxtBuilding],[Forms]![frmDataEntry]![TxtUnit],[Forms]![frmDataEntry]![TxtTask],[Forms]![frmDataEntry]![TxtVend],[Forms]![frmDataEntry]![TxtScore1],[Forms]![frmDataEntry]![TxtScore2],[Forms]![frmDataEntry]![TxtScore3],[Forms]![frmDataEntry]![TxtBldrInspDt]"

DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQLAppend
DoCmd.SetWarnings (warningson)

Me.CboCommumity = ""
Me.TxtBuilding = ""
Me.TxtUnit = ""
Me.CboTask = ""
Me.CboVendor = ""
Me.TxtScore1 = ""
Me.TxtScore2 = ""
Me.TxtScore3 = ""
Me.TxtInspDt = ""

Me.CboCommunity.SetFocus

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

What code do I need to accomplish the validation and where do I place the validation code? Your help/suggestion is greatly appreciated.

Thanks

 

[Matt Greatorex]

I think something like the following should work for you.
You may need to tweak it, if I've missed a space or something like that.

1) Set up the criteria
2) Check if any records match it (in this case, I'm counting the Community field, but if not every record has a value for this field, you'll want to change to another one)
3) If so, tell the user
4) If not, carry on as you're doing now

Hope it helps.

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
Dim strSQLAppend As String
Dim strSQLCriteria As String

strSQLCriteria = "Community = '" & [Forms]![frmDataEntry]![TxtCommunity] & "' " & _
"AND Building = '" & [Forms]![frmDataEntry]![TxtBuilding] & "' " & _
"AND Unit = '" & [Forms]![frmDataEntry]![TxtUnit] & "' " & _
"AND Task = '" & [Forms]![frmDataEntry]![TxtTask] & "'"

If Dcount("[Community]", "dbo_tblActivity", strSQLCriteria) <> 0 Then

    Msgbox "That combination already exists"

else
[I]  
     'StrSQLAppend = ' 
     ..
     ..
     ..
     ..
     'Me.CboCommunity.SetFocus'}
[/I]
end if

Exit_cmdAdd_Click:
    Exit Sub

Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
    
End Sub

 

[Zurich98]

Matt,

Thank you very much for your response. It is working great!!! If it is not too much trouble, how can I display the values of the 4 fields that the user enters to tell them that it is already exists? for example:

Msgbox = strSQLCriteria & "already exists"

of course i try this and it does not work. You probably can tell that i'm not an Access developer. Again, thank you for your help. I'm sending many gold stars your way.

 

[Zurich98]

I figure it out how to assign the values the user enter in the msgbox.

MsgBox " " & strSQLCriteria & " already exists".

Thanks for all your helps.

 

[Niniel]

How can I tell it to do nothing after the Then statement? I only want to check silently if records already exist. If they do, nothing is to be done, but if they don't, an action is to be performed.

Never mind, I found a solution.