Pulling data into a form and making it save

[TheDrescher]

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record to
the database these fields appear blank in the "Records" table I'm saving to.
I understand this is happening because the control source on these controls
is the formula instead of the bound field in the table as it should be, but I
can't find another way to get the correct data to populate. Please Help!
Thanks!

 

[Jeff Boyce]

One approach might be to bind those controls to their respective fields and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[TheDrescher]

Jeff, that's a good way to go about it. I originally checked to see if it
were possible before I posted. I forgot to mention I'm running Access 2003,
I don't seem to have OnCurrent as an event option, thoughts?

One approach might be to bind those controls to their respective fields and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP
table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record to
the database these fields appear blank in the "Records" table I'm saving
to.
I understand this is happening because the control source on these
controls
is the formula instead of the bound field in the table as it should be,
but I
can't find another way to get the correct data to populate. Please Help!
Thanks!

.

 

[Jeff Boyce]

Open the form in design view. Check the events ... I'm pretty sure A03 has
the OnCurrent event, that triggers when a (new) record is loaded into the
form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff, that's a good way to go about it. I originally checked to see if it
were possible before I posted. I forgot to mention I'm running Access
2003,
I don't seem to have OnCurrent as an event option, thoughts?

One approach might be to bind those controls to their respective fields
and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP
table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record
to
the database these fields appear blank in the "Records" table I'm
saving
to.
I understand this is happening because the control source on these
controls
is the formula instead of the bound field in the table as it should be,
but I
can't find another way to get the correct data to populate. Please
Help!
Thanks!

.

 

[TheDrescher]

Jeff,

The original control for this was simply a text box, because you really
don't need to select anything after the code pulls up the name. Is this the
best way or should I utilize something else? Thanks!

One approach might be to bind those controls to their respective fields and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP
table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record to
the database these fields appear blank in the "Records" table I'm saving
to.
I understand this is happening because the control source on these
controls
is the formula instead of the bound field in the table as it should be,
but I
can't find another way to get the correct data to populate. Please Help!
Thanks!

.

 

[Jeff Boyce]

Wouldn't that depend on what the underlying data is? You've already
identified that it is for display only, right?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

The original control for this was simply a text box, because you really
don't need to select anything after the code pulls up the name. Is this
the
best way or should I utilize something else? Thanks!

One approach might be to bind those controls to their respective fields
and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP
table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record
to
the database these fields appear blank in the "Records" table I'm
saving
to.
I understand this is happening because the control source on these
controls
is the formula instead of the bound field in the table as it should be,
but I
can't find another way to get the correct data to populate. Please
Help!
Thanks!

.

 

[TheDrescher]

Jeff,
Under the events tab of the properties for that control, there doesn't seem
to be an OnCurrent event listed, I populated all events relating to that
control (with the exception of the mouse moves and key presses) with the code
I mentioned previously: =DLookUp("[Manager]","[EMP]","ID=" &[EmpName]).
Returning to Form View it now simply shows a drop down menu containing all
the manager names instead of the one assigned to the particular employee.

Open the form in design view. Check the events ... I'm pretty sure A03 has
the OnCurrent event, that triggers when a (new) record is loaded into the
form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff, that's a good way to go about it. I originally checked to see if it
were possible before I posted. I forgot to mention I'm running Access
2003,
I don't seem to have OnCurrent as an event option, thoughts?

One approach might be to bind those controls to their respective fields
and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP
table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record
to
the database these fields appear blank in the "Records" table I'm
saving
to.
I understand this is happening because the control source on these
controls
is the formula instead of the bound field in the table as it should be,
but I
can't find another way to get the correct data to populate. Please
Help!
Thanks!



.

.

 

[TheDrescher]

That's correct, the whole point is that the person filling out the database
won't have to go anywhere to look up the employee's manager; it simply
auto-populates. If I add the code into a text box as the control source, it
was pulling the data correctly, but then when I'd go to save to a table, it
would just populate a null field in the table itself, I'd like the text that
was displayed to save to the table like the rest of the form data.

Wouldn't that depend on what the underlying data is? You've already
identified that it is for display only, right?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

The original control for this was simply a text box, because you really
don't need to select anything after the code pulls up the name. Is this
the
best way or should I utilize something else? Thanks!

One approach might be to bind those controls to their respective fields
and
use your DLookup function to feed values to them.

Would it work to run the DLookup function in code from the form itself,
rather than those controls? Perhaps using the OnCurrent event?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Okay, on my MainPage form I have two controls (named ManagerSelect and
TenureBox) that pull information conditionally from fields in the EMP
table.
I achieved this by using the DLookUp function as the control source:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName]) for ManagerSelect
=DLookUp("[AdjServiceDate]", "[EMP]", "ID=" &[EmpName]) for TenureBox.
These values are populating fine, but when I go to save the new record
to
the database these fields appear blank in the "Records" table I'm
saving
to.
I understand this is happening because the control source on these
controls
is the formula instead of the bound field in the table as it should be,
but I
can't find another way to get the correct data to populate. Please
Help!
Thanks!



.

.