relay back to table

  • Thread starter Thread starter bob gee
  • Start date Start date
B

bob gee

I have a small problem transfering the result of a code
formula from the form (named form1)to the table (named
table1) that supports the form. First I would input the
number, then the field named output contains a
ControlSource entry that states "=ConvertFraction
([input])" NOTE: The quotes are to quote the expression
and are not actually entered. ConvertFractions is the
code that performs the formula and input is the name of
the input field. It reads a text string and outputs a
number.

This relays the input to the table field named,
appropriately, input. That part works, but it is not
relaying the output to the output field. It doesn't
matter if I must create another field, but both input and
output must be relayed to the table and shown on the form.

Is this enough info to help anyone help me? I am not that
good at codes, expressions or knowing which control
executes what. The info for what the code is was gotten
from http://tinyurl.com/29nz5. It looks like it came from
a posting at the microsoft newsgroups.

thanks in advance. the functions that this code executes
will save me gobbs and gobbs of time. Right now it's just
a nifty trick.
 
Bob

A review of the tablesdbdesign 'group will reveal a strong consensus against
storing calculated values in a table. If you have a formula/function that
generates the value, why not use that in a query? Beside the problem with
all the additional coding you'll have to do to keep stored calculated values
synchronized with all their pieces, retrieving a value from the hard drive
is considerably slower than calculating it in memory.
 
I'm not quite sure if i understand exactly what you're
saying. but will excell be able to read the text string
(the fraction) as a number for comaprative analysis? All
measurements that I will record will be decimal, but many
of our schematics have either fracional or decimal. I
must look up each fraction on a chart before I enter some
numbers. I gets a little slow sometimes.

It is far beyond my abilities to be making code to do
this work-a-round. I have some people who pull info off
my Access database for their own reasons. Since Microsoft
forgot to put Access with their Office suite, our company
is being chintsy with Licences for Access. So some people
use Excell for SPA porpouses. Our company lacks nerds
that can program in VB, VBA or such.
 
I have a small problem transfering the result of a code
formula from the form (named form1)to the table (named
table1) that supports the form. First I would input the
number, then the field named output contains a
ControlSource entry that states "=ConvertFraction
([input])" NOTE: The quotes are to quote the expression
and are not actually entered. ConvertFractions is the
code that performs the formula and input is the name of
the input field. It reads a text string and outputs a
number.

This relays the input to the table field named,
appropriately, input. That part works, but it is not
relaying the output to the output field. It doesn't
matter if I must create another field, but both input and
output must be relayed to the table and shown on the form.
Click to expand...

Looking at the rest of the thread, I think this is one of the cases
where you do indeed want to "store derived data" - you're actually
storing the REAL data (.375) instead of the user-friendly 3/8, for
example.

The way I'd suggest doing it is to have two textboxes on your form:
one, unbound (blank Control Source), enabled, in the tab order so the
user can enter fractions into it (let's call it txtFrac); the other
would actually be bound to the numeric table field Output. It might be
invisible if the user will always enter a fraction, or you might allow
it to be visible and enabled so the user can choose to type the
decimal value into it directly. Let's call it txtReal.

Open the Form in design view and select its BeforeUpdate event from
the Events tab of the form properties. Click the ... icon and invoke
the Code Builder. Access will give you Sub and End Sub lines: between
them put just one line -

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtReal = ConvertFraction(Me!txtFrac)
End Sub


If you want to be more thorough, you'll want to check for NULL
entries, trap errors and so on - but this should get you started!
 
John Vinson said:
hey! I'm a nerd who can program in VBA! (and I'm proud of it!) <g>
Click to expand...


I thought his usage sounded a bit perjorative. Long live nerds! Down with
non-nerds!!

Cheers,
TC
 
I feel so stoopid. I entered the folowing code into a
module:

Function ConvertFraction(strGetNumber As String) As Double
' Will convert a fraction, such as 12 3/4 to
' it's decimal equivalent, 12.75
Dim dblFraction As Double
Dim intPosition As Integer
Dim strTop As String
Dim strBottom As String
Dim dblWhole As Double
Dim strFraction As String

intPosition = InStr(strGetNumber, "/")
If intPosition = 0 Then
ConvertFraction = strGetNumber ' It's a whole
number
Exit Function
End If

intPosition = InStr(strGetNumber, " ")
If intPosition > 0 Then
dblWhole = Val(Left(strGetNumber, intPosition - 1))
Else
dblWhole = 0
End If

strFraction = Mid(strGetNumber, intPosition + 1)
intPosition = InStr(strFraction, "/")
strTop = Left(strFraction, intPosition - 1)
strBottom = Mid(strFraction, intPosition + 1)
dblFraction = Val(strTop) / Val(strBottom)
ConvertFraction = dblWhole + dblFraction

Exit_Function:
Exit Function

End Function

_____
And made the unbound text box for input "txtFrac" and
the "txtReal" and bound txtReal to the table's output
field. In the txtReal I entered, in the BeforeUpdate I
did the ... Code Builder and did the "Me!txtReal =
ConvertFraction(Me!txtFrac)" entry.

And, Nothing. the txtReal field was blank.

I also tried entering in txtReal's control source:
=ConvertFraction([input])

Is there something else I'm missing? I even did it in a
clean Access database.
 
What you have should work.

However, I would suggest you place the code in the after update event..and
NOT the before update event.

So, the code via the code builder in the after update event should look like

Private Sub txtFrac_AfterUpdate()

me!txtReal = convertFraction(me!textFrac)

End Sub

Now the above should work. Note that the after update event only occurs WHEN
YOU modify the txtFrac field. That means if you move to another record..you
will have to clear out the txFrac field before you enter another value (but,
lets get one thing working at a time).
 
Back
Top