Access 2000: How does one use this data? 1

[ladyck3]

Let me ask this... If we have a form with a field that has a calculation in it, how can this result be reported elsewhere in the database?

If I have a calculation done using VBA in that unbound text field, now can I utilize that result to pull up information concerning that number in a corresponding table.

Let's say it turns up the number 3. This number pertains to Blue Shirts. I have a table for Blue Shirts with the numbers 1 - 9 and each record 1 - 9 has a description. So if the result on the form comes to 3, what mechanism do I need to use to have this result which we can see then pull up the description for the proper blue shirt?

Is this a report or Query, I see no way to pull this type of field into a report or a query. Basically the result will have a corresponding table to go to in order to pull up the appropriate dialog or description regarding that result.

Thanks (I've asked this before and have not yet gotten an answer which I understand, I guess) sorry if it is repeated, but I'm asking in a different fashion to see if it will click for anyone.

If this requires coding, please be explicit in what needs to be done, I am just learning.

Laurie

 

[bluedragon2]

Suggestion:

If you have the two fields related, then insert a subform into your form

 

[fneily]

Let's keep this simple since you're a neophyte in VBA. I'll assume the form that has the calculation on it is made from a single table. Go to design view of that table and add another field called Type, for example. Data type will be Number.
Now return to your form. Go to design view of your form. Click on the calculation box. Display it's property sheet (the button at the top of your screen that has a hand on it). Click the Event tab. Click the box next to AfterUpdate. Click the button with three dots (called Build Button). Select Code Builder and click OK. Type:

[Type] = [name of calculation box]

To find the name of calculation box, click on the ALL tab and it's at the top in an option called Name.

Now that result is stored in the table and you can use it to link to the table for shirts in a query to get the shirt descriptions.

Neil

 

[ladyck3]

I was so Jazzed to see you helping again Neil but have a problem. I printed what you wrote and followed exactly, but it did not work, it created a new record in my table with a result of Zero.

If you remember me, you had helped in the past along with Tony Jollans to convert a name into a number, reduced to a signle digit either 1-9 or 11 or 22.

My table has 2 columns, Name and Birthdate.

From this on the form I can either call up names already entered or enter new ones and it will calculate upon entry.

My form shows the following:

Name at birth Resulting number
Date of Birth Day of week Resulting number

So maybe I need to create 2 columns, one for each "resulting number" because this is where the VBA Code resides.

??

Thanks Neil!!!

I don't know how to create a link to the thread in this text format of email but the thread which shows what we have so far is at: thread68-569216

Laurie

 

[fneily]

My mistake. The line should read:

Me![type] = Me![name of calculation box]

 

[ladyck3]

Neil,

I did what you said in the AFTER UPDATE.. well it did not work...

However, please correct me if i'm wrong.. what I did do was this (figuring I could kill 2 birds with one stone).

The form is called RESULT
... the calculation box for the Name is called "Result"
... the calc for the birthdate is called "BirthNumber"

So, I applied the same to both...On my table, I created a column between Name and BirthDate, it is called NameRes..

In the After Update on the RESULT field for the name, I entered: Me![NameRes] = [Result]

On the after update for the BirthNumber field for the birthdate I entered: Me![Type] = [Result]

Neither, not one, the other, or both worked. Am I missing something? Wait, I have a space on either side of the = let me eliminate those....

brb

well it put the spaces in on its own accord. The only item in my table that has a digit is the one new entry I made, the form calculates ok but the table shows a zero in both of the fields added. The balance which were already in the table show nothing and the line for a new record shows zeros.

Ok, what am I doing wrong?

Thanks in advance, I appreciate you sticking with me..

Laurie

 

[fneily]

First, don't name things the same. You say you have a form called "Result" and a textbox named "Result". Change one.
Put Me![Nameres] = [Controlnameresult] on the OnCurrent event of the form and see if that works.

 

[ladyck3]

I've already got the following in OnCurrent:

If IsNull(Me![Name]) Then
Me![Result] = 0
Else
Me![Result] = NameSum(Me![Name])
End If

If IsNull(Me![BirthDATE]) Then
Me![BirthNumber] = 0
Else
Me![BirthNumber] = DateSum(Me![BirthDATE])
End If

End Sub

==================
BTW, the form is named RESULTS and the coded field for the name is called RESULT. The coded field for the date is called BirthNumber.

I put that code at the beginning before the code listed above in OnCurrent and got an error

Runtime Error 2465
Microsoft Access can't find the field "]" referred to in your expression.

I went back and edited the code it reads
Me![NameRes] = [Result] and it works on 2 of the items now, just 2 of the 6 in my table have the results appearing in the table itself. Its a start.

I need to get the same thing to happen for all records... for the BirthNumber as well.

Gonna go play... wonder how come only 2 of my 6 entries work, hmmm... but again its a start

Thanks FN....

Laurie

 

[ladyck3]

Neil...

Another Star for you... this worked, I even did the BirthNumber info in the OnCurrent (not sure if I could put 2 in there) and it worked just great!!!

My table now has 4 fields, name (result) birthdate (result) [not the official field names] but the point is, I now am closer.

Hafta start a new post for the next step.

Its shaping up.... yes indeedy dooo... it is shaping up...

I thank you so very much!
Laurie

 

[ladyck3]

Ya know, when I go back and look at my table, the first record has no data in the NameRes and Type fields, the rest of the records do but these two do not.

And the number are changing what used to be 11 on one entry is now 5, but the FORM shows teh correct 11.

The first name in my table shows the name, the birthdate but the result fields are empty/blank not even a zero. If I look at the info via the Form, the info is there.

I do not have the table and form open at the same time.

Again, what am *I* doing WRONG?

It did work!!

Laurie

 

[fneily]

Make sure that the field names NameRes and Type are in the Control Source for your calculated textboxes. Go to design view of your form, click on the textbox, bring up it's property sheet, and check Control Source. If it's blank, click in the box, click on the dropdown arrow and select the appropriate field name (NameRes or Type).

 

[ladyck3]

LOL.. oh your just ROCk! I forgot to check that.. I knew about that, even... that's how I set up the fields to pull just the &quot;Date&quot; such as the 28th (10/28/1980) <---not MY birthday.. my daughter) so I have a field on the form now that show &quot;Day/Date&quot; and Day of the week by setting the control to the BIRTHDATE field and setting the formats to dd and dddd respectively.

Please also see my new thread

thread68-578179

I forgot to put Access in the Subject ;(

Thanks FN or Neil... you have made my day!

Laurie