Front End/Back end VBScript

[Justin]

I was hoping someone could direct me to a site or show me some example with
VBscript to work with a front end/back end application.

On the front end I want a form for employee time entry which will list all 7
days. We'll call it 'frm_timesheet'. On the backend I'll have a table
called 'tbl_timesheet'.

I want the frm_timesheet to be able to pull a record from the tbl_timesheet
datasource, if it exists. If no record exists, then I'd have other VB
script running to set default values (clock in, clock out times, etc, for the
individual selected).

I do not want any user made entry on the frm_timesheet to be saved
automatically by Access. The only way I want information to be saved is by
hitting a 'save' button.

The way I figured I could do this is with ADO code. ADO would call up the
tbl_timesheet and fill in the days of the week for frm_timesheet if an entry
exists for that individual. Otherwise default values, through VBScript,
would populate the fields. None of this data is saved, as they are sitting
in unbound fields on the form. When the save button it hit, the data in the
unbound fields will then be saved to the tbl_timesheet through ADO.
Obviously if an entry for that day and individual exists, I'd want the code
to overwrite the old entry - not create a new entry.

I'm not very familiar with ADO code. I was hoping someone could help me
with an example. Or if you know of a better way for me to go about doing
this.

If one could show me, in detail, how to have ADO code pull a field from the
tbl_timesheet and how to save a record if it is new or overwrite if it
exists, I can take the code from there and apply it to all my fields.

Please explain your code lines so I can understand what they do.

I'd really appreciate it.

Thanks!

 

[Tim Ferguson]

I have to wonder why you are using access and then determining to
replicate what the UI will do for you. I don't recognise the need for:

The only way I want information to be saved
is by hitting a 'save' button.

Is there some specific reason for this. If it's a need for strict
validation, then that is what the Before_Update() event was invented for.
If you think that people will think before clicking the Save button, then
you are mistaken. If it's just paranoia, then think very hard before you
decide to replicate basic Access functionality. "What's the purpose of
buying a dog and then barking yourself?"

I was hoping someone could direct me to a site or show me some example
with VBscript

Point of order: VBScript is a different language from VBA and is not used
within Access. I assume that the rest of this post actually means VBA.

I do not want any user made entry on the frm_timesheet to be saved
automatically by Access. The only way I want information to be saved
is by hitting a 'save' button.

In that case you need an unbound form.

The way I figured I could do this is with ADO code.

Again, just to nitpick. I am guessing that the "back end" is a jet
database, in which case DAO will be faster and more flexible than ADO.

call up
the tbl_timesheet and fill in the days of the week for frm_timesheet
if an entry exists for that individual.

public sub form_open()
' see if there is a record
if dcount("*", "timesheet", "ID = " & "20994")>0 then

' there is a record, collect it
jetSQL = "select * from timesheet " & _
"where ID = " & "20994"
set rs = db.openrecordset(jetSQL, dbopensnapshot, dbforwardonly)

' copy the values to the controls on the form
with rs
me.txtname = !Name
me.txtLastTimeIn = !LastTimeIn ' etc
' tidy up the recordset
.close

end with

' remember what we're doing with a form-global var
g_NewRecord = True

else ' there's no recordset
me.txtName = "eric"
me.txtLastTimeIn = "07:10"
' etc

g_NewRecord = False
end if

end sub

When the
save button it hit, the data in the unbound fields will then be saved
to the tbl_timesheet through ADO. Obviously if an entry for that day
and individual exists, I'd want the code to overwrite the old entry -
not create a new entry.

private sub cmdSave_Click
if g_newrecord then
' we are inserting a new record, so make an insert command
' note how data types must have the correct delimiters
jetsql = "insert into timesheet (ID, name, lasttimein) " & _
"values (" & format(me.txtID,"000000") & ", " & _
"""" & me.txtname & """, " & _
format(cdate(me.lasttimein),"\#hh\:nn\") & ")"

' debugging
msgbox jetsql

