Textbox, Date Function

[Asif]

I have a textbox (txt_StartDate) and I have a listbox (lst_CableEnds).
The listbox picks up data from a query (qry_Cables) and the contents
of that listbox are Bin Number, Date and Quantity. Just to make
something clear txt_StartDate and Date are two different fields with
different information. txt_StartDate is the date when the bin was
started and Date is the individual dates when something is emptied
into a bin. What I want to do is when I go to a new record the
contents of the listbox are cleared but I want the value in
txt_StartDate to change so that it includes the last date in the
listbox before it was cleared i.e. if the last entry in the listbox is
23/04/07 and I go to a new record then txt_StartDate should show
23/04/07.

Any suggestions??

 

[GeoffG]

Asif:

You need to write code for the Form's On Current event.

In fact, it would be a bad idea to put the date from the ListBox into the
"txt_StartDate" Textbox. If you do, you will dirty the new record (ie you
will have begun to edit it) and Access will then try to save the record when
you move to another record. The user may prefer to abandon the new record
by simply clicking the Previous Record button. Therefore, it would be better
to put the ListBox date into the the DefaultValue property of the TextBox -
then the user can easily abandon the record.

You will need to set the Form's On Current property to [Event Procedure] and
use code similar to the following.

Regards
Geoff


Option Compare Database
Option Explicit

Private mdatStored As Date

Private Sub Form_Current()

' Assume the *THIRD* column of the ListBox
' is the Date column. One is subtracted
' because columns are counted from zero:
Const LSTBOX_COLNO As Integer = 2

Dim lngListCount As Long
Dim lngLastRow As Long

' See if we're at a new record:
If Me.NewRecord Then
GoTo AtNewRecord
Else
GoTo NotAtNewRecord
End If

Bye:

Exit Sub

AtNewRecord:

' Clear the ListBox:
Me.lst_CableEnds.RowSource = ""

' If the form has just been opened,
' the stored date will be zero.

' If a date has been stored, then put it in
' the TextBox's DEFAULTVALUE property, so the
' new record can be abandoned (not saved):
If mdatStored > 0 Then
Me.txt_StartDate.DefaultValue = "#" & mdatStored & "#"
Debug.Print Me.txt_StartDate.DefaultValue
End If

' Finish:
GoTo Bye

NotAtNewRecord:

' Populate the ListBox:
Me.lst_CableEnds.RowSource = "qry_Cables"
Me.lst_CableEnds.Requery

' Get ListBox row count:
lngListCount = Me.lst_CableEnds.ListCount

' Get last row number (rows are counted
' from zero):
lngLastRow = lngListCount - 1

' Store date from appropriate column of
' last row of ListBox:
mdatStored = _
Me.lst_CableEnds.Column(LSTBOX_COLNO, lngLastRow)

' Finish:
GoTo Bye

End Sub