Go to Control with IIf statement

[Guest]

I would like to have a condition on a form control that will set the focus to
another control on the form. I have a drop down box in a form based on a
Category table and if that selection is one of two choices, then I need a
message box to tell them to fill in another control and a GoTo control to set
the focus to the other field. So far, I built a macro and assigned the macro
to the After Update event of the actual control. It works, however, no
matter which category is picked, the MsgBox pops up and sets focus to the
other field even though I only want two specific categories.

 

[Al Campagna]

Adriana,
It woulkd have been helpful had you given a bit more detail, like the name of the
combo, the values in the combo, the names of the fields you want to go to, and what
selection goes to what field, and your Macro actions.

I never use macros... but I don't think Macros has an IFF action, so that's a problem
to start with.
Use an Event Procedure that triggers on the AfterUpdate event of your combo (ex.
cboSomeChoice)
(use your own object names)
Private Sub cboSomeChoice_AfterUpdate()
If cboSomeChoice = "X" Then
MsgBox "Must fill in Field1", vbOKOnly
Me.Field1.SetFocus
Else
MsgBox "Must fill in Field2", vbOKOnly
Me.Field2.SetFocus
End If

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

It still doesn't work. Let me explain further. I have a category table with
three fields: ID (autonumber), category with abbreviated letters such as SHL
and COV (primary key set to this), and a description of the categories. In
another table that the form is based on, I have this combo box as a field set
to number property with a select statement (bound column is one). I only
want the focus to be set to field "ApprovedPMSNo" if category SHL or COV is
chosen with the message box popping up, otherwise the focus goes to the next
field which is "Supplier" (the normal tab order). This is what I put in the
After Update of my combo box and even if I pick other categories, the macro
runs.

If cboCategory = "SHL" Or "COV" Then
MsgBox "Must fill in Approved Pantone Number", vbOKOnly
Me.ApprovedPMSNo.SetFocus
Else
Me.Supplier.SetFocus
End If

 

[Al Campagna]

Adriana,
When you say "the macro runs" make sure to indicate what it "did", rather than just
"ran".
Does it always go to ApprovedPMSNo?

There is a problem with your expression...

If cboCategory = "SHL" Or cboCategory = "COV" Then
MsgBox "Must fill in Approved Pantone Number", vbOKOnly
Me.ApprovedPMSNo.SetFocus
Else
Me.Supplier.SetFocus
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Actually, I used the Code Builder in the After Update event of the combo box,
not a macro. I used the expression below and it doesn't do anything.
Meaning it will not give me a message box or sets focus even if I select COV
or SHL.

 

[Al Campagna]

Adriana,
Sounds like you have your code in the wrong place... and it's not firing at all... as
opposed to not working properly.
When in doubt as to firing off code on an event, add a Beep in the code. No Beep =
didn't run... as opposed to ran but failed.

I take it cboCategory is on the main form, and so is Supplier and ApprovedPMSNo

1. First, delete any code you have added regarding this problem, so we can start
fresh...
2. In Design mode, select the combo, and find it's AfterUpdate event in the Properties
box.
3. Place your cursor in the text box to the right of AfterUpdate.
4. Use the drop down arrow on the right of that text box, and select Event Procedure.
5. Now click the 3 dot button (...) on the right.
6. You'll see this...

Private Sub cboCategory_AfterUpdate()

End Sub

Now add my code between those 2 lines...

Private Sub cboCategory_AfterUpdate()
If cboCategory = "SHL" Or cboCategory = "COV" Then
MsgBox "Must fill in Approved Pantone Number", vbOKOnly
Me.ApprovedPMSNo.SetFocus
Else
Me.Supplier.SetFocus
End If
End Sub

Test it... it should work.
**If it doesn't, I need you to copy and paste your code (exactly as you have it) into your
reply, and also give me all the details about cboCategory.
(Columns/Fields/NoOfCols/ColWidths/BoundColumn)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

I did all this, but if I write cboCategory, this will not work, I have to
write category as the Private sub name. It still doesn't work. Any other
ideas? Should I put the event in the form properties of the form, not the
control? Yes, Category, Supplier and ApprovedPMSNo is on the main form.

 

[Guest]

Sorry, here is all the information you asked for:

Private Sub Form_AfterUpdate()
If Category = "50" Or Category = "53" Then
MsgBox "Must fill in Approved Pantone Number", vbOKOnly
Me.ApprovedPMSNo.SetFocus
Else
Me.Supplier.SetFocus
End If
End Sub

Category is a datatype "Number" in the main form, but is it's own table. In
the form, it has 3 columns, 0";0.5";2" columns widths, the primary key is set
to Category and has an autonumber (50 is COV and 53 is SHL), bound column is
1.
(Columns/Fields/NoOfCols/ColWidths/BoundColumn).

Seems like maybe it should be in Form properties, not on the control itself,
but I could be wrong. It sort of worked out like that in the After Update
event of the Form properties except that you have to go to the next record
for the event to work, which is bad, it needs to happen after you update the
Category field. So if it does need to be in the Form properties, which event
would that be?
Sorry for being a pain.

 

[Al Campagna]

Adriana,
The code doesn't go in the "Form" AfterUpdate event. It goes in the AfterUpdate event
of the combo box. (Category?)
I don't know what your object names are in all cases, so you need to replace my sample
names with yours.

You need to follow the instructions I laid out exactly....

Also, you keep changing the problem. Last time it was "COV" and "SHL"... now it's "50"
or "53".
You didn't indicate what fields are in the 3 columns, and what those values are, and
what type they are.
If 50 and 53 are numbers, and are in column(0) (the very first column of the three)..
then...

Private Sub Category_AfterUpdate()
If Category = 50 Or Category = 53 Then
MsgBox "Must fill in Approved Pantone Number", vbOKOnly
Me.ApprovedPMSNo.SetFocus
Else
Me.Supplier.SetFocus
End If
End Sub

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Thank you, that works with the numbers instead of SHL or COV for some reason
(even though the primary key in the Category table is set to category, not
autonumber). You're right, it doesn't work if you place it in the Form
properties because you would have to go to a new record for the event to work
(it wouldn't work if users tabbed over or put their cursor in another text
box).

While I have you here, do you know if there's anyway to make one part of a
report landscape view and another part portrait view? I have one master
report with several subreports (one of which is a pivot chart I made into a
form for viewing and printing purposes) with page breaks in between. I need
two of the pages to be landscape view, is this possible?

 

[Al Campagna]

My initial opinion = No. But there may be some work-arounds out there, posted in the
Access groups, but I don't think I'd recommend them unless you have good coding skills,
and can handle printer definitions.
But, you can do a Google Groups search against the Access Groups with something like...
"public.access" "report" "both" "landscape" "portrait"
and read up for yourself and decide if you want to try some of the suggestions.

Taking the path of least resistance... I'd create separate reports.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."