data entry form problem

[simon]

I created a form for data entry based on a table. One of the fields on the
form generates data though dlookup from another table. On teh form all looks
OK, but that data does not appear in the field on the table. Actually same
happens with data field on teh form. I used Date() so that on teh fporm the
date is genarated automatically, but date does not appear on teh table.
What am I doing wrong and how can I fix it?
thanks

 

[Al Campagna]

simon,
Say that your using the DLookup for a table field named [SomeValue]

It sounds like you're creating calculated fields, where the Control
Source
of the control is not bound to any table field. Example ControlSource...
= Dlookup(some field, some table, some criteria)

That may "display" the correct value... but Access has no way of knowing
what table field that value was intended for. You need to "bind" the
control
to a table field. The ControlSource should be...
[SomeValue]

Now, on some event in the subform, by Macro or Event Procedure you would
code to update the value of the "bound" [SomeValue] control on the form.
ex.
Me.SomeValue = DLookup(some field, some table, some criteria)
------------
The same applies to your Date field (ex. [DOC] (Date of Creation))
ControlSource = Date()
That "displays" the correct date, but does nothing to update [DOC] in
your
table.

In this case, binding the field... to ControlSource...
[DOC]
and setting the DefaultValue of [DOC] to...
=Date()
will update [DOC] with the current Date whenever a new subform record is
added.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Simon]

Hi there..

thanks for the reply.. I actually solved the issue with the date by, as you
said, putting =Date() into the table field as default. But the same does not
work for

=DLookUp("Provider","ORP Matrix","[no] =" & Forms![Referrals ORP]!ID)

as I said, on the form, this is in control source, but cannot put it into
table as default. the issue might be that even on the form this comes up
with error as at the beginning until an autonumber is allocated for ID. Once
any field is entered ID is allcoated and dlookup works and puts the data I
want. But on the table I guess this does not work. I need a way to update the
table field after the data entry. Is that possible or should I do something
so that let's say every night the data is updated? How can I do that?

thanks for your help

Simon

simon,
Say that your using the DLookup for a table field named [SomeValue]

It sounds like you're creating calculated fields, where the Control
Source
of the control is not bound to any table field. Example ControlSource...
= Dlookup(some field, some table, some criteria)

That may "display" the correct value... but Access has no way of knowing
what table field that value was intended for. You need to "bind" the
control
to a table field. The ControlSource should be...
[SomeValue]

Now, on some event in the subform, by Macro or Event Procedure you would
code to update the value of the "bound" [SomeValue] control on the form.
ex.
Me.SomeValue = DLookup(some field, some table, some criteria)
------------
The same applies to your Date field (ex. [DOC] (Date of Creation))
ControlSource = Date()
That "displays" the correct date, but does nothing to update [DOC] in
your
table.

In this case, binding the field... to ControlSource...
[DOC]
and setting the DefaultValue of [DOC] to...
=Date()
will update [DOC] with the current Date whenever a new subform record is
added.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I created a form for data entry based on a table. One of the fields on the
form generates data though dlookup from another table. On teh form all
looks
OK, but that data does not appear in the field on the table. Actually same
happens with data field on teh form. I used Date() so that on teh fporm
the
date is genarated automatically, but date does not appear on teh table.
What am I doing wrong and how can I fix it?
thanks

 

[Al Campagna]

Simon
The DLookup (as a DefaultValue) won't work because when the
blank new form comes up, the Dlookup is missing the value arguments
it needs to function.
A Text Control's Default Values are created when the blank New
record is opened.

Now before we can go any further, I have to understand how your
DLookup can work at all... given the setup you've described.That's because a Refresh probably occured against the form, the ID
got it's autonumber value, and the DLookup was now able to complete
it's function.

OK so far... *BUT*...
If... [ID] is an autonumber bound text control on the form... then, by
Access built-in default, each new record causes that [ID] value to
increment by +1. (ex. 596, 597, 598, etc...)
How can you then use that [ID] value to DLookup a "Provider" in another
table?
Do you have a Provider in table "Orp Matrix" with a [NO] value of 596?
597?
598?

I'm sure we can get this, but please describe/clarify further...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hi there..

thanks for the reply.. I actually solved the issue with the date by, as
you
said, putting =Date() into the table field as default. But the same does
not
work for

