What's wrong with this code please?

[TryingMyBest]

Hi there ,

I have written this code to check for an entry in my table and return an error if the entry already exists....this works for the first entry in each case but not for subsequent entries.....why?

Private Sub cmdAddToAudit_Click()
If IsNull(Me.txtAuditNumber) Then
MsgBox "You must choose an audit", vbOKOnly, "Error"
[COLOR=Red]ElseIf Me.cboProcessName.Value = DLookup("[ProcessName]", "TBLAuditRecords", "[AuditNumber]=[Forms]![frmAuditProgrammePlanning]![txtAuditNumber]") Then
MsgBox "You have already added this process to this audit", vbOKOnly, "Error"[/COLOR]
Else
DoCmd.RunSQL "INSERT INTO TBLAuditRecords (AuditNumber, ProcessName) VALUES ([Forms]![frmAuditProgrammePlanning]![txtAuditNumber],[Forms]![frmAuditProgrammePlanning]![cboProcessName])"
DoCmd.Requery "SUBAuditDetails"
End If
End Sub

I've highlighted the bit of the code that I think is a problem.

My table is as follows:

TBLAuditRecords
Ref (autonumber)
AuditNumber
ProcessName

I need to be able to add many processes to one audit without duplicating the processes for each audit.....for example

This is ok:
AuditNumber ProcessName
1 Incident Management
1 Problem Management
2 Incident Management
2 Problem Management

This isn't:
AuditNumber ProcessName
1 Incident Management
1 Problem Management
1 Incident Management
1 Problem Management

Any help/guidance as to a better way to do this is much appreciated.

Thanks
Jo

 

[WayneRyan]

Jo,

Look at the [Forms...] reference inside the DLookUp. You have to give
Access a chance to substitute its value by isolating it.

Private Sub cmdAddToAudit_Click()
If IsNull(Me.txtAuditNumber) Then
   MsgBox "You must choose an audit", vbOKOnly, "Error"
ElseIf Me.cboProcessName.Value = DLookup("[ProcessName]", "TBLAuditRecords", "[AuditNumber] = " & [Forms]![frmAuditProgrammePlanning]![txtAuditNumber]) Then
   MsgBox "You have already added this process to this audit", vbOKOnly, "Error"
Else
   DoCmd.RunSQL "INSERT INTO TBLAuditRecords (AuditNumber, ProcessName) VALUES ([Forms]![frmAuditProgrammePlanning]![txtAuditNumber],[Forms]![frmAuditProgrammePlanning]![cboProcessName])"
   DoCmd.Requery "SUBAuditDetails"
End If
End Sub

Merry Xmas,
Wayne

 

[TryingMyBest]

Thanks

I've got it fixed now....thanks