Error 3075

I’ve looked at nearly every post and can’t find an answer so many thanks for
any help.

I have a value for FloorProgCriteriaID that’s populated via this code on a
form open event:
Me.FloorProgCriteriaID = _
[Forms]![frmSelectFloorProgCriteria] _
![lstFloorProgCriteria]

Can someone please help me figure out why I’m getting a run-time 3075 syntax
error (missing operator) in query expression '[AuditID] = 12 And
[FloorProgCriteriaID] = And [AuditorID] = 'T5925MS''
(I didn’t leave out the value after [FloorProgCriteriaID]. The value returned
is Null based on the Debugger).

Private Sub Form_Current()
'Display the number of observations that
'remain available for the selected criteria
Me.CountOfObservations = _
Me.FloorProgMaxObservations - _
(DCount("*", "tblFloorProgAudit", _
"[AuditID] = " & Me.AuditID & _
" And " & "[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID & " And " & _
"[AuditorID] = '" & Me.AuditorID & "'"))

--
Message posted via http://www.accessmonster.com

Hi -

The syntax error is due to the fact that Me.FloorProgCriteriaID has no value
(is Null). So, this means that the On Open event is not setting a proper
value to it. Is [lstFloorProgCriteria] a list box, and if so, does it have a
default value, so that it cannot be null?

John


T5925MS wrote:
I’ve looked at nearly every post and can’t find an answer so many thanks for
any help.

I have a value for FloorProgCriteriaID that’s populated via this code on a
form open event:
Me.FloorProgCriteriaID = _
[Forms]![frmSelectFloorProgCriteria] _
![lstFloorProgCriteria]

Can someone please help me figure out why I’m getting a run-time 3075 syntax
error (missing operator) in query expression '[AuditID] = 12 And
[FloorProgCriteriaID] = And [AuditorID] = 'T5925MS''
(I didn’t leave out the value after [FloorProgCriteriaID]. The value returned
is Null based on the Debugger).

Private Sub Form_Current()
'Display the number of observations that
'remain available for the selected criteria
Me.CountOfObservations = _
Me.FloorProgMaxObservations - _
(DCount("*", "tblFloorProgAudit", _
"[AuditID] = " & Me.AuditID & _
" And " & "[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID & " And " & _
"[AuditorID] = '" & Me.AuditorID & "'"))

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

Yes it is a list box. It comes from frmSelectFloorProgCriteria. Here’s the
code that sends the value:

Private Sub lstFloorProgCriteria_DblClick _
(Cancel As Integer)
DoCmd.OpenForm "frmFloorProgAudit", _
acNormal, , , acFormEdit, acWindowNormal _
DoCmd.Close acForm, Me.Name _
End Sub

When the form opens, the value is stored in the proper field as expected. For
some reason, DCount is not seeing it???


J_Goddard wrote:
Hi -

The syntax error is due to the fact that Me.FloorProgCriteriaID has no value
(is Null). So, this means that the On Open event is not setting a proper
value to it. Is [lstFloorProgCriteria] a list box, and if so, does it have a
default value, so that it cannot be null?

John

I’ve looked at nearly every post and can’t find an answer so many thanks for
any help.
[quoted text clipped - 21 lines]
Me.FloorProgCriteriaID & " And " & _
"[AuditorID] = '" & Me.AuditorID & "'"))


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

Here's the rest of the code for the form with the DCount function:

Option Compare Database _

Private Sub cboEmployeeID_AfterUpdate() _

'Move the focus to AuditorComments after a
'record update
Me.AuditorComments.SetFocus _
End Sub _

Private Sub cmdCancelAndClose_Click() _

'Cancel observation and close this form _
On Error Resume Next _
DoCmd.RunCommand acCmdUndo _
DoCmd.Close acForm, Me.Name _
End Sub _

Private Sub _
cmdCancelAndSelectDifferentCriteria_Click() _

'Cancel the current observation, close this
'form, and then open the
'frmSelectFloorProgCriteria
On Error Resume Next _
DoCmd.RunCommand acCmdUndo _
DoCmd.OpenForm _
"frmSelectFloorProgCriteria", acNormal, , , _
acFormAdd, acWindowNormal _
DoCmd.Close acForm, Me.Name _
End Sub _

Private Sub cmdSaveAndClose_Click() _

'Save the current record and create a new
'record
On Error Resume Next _
DoCmd.Close acForm, Me.Name _
End Sub _

Private Sub cmdSaveAndMakeAnother_Click() _

'Temporarily set the default value for new
'records to the same FloorProgCriteriaID
'Save the current record and create a new
'record
Me![FloorProgCriteriaID].DefaultValue = _
Chr$(34) & Me![FloorProgCriteriaID] & Chr$(34) _
On Error Resume Next _
DoCmd.GoToRecord , , acNewRec _
Me.FloorProgCriteriaID = _
Me.FloorProgCriteriaID _

'Move the focus to AuditorComments after a
'record update
Me.AuditorComments.SetFocus _
End Sub _

Private Sub _
cmdSaveAndSelectDifferentCriteria_Click() _

'Save the current observation, close this
'form, and then open the
'frmSelectFloorProgCriteria
On Error Resume Next _
'If an error occurs, start running code here _
DoCmd.OpenForm _
"frmSelectFloorProgCriteria", acNormal, , , _
acFormAdd, acWindowNormal _
DoCmd.Close acForm, Me.Name _
End Sub _

Private Sub Form_BeforeUpdate(Cancel As _
Integer) _