=DLookUp("Provider","ORP Matrix","[no] =" & Forms![Referrals ORP]!ID)

as I said, on the form, this is in control source, but cannot put it into
table as default. the issue might be that even on the form this comes up
with error as at the beginning until an autonumber is allocated for ID.
Once
any field is entered ID is allcoated and dlookup works and puts the data I
want. But on the table I guess this does not work. I need a way to update
the
table field after the data entry. Is that possible or should I do
something
so that let's say every night the data is updated? How can I do that?

thanks for your help

Simon

simon,
Say that your using the DLookup for a table field named [SomeValue]

It sounds like you're creating calculated fields, where the Control
Source
of the control is not bound to any table field. Example ControlSource...
= Dlookup(some field, some table, some criteria)

That may "display" the correct value... but Access has no way of
knowing
what table field that value was intended for. You need to "bind" the
control
to a table field. The ControlSource should be...
[SomeValue]

Now, on some event in the subform, by Macro or Event Procedure you
would
code to update the value of the "bound" [SomeValue] control on the form.
ex.
Me.SomeValue = DLookup(some field, some table, some criteria)
------------
The same applies to your Date field (ex. [DOC] (Date of Creation))
ControlSource = Date()
That "displays" the correct date, but does nothing to update [DOC] in
your
table.

In this case, binding the field... to ControlSource...
[DOC]
and setting the DefaultValue of [DOC] to...
=Date()
will update [DOC] with the current Date whenever a new subform record is
added.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I created a form for data entry based on a table. One of the fields on
the
form generates data though dlookup from another table. On teh form all
looks
OK, but that data does not appear in the field on the table. Actually
same
happens with data field on teh form. I used Date() so that on teh fporm
the
date is genarated automatically, but date does not appear on teh table.
What am I doing wrong and how can I fix it?
thanks

 

[Simon]

Yes, the other table covers numbers from 1 to 700..so, it works...

Simon
The DLookup (as a DefaultValue) won't work because when the
blank new form comes up, the Dlookup is missing the value arguments
it needs to function.
A Text Control's Default Values are created when the blank New
record is opened.

Now before we can go any further, I have to understand how your
DLookup can work at all... given the setup you've described.That's because a Refresh probably occured against the form, the ID
got it's autonumber value, and the DLookup was now able to complete
it's function.

OK so far... *BUT*...
If... [ID] is an autonumber bound text control on the form... then, by
Access built-in default, each new record causes that [ID] value to
increment by +1. (ex. 596, 597, 598, etc...)
How can you then use that [ID] value to DLookup a "Provider" in another
table?
Do you have a Provider in table "Orp Matrix" with a [NO] value of 596?
597?
598?

I'm sure we can get this, but please describe/clarify further...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hi there..

thanks for the reply.. I actually solved the issue with the date by, as
you
said, putting =Date() into the table field as default. But the same does
not
work for

=DLookUp("Provider","ORP Matrix","[no] =" & Forms![Referrals ORP]!ID)

as I said, on the form, this is in control source, but cannot put it into
table as default. the issue might be that even on the form this comes up
with error as at the beginning until an autonumber is allocated for ID.
Once
any field is entered ID is allcoated and dlookup works and puts the data I
want. But on the table I guess this does not work. I need a way to update
the
table field after the data entry. Is that possible or should I do
something
so that let's say every night the data is updated? How can I do that?

thanks for your help

Simon

simon,
Say that your using the DLookup for a table field named [SomeValue]

It sounds like you're creating calculated fields, where the Control
Source
of the control is not bound to any table field. Example ControlSource...
= Dlookup(some field, some table, some criteria)

That may "display" the correct value... but Access has no way of
knowing
what table field that value was intended for. You need to "bind" the
control
to a table field. The ControlSource should be...
[SomeValue]

Now, on some event in the subform, by Macro or Event Procedure you
would
code to update the value of the "bound" [SomeValue] control on the form.
ex.
Me.SomeValue = DLookup(some field, some table, some criteria)
------------
The same applies to your Date field (ex. [DOC] (Date of Creation))
ControlSource = Date()
That "displays" the correct date, but does nothing to update [DOC] in
your
table.

