Concept only required

[Bob Phillips]

Yes, VBA always works in US dates. What I tend to do is to declare a date variable and load that.

[mdmackillop]

Hi Ted,
I didn't delete your open form code, I just added a button for use during the design process.
With regard to date; do you need the actual day date? It occurs to me that appraisals might be over a couple of days, and will only take place periodically, so Month/Year might be sufficient.
Before you progress too much further with detailed coding, you need to decide about writing/reading data from the spreadsheet, how you want it to appear, and how this is to be stored for successive evaluations. How does it cater for new employees etc.

[Aussiebear]

Hmmm..... yet another wall to bust through.


I had intended for the data to be stored in a flat file format as laid out on Sheet 2. Button on Form for "new" employees data to be entered.

Do I take it that because you have asked this question that you forsee some problems? I haven't thought to much about this side of it because it re- inforces the point that this is becoming very quickly an "Access" project.

[Aussiebear]

Hi Ted,
With regard to date; do you need the actual day date? It occurs to me that appraisals might be over a couple of days, and will only take place periodically, so Month/Year might be sufficient.

From what I've seen of the appraisals being conducted at work, they simply get a couple of the mid level management together and nut it out over a couple of hours. It often leads to the conclusion that if you happen to PO someone because of some incidental issue, you could go into an evaluation with several knives in your back. What you might call "Shot Duck Syndrome".

[mdmackillop]

Do I take it that because you have asked this question that you forsee some problems? I haven't thought to much about this side of it because it re- inforces the point that this is becoming very quickly an "Access" project.

Hi Ted,
No serious problems. I was thinking more about possible layouts subject to the form use. If you are only writing the data to store it, it might all be done in single rows. For presentation, printing, another format may be more suitable. I was really making the point that you should be clear about your needs before tackling the next stage.
Re Access, it could be done there, but a bit of a sledgehammer approach for this amount of data.

[Bob Phillips]

I haven't thought to much about this side of it because it re- inforces the point that this is becoming very quickly an "Access" project.

Ted,

for what it is worth, IMO the only reason for an Access project over an Exceproject is because of a personal preference for Access (the obverse could be argued for Excel although I would argue not ).

This is using the application as the platform, which is not the same as the datastore. Here it makes little difference in concepts IMO, just depending upon volumes (and here I would prefer SQL Server over Access any day). It isn't that diificult to develop within Excel, and if well written, transfer of the data to Access or SQL server later is not that onerous.

[Aussiebear]

The company would in my opinion, simply write their own version in Progress anyway. So all of this is just a learning tool for myself (and anybody else who might be trying to follow the build).

Can't say that I've actually had any experience with SQL Server, although I've probably used things with it and simply not recognised the process as such.


So getting back to the matters at hand.... Date formatting is simply a case of Format (DDate, "ddmmyy")?

[Bob Phillips]

Of that ilk, yes.

[Aussiebear]

There's an old adage that people use when building a homebuilt aircraft. "90% built with 90% to go", and it seems it can easily apply to coding as well.

I've added two msgboxes, one to indicate if an employee has not been selected, and the second when there are currently no points to allocate.

I have failed to correctly format the date in the txtReviewdate textbox, and am unable to get the data in the form to write to the sheet. It clears the form correctly but doesn't write the data to any sheet let alone the "Employee Data" Sheet.

I think I shall leave the Calendar visible on the Employee details page as this doesn't overcrowd the page.

Could someone have a look please so I can wrap this thread up.

Desperate Aussiebear ( and Frustrated)

[mdmackillop]

Part 1:
You forgot to set Tgt to anything.
[vba]Application.ScreenUpdating = False
On Error Resume Next
'ActiveSheet.Sheet2.Select
LRow = Sheets("Employee Data").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Tgt = Sheets(2).Cells(LRow, 1)
' Write data to sheet 2
[/vba] When it comes to clearing the form, your code works, but you must really like typing! Look at the PageTotal function for a methodology you might use for that.

[mdmackillop]

Part 2:
[VBA]Private Sub Calendar1_Click()
Me.txtReviewDate = Format(Calendar1.Value, "dd/mm/yy")
End Sub
[/VBA]

