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
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