Error 3075 Syntax Error

  • Thread starter Thread starter niuginikiwi
  • Start date Start date
N

niuginikiwi

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
 
niuginikiwi said:
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[/QUOTE]


Could it be that cboSupplier.Column(0) is null?  Set a breakpoint at "If
DCount(..." and examine the value of strCriteria at that point.
Click to expand...
 
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 said:
niuginikiwi said:
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[/QUOTE]


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)
[/QUOTE]
Click to expand...
Click to expand...
 
niuginikiwi said:
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?
Click to expand...

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?
 
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.
 
niuginikiwi said:
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.
Click to expand...

So, if one of the controls is Null, do you want to consider it to match a
record that has a Null in in that field, or do you not want to include it in
the matching at all?

Regardless, the BeforeInsert event is no good for this, because it fires
when the user first begins to edit data in a new record. Instead, you'll
want to use the BeforeUpdate event, but put all this logic in a conditional
block that tests whether this is a new record ("If Me.NewRecord Then ...").
 
Back
Top