Confirmation with yes/no field

[Guest]

In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[J. Goddard]

You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John

 

[Guest]

Forgive me, I'm having difficulties. I tried the following code after
changing the name of the subform and the field name, but it doesn't work.
Am I doing something wrong?

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormJobDetails] where [SubformJobDetails].[Job Number] = " & me![Job
Number] &
" and isnull([SubformJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If

You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John

In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[J. Goddard]

Hi -

You are querying the table underlying the subform, so instead of
[subformjobdetails] , use the table name.

John

Forgive me, I'm having difficulties. I tried the following code after
changing the name of the subform and the field name, but it doesn't work.
Am I doing something wrong?

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormJobDetails] where [SubformJobDetails].[Job Number] = " & me![Job
Number] &
" and isnull([SubformJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If


:

You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John


Bill Neilsen wrote:

In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[Guest]

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[TblJobDetails] where [TblJobDetails].[Job Number] = " & me![Job Number] &
" and isnull([TblJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If

I've changed the code and it still doesn't work! I really appreciate your
help and I really don't want to be a pain, but there must be something I'm
doing wrong.

Hi -

You are querying the table underlying the subform, so instead of
[subformjobdetails] , use the table name.

John

Forgive me, I'm having difficulties. I tried the following code after
changing the name of the subform and the field name, but it doesn't work.
Am I doing something wrong?

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormJobDetails] where [SubformJobDetails].[Job Number] = " & me![Job
Number] &
" and isnull([SubformJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If


:

You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John


Bill Neilsen wrote:


In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[Guest]

The error msg says there is a syntax error on the second line

set rst=currentdb.openrecordset( "Select count(*) as N from

- could that be right?

Hi -

You are querying the table underlying the subform, so instead of
[subformjobdetails] , use the table name.

John

Forgive me, I'm having difficulties. I tried the following code after
changing the name of the subform and the field name, but it doesn't work.
Am I doing something wrong?

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormJobDetails] where [SubformJobDetails].[Job Number] = " & me![Job
Number] &
" and isnull([SubformJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If


:

You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John


Bill Neilsen wrote:


In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[pietlinden]

Why are you doing a recordcount on a query that will return one record?
if you want to know if records exist, open a snapshot recordset, and
get a recordcount. Then you can act on the recordcount.

 

[J. Goddard]

The whole SQL statement needs to be on one line (unless you know how to
continue a long VBA statement from one line to the other); your browser
(or mine) is wrapping the lines.

If rst!N > 0 Then
Msgbox "Warning - not all jobs are complete"
cancel = -1
End If
set db=nothing

John

The error msg says there is a syntax error on the second line

set rst=currentdb.openrecordset( "Select count(*) as N from

- could that be right?

:

Hi -

You are querying the table underlying the subform, so instead of
[subformjobdetails] , use the table name.

John


Bill Neilsen wrote:

Forgive me, I'm having difficulties. I tried the following code after
changing the name of the subform and the field name, but it doesn't work.
Am I doing something wrong?

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormJobDetails] where [SubformJobDetails].[Job Number] = " & me![Job
Number] &
" and isnull([SubformJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If


:



You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John


Bill Neilsen wrote:



In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[Guest]

It still won't work, thanks for the help mate, I might try and find another
way.

The whole SQL statement needs to be on one line (unless you know how to
continue a long VBA statement from one line to the other); your browser
(or mine) is wrapping the lines.

If rst!N > 0 Then
Msgbox "Warning - not all jobs are complete"
cancel = -1
End If
set db=nothing

John

The error msg says there is a syntax error on the second line

set rst=currentdb.openrecordset( "Select count(*) as N from

- could that be right?

:

Hi -

You are querying the table underlying the subform, so instead of
[subformjobdetails] , use the table name.

John


Bill Neilsen wrote:


Forgive me, I'm having difficulties. I tried the following code after
changing the name of the subform and the field name, but it doesn't work.
Am I doing something wrong?

Dim rst As Recordset, N As Integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormJobDetails] where [SubformJobDetails].[Job Number] = " & me![Job
Number] &
" and isnull([SubformJobDetails].[ Date Completed])" )

If rst!N > 0 Then
..issue warning
cancel event
End If


:



You could put a validation check in the Before Update event of the "all
Jobs Finished" checkbox:

for example:

dim rst as recordset, N as integer
set rst=currentdb.openrecordset( "Select count(*) as N from
[SubFormTable] where [subformtable].[job number] = " & me![job number] &
" and isnull([subformtable].[date finished])" )

if rst!n > 0 then
..issue warning
cancel event
endif

John


Bill Neilsen wrote:



In my servicing monitoring database I have a form (Service Details) with a
subform (Job Details).
The form has the main details eg Job Number Date In, Date Due, "All Jobs
Finished" (yes/no), etc.
The subform has individual jobs that are part of the main service. It has
job details including "Date Finished".
Currently it's possible to close the service by ticking the "All Jobs
Finished" check box and still have individual jobs still open (Date Finished)
field blank.
How can I have a message warning that there are still jobs open for the
service.

 

[Guest]

I've tried the other way to no avail and I'd like to give your way a shot,
but I don't understand. Can you give me a bit more info?