IF...THEN statement on pop-up form: possible?

[scubadiver]

On a pop-up form I have a combo box whose choice needs to be compared to the
information in the field of the LAST record in the subform for the CURRENT
record in the main form (make sense?)

Generically, what would my IF...THEN statement look like?

Cheers in advance

 

[Barry Gilbert]

Generally, you should try to avoid cross-form references. If the popup form
is opened from the main form, pass in the last record's value in the popup
form's OpenArgs. Then compare the value from within the popup form.

For example, in the button that opens the popup form, you could use
something like:

Me.subFormName!Form!RecordsetClone.MoveLast
DoCmd.OpenForm "myFormName", WindowMode:= acDialog,
OpenArgs:=Me.subFormName!Form!RecordsetClone!myFieldName

Then in the routine in the popup form where you want to compare values:
If Me.myCombo <> Me.OpenArgs Then
MsgBox "They don't match!"
End If

Barry

 

[scubadiver]

"yuck!" is the simplest response!

When a record is entered into the subform, and the "dept" field doesn't
match the "dept" field in the main form, the pop-up form appears so I know
the cross referencing works.

An alternative idea I have thought of is if I include a new field on the
main form for "dept" which is populated with the information for the latest
record in the subform and gets updated every time a new record is entered.
That would be an easier method to compare. Surely there is a method for doing
this?

Help would be appreciated.

 

[Barry Gilbert]

Ah. I understand better what you're trying to do.

Instead of trying to detect if the values match or not, why not make it so
that the user has no choice. In the DefaultValue property of the control on
the subform, point to the value on the main form. Then lock it so the user
cannot change it. Better yet, hide the control.

I wonder, though, if the both forms' records are related, why you need to
store the dept value in both tables.

Barry

 

[scubadiver]

Members of staff can work in more than one department (which would have a
different cost centre) so I need a way to authorise records.

The "dept" field in the main form is locked and disabled and is used for
filtering purposes so it makes it easier to find records.

What I have so far is that a pop-up form will appear when the department
that is recorded in the sub form does not match the department in the main
form. In this pop-up form I have a combo box which lists the departments and
a text box for a password, both relating back to a table.

It has just occurred to me that a member of staff can work for more than one
department during a week which makes it more complicated. So I have hit upon
the idea of a hidden field on the main form which is populated with the
department name from the latest record in the subform then detecting a match
will be easier. If they match then the password box is unlocked and enabled
(I realised that even if the departments don't match a password can still be
entered).

So what I really need is the code to populate a hidden field with the
department name from the last record in the subform.

 

[Barry Gilbert]

In the subform's AfterUpdate event, write code to pass the value up to the
parent. Something like this:

Me.RecordsetClone.MoveLast
Me.Parent.MyHiddentTextbox = Me.RecordsetClone!MySubformsFieldName

Barry

 

[scubadiver]

Hi,

thanks for the code. I have a couple of queries

(a) If I enter the department name and then change it, the parent box
doesn't change.

(b) Should the parent box have a bound control source?

cheers

 

[Barry Gilbert]

Hi,

thanks for the code. I have a couple of queries

(a) If I enter the department name and then change it, the parent box
doesn't change.

Hmm. How about putting the RecordSetClone code in the subform textbox's
AfterUpdate?

(b) Should the parent box have a bound control source?

Not sure. A ControlSource is used if you want to save the data in the control.