Excel Form: Can't format date in a Multi-column Listbox

S

Sue

Hi there

For some reason data that's fed into a multi-column listbox on an
Excel Form via an SQL query from Access, won't maintain it's shortdate
format of dd/mm/yyyy (we're in Aus, so our preferred format). Even
though this is the specified format within the design of the Access
table, and the code that sits behind the text boxes on the form (into
which new data is entered in the first instance - code below)..

Even weirder is that every now and then when I reopen that record in
the Excel form it does switch over to [at least] d/mm/yyyy??

Is it possible to format a column with a multi-column listbox??

Any help would be really appreciated..

Cheers in advance
Sue

____________________________________________________

Private Sub cmbok_click()

On Error GoTo ErrMsg:

Set ws = DBEngine.Workspaces(0)
Call FindDatabasePath
Set db = ws.OpenDatabase(path1)
Dim rsA2 As Recordset
Const DateFormat = "dd/mm/yyyy"

If Me.txResources = "" Or IsNull(Me.txResources) Then
MsgBox "Please type the name of the Resource in the textbox before
clicking this button, or select an existing name from the list of
Available Resources and click 'Add'.", vbOKOnly, "Add New Employee"
Exit Sub

End If

Set rsA2 = db.OpenRecordset("SELECT * From tblResource")
With rsA2
.AddNew
.Fields("ResourceName") = Me.txResources.Value
.Fields("BusinessUnit") = Me.txBusinessUnit.Value
.Fields("LineManager") = Me.txLineMgr.Value
.Fields("StartDate") = Format(Me.txStart.Value, DateFormat)
.Fields("FinishDate") = Format(Me.txEnd.Value, DateFormat)
.Fields("% Effort") = Me.txEffort.Value
.Fields("CostCentre") = Me.txCostCentre.Value
.Update
End With

Call RequeryResourcesLists

Me.txResources.Value = ""
Me.txBusinessUnit.Value = ""
Me.txLineMgr.Value = ""
Me.txStart.Value = ""
Me.txEnd.Value = ""
Me.txEffort.Value = ""
Me.txCostCentre.Value = ""

Me.lstboxAvailableResources.SetFocus

rsA2.Close
Exit Sub

ErrMsg:
MsgBox "You must complete all fields before clicking ok", vbOKOnly,
"Add New Resources"

Exit Sub

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top