else
' it's an old record, so we need an update
jetsql = "update timesheet set " & _
"name = """ & me.txtname & """, " & _
"lasttimein = " & format(cdate(me.lasttimein),"\#hh\:nn\") & _
"where id = " & format(me.txtID,"000000")

end if

' debugging
msgbox jetsql

' carry out the command
db.execute jetsql, dbfailonerror

end sub



But I still think it's overkill....


B Wishes


Tim F

 

[Justin]

Thanks for the info, even though you don't agree with it.

I'll consider what you said and see about using the Access UI instead of
creating my own VBA to do the same thing. This raises some new questions, if
you don't mind:

Right now, each day is its own subform. The main form has a search function
for week_id and emp_id. This way when week_Id and emp_id are entered, Access
will search the corresponding 7 tables (for each day of the week) for the
week_id and emp_id to find a match. If no match is found, the default times
will be loaded (through VBA).

Problems:
1) I'm not sure how to run a search based off of more than one field. What
would the VBA code look like to search based off of week_id and emp_id.
Also, do I have to do any coding under each subform for them to pull a
record, or will a requery of the form have Access automatically do that?

2) Following what you said, and entering in a save option under the
BeforeUpdate() event, I'd then have a problem with the end user being
prompted to save or cancel every time they exit a day (each subform). I
want the end user to be able to make an entry in all 7 subforms without being
prompted each time they exit a subform if they want to save. However, I know
Access will automatically save a record once the subform loses focus and I
believe there is no way to prevent this.

So, is it possible for me to place all 7 days on the main form and not use
subforms at all?

3) Just a quick question not entirely related...I haven't been able to
figure out how to call up a public sub in VBA, located under a different
form, and then return to the current sub and continue on.

It would sure make things much easier if I could just call up a sub rather
than having to repeat code under multiple forms. Could you show me the
proper code to call up

public sub Calendar()

which is located in the form frm_timesheet so I can use that code within any
other form from that database?

Thanks for your help!

 

[Klatuu]

Not only do I do my own barking, I pee on the carpet myself.
(The lady that brings the mail is really upset about my bitting her butt)

 

[Tim Ferguson]

1) I'm not sure how to run a search based off of more than one field.

SELECT Something
FROM Somewhere
WHERE OneField = "One thing"
AND AnotherField = "Another thing"
AND AThirdField IS NULL

What would the VBA code look like to search based off of week_id and
emp_id.

Without knowing your data structure I can't begin to answer that. Things
like

Right now, each day is its own subform.

smells like a Major Design Problem...

or will a requery of the form have Access
automatically do that?

If you are using a unbound form, then there is nothing to requery.

2) Following what you said, and entering in a save option under the
BeforeUpdate() event,

No: that is not what I said. The BeforeUpdate event only occurs on a
bound form as part of the saving process. What I said was to let Access
handle the data movements, and intercept it (via the BeforeUpdate event)
to ensure tight validation.

So, is it possible for me to place all 7 days on the main form and not
use subforms at all?

Personal grundge: I am totally allergic to subforms and never ever use
them under any circumstances. Ergo, yes, is it possible not to use them,
but I don't have a clear idea of your needs.

3) Just a quick question not entirely related...I haven't been able to
figure out how to call up a public sub in VBA, located under a
different form, and then return to the current sub and continue on.

You need to read more about scoping in VB. The reason for hiding a
subroutine on a form (special kind of Class) is to keep it away from
other bits of code. If it needs to be publicly available, it should be in
a Module. Remember to mark it Public Sub rather than Private Sub.

The exception to this is when you want to make an already-open form do
something. In this case, just marking the sub Public will make it appear
as a method of the form, and you can use it just like any other method:

' create a handle: this line will load the form if it's not
' already loaded, which may or may not be what you want
' (usually not, in my experience...)
Set frm = Forms("frm_TimeSheet")

' now call the code. The Call keyword is strictly
' unneccesary, but I think it makes things more legible
Call frm.Calendar


Hope that helps


Tim F

 

[Justin]

Personal grundge: I am totally allergic to subforms and never ever use

them under any circumstances. Ergo, yes, is it possible not to use them,
but I don't have a clear idea of your needs.

Basically,
I'm trying to create a timesheet entry form which will list all 7 days and
keep track of individuals clock in and clock out times for each fiscal week
of the year.

Lets start from scratch...forget everything I've asked about DAO and how to
have VBA pull data from a table.

Right now I have a main form with 7 subforms bound to a table for each day.
The main form is unbound because no data entry occurs in it. All data entry
occurs in the subforms.

I identify the work week with a unique key: <week#><year> For instance,
this week would have the code 352005. I had to create my own week ID because
our fiscal weeks do not follow the Access calendar year.

I originally planned to have 1 table store all timesheet data. The unique
identifier would be WeekID, EmpID and Date. However, I couldn't figure out
how to make the form know that the entry for Monday should be for Monday and
Tuesday for Tuesday, etc.

For example,

Say an entry exists for Monday, 8/29/05 and Wednesday, 8/31/05 in the
tbl_timesheet. Their unqiue identifer is 352005 (weekID) and 7 (empID).

So the two entries would look like this in the table:
352005, 7, 8/29/05
352005, 7, 8/31/05

On the form I'd have 7 entry fields for Monday through Sunday for clock in
and clock out times. When I'd select the weekID and empID I want, I couldn't
figure out how to let the form know that the entry that exists under 352005,
7, 8/29/05 should be loaded into Monday and 352005, 7, 8/31/05 should be
loaded into Wednesday, while all other days would default to the value of the
employee's scheduled clock in and clock out times, since no entry exists for
them.

I figured the way i could make Access recognize the different entries by day
was to create a seperate table for each day. This way I would create a
subform for each day. For the subform Monday, if the entry 352005, 7
existed, it would load it. The third identifier would not be required, as
the tables act as that third identifier.

But I'm still stuck because I don't know how to make the main form run a
search based off of the weekID and empID, the same boat I'm in as before.

Currently only know how to run a search on a form like this:

Private Sub Combo2_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Material] = '" & Me![Combo2] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This example will find the entry for the search term I enter in the combo
box and will find any corresponding data to that search term under any of my
subforms. I don't know how to create a multi search.

I believe this multi search issue is the base of my problem no matter what
angle I try this from. If you could help me figure this out, I think I could
figure it out from there.

I'm unfamiliar with the code you showed me:

SELECT Something
FROM Somewhere
WHERE OneField = "One thing"
AND AnotherField = "Another thing"
AND AThirdField IS NULL

Is that code usable in my situation? It looks like a SQL statement. Is
that usable in a form under the afterupdate event of a control? For
instance, I would want to select the weekid (which Access selects the current
fiscal week by default) and then select the emp ID, at which time the form
would then try and search for any existing entries under that WeekID and
EmpID for Monday-Sunday and fill in the appropriate day.


I'm probably going about this all wrong, and I'm willing to start from
scratch if you have an idea on how to better structure my database.

Thanks for all your help!

 

[Tim Ferguson]

Lets start from scratch...forget everything I've asked about DAO and
how to have VBA pull data from a table.

Right now I have a main form with 7 subforms bound to a table for each
day. The main form is unbound because no data entry occurs in it.
All data entry occurs in the subforms.

Ummm... if we are really going to start from scratch, we need to get the
schema design right first: that's the tables and relationships.

It seems to me you have two entities to control: there are People (you
have not mentioned them much, but I assume they are there) and there are
TimeSlots. Every TimeSlot belongs to exactly one Person, but each Person
can have zero-or-many TimeSlots. This is a very simple one-to-many
relationship.

You could have a structure something like:

People(*EmpNo, FName, LName, etc etc)

TimeSlots (
EmpNo Long Integer // assuming that People.EmpNo is a autonumber
// or a Long Integer itself
WorkDate DateTime // don't call it Date - it's a reserved word
ValidationRule "WorkDate = CLng(WorkDate)"
// forces it to be a date rather than a time
TimeIn DateTime
ValidationRule "TimeIn < 1.0" // forces it to be a time
TimeOut DateTime
ValidationRule "TimeOut < 1.0" // ditto. Assume you can't
// work past midnight?

Primary Key = (EmpNo, WorkDate)
Foreign Key = EmpNo references People(EmpNo)
)


You need to make sure you understand this and how to make it work. You
can get the times for a particular employee by SELECTing WHERE EmptNo=
1023 and so on. You can get the times for employees for a particular day
WHERE WorkDate=#2005-07-21#". You can check for Mondays using a criterion
like Weekday(WorkDate)=3 and so on. You can look at average times of
arrival, total hours worked and so on much, much easier in a single
table.

Your current structure <<a table for each day>> is characterised by
"storing information in table names" and is a Bad Idea and will create a
great deal of extra work later on.

Managing the user interface is a separate challenge, and comes _after_
the design. If you really want to use seven separate lists, you can quite
easily use subforms or list boxes, querying something like

SELECT ALL WorkDate, TimeIn, TimeOut
FROM TimeSlots
WHERE EmpNo = 1037
AND WeekDay(WorkDate) = 3

which will get you all the Wednesdays. Etc. Alternatively, you could also
use unbound textboxes and get the appropriate values yourself. The
"best" solution depends on how your users will interact with it and what
information they have on hand or what they need to see.

Hope that makes some sense


Tim F

 

[Justin]

I've figured out how to place all time entries in the same table. The issue
I'm having is getting the form to list the entries for a week based off of
the employee and day of the week. I require the weekID for reports. This
way, with the entry of one code (the weekID), I can easily group the 7 dates
that belong to that weekID, rather than individually having to identify all 7
days for that week.

So my search criteria would be weekID, empID, and clockindate.

In the form, I'll have a drop-down combo box to select the weekID. Then a
drop-down combo box for the empID (based off emp name). After the empID
combo box is filled in, I want the form to search for all week day entries
for that work week.

Now, under the weekID table, I have an entry called 'StartDate'. This start
date is 'Monday' for every weekID. For the day of Monday, I need the form to
search for an entry based off of weekID and empID where startdate =
clockindate (since it is Monday).

For Tuesday, it would search for an entry with the criteria of the weekID,
empID, and startdate +1 (dateadd function) = clockindate

Wednesday would be startdate +2 = clockindate, etc, etc...

Please be specific on how and where I would need to place this VBA code to
make it work? If I can get over this hump, it's smooth sailing from there

Recap:
I'm trying to create an updatable form that will have a data entry for
Monday-Sunday. When a weekID and empID are selected, I want the form to
search the tbl_timesheet and try to find an existing entry for each day of
the week for that week of that employee.

I realize I may not be going about this the right way...but it's in a way I
can understand it. Please show me the VBA code required and where to place
it in order to get the results I seek.

Thanks so much for your patience and your time.

 

[Tim Ferguson]

I require the weekID for reports.

You don't need a field with the weekid: it's a function of the date. You
can calculate it in the query that you base the reports on. What are you
going to do with a date of May 27th and a week number 4?

individually having to identify all 7 days for that week.

You don't individually identify anything. You can query on

WHERE DatePart("ww", ClockInDate) = 21

for example

So my search criteria would be weekID, empID, and clockindate.

No: it would be EmpID and DatePart(etc)

In the form, I'll have a drop-down combo box to select the weekID.

Why not a calendar?

Then a drop-down combo box for the empID (based off emp name).
Okay.

After the empID combo box is filled in, I want the form to search
for all week day entries for that work week.

Okay: but all this stuff about WeekStart is unneccesary and likely to
cause bugs. Let Access do the work for you: that's what it is there for.

Here are some queries to collect the correct records:

PARAMETERS Forms!TimeClockForm!cboEmpNumber NUMERIC,
Forms!TimeClockForm!txtWeekNum DATETIME;
SELECT ClockInDate,
WEEKDAYNAME(WEEKDAY(ClockInDate)) AS ClockInDay,
StartTime
FROM tbl_TimeSheet
WHERE empID = Forms!TimeClockForm!cboEmpNumber
AND DatePart("ww", ClockInDate) = Forms!TimeClockForm!txtWeekNum
ORDER BY ClockInDate ASC;

or you could change it to (note: the parameter is different!)

WHERE empID = Forms!TimeClockForm!cboEmpNumber
AND Forms!TimeClockForm!txtStartDate <= ClockInDate
AND ClockInDate < Forms!TimeClockForm!txtStartDate + 7

As for the form, you could do something sneaky like having the UNBOUND
textboxes called txtClockInMonday, txtClockInTuesday etc etc. You could
have a command button called Look Up, and a click event like this:

private sub cmdLookUp_Click()
dim rst as DAO.Recordset
' remember to check for garbage input first
If IsRubbishEmpIDOrWeekNum Then Exit Sub

' get the query: this is the querydef on the query we
' made up earlier
Set rst = QueryDefs("GetClockInValues").OpenRecordset( _
dbOpenSnapshot, dbForwardOnly")

Do While Not rst.EOF
' cunning way to fill the text boxes
' assume they have been cleared out first
Me.Controls("txtClockIn" & rst!ClockInDay).Value = _
rst!StartTime

' get the next record
rst.MoveNext
Loop

' clear up
rst.Close
end sub


Saving them works the other way round: just use the values and the names
of the text boxes to create a set of INSERT or UPDATE commands (or open a
recordset like that one and push them straight into the records). The
biggest problem that I can see is knowing when there is a record there
(UPDATE) and when it's a new value (INSERT or .AddNew). But that must be
part of your Overall Design Plan...


Does this make sense so far?

Tim F