IM REALLY STUCK 2

[spoon1]

Hi.. really stuck here. I have a form that has a text box named "DUT". The text box is bound to a table field also named "DUT".
***************************
The form field "DUT" has an expression which reads
=[adm_ars]-Date() where [adm_ars]is a date that is entered by the user. that date is subtracted by the present date and the answer gives me a number. That expression works fine.
***************************
Since the form field "DUT" is bound to the table field "DUT" it should write the answer to the expression in the table, but it does not.
***************************
I don't even need it to be written to a table, I just want to use this data for a report, but the report function only wants query data or table data- NOT form field data.
**************************
I was told to use code instead of an expression- do you think this is correct? I don't see why I should have to write code, because the expression works fine. If I do have to use code, where do I start? On before update, after update... on dirty, on change ... etc. I just don't know.
**************************
Thank you for any help you can give me.
Christina

 

[GingerR]

how does the user-entered date get in there? from a form text box?

say you have your form and it's called MainForm.
you have a field called datUserDate. a user types in a date.
you hit a button and it opens the report.
on the report is a text box, and in the text box is a formula that says:

=forms!MainForm!datUserDate - Date()

 

[spoon1]

Ginger, I tried what you wrote, and still it doesn't work- it doesn't take ANY text box data from the form at all. Thank you for your suggestion anyway. BTW I tried with parenthesis and without. ??????????????????????????????
I don't know what the heck is wrong!!!!!!

 

[Steve101]

Christina,

A control on a form is only bound to a form, if:
(a) the form has an associated recordsource, and
(b) the control has a ControlSource property which links back to a particular field on the form's recordsource.


Reading between the lines, I think that your form's DUT control is not bound back to the recordsource, as it has an expression associated with it (ie. [blue]=[adm_ars]-Date()[/blue]) as opposed to a fieldname associated with the recordsource.

There are several ways of getting this derived control's value back to the table. Here's one:

(a) Add another control to the form. Lets call it DUTBound. Give it a ControlSource of DUT, which links it back to the table. Set its Visible property to False, so that the control is not visible to the user.

(b) Create a BeforeUpdate event for the form, with the following code in the event:
[tt]
Me!DUTCopy = ME!DUT
[/tt]
This will force the content of the formula into the invisible control which IS bound to the appropriate field.

(c) Thereafter, new /altered records will have their DUT updated, essentially according to the formula.

Note, as already pointed out, you dont really need to hold this value in the table or derive it in the form. Its always derivable via formula in the report. the point or my post is that a control on a form is only bound to a field in the recordsource if a 'mapping' entry is made in the Controlsource property.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[GingerR]

yes--make your text box not have any control source.

 

[spoon1]

Thank you both for your help.
Me!DUTCopy = Me!DUT did the trick- I looked up the word Me! but couldn't find it.. is it reserved by access or visual basic- ? I'm goona get a handle on this stuff soon.
Thanx again!

 

[Steve101]

Correction to my earlier post:

[blue]A control on a form is only bound to a form, if: ...[/blue]

should read:

[blue]A control on a form is only bound to a table's field, if: ...[/blue]



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

Me is just a short way to reference the current form (or report) from code within the current form (or report).

For example, if you're writing code in the BeforeUpdate event of a form called frmYourForm for a control called txtControl, then you can refer to this control as:
[tt]
me!txtControl
or
Forms!frmYourForm!txtControl
or
txtControl
[/tt]
These should all work, though the third is not good practice, as it makes it hard to distinguish a control on a field from a variable which is just internal to the code.

However the better practice (and fourth alternative) is to declare a variable of type Form, and then assign it a value of the current form (ie. Me), and then use this variable for any references to forms control in the code thereafter; for example:
[tt]
Dim F as form
Set F = Me
....
F!txtControl = "The cat sat on the mat"
...
...
F!txtControl = "The dog bit the cat"
[/tt]
The benefits of this approach are:
(a) The declared form variable is short and explicit
(b) The code can easily be moved around between different forms.
(c) It can also be easily relocated out of a form to a Global module. In Global modules, 'me' is not recognised (cos the code is not in a form or report), so only a single line (the assignment line for F) needs to change.

Hope this clarifies it,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[spoon1]

Great! Thanks for the information!!