applying logic in form controls

[Pitu]

Hi,

I have a bound form with two combobox and one text box. now i want to
display a certain value yes or no in the text box based upon the combination
of values selected from both the comboboxes. how do i do that?

 

[Rob Oldfield]

The best syntax would depend on exactly what conditions you want to use, but
it would probably be a combination of selects and ifs. Something like...

select case me.cbo1
case "a","b","c"
if me.cbo2="x" then
me.txt1="Yes"
else
me.txt1="No"
endif
case else
if me.cbo2="y" then
me.txt1="Yes"
else
me.txt1="No"
endif
end select

 

[Rob Oldfield]

Oops. Hit 'send' before I'd finished. That code would go into a sub, which
would be called in the after update event of each of the combos (so that the
text box is immediately updated), and also in the current event of the form
(so that it's updated when you move from record to record).

 

[Pitu]

Thanks Rob,

I got the idea so let me work on it and will ask if it does not work.

 

[Pitu]

this is just the test code. I have two combobox named TriggerSource and
TriggerType and name of the textbox is Text15. Triggersource has list of
values- Other and NCR. whereas triggertype has value of Judgement. Here is
the code that I wrote in triggersource_afterupdate event and also
triggertype_afterupdate event as well as form_current event which gives me an
error that you cannot reference property or method of a control without
setting focus.

Private Sub TriggerSource_AfterUpdate()
Select Case Me.TriggerSource.Text
Case "Other"
If Me.TriggerType.Text = "Judgement" Then
Me.Text15 = "otherjudgement"
End If
Case "NCR"
If Me.TriggerType.Text = "Judgement" Then
Me.Text15 = "ncrjudgement"

Case Else
Me.Text15 = "hi"
End Select
End Sub

Don't know what to do now

 

[Rob Oldfield]

The actual error is because you're using the Text property, which is only
available when the control has the focus. (You can use it to get at the
current contents of a textbox while you're typing in it.)

You're also missing an end if after the line Me.Text15 = "ncrjudgement"

So it would need to be...

Private Sub TriggerSource_AfterUpdate()
Select Case Me.TriggerSource
Case "Other"
If Me.TriggerType = "Judgement" Then
Me.Text15 = "otherjudgement"
End If
Case "NCR"
If Me.TriggerType = "Judgement" Then
Me.Text15 = "ncrjudgement"
End If
Case Else
Me.Text15 = "hi"
End Select
End Sub

Note that if triggersource is 'Other' and triggertype is anything other than
'Judgement' then you're not going to be doing anything and you'll just end
up with whatever was in Text15 before.

Last thing: you need to put this into one sub rather than repeating three
times. Somewhere in the form's code window (outside any of your other
routines) just add...

private sub SetText15()
Select Case Me.TriggerSource
(and the rest of the corrected code)
end sub

then each of the after update events and the current event would look
like...

Private Sub TriggerSource_AfterUpdate()
SetText15
End Sub

If you have the code repeated three times then it becomes cumbersome to
remember to update all three when you make a change to the logic.

 

[Pitu]

Hi Rob,

I tried doing what you mentioned but then the textbox is always showing the
value hi by default on form load. and also does not change when the try to
select different values in the combobox. So looks like the code is directly
going to the case else statement. Can you suggest anything for this.

 

[Rob Oldfield]

Does triggersource display a value when the form loads? If not then the
case else statement will always kick in.

The best way to test it is this:

Go to the code window and right click the select case line. Choose Toggle,
Breakpoint. That line will be highlighted.
Now close the code window and switch to form view. It should run through
the code and stop when it reaches the breakpoint you just set.
Hit F8. It will move to the next line to process.
Hit F8 again... etc, etc.

What happens?

 

[Pitu]

I am a learner so I have never used teh debugger but I followed the steps
that you told. When i press F8 after select case statement it goes to the
case "Other". after pressing F8 it goes to Case "NCR" and after pressing F8
it goes to Case Else and then after pressing F8 it goes to Me.Text. at every
point if i point cursor on select case statement it shows Me.TriggerSource =
0. I don't understant what this means.

 

[Rob Oldfield]

It means that the combo is based on multiple columns - displaying one thing
while it actually *equals* something different.

Go the design view of the form and select triggersource and display its
properties. What does it say in column widths on the format tab and what
does it say in the row source on the data tab?

 

[Rob Oldfield]

....and I'll explain in more detail what the problem is when I get that back.

 

[Pitu]

It says SELECT TriggerSourcetbl.id, TriggerSourcetbl.Triggersource FROM
TriggerSourcetbl; in rowsource on data tab and 0";1" on colum widths of
format tab.

 

[Pitu]

It says SELECT TriggerSourcetbl.id, TriggerSourcetbl.Triggersource FROM
TriggerSourcetbl; in rowsource on data tab and 0";1" on colum widths of
format tab.

 

[Rob Oldfield]

Good. That's what I was expecting.

What that means is that the combo is pulling it's information back from the
query SELECT TriggerSourcetbl.id, TriggerSourcetbl.Triggersource FROM
TriggerSourcetbl. Now if you run that query then you'll get two columns of
information, but the 0";1" means that the first column will have zero width
so just won't display. But if you now look in the same place, at the
properties of triggersource, then the bound column (on the data tab) will be
set to 0 (where that is a zero based index to which column to *actually* use
as the "Value" of that control) which means that although the combo is
*displaying* 'Other' (or whatever) then its actual value is 0.

So, me.triggersource actually appears to Access as 0, which means that your
code doesn't work and just falls though to the Else statement. Two ways
around that: either just figure out what ID number actually relates to
Other and NCR and use those instead by looking at TriggerSourcetbl, or use
the .Column property of the combo instead (where you replace If
Me.TriggerType = "Judgement" with If Me.TriggerType.column(1) = "Judgement"
to just ignore the column widths thing.

It will probably help to make sense of that by changing the column widths
thing to .25",.75" for the moment.

Out of interest, did you set this form up using wizards, or someone else did
it?

 

[Pitu]

Hi Rob,

I am still waiting for your reply. Let me know if you need more information
to answer. Please read the previous post for the details that you asked.

 

[Rob Oldfield]

Hmm. Can you not see it in reply to your other reply?

 

[Pitu]

i am so sorry. i just missed your post to read. I did not use the wizard to
create the form. I just used create form in design view. and then wrote the
code that you gave me. Let me try doing what you suggested and will get back
to you. Thanks.

 

[Pitu]

It is still not working. I tried doing this with .column(1) and .Column(2)
since Id is first column and value ie "other" is second column. It is still
not working. I also tried doing just id instead of value but it still won't
work. It still displays hi in the text box. Here is the code

Select Case Me.TriggerSource
Case Me.TriggerSource.Column(2) = "Other"
If Me.TriggerType.Column(2) = "Judgement" Then
Me.Text15 = "otherjudgement"
End If
Case Me.TriggerSource.Column(2) = "NCR"
If Me.TriggerType.Column(2) = "Judgement" Then
Me.Text15 = "ncrjudgement"
End If
Case Else
Me.Text15 = "hi"
End Select

 

[Rob Oldfield]

Almost there I think. The problem with your attempt here is that the column
count is zero based - so column(0) is the first column and column(1) is the
second. So try it with Me.TriggerSource.Column(1) instead. (Though note
that the bound column - where the column number of that is set on the data
tab - this time starting from 1 instead of 0.... sheesh.... would not need
the column reference)

I note that you've also updated the triggertype references to .column as
well. You'd certainly need to do that if the properties of that one look
the same as triggertype, but it'd be worth checking.

If it doesn't work, then try the same debugging method as before to step
through your code. It's very useful for seeing at which point your logic
goes bang.

 

[Rob Oldfield]

My turn to miss something. I didn't see your bit about already trying it
with column(1). Try the debugging thing and waving the mouse at things as
you did before.