Link to home
Start Free TrialLog in
Avatar of decent1
decent1Flag for United States of America

asked on

Microsoft Access 2010 Default Value in a Form-Field to Populate a Table-Field

I am updating this database again.  I updated it from Access 2003 to 2007 a few years ago, and now from Access 2007 to 2010.  The 2007 version feature for use of =CurrentUser() as a system parameter is no longer available in Access 2010.

In a multi-user environment, I have a table, [tblClient-Contacts], and a form (subform), [sfrmClient-Contacts] - the subform is used exclusively to populate the table.  The "Common Expressions" Time and Date are successful default values when initial entry is made in a new client-contact record.  There are two fields common in both the table and the subform: [Agent] and [Ofc] .  These two fields also need to populate upon record initiation.  I have a query that identifies the agent who is logged onto the computer named, [qryAgent-ID], and it works fine (thanks to a previous Experts-Exchange answer).  That query includes the agent's [ID] and the agent's office code [Agent-OFC].  In [sfrmClient-Contacts], in the [Agent] field's Data-Property, Default Value I placed:  =[qryAgent_ID]![ID].  And in the [Ofc] field's Data-Property, Default Value I placed:  =[qryAgent_ID]![Agent-Ofc].  The result in both fields is: #Name?

I also attempted to establish a default value in the table, which did not work.

And, in another feeble attempt, I joined [qryAgent_ID] to [tblClient-Contacts], in [qryClient-Contacts], showing all records of [tblClient-Contacts] and only those records of [qryAgent_ID] that match, and then assigned that query as the subform's Record-Source, and followed suite in the Default Value Parameter.  Same error: #Name?

Please point me in the right direction - somebody always does, for which I am VERY thankful. dlm
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of decent1

ASKER

peter57r,

It works! Almost perfect. Yes, qryAgent_ID only has one record in it with the current user's details.  The subform, sfrmClient-Contacts contains "many" contact records with the client.  The subform resides in the main form, frmClient, the "one" side of the relationship.

Your code was drop-in, excellent and appreciated.

What I am hoping can be improved upon:
1) On initialization the two fields [Agent] and [Ofc] still display #Name?  But, as soon as data is placed into the field [Comments], then [Agent] populates.  After entering comments and upon TAB out of record/row, [Ofc] populates.  Fields are populated - much better than I had before!  If there is a way to suppress the error message, that would be good.
2) Upon TAB out of the record above, the following record/row populates with [Agent].  In most cases, after making comments in one record, the agent is done with that client and is ready to go to the next.  But, because the next record has been populated with [Agent], any attempt to navigate away from the Client record produces the error message, "You must enter a value in the ..." - into fields that are required.  A quick tap on ESCape solves the problem, but is mildly annoying.  It would be good if the [Agent] field did not populate until initiation of the record by a humanoid.
If you are seeing #Name? errors then you have set your controlsource to something that is not valid.

You should also have  removed the entries you previously had in the Default property.
Avatar of decent1

ASKER

peter57r,

I removed the previous entries in the Data Property, Default Values, and that fixed the “#Name?” error (duh).  Thanks!

The [Agent] and [Ofc] fields still filled incrementally, with [Agent] populating upon entering the record and then [Ofc] populating upon exiting the record.  To see if I could get [Ofc] to populate first, I moved that line up - changed the Code to:

If Me.NewRecord Then
Me!Ofc = DLookup("Agent-Ofc", "qryAgent_ID")
Me!Agent = DLookup("ID", "qryAgent_ID")
Else
' do nothing
End If

Now both fields populate simultaneously upon entering the record.

PROBLEM:  Upon leaving the "Current" record, the next record becomes "Current" and the data is populated, when no entry in the next record is intended.  Because of "required" fields in the record, an error message pops up when the user attempts to navigate to the next Client-Record.  Is there another "Event" that would update the fields upon exiting the record?  Maybe this would prevent the next record from populating, and would prevent the need to hit ESC before going to the next Client.

I sincerely appreciate your assistance thus far!

decent1
Avatar of decent1

ASKER

Thanks for the great assistance, and please forgive the delay in feedback!  A bit of disruption in my life, but still proceeding with this database.