Re: Go to Control with IIf statement

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" wrote:

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."

"Adriana" <Adriana@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E9A6A7E7-07AD-47D4-AC2E-CA13CF5AFC71@xxxxxxxxxxxxxxxx
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" wrote:

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."

"Adriana" <Adriana@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CC5418E5-5571-45EA-B8FB-D031151C0D5A@xxxxxxxxxxxxxxxx
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" wrote:

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."

"Adriana" <Adriana@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CE743852-ECA1-4C1F-9D47-02DE43345932@xxxxxxxxxxxxxxxx
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.









.