Front End/Back end VBScript

[Guest]

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

 

[Guest]

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!

 

[Guest]

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