'Limits the number of records allowed based
'on the field FloorProgMaxObservations.
If DCount("*", "tblFloorProgAudit", _
"[AuditID] = " & Me.AuditID & " " & "AND _
[FloorProgCriteriaID] = " & _ _
Me.FloorProgCriteriaID & " " & "AND _
[AuditorID] = """ & Me.AuditorID & """") = _ _
Me.FloorProgMaxObservations.Value _
Then _
MsgBox "You're attempting to exeed the _
maximum number of observation allowed for _
this criteria set. Click OK and then delete this _
observation." _
Cancel = True _
End If _

'Require a comment for any records where
'Unsatisfactory is Yes.
If IsNull(Me.AuditorComments) And _
(Me.Unsatisfactory) = True Then _
MsgBox "You must add auditor _
comments for all unsatisfactory observations.", _
vbOKOnly _
Cancel = True _
End If _

'Require an EmployeeID for any records are
'from Program 20 and Unsatisfactory is Yes.
If (Me.FloorProgID) = 20 And _
IsNull(Me.EmployeeID) Then _
MsgBox "You must include the _
Employee's ID for all Employee Interview _
Questions.", vbOKOnly _
Cancel = True _
End If _
End Sub _

Private Sub Form_Current() _

'Display the number of observations that
'remain available for the selected criteria
Me.CountOfObservations.Value = _
Me.FloorProgMaxObservations.Value - _
(DCount("*", "tblFloorProgAudit", "[AuditID] = _
" & _ _
Me.AuditID.Value & " And " & _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID.Value & " And " & _
"[AuditorID] = '" & _ _
Me.AuditorID.Value & "'")) _
End Sub _

Private Sub Form_Open(Cancel As Integer) _

'Open form at a new record with criteria
'selected from frmSelectFloorProgCriteria
DoCmd.GoToRecord , , acNewRec _
Me.FloorProgCriteriaID = _
[Forms]![frmSelectFloorProgCriteria]![lstFloorP _
rogCriteria] _

'Enable the EmployeeID combo box for
'program 20 only.
If (Me.FloorProgID) = 20 Then _
Me.cboEmployeeID.Enabled = True _
End If _
If (Me.FloorProgID) 20 Then _
Me.cboEmployeeID.Enabled = False _
End If _

'Enable the Add New Employee command
'button for program 20 only.
If (Me.FloorProgID) = 20 Then _
Me.cmdAddNewEmployee.Enabled = True _
End If _
If (Me.FloorProgID) 20 Then _
Me.cmdAddNewEmployee.Enabled = False _
End If _

'Enable the EmployeeName text box for
'program 20 only.
If (Me.FloorProgID) = 20 Then _
Me.txtEmployeeName.Enabled = False _
Me.txtEmployeeName.Locked = True _
End If _
If (Me.FloorProgID) 20 Then _
Me.txtEmployeeName.Enabled = False _
Me.txtEmployeeName.Locked = False _
End If _
Me.Unsatisfactory.SetFocus _
End Sub _

Private Sub Form_Undo(Cancel As Integer) _

'Disable the employee section of the form
'after a record undo
Me.cboEmployeeID.Enabled = False _
Me.cmdAddNewEmployee.Enabled = False _
Me.txtEmployeeName.Enabled = False _
Me.txtEmployeeName.Locked = False _
End Sub _

Private Sub cmdDelete_Click() _
On Error GoTo cmdDelete_Click_Err _

On Error Resume Next _
DoCmd.GoToControl _
Screen.PreviousControl.Name _
Err.Clear _
If (Not Form.NewRecord) Then _
DoCmd.RunCommand acCmdDeleteRecord _
End If _
If (Form.NewRecord And Not Form.Dirty) _
Then _
Beep _
End If _
If (Form.NewRecord And Form.Dirty) Then _
DoCmd.RunCommand acCmdUndo _
End If _
If (MacroError 0) Then _
Beep _
MsgBox MacroError.Description, _
vbOKOnly, "" _
End If _

cmdDelete_Click_Exit: _
Exit Sub _

cmdDelete_Click_Err: _
MsgBox Error$ _
Resume cmdDelete_Click_Exit _

End Sub _

Private Sub Previous_Record_Click() _
On Error GoTo Previous_Record_Click_Err _

On Error Resume Next _
DoCmd.GoToRecord , "", acPrevious _
If (MacroError 0) Then _
Beep _
MsgBox MacroError.Description, _
vbOKOnly, "" _
End If _

Previous_Record_Click_Exit: _
Exit Sub _

Previous_Record_Click_Err: _
MsgBox Error$ _
Resume Previous_Record_Click_Exit _

End Sub _

Private Sub Next_Record_Click() _
On Error GoTo Next_Record_Click_Err _

On Error Resume Next _
DoCmd.GoToRecord , "", acNext _
If (MacroError 0) Then _
Beep _
MsgBox MacroError.Description, _
vbOKOnly, "" _
End If _

Next_Record_Click_Exit: _
Exit Sub _

Next_Record_Click_Err: _
MsgBox Error$ _
Resume Next_Record_Click_Exit _

End Sub _

Private Sub Last_Record_Click() _
On Error GoTo Last_Record_Click_Err _

DoCmd.GoToRecord , "", acLast _

Last_Record_Click_Exit: _
Exit Sub _

Last_Record_Click_Err: _
MsgBox Error$ _
Resume Last_Record_Click_Exit _

End Sub _

Private Sub cmdAddNewEmployee_Click() _
On Error GoTo _
cmdAddNewEmployee_Click_Err _

DoCmd.OpenForm "frmAddEmployee", _
acNormal, "", "", , acNormal _

cmdAddNewEmployee_Click_Exit: _
Exit Sub _

cmdAddNewEmployee_Click_Err: _
MsgBox Error$ _
Resume cmdAddNewEmployee_Click_Exit _

End Sub _

--
Message posted via http://www.accessmonster.com