Alert about existing record

[senditontome@gmail.com]

Hi,

As a relative Newbie, I'd appreciate a little assistance with an A2003
DB I'm trying to enhance.

This DB handles reports made by security staff on a shift by shift
basis based on two shifts per day. The two relevant fields are called
Datum and Shift.

I want to trigger an alert whenever the user tries to create a new
report when a report for that Datum / Shift combination already
exists. Ideally the alert would be triggered in the After Update Event
in the Shift. If the alert wasn't triggered then another form would
open so allowing the user to create a new report.

I have made a query called qryAlertExistin g which produces distinct
Datum/Shift results and I wanted to use this with a bit of code I've
taken from another of my DB's which triggers an alert but is based on
searching one field only. What I want now is to modify this code to
consider two fields (Datum & Shift) and to flag up if the proposed new
combination already exists.

The code is :-
If DCount("Datum", "QryAlertExisti ng")<1 Then
DoCmd.OpenForm "FrmAddNewRepor t"
Else
MsgBox "A report already exists for that Shift on that Date""
End if


Many thanks,

Yvonne

[Roger]

Re: Alert about existing record

On May 8, 3:20 am, senditont...@gm ail.com wrote:

Hi,
>
As a relative Newbie, I'd appreciate a little assistance with an A2003
DB I'm trying to enhance.
>
This DB handles reports made by security staff on a shift by shift
basis based on two shifts per day. The two relevant fields are called
Datum and Shift.
>
I want to trigger an alert whenever the user tries to create a new
report when a report for that Datum / Shift combination already
exists. Ideally the alert would be triggered in the After Update Event
in the Shift. If the alert wasn't triggered then another form would
open so allowing the user to create a new report.
>
I have made a query called qryAlertExistin g which produces distinct
Datum/Shift results and I wanted to use this with a bit of code I've
taken from another of my DB's which triggers an alert but is based on
searching one field only. What I want now is to modify this code to
consider two fields (Datum & Shift) and to flag up if the proposed new
combination already exists.
>
The code is :-
If DCount("Datum", "QryAlertExisti ng")<1 Then
    DoCmd.OpenForm "FrmAddNewRepor t"
Else
    MsgBox "A report already exists for that Shift on that Date""
End if
>
Many thanks,
>
Yvonne

use the 'where' clause in dcount, assuming the field on your form for
shift is called 'shift'

If DCount("Datum", "QryAlertExisti ng", "shift = " & shift)<1 Then
DoCmd.OpenForm "FrmAddNewRepor t"
Else
MsgBox "A report already exists for that Shift on that Date""
End if

[senditontome@gmail.com]

Re: Alert about existing record

Hi Roger,

As I'm still very much on the learning curve, I'm not too sure where I
put the word 'Where' in the code. I used the code you suggested
without a 'Where' and it came back with the message;_

Syntax error (missing operator) in query expression 'Shift = Late'

Also, would it be better if the text boxes were renamed txtDatum and
TxtShift as opposed to Datum and Shift after the fields they
represent?

Much appreciated.

Yvonne

[Roger]

Re: Alert about existing record

On May 8, 5:16 am, senditont...@gm ail.com wrote:

Hi Roger,
>
As I'm still very much on the learning curve, I'm not too sure where I
put the word 'Where' in the code. I used the code you suggested
without a 'Where' and it came back with the message;_
>
Syntax error (missing operator) in query expression 'Shift = Late'
>
Also, would it be better if the text boxes were renamed txtDatum and
TxtShift as opposed to Datum and Shift after the fields they
represent?
>
Much appreciated.
>
Yvonne

if you put the cursor over the word 'dcount' and hit f1 for help,
you'll see that this function takes up to three parameters and the
third is a 'where' expression

since the shift field is textual it needs to be surrounded by single
quotes, your code should look like

If DCount("Datum", "QryAlertExisti ng", "shift = 'Late'")<1 Then
DoCmd.OpenForm "FrmAddNewRepor t"
Else
MsgBox "A report already exists for that Shift on that Date""
End if


that is double-quote shift equal single-quote late single-quote double-
quote

as to renaming text boxes, it's a matter of preference.. I like the
text box name to match the field name so that I don't need to do
mental translation when working with vba

[senditontome@gmail.com]

Re: Alert about existing record

Roger,

All sorted now, many thanks.

Yvonne