Date Forced Popup

[shane]

I have a table where the fields ecd (expected completion date) and acd
(actual completion date), and acdlate exist.

I have two forms "complaints1" and "acdlate".

If in "complaints1" the acd entered is greater than the ecd I want form
"acdlate" to pop up on the same record for which the dates were entered.

"acdlate" has fields "notificationnumber" and "reasonlate".

all fields come from the same table "complaints".


In complaints1 on the update event for "acd" I have entered the code

Private Sub ACD_AfterUpdate()
If Forms!complaints1!ECD < Forms!complaints1!ACD Then
DoCmd.OpenForm "acdlate", , , Forms!acdlate!notificationnumber =
Forms!complaints1!notificationnumber, acFormEdit, acDialog
End If
End Sub

When the acd field is updated with a date greater than ecd I see the erro:
"...couldn't find the form "acdlate"...." Spelling is correct. What's going
on?

form acdlate is set up with the border = dialogue and popup = yes.

Any help is appreciated.

 

[GBA]

sanity check time: temporarily for testing purposes....put a button on that
main form and define it as opening up that pop up.....see if it works...if
not - resolve that issue this way first....

 

[kate]

There are a number of issues here:

1. From your description you don't in fact want the acdlate form to be a
popup form per se, but merely to open in dialogue mode. A popup form is
one
which remains open over another form even when the other form has focus, a
form opened in dialogue mode must be closed or hidden before focus can be
returned to another form (or anything else for that matter). So change
its
Popup property to False (No).

2. As you want the form to open at the current record, and both forms are
bound to the complaints table, you need to ensure that the record is
explicitly saved before opening the second form.

3. You are referencing the acdlate form in the WhereCondition argument of
the OpenForm method before the form is opened, hence the error.

4. The WhereCondition argument of the OpenForm method is a string
expression
which filters the form. The form will be filtered to those records where
the
expression evaluate to True, so you need to include the relevant field
name
and the = sign in a literal string and concatenate the value of the
relevant
control in the current form to it. If the field is of text data type you
need to wrap the value in quotes characters in the expression.

Putting all that together the code for the ACD controls event procedure
would
be as follows:

Const conFORM = "acdlate"
Dim strCriteria As String

' first make sure neither control is Null
If Not IsNull(Me.ACD) And Not IsNull(Me.ECD) Then
If Me.ECD < Me.ACD Then
' ensure current record is saved
Me.Dirty = False

strCriteria = "notificationnumber = " & Me.notificationnumber

' open acdlate form in dialogue mode filtered to current record
DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
End If
End If

The above assumes that notificationnumber is a number data type. If it’s
a
text data type amend the code with:

strCriteria = "notificationnumber = """ & Me.notificationnumber &
""""

to wrap the value in quotes characters. Each pair of contiguous quotes
characters within the literal strings delimited by quotes characters is
interpreted as a literal quotes character.

Ken Sheridan
Stafford, England

 

[shane]

Thanks Ken. That did the trick.

There are a number of issues here:

1. From your description you don't in fact want the acdlate form to be a
popup form per se, but merely to open in dialogue mode. A popup form is one
which remains open over another form even when the other form has focus, a
form opened in dialogue mode must be closed or hidden before focus can be
returned to another form (or anything else for that matter). So change its
Popup property to False (No).

2. As you want the form to open at the current record, and both forms are
bound to the complaints table, you need to ensure that the record is
explicitly saved before opening the second form.

3. You are referencing the acdlate form in the WhereCondition argument of
the OpenForm method before the form is opened, hence the error.

4. The WhereCondition argument of the OpenForm method is a string expression
which filters the form. The form will be filtered to those records where the
expression evaluate to True, so you need to include the relevant field name
and the = sign in a literal string and concatenate the value of the relevant
control in the current form to it. If the field is of text data type you
need to wrap the value in quotes characters in the expression.

Putting all that together the code for the ACD controls event procedure would
be as follows:

Const conFORM = "acdlate"
Dim strCriteria As String

' first make sure neither control is Null
If Not IsNull(Me.ACD) And Not IsNull(Me.ECD) Then
If Me.ECD < Me.ACD Then
' ensure current record is saved
Me.Dirty = False

strCriteria = "notificationnumber = " & Me.notificationnumber

' open acdlate form in dialogue mode filtered to current record
DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
End If
End If

The above assumes that notificationnumber is a number data type. If it’s a
text data type amend the code with:

strCriteria = "notificationnumber = """ & Me.notificationnumber &
""""

to wrap the value in quotes characters. Each pair of contiguous quotes
characters within the literal strings delimited by quotes characters is
interpreted as a literal quotes character.

Ken Sheridan
Stafford, England

 

[Hans Up]

' first make sure neither control is Null
If Not IsNull(Me.ACD) And Not IsNull(Me.ECD) Then
If Me.ECD < Me.ACD Then

I don't understand the value of confirming both ACD and ECD are not Null
before "If Me.ECD < Me.ACD Then". If either is Null, that expression
can not return True ... so why bother testing them beforehand?

I stared at this a while, Ken, and I can't see what I'm overlooking.

Regards,
Hans

 

[Hans Up]

In reality I should have expanded the code to do whatever is appropriate if
either is Null. The most likely course of action would be to alert the user
and probably set both to Null in the event of one or the other being Null.
Without being certain about the actual constraints, however, I'm a little
reluctant to be too specific with this sort of validation as an OP might
unwittingly accept whatever I put in when its not in fact appropriate to
their business model.

Another option of course, and probably a better one, would be to anticipate
an invalid operation in the control's BeforeUpdate event procedure, setting
the return value of its Cancel argument to True if the operation is invalid.

Greetings Ken,

You were spot on when you said "There are a number of issues here". I
was focused on the logic of only one detail. The recent exchange
between you and James Fortune, regarding SQL issues, inspired me to pay
closer attention to details. And that has (generally) been a good thing.

So, thank you, sir. And keep up the good work. You are a real asset!

Regards,
Hans

 

[Hans Up]

Thank you Hans, its very much appreciated. As I said to James all I really
do here is pass on a few things I've picked up over the years (including some
bad habits, no doubt!). If people find them useful its very gratifying.

You're welcome, Ken. Another aspect of your exchange with James caught
my attention. Both of you display display a masterful command of your
language. In fact, that seems fairly common among you Commonwealth
folks in here. Quite impressive.

I said "your" language because it don't bear more'n a passin'
resemblance to the Merikan Hillbilly variation I learnt!

Cheers,
Hans