and

[VBA]Tgt.Offset(, 1) = DateValue(Me.txtReviewDate.Text)
[/VBA]

[mdmackillop]

[VBA] 'Clears Data from the Multipage control form once written to the sheet
Dim tb As Control
For Each tb In Me.Controls
If tb.Type = "MSForms.TextBox.1" Then
tb.Text = ""
End If
Next
[/VBA]

[Aussiebear]

Will do..as soon as I finish standing in the corner.

As regards the methodology for writing data. You wrote the following

[VBA]' Get Total for a referred Page
Function PageTotal(Pg As Control)
Dim Crlt As Control, Tot As Long
'Look at each control on Pg.Contol
For Each Control in Pg.Control
'If Control Name Starts with txt then add it to the Total
If Left(Crlt.Name, 3) = "Txt" Then
'If Blank, Use 0 As value( prevents error)
If Ctrl = "" Then
Tot = Tot + 0
Else
Tot = Tot + Clng(Ctrl)
End If
End If
Next
'Pass value to the Function
PageTotal = Tot
End Function
[/VBA]

I'm therefore assuming I can write a function called
[VBA]Function WriteClearData (Pg As Control)[/VBA]

I need only the value of the cbo and txtreviewDate values from Multipage.page0 and then all the values of the controls which start with txt on all other pages. So i have a multiple select condition.

[VBA]'If PgNo.Value = "0" and left(Ctrl.Name , 3) = "cbo" then
LRow = Sheets("Employee Data").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Tgt = Sheets(2).Cells(LRow, 1)
Tgt.Offset(, 0) = cboEmployee.text
[/VBA]

Then I have to go back and find the txtReviewDate.Value and prepare to add it too.

If Left(Ctrl.Name, 13) = "TxtReviewDate" Then... and so on

By the time I've done all this, I'm going to finish up with as much typing as I have with the origonal mess, won't I?

[Aussiebear]

Sorry, was away creating mass confusion and didn't see your post.

[mdmackillop]

Just for the future, if you store your textbox names in an array, it makes them more amenable to manipulation. You can write the data to the spreadsheet and also get data from the spreadsheet with a simple loop.
[VBA]Dim arr(5)
arr(0) = "cboEmployee"
arr(1) = "txtReviewDate"
arr(2) = "txtEthics"
arr(3) = "txtReliability"
arr(4) = "txtGenComms"
arr(5) = "txtGenTeamWork"

Tgt.Offset(, 0) = Me.Controls(arr(0)).Text
Tgt.Offset(, 1) = DateValue(Me.Controls(arr(1)).Text)
For i = 2 To 5
Tgt.Offset(, i) = Me.Controls(arr(i)).Text
Next
[/VBA]

or
[VBA] For i = 0 To 5
Me.Controls(arr(i)).Text = Tgt.Offset(, i)
Next
[/VBA]

[Aussiebear]

MD, you've got me running back and forth, I don't know whether I'm Arthur or Martha..

I'll fix each component when i get back. Off fishing with youngest daughter and she's eventually arisen from her room. She's going to be a handful tomorrow when we need to be on the water by 5am.

Thanks for your help.

[mdmackillop]

Hi Ted, I wouldn't rewrite what you have that's working, just a pointer for the future.

[mdmackillop]

Off fishing with youngest daughter

That's a much better thing to do than mess around with this VBA stuff. Hope you catch a big one!

[mdmackillop]

So how did the fishing go?

Here's a possible spreadsheet layout for storage of data which can be written to and retrieved by the userform based on name and date. It also demonstrates changes in scores or whatever in a potentially useful display.
Let us know how you get on coding for this. Happy to assist as required.
Regards
Malcolm

[Aussiebear]

On the water at the first crack of light, lines in, full of expectation, ...

Things looked good, fresh bait, new tackle, incoming tide, others yet to turn up


and we waited......



Shifted location after an hour......



Still nothing.....



Shifted again....... And again..... and again....



Eventually went back to the main bridge and fished under there to get onto some yellow tailed Dart.