Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Form: Can't format date in a Multi-column Listbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling a Multi-Column Listbox | Excel Programming | |||
Multi Column Listbox Help | Excel Programming | |||
Multi-field listbox in an Excel form | Excel Discussion (Misc queries) | |||
populating a multi-column Listbox | Excel Programming | |||
Date formatting in a multi column listbox | Excel Programming |