How to Auto Fill a Text Box with "The One Above"

[doyle60]

I have an unbound form with a combo box. The combo box, based on a
table, has dates in it that are in descending order. After I choose a
value for the combo box, I want an invisible text box to be the value
of the preceding date. How do I do this in code?

So if the combo box selections are:

1/25/05
2/23/05
3/28/05
4/27/05
5/28/05
6/25/05

and the user picks 4/27/05, I want the invisible text box to be
3/28/05.

If the user picks 1/25/05, being the first on the list, I want it to
return 1/25/05.

Thanks,

Matt

 

[Tim Ferguson]

I want an invisible text box to be the value
of the preceding date. How do I do this in code?

Can't think why you want a invisible text box rather than an honest-to-
goodness variable, but still:

With MyCombo
' bug out if it's the top row
If .ListIndex < 1 Then exit sub

' get the previous value
TempDate = .ItemData(.ListIndex -1)
' and put it somewhere
MyInvisibleTextBox.Value = TempDate

End With


.... usual warnings about untested air code....

Hope it helps


Tim F

 

[doyle60]

Does the field in question on the table have to be in ascending order
for this to work? If so, would it be hard to make it so it doesn't?
I'm just worried that this could be currupted too easily---the database
is in the state of being built still. I suppose I could make a query
out of the table first.

Also, the reason I want the text box is because I then know what to do
with it. I'm not quite familiar with how to handle the varible in the
query criteria row, nor how to write it.

I will use the text box names in the query criteria, something like
this:

Between Forms!Namefrm!txtLow And Forms!Namefrm!txtHigh

If you can rewrite that, I'd do it that way. But I'm not sure if it is
too difficult or not. I have to understand in two months from now when
I try to emulate this procedure for something else.

Thanks so much,

Matt

 

[Tim Ferguson]

Does the field in question on the table have to be in ascending order
for this to work?

Don't really understand what you mean: you said you wanted the value
before the one the user chose. Oh hold on -- you want the next one _down_
not up, sorry. Try this

With MyCombo
' bug out if it's the bottom row
If .ListIndex = .ListCount -1 Then exit sub

' get the next value down
TempDate = .ItemData(.ListIndex +1)
' and put it somewhere
MyInvisibleTextBox.Value = TempDate

End With

I suppose I could make a query out of the table first.

If this means what I think you mean, then YES you should use a rowsource
like

SELECT ALL MyDate
FROM MyDatesTable
WHERE Something=TRUE
ORDER BY MyDate DESC

You cannot trust an unsorted table recordset under any circumstances.
Note that the listbox itself must be UNsorted, because in USian
03/20/2005 comes after 04/20/2004 but it would be sorted alphabetically
in front of it.

I will use the text box names in the query criteria, something like
this:

Between Forms!Namefrm!txtLow And Forms!Namefrm!txtHigh

' you need whether using text boxes or variables: Jet is NOT
' regionally-friendly
Const fmtJetDate As String = "\#yyyy\-mm\-dd\#"

' force proper date manipulations: text strings can be
' badly screwed
Dim NextDate as Date
Dim PriorDate as Date

NextDate = CDate(cboMyCombo.Value)
PriorDate = CDate(GetPriorValueFrom(cboMyCombo)) ' see above!

' You can use BETWEEN, but it may get snagged if there are
' time values embedded in the records.
'
' The rigorous way is to use inequalities, so that all
' date time values are included up to (but not including)
' midnight on the following day, vis:-
'
jetCriterion = _
"SomeDate >= " & Format(PriorDate, fmtJetDate) & _
"AND SomeDate < " & Format(NextDate+1, fmtJetDate)

' Do this until you really know that it's getting the correct values
MsgBox jetCriterion


Just like eating an elephant -- one bite at a time. If you are going to
be coming back to this code in a couple of months, make sure you leave
lots of comment lines -- at least 50% and preferably about 2:1.

Hope it helps


Tim F