How to find the Next greater Number????? 2

[tormented]

I have two combo boxes. ComboA and ComboB. I would like to have comboB default to the NEXT greater number than what was selected in comboA. Any Suggestions?

 

[KenReay]

Hi

In the got focus event of comboB, put

If IsNUll(comboB) Then
combob = comboA + 1
End if

In the after update event of combo A , put similar code

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tormented]

Ken
Thanks for your reply,

Sorry I should have given a little more info.
Both ComboA and ComboB get ther values from TableA,FeildA
Feild A has records which are numbers, which are not even. IE 12.2,
14.4,
17.9,
23.6,
so on and so on
If comboA=12.2 then I would like ComboB to get the next record from FeildA which would be
ComboB=14.4
With the code
Comboa+1
ComboB returns a value of 13.2 instead of the "14.4" I would like

Any help would be great?
Thanks Tormented

 

[CajunCenturion]

Rather than increment the the value of the ComboA, I think you might want to increment the .ListIndex property of the combobox, and then secure the corresponding value.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

CajunCenturion,

I afraid I newer to Access. Can you explain about .listindex a bit more as anything I've read on it isn't to clear. As well as for securing the corresponding value.
I will be away from computer for a while so I will check back tonight. Thanks for any help in advance.

Tormented

 

[CajunCenturion]

The .ListIndex is like an array subscript into the ComboBox.

When .ListIndex is 0 - you're looking at the first value in the list
When .ListIndex is 1 - it's the second value in the list
When .ListIndex = 2 - the third value in the list, and so on.

So, in your situation, if ComboA has a value of 12.2, you need to know where in the list that 12.2 appears which would be
ComboA.ListIndex. Assuming that ComboA and ComboB are ordered the same, you want the next item in the list, whatever it's value happens to be.
ComboB.ListIndex = ComboA.ListIndex + 1
This increments to Index into the list, which I think is what you're after, it's not a increment of the Value, which is what you're doing.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

Finally got back to the computer. This works to a point. Instead of the number that I require ie "14.4" in the text of ComboB I now get the listindex number of ComboA+1 into the text of ComboB

Is there a way you can reference the Listindex but insert the text that the List index is tied to.

Thanks for all the help so far!!
Tormented

 

[tormented]

I forgot to mention
ComboB.listindex= ComboA.listindex+1

Gives the error
"Improper use of Listindex property"

So I changed the code to
ComboB=ComboA.listindex +1

Thanks

 

[CajunCenturion]

I'm sorry, I sometimes forget that in VBA you cannot set the .ListIndex property (it's read-only in VBA) like in you in VB6.

The statement ComboB=ComboA.listindex + 1 is the same as ComboB.Value=ComboA.listindex+1 because the .Value property is the default property of a combobox and we've already discussed why that is not the desired course of action.

However, you can force a specific item from the Combo box by referenceing the ItemData array. I would try the following assignment.

ComboB.Value = ComboB.ItemData(ComboA.ListIndex + 1)

You alsmo might to to insert a range check, something like the following:

Offset = ComboA.ListIndex + 1
If (Offset < ComboB.ListCount) Then
ComboB.Value = ComboB.ItemData(Offset)
End If






Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

cajunCentrion

Thank you I have given you a star

I am having one other problem with this now

ComboA and B(rowsource) are controlled by another=ComboC thru a query


If comboC= &quot;apples&quot; then
ComboA and B show list of only &quot;apples &quot;

If comboC= &quot;oranges&quot; then
ComboA and B show list of only &quot;oranges &quot;

Your ItemData solution solve my problem partially

no matter what the user selects to start for comboC everything works fine for comboA and ComboB

The problem is when they change there selection for comboC that the itemdata for comboB doesn't seem to recognize that
the &quot;list&quot; has change and still keeps the old Itemdata from before.

This seems hard to explain but I hope out of this you understand that the Combo's are working fine other than the fact that ComboB Itemdata doesn't seem to realize that the list has changed and still picks from the old or first list.


Thank-you for all your help.
I hope you can help with this as well.

Tormented

 

[CajunCenturion]

Perhaps you could show the code that clears and reloads ComboA and ComboB after a new selection from ComboC.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

here it is

As well I thought you should know that if i go back and delete comboB then Everything works fine! It will not if there is a value already there

If Not IsNull([comboC]) And [ComboC] <> &quot;&quot; Then
ComboA.RowSource = &quot;SELECT table.feildA, Table.FeildB FROM Table WHERE ((Not (table.feildA)='null') AND ((Table.FeildB)='&quot; & [ComboC] & &quot;'))ORDER BY Table.FeildC;&quot;
Else
comboA.RowSource = &quot;WhateverQuery&quot;
End If

ComboA.Requery
end Sub

Thanks

 

[CajunCenturion]

The code that you posted shows how the values in ComboA are being updated, but ComboB is not being updated. I would think that you would need to do much the same to ComboB to requery it's data, just as you requery ComboA after a change in ComboC.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

CajunCenturion

Every combo requery's it's data. I'm sorry I forgot to mention that every combo has it's own code as shown above.