In this case, binding the field... to ControlSource...
[DOC]
and setting the DefaultValue of [DOC] to...
=Date()
will update [DOC] with the current Date whenever a new subform record is
added.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I created a form for data entry based on a table. One of the fields on
the
form generates data though dlookup from another table. On teh form all
looks
OK, but that data does not appear in the field on the table. Actually
same
happens with data field on teh form. I used Date() so that on teh fporm
the
date is genarated automatically, but date does not appear on teh table.
What am I doing wrong and how can I fix it?
thanks

 

[Al Campagna]

OK... I'm still not sure why you're doing that... but onward
and upward....

Let's say the form control your using a Dlookup for is [Provider]...

Instead of having a DLookup as your Provider ControlSource,
you should have the Provider field from your table entered there.
Upon some form event (which I can't determine from your description),
you need to programmatically update the Provider field with the DLookup
function.
That event should trigger a Refresh... to make sure the form's [ID]
value has been established.
Let's say you have a control on the form named [XYZ], and after you
enter a value for that field, you would like to also update Provider.
Use the XYZ AfterUpdate event to...

Private Sub XYZ_AfterUpDate()
Refresh
Me.Provider = DLookUp("Provider","ORP Matrix","[no] =" &
Forms![Referrals ORP]!ID)
End Sub
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Yes, the other table covers numbers from 1 to 700..so, it works...

Simon
The DLookup (as a DefaultValue) won't work because when the
blank new form comes up, the Dlookup is missing the value arguments
it needs to function.
A Text Control's Default Values are created when the blank New
record is opened.

Now before we can go any further, I have to understand how your
DLookup can work at all... given the setup you've described.

...comes up with error as at the beginning until an
autonumber is allocated for ID...

That's because a Refresh probably occured against the form, the ID
got it's autonumber value, and the DLookup was now able to complete
it's function.

OK so far... *BUT*...
If... [ID] is an autonumber bound text control on the form... then,
by
Access built-in default, each new record causes that [ID] value to
increment by +1. (ex. 596, 597, 598, etc...)
How can you then use that [ID] value to DLookup a "Provider" in
another
table?
Do you have a Provider in table "Orp Matrix" with a [NO] value of
596?
597?
598?

I'm sure we can get this, but please describe/clarify further...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hi there..

thanks for the reply.. I actually solved the issue with the date by, as
you
said, putting =Date() into the table field as default. But the same
does
not
work for

=DLookUp("Provider","ORP Matrix","[no] =" & Forms![Referrals ORP]!ID)

as I said, on the form, this is in control source, but cannot put it
into
table as default. the issue might be that even on the form this comes
up
with error as at the beginning until an autonumber is allocated for ID.
Once
any field is entered ID is allcoated and dlookup works and puts the
data I
want. But on the table I guess this does not work. I need a way to
update
the
table field after the data entry. Is that possible or should I do
something
so that let's say every night the data is updated? How can I do that?

thanks for your help

Simon

:

simon,
Say that your using the DLookup for a table field named
[SomeValue]

It sounds like you're creating calculated fields, where the
Control
Source
of the control is not bound to any table field. Example
ControlSource...
= Dlookup(some field, some table, some criteria)

That may "display" the correct value... but Access has no way of
knowing
what table field that value was intended for. You need to "bind" the
control
to a table field. The ControlSource should be...
[SomeValue]

Now, on some event in the subform, by Macro or Event Procedure you
would
code to update the value of the "bound" [SomeValue] control on the
form.
ex.
Me.SomeValue = DLookup(some field, some table, some criteria)
------------
The same applies to your Date field (ex. [DOC] (Date of Creation))
ControlSource = Date()
That "displays" the correct date, but does nothing to update [DOC]
in
your
table.

In this case, binding the field... to ControlSource...
[DOC]
and setting the DefaultValue of [DOC] to...
=Date()
will update [DOC] with the current Date whenever a new subform record
is
added.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I created a form for data entry based on a table. One of the fields
on
the
form generates data though dlookup from another table. On teh form
all
looks
OK, but that data does not appear in the field on the table.
Actually
same
happens with data field on teh form. I used Date() so that on teh
fporm
the
date is genarated automatically, but date does not appear on teh
table.
What am I doing wrong and how can I fix it?
thanks