Prev: Primary Key that Contains Number as a String & starting with zero
Next: Continuous form / sequential numbering
From: niuginikiwi on 25 Feb 2010 15:45
I am getting eror 3075 saying there is a syntax error. I just can't spot the
mistate. Can anyone help?
Here the code behind the form's Before Insert event.

Private Sub Form_BeforeInsert(Cancel As Integer)
'Check for duplicate records

Dim strCriteria As String
strCriteria = "[ProductName]= """ & [Forms]![Products]![ProductName] &
""""
strCriteria = strCriteria & " Or [Code]= """ & [Forms]![Products]![Code]
& """"
strCriteria = strCriteria & " And [SupplierID]= " &
[Forms]![Products]![cboSupplier].Column(0) & ""

If DCount("*", "qryProductSupplier", strCriteria) >= 1 Then
If MsgBox("This product supplied by the current supplier " & vbCrLf
& " you have selected and is on record " & vbCrLf & "Are you sure you want to
add/edit this code", vbYesNo, "Duplication Check") = vbNo Then
Me.Undo
End If
End If
End Sub
From: Dirk Goldgar on 25 Feb 2010 16:33
"niuginikiwi" <niuginikiwi(a)discussions.microsoft.com> wrote in message
news:5B9AADBC-4AE9-466A-8ABA-E9D6DBC708DD(a)microsoft.com...
>I am getting eror 3075 saying there is a syntax error. I just can't spot
>the
> mistate. Can anyone help?
> Here the code behind the form's Before Insert event.
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
> 'Check for duplicate records
>
> Dim strCriteria As String
> strCriteria = "[ProductName]= """ & [Forms]![Products]![ProductName] &
> """"
> strCriteria = strCriteria & " Or [Code]= """ &
> [Forms]![Products]![Code]
> & """"
> strCriteria = strCriteria & " And [SupplierID]= " &
> [Forms]![Products]![cboSupplier].Column(0) & ""
>
> If DCount("*", "qryProductSupplier", strCriteria) >= 1 Then
> If MsgBox("This product supplied by the current supplier " & vbCrLf
> & " you have selected and is on record " & vbCrLf & "Are you sure you want
> to
> add/edit this code", vbYesNo, "Duplication Check") = vbNo Then
> Me.Undo
> End If
> End If
> End Sub


Could it be that cboSupplier.Column(0) is null? Set a breakpoint at "If
DCount(..." and examine the value of strCriteria at that point.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: niuginikiwi on 25 Feb 2010 17:30
Hi Dirk,

It appears to be the null that is causing it.
How can I handle the nulls in this situation?
Need some help please?


"Dirk Goldgar" wrote:

> "niuginikiwi" <niuginikiwi(a)discussions.microsoft.com> wrote in message
> news:5B9AADBC-4AE9-466A-8ABA-E9D6DBC708DD(a)microsoft.com...
> >I am getting eror 3075 saying there is a syntax error. I just can't spot
> >the
> > mistate. Can anyone help?
> > Here the code behind the form's Before Insert event.
> >
> > Private Sub Form_BeforeInsert(Cancel As Integer)
> > 'Check for duplicate records
> >
> > Dim strCriteria As String
> > strCriteria = "[ProductName]= """ & [Forms]![Products]![ProductName] &
> > """"
> > strCriteria = strCriteria & " Or [Code]= """ &
> > [Forms]![Products]![Code]
> > & """"
> > strCriteria = strCriteria & " And [SupplierID]= " &
> > [Forms]![Products]![cboSupplier].Column(0) & ""
> >
> > If DCount("*", "qryProductSupplier", strCriteria) >= 1 Then
> > If MsgBox("This product supplied by the current supplier " & vbCrLf
> > & " you have selected and is on record " & vbCrLf & "Are you sure you want
> > to
> > add/edit this code", vbYesNo, "Duplication Check") = vbNo Then
> > Me.Undo
> > End If
> > End If
> > End Sub
>
>
> Could it be that cboSupplier.Column(0) is null? Set a breakpoint at "If
> DCount(..." and examine the value of strCriteria at that point.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
From: Dirk Goldgar on 25 Feb 2010 18:10
"niuginikiwi" <niuginikiwi(a)discussions.microsoft.com> wrote in message
news:89C60BE9-5C90-4C32-B79A-9CEE0D8DEF14(a)microsoft.com...
> Hi Dirk,
>
> It appears to be the null that is causing it.
> How can I handle the nulls in this situation?
> Need some help please?

How do you *want* to handle nulls in this situation? That is, what do you
want to have happen when the control is Null? Does that constitute an error
that should prevent the record from being added, or is it okay to be adding
a record that has a Null in that field? Are all three of those controls
required, or can any of them be Null?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: niuginikiwi on 25 Feb 2010 18:52
Hi Dirk,

ProductName and cboSupplier fields cannot be null. They have to be filled in.
However, I want the record to go ahead if they were nulls BUT will only
throw the duplication message when the DCount function finds another existing
record and then decide from the vbYesNo.
Yes to add and No to undo.

what seems to be happening is as soon as an entry is made the procedure
checks those fields and detects the nulls and throws the syntax error.

Maybe this code should go under the before update event of the form??

All I want to achieve here is to look at the current record that is
added/edited and make sure it does not match the combination of criteria
supplied and if it does, prompt the vbYesNo message box and if it isn't go
and save the record.




> How do you *want* to handle nulls in this situation? That is, what do you
> want to have happen when the control is Null? Does that constitute an error
> that should prevent the record from being added, or is it okay to be adding
> a record that has a Null in that field? Are all three of those controls
> required, or can any of them be Null?
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
 |  Next  |  Last
Pages: 1 2
Prev: Primary Key that Contains Number as a String & starting with zero
Next: Continuous form / sequential numbering