In my rush to get back to you I forgot to put the equation for ComboB as well. It is exactly the same as ComboA's. Actually there are about 10 combos but for explaination sake I have been using examples to help.

Is there any way to delete the value or text of comboB based on a change to ComboC. That seems to work if I do it manually.

I tried to set the value of ComboB=&quot; &quot;
but that doesn't work because you have to delete the value of ComboB for the Itemdata to work, not just set the value to &quot; &quot;


Your patience and help have been much appreciated. Thank-you.

Tormented

 

[CajunCenturion]

Let's have a look at the actual ComboB code.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

CajunCenturion,

Here are the codes I am using to requery the combo boxes!


comboA=Westwardsignals1
comboB=Westwardsignals2
ComboC=Subdivisions

Private Sub westwardSignals1_enter()
If Not IsNull([Subdivisions]) And [Subdivisions] <> &quot;&quot; Then
WestwardSignals1.RowSource = &quot;SELECT Signals.WestboundSignals, Signals.Subdivision,Signals.Order FROM Signals WHERE ((Not (Signals.WestboundSignals)='null') AND ((Signals.Subdivision)='&quot; & [Subdivisions] & &quot;'))ORDER BY Signals.Order;&quot;

Else
WestwardSignals1.RowSource = &quot;WestwardSignalsQuery&quot;
End If

WestwardSignals1.Requery


End Sub

Private Sub WestwardSignals2_enter()
If Not IsNull([Subdivisions]) And [Subdivisions] <> &quot;&quot; Then
WestwardSignals2.RowSource = &quot;SELECT Signals.WestboundSignals, Signals.Subdivision FROM Signals WHERE ((Not (Signals.WestboundSignals)='null') AND ((Signals.Subdivision)='&quot; & [Subdivisions] & &quot;'))ORDER BY Signals.Order;&quot;
Else
WestwardSignals2.RowSource = &quot;WestwardSignalsQuery&quot;
End If

WestwardSignals2.Requery
End Sub


Thanks again
Tormented

 

[CajunCenturion]

I can see where both WestwardSignals1 and WestwardSignals2 are both being required, but in neither case do I see where the Subdivisions combobox is being required. The problem is that the Subdivisions combobox is not being required at the corret time, which if I properly understand the problem is whenever either WestwardSignals1 or WestwardSignals2 are changed. Where in your code to you have the line?

Subidivisions.Requery


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

CajunCenturion,

Not to sure I follow?

Both Westward1 and 2 both pull up lists of whatever Subdivisions is set to. If I cange Subdivisions and then go back to west1 or west2, they now represent the change I have just made to Subs.

My problem was that while the &quot;list&quot; for west1 or 2 had changed the itemdata still saw the old list before the change to subs.

Do I need to implement the Subs.requery because of a timing issue?

Are you saying that will my list might have changed in westward1 or 2 the itemdata doesn't reconize it at time?

If this is the case where might I implement the Subdivisions.requery?

Thanks
Tormented

 

[CajunCenturion]

I think they problem may be when you are updating the combos. Because you are using the _Enter events for the updates, neither combo will update unless you actually enter that combobox. So if you change the Subdivision combo, and then you click on Signals1, you will update Signals1, but not Signals2 because you have not caused the _Enter event to be fired for Signals2.

I would suggest that you update Both Signals Combos in the _Exit event of Subdivision. And I would also insure, that since the Signals combos are so highly related, I would never allow one to be updated with also updating the other.

Private Sub Subdivisions_Exit()
UpdateSignals
End Sub

Private Sub westwardSignals1_enter()
UpdateSignals
End Sub

Private Sub westwardSignals2_enter()
UpdateSignals
End Sub

Private Sub UpdateSignals()
If Not IsNull([Subdivisions]) And [Subdivisions] <> &quot;&quot; Then
WestwardSignals1.RowSource = &quot;SELECT Signals.WestboundSignals, Signals.Subdivision,Signals.Order FROM Signals WHERE ((Not (Signals.WestboundSignals)='null') AND ((Signals.Subdivision)='&quot; & [Subdivisions] & &quot;'))ORDER BY Signals.Order;&quot;

Else
WestwardSignals1.RowSource = &quot;WestwardSignalsQuery&quot;
End If
WestwardSignals1.Requery

If Not IsNull([Subdivisions]) And [Subdivisions] <> &quot;&quot; Then
WestwardSignals2.RowSource = &quot;SELECT Signals.WestboundSignals, Signals.Subdivision FROM Signals WHERE ((Not (Signals.WestboundSignals)='null') AND ((Signals.Subdivision)='&quot; & [Subdivisions] & &quot;'))ORDER BY Signals.Order;&quot;
Else
WestwardSignals2.RowSource = &quot;WestwardSignalsQuery&quot;
End If

WestwardSignals2.Requery
End Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tormented]

CajunCenturion ,


Many thanks for all your help, everything is working great As you have helped with two problems I will give you two stars. Thank you again. I am quite new to Access and to this site and am glad there are individuals like yourself to help with the &quot;bumps in the road!&quot;

Tormented