How Do I Transfer the Contents of an Unbound Textbox to a Field?

[JessiRight77]

Hello...

I am using an unbound textbox to calculate some numbers. But I need
to transfer these numbers to a Field so that it can be stored in a
table.

Can this be done automatically?

Thanks,
Jessi

 

[John Vinson]

Hello...

I am using an unbound textbox to calculate some numbers. But I need
to transfer these numbers to a Field so that it can be stored in a
table.

Can this be done automatically?

Yes... but it probably should not be done.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson[MVP]

 

[JessiRight77]

Thanks for your help, John.

I do appreciate and understand your wise warnings, but my friends still
insist that they need to store the number in a table's field.

Is there any way to do this with some kind of "update" feature on the
form that would re-save the new value to the field if the textbox'
value changed? If not, they still want to store the value in the
table.

Thanks,
Jessi

 

[JessiRight77]

If I can convince them NOT to store the results in the table (by using
a calculated field in a query, which is the basis of their report)...
could you please help me understand why I keep getting an "Overflow"
message when I run the query?

I created a query with the two date fields, along with a third
calculated field which I want to yield the net working days after
subtracting weekends and holidays. I get this number by using a
function. My expression for the calculated field is: NetDays:
WorkingDays2([fldDischgDate],[fldInitApptDate]).

When I run the query, however, I keep getting an "Overflow" message.
Did I do this wrong?

Thanks,
Jessi

 

[Larry Daugherty]

Pasting your complete function into your next post might help.

In the meantime, Open the code editor and set a breakpoint on the
first line of executable code in WorkingDays2(). Run the report in
question. The debugger will stop at your breakpoint. Open the
Immediate window. You can now check and even change the values of
variables of interest. Single step your way thru the code, looking
for anomalies.

By the way, you might visit www.mvps.org/access It has lots of Access
lore and guidance.

HTH
--
-Larry-
--

If I can convince them NOT to store the results in the table (by using
a calculated field in a query, which is the basis of their report)...
could you please help me understand why I keep getting an "Overflow"
message when I run the query?

I created a query with the two date fields, along with a third
calculated field which I want to yield the net working days after
subtracting weekends and holidays. I get this number by using a
function. My expression for the calculated field is: NetDays:
WorkingDays2([fldDischgDate],[fldInitApptDate]).

When I run the query, however, I keep getting an "Overflow" message.
Did I do this wrong?

Thanks,
Jessi

Thanks for your help, John.

I do appreciate and understand your wise warnings, but my friends still
insist that they need to store the number in a table's field.

Is there any way to do this with some kind of "update" feature on the
form that would re-save the new value to the field if the textbox'
value changed? If not, they still want to store the value in the
table.

Thanks,
Jessi

 

[Joseph Meehan]

Thanks for your help, John.

I do appreciate and understand your wise warnings, but my friends
still insist that they need to store the number in a table's field.

Is there any way to do this with some kind of "update" feature on the
form that would re-save the new value to the field if the textbox'
value changed? If not, they still want to store the value in the
table.

Thanks,
Jessi

Friends don't let friends violate good table design.

 

[John Vinson]

Thanks for your help, John.

I do appreciate and understand your wise warnings, but my friends still
insist that they need to store the number in a table's field.

They understand that this means that the integrity of the data is
suspect, and that the number they see on their reports could be simply
WRONG, and they still want wrong data? If they do...

Is there any way to do this with some kind of "update" feature on the
form that would re-save the new value to the field if the textbox'
value changed? If not, they still want to store the value in the
table.

Use the Form's BeforeUpdate event to copy the data from the unbound
control containing the calculated value, into a bound control (which
might or might not be visible).

Better... post the SQL and your function VBA and fix it. Overflow may
mean that your code is misinterpreting a date as a arithmetic division
operation, or some such annoyance.

John W. Vinson[MVP]

 

[JessiRight77]

If I can fix the "Overflow" problem in the query, and show them that
they will still be able to display the calculated results on a report,
then perhaps I can convince them.

They need to subtract two dates (hospital discharge and followup
appointment date) to determine the number of working days (that
excludes weekends and holidays).

I found a function by Arvin Meyer that will calculate the working days,
so I pasted it into a general module, and referenced it in a textbox
(txtCountDays) on their form by typing
"=WorkingDays2([fldDischgDate],[fldInitApptDate])" in the Control
Source. This works fine, BUT displays an #Error in those records that
do not contain BOTH dates.

They want to use this number in queries and reports for certain
records, so they are insisting that it be stored in a table.

The FUNCTION I am using is:

Public Function WorkingDays2(fldDischgDate As Date, fldInitApptDate As
Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: fldDischgDate As Date
' fldInitApptDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between them
' Note that this function has been modified to account for holidays. It
requires a
' table named T_Holiday with a field named fldDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [fldDate] FROM T_Holiday",
dbOpenSnapshot)

fldDischgDate = fldDischgDate + 1
'To count fldDischgDate as the 1st day comment out the line above

intCount = 0

Do While fldDischgDate <= fldInitApptDate

rst.FindFirst "[fldDate] = #" & fldDischgDate & "#"
If Weekday(fldDischgDate) <> vbSunday And Weekday(fldDischgDate) <>
vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

fldDischgDate = fldDischgDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function
__________________________

Per your suggestion, I tried to reference this same function in a
simple test query that contained the hospital discharge date
(fldDischgDate) and the initial followup date (fldInitApptDate), along
with an expression that referenced the function: NetDays:
WorkingDays2([fldDischgDate],[fldInitApptDate]).

When I run the query, however, I get a continuous "Overflow" message
and I suspect it is because many of their records do not contain both
dates...

The SQL code in the query is:

SELECT [HOSPITAL DISCHARGES].fldDischgDate, [HOSPITAL
DISCHARGES].fldInitApptDate,
WorkingDays2([fldDischgDate],[fldInitApptDate]) AS NetDays
FROM [HOSPITAL DISCHARGES];

Thanks for your help,
Jessi

 

[JessiRight77]

P.S. to John:

<<Use the Form's BeforeUpdate event to copy the data from the unbound
<<control containing the calculated value, into a bound control (which
<<might or might not be visible).

I also attempted your reluctant suggestion above, but wasn't sure how
to do it. I typed the following into the form's "BeforeUpdate" event,
but... it didn't work:

[fldNoDays_DischgAndAppt]=[txtCountDays]

I'm sure this wasn't right way to do it, though... What should
I have done differently?

Thanks,
Jessi

 

[John Vinson]

"=WorkingDays2([fldDischgDate],[fldInitApptDate])" in the Control
Source. This works fine, BUT displays an #Error in those records that
do not contain BOTH dates.

How many working days are there between #7/24/2006# and an unspecified
date?

Try:

= Iif(IsNull([fldDischgDate]) Or IsNull([fldInitApptDate]), Null,
WorkingDays2([fldDischgDate],[fldInitApptDate])

to return a NULL value if either date is NULL.

They want to use this number in queries and reports for certain
records, so they are insisting that it be stored in a table.

Have you explained (again) that it is possible - indeed preferable -
to calculate this field in a Query, and that that calculated field can
be used for searching, sorting, reporting, exporting, etc. WITHOUT it
being stored?

To answer your other question here: you would not put the expression
directly in the Before Update line. Instead, click the ... icon and
choose "Code Builder". Access will give you a Sub and an End Sub line
in the VBA editor; you'ld put approximately that same line between
them:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me.[fldNoDays_DischgAndAppt] = Me.[txtCountDays]
End Sub

The "Me." is shorthand for "look on this form for a control of this
name". You will need a bound control named fldNoDays_DischgAndAppt, or
(better) bind a control named (say) txtNoDays_DischgAndAppt to the
field and use that name.

John W. Vinson[MVP]

 

[JessiRight77]

Try:

= Iif(IsNull([fldDischgDate]) Or IsNull([fldInitApptDate]), Null,
WorkingDays2([fldDischgDate],[fldInitApptDate])

Cool! It worked... so I may be able to convince them to take this
route after all.

You're a good man, John. You've helped me before... please know that
it is greatly appreciated.

Thanks,
Jessi