If a field is greater than 0 then...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to construct a macro with a condition. I have a query that
counts the number of records based on a criteria. The query does the count
correctly but how do I set the condition correctly. I have tried this in vba
and using the interface and can't seem to get it to work. Here is the vba
code:


Private Sub CmdOK_Click()


Dim DB As Database
Set DB = CurrentDb
Dim rec As DAO.Recordset

With CodeContextObject
.Visible = True
DoCmd.SetWarnings False
' Count if Work ID number already exists in table
DoCmd.OpenQuery "qryCountIDTR", acViewNormal, acEdit
Set rec = DB.OpenRecordset("tblCountWR")
If CountOfWorkReqID > "0" Then
' Open form to select if WR is rework
DoCmd.OpenForm "qryWRExist", acNormal, "", "", , acNormal
DoCmd.SetWarnings True


End If
' Append Data from Quality Checklist
DoCmd.OpenQuery "qryappendTR", acViewNormal, acEdit
' Update table with Work Request (Check ID), date, Checklist type,
etc. - Product
DoCmd.OpenQuery "qryUpdateTR", acViewNormal, acEdit
' Create Record for Tracking Time Spent
DoCmd.OpenQuery "qrycreateTimeTR", acViewNormal, acEdit
' Open form with fields to be updated/edited
DoCmd.OpenForm "frmInitialTR", acNormal, "", "", , acNormal
DoCmd.SetWarnings True
DoCmd.CancelEvent
End With



If IsNull(Me.[TxtWorkReq]) Then

MsgBox "Work Request Field Has No Data!!"
DoCmd.GoToControl "TxtDate"
Me![TxtWorkReq].SetFocus



End If


Me.[TxtWorkReq].SetFocus
Me![CmdOK].Visible = False
Me![CmdCancel].Visible = False
Me![PauseReturn].Visible = True



End Sub

Private Sub TxtWorkReq_AfterUpdate()

DoCmd.OpenQuery "qryStartTR"


End Sub

Private Sub TxtWorkReq_Change()
If IsNull(Me.[TxtWorkReq]) Then

MsgBox "Work Request Field Has No Data!!"
DoCmd.GoToControl "TxtDate"
Me![TxtWorkReq].SetFocus

Exit Sub

End If
End Sub

The query is a make table query and it makes the table correctly. Then I
open the table and determine if the field is greater than 0. If so, it
should open a form that gives options and stops at that point. The field
shows greater than 0 but the code keeps running and does not pull up the form
and stop the rest of the code.

Any help would be appreciated.
 
Rob,

First point, this is not a macro, it is a VBA procedure, which in Access
is quite a different kettle of fish.

What is CountOfWorkReqID? It looks like it might be a field in the
tblCountWR query. If so, you will need to say so, i.e. the syntax would be:
If rec!CountOfWorkReqID > ...
Also, what is the data type of this field. It would appear to be a
number, in which case it will never be greater than "0" which is a text
value. Try...
If rec!CountOfWorkReqID > 0 Then

Or, you could forget the recordset idea... this would be simpler and
more efficient:
If DLookup("[CountOfWorkReqID]","tblCountWR") > 0 Then

--
Steve Schapel, Microsoft Access MVP
I am trying to construct a macro with a condition. I have a query that
counts the number of records based on a criteria. The query does the count
correctly but how do I set the condition correctly. I have tried this in vba
and using the interface and can't seem to get it to work. Here is the vba
code:


Private Sub CmdOK_Click()


Dim DB As Database
Set DB = CurrentDb
Dim rec As DAO.Recordset

With CodeContextObject
.Visible = True
DoCmd.SetWarnings False
' Count if Work ID number already exists in table
DoCmd.OpenQuery "qryCountIDTR", acViewNormal, acEdit
Set rec = DB.OpenRecordset("tblCountWR")
If CountOfWorkReqID > "0" Then
' Open form to select if WR is rework
DoCmd.OpenForm "qryWRExist", acNormal, "", "", , acNormal
DoCmd.SetWarnings True


End If
' Append Data from Quality Checklist
DoCmd.OpenQuery "qryappendTR", acViewNormal, acEdit
' Update table with Work Request (Check ID), date, Checklist type,
etc. - Product
DoCmd.OpenQuery "qryUpdateTR", acViewNormal, acEdit
' Create Record for Tracking Time Spent
DoCmd.OpenQuery "qrycreateTimeTR", acViewNormal, acEdit
' Open form with fields to be updated/edited
DoCmd.OpenForm "frmInitialTR", acNormal, "", "", , acNormal
DoCmd.SetWarnings True
DoCmd.CancelEvent
End With



If IsNull(Me.[TxtWorkReq]) Then

MsgBox "Work Request Field Has No Data!!"
DoCmd.GoToControl "TxtDate"
Me![TxtWorkReq].SetFocus



End If


Me.[TxtWorkReq].SetFocus
Me![CmdOK].Visible = False
Me![CmdCancel].Visible = False
Me![PauseReturn].Visible = True



End Sub

Private Sub TxtWorkReq_AfterUpdate()

DoCmd.OpenQuery "qryStartTR"


End Sub

Private Sub TxtWorkReq_Change()
If IsNull(Me.[TxtWorkReq]) Then

MsgBox "Work Request Field Has No Data!!"
DoCmd.GoToControl "TxtDate"
Me![TxtWorkReq].SetFocus

Exit Sub

End If
End Sub

The query is a make table query and it makes the table correctly. Then I
open the table and determine if the field is greater than 0. If so, it
should open a form that gives options and stops at that point. The field
shows greater than 0 but the code keeps running and does not pull up the form
and stop the rest of the code.

Any help would be appreciated.
Click to expand...
 
Thanks for the response. I came to almost the same conclusion; I used DCount
and it worked fine. I'll remember DLookup also for the future. Thanks again,

Rob

Steve Schapel said:
Rob,

First point, this is not a macro, it is a VBA procedure, which in Access
is quite a different kettle of fish.

What is CountOfWorkReqID? It looks like it might be a field in the
tblCountWR query. If so, you will need to say so, i.e. the syntax would be:
If rec!CountOfWorkReqID > ...
Also, what is the data type of this field. It would appear to be a
number, in which case it will never be greater than "0" which is a text
value. Try...
If rec!CountOfWorkReqID > 0 Then

Or, you could forget the recordset idea... this would be simpler and
more efficient:
If DLookup("[CountOfWorkReqID]","tblCountWR") > 0 Then

--
Steve Schapel, Microsoft Access MVP
I am trying to construct a macro with a condition. I have a query that
counts the number of records based on a criteria. The query does the count
correctly but how do I set the condition correctly. I have tried this in vba
and using the interface and can't seem to get it to work. Here is the vba
code:


Private Sub CmdOK_Click()


Dim DB As Database
Set DB = CurrentDb
Dim rec As DAO.Recordset

With CodeContextObject
.Visible = True
DoCmd.SetWarnings False
' Count if Work ID number already exists in table
DoCmd.OpenQuery "qryCountIDTR", acViewNormal, acEdit
Set rec = DB.OpenRecordset("tblCountWR")
If CountOfWorkReqID > "0" Then
' Open form to select if WR is rework
DoCmd.OpenForm "qryWRExist", acNormal, "", "", , acNormal
DoCmd.SetWarnings True


End If
' Append Data from Quality Checklist
DoCmd.OpenQuery "qryappendTR", acViewNormal, acEdit
' Update table with Work Request (Check ID), date, Checklist type,
etc. - Product
DoCmd.OpenQuery "qryUpdateTR", acViewNormal, acEdit
' Create Record for Tracking Time Spent
DoCmd.OpenQuery "qrycreateTimeTR", acViewNormal, acEdit
' Open form with fields to be updated/edited
DoCmd.OpenForm "frmInitialTR", acNormal, "", "", , acNormal
DoCmd.SetWarnings True
DoCmd.CancelEvent
End With



If IsNull(Me.[TxtWorkReq]) Then

MsgBox "Work Request Field Has No Data!!"
DoCmd.GoToControl "TxtDate"
Me![TxtWorkReq].SetFocus



End If


Me.[TxtWorkReq].SetFocus
Me![CmdOK].Visible = False
Me![CmdCancel].Visible = False
Me![PauseReturn].Visible = True



End Sub

Private Sub TxtWorkReq_AfterUpdate()

DoCmd.OpenQuery "qryStartTR"


End Sub

Private Sub TxtWorkReq_Change()
If IsNull(Me.[TxtWorkReq]) Then

MsgBox "Work Request Field Has No Data!!"
DoCmd.GoToControl "TxtDate"
Me![TxtWorkReq].SetFocus

Exit Sub

End If
End Sub

The query is a make table query and it makes the table correctly. Then I
open the table and determine if the field is greater than 0. If so, it
should open a form that gives options and stops at that point. The field
shows greater than 0 but the code keeps running and does not pull up the form
and stop the rest of the code.

Any help would be appreciated.
Click to expand...
Click to expand...
 
Back
Top