Access 2007 Update subform values with another subform

sounds like a job for a overriding VBA function

I thought that was at least one option.  My SQL and VB are both very rusty and I am new to 2007.  I have searched the web for several hours and found very little and I believe a comprehensive example here will benefit both me and other Access users.  Please be as specific as possible both in the implementation of the solution and the commenting of any code.  I would really appreciate some help on this one.

1) Is a SINGLE Reference tied to a SINGLE Applicant? You're storing the Applicant_ID in the Reference table, so it would seem to be so, but that doesn't really seem to be a good relationship. If a SINGLE Reference can be tied to MULTIPLE Applicants, and a SINGLE Applicant can be tied to MULTIPLE References, then you have a Many-to-Many relationship, which is not the way your database is structured.

2) You have Reference and Reference_Response setup as a 1-to-Many relationship, with the Reference_Response table as the "1" side and the Reference table as the "Many" side. This means that a SINGLE Reference_Response can be associated with MANY References. Is this relationship correct? It would seem this relationship is backwards - a Single Reference should be related to Many Reference_Responses.

3) What's the overall purpose of this database? Can you define (in non-technical terms) what your database is meant to accomplish?

RE:1 -  A single Applicant will have multiple references.  You are right on the many to many with the Reference to Application Tables.  I will likely not create the reference, but double entry the fields to the Reference_Response table and leave the Reference table as a stand alone table.  I don’t believe this will affect my question as far as coping values.

RE-2 – Yes this relationship is correct.  For each reference instance, there can only be a single set of fields (reference_response) that it is related to

RE-3 – The goal of this application is to reduce to amount of handwritten processing on a credit application.  When an application comes into the company it contains information (including the fields in the Applicant table).  It also includes a list of references (3-7) that have the company name, phone, fax, and account number.  The first generation primary goal is to use this information to generate fax credit surveys that will be sent to the references.  There will be some workflow to monitor date fax sent, status of app, etc, but that is beyond my question.

Here is the kicker.  It is a small industry and many Applicants use the same References, but misspell names, write down incorrect phone and fax #.  So, the client wants to maintain a database of References, when an application comes in, the data entry clerk will search for the Reference record on the subform and then use an insert button to add the record to the application instead of using the client’s phone numbers, etc for each credit Reference.

Kicker 2.  The Reference table cannot hold the unique account numbers for each Reference that is on the application form, because, although the applications may share the same references, the references account numbers will always be different.  So, the Reference_Response and Reference Table combined to make the Reference General Query is the linked form on the main application input form.

Goal restated – search the Reference_Input subform generated from the Reference Table using the search tool on the menu bar at the bottom of the form. It is not linked to the main form.  Once a record is found, (HERE IS WHERE U COME IN) find some solution to copy the values of the Reference record to the form that is linked to the Application form that includes both Reference fields and the Reference_Response field Account_Number.   Once the field values are copied, an account number must be entered (to make the reference specific to the account).  If the code asks the user in a pop up window for the account number and updates the table that would be a welcome bonus.

Thanks for your interest.  Hope this helps…  eric

Per my post above, her is the revised database with updated tables and relationships.  The structure looks good to me, but  I still do not believe it will affect my question of transferring the values on the unlinked reference subform to the linked form with a way to add an account number.   Thanks….  eric
EE-test-database-r2.zip

Then your relationship between Reference and Reference_Response should be a 1-to-1, not a 1-to-Many. A small point, perhaps, but it can have significant impact on the way your data is handled - but read further, since I believe this relationship is flawed.

<It is a small industry and many Applicants use the same References, but misspell names, write down incorrect phone and fax #.  So, the client wants to maintain a database of References, when an application comes in, the data entry clerk will search for the Reference record on the subform and then use an insert button to add the record to the application instead of using the client’s phone numbers, etc for each credit Reference.>

It seems to me that your current relationships are flawed. A single Applicant will have several References, and those References can be shared among different Applicants. This is a classic Many-to-Many relationship, and the only way to correctly represent that is through the use of a "join" or "bridge" table.  This "join" table would serve as the method to insure accuracy - you woudn't store the actual Company data in with a "Reference", but instead you would relate an Applicant with a Reference, and build a query that would pull the current Reference data for an Applicant.

Applicant
----------------
ID
Company
etc etc

Reference
----------------
ID
Company
Phone
etc etc

Applicant_References
---------------------------
ID
ApplicantID
ReferenceID

Now relate Applicant_References with Reference_Response by storing the Applicant_References.ID in the Refernce_Response table (you'd have to add a new field to this table). This would be the proper way to represent those data with each other.

If you do this, you can easily represent this via a Subform/Mainform relationship. Build a form based on your Applicant, and another form based on Applicant_References. On the Applicant_References form, use a Combo to select the Reference to be associated with your Applicant.

Open the Applicant form in the example I've supplied and you'll see what I'm talking about.



EE-test-database.zip

Thank you, my first versions of this database were very similar to yours using a join in relational tradition and in most cases and, probably this one, you are correct.  My most recent example is less normalized, but provides the same function as it repeats the information in the Reference Table in the Reference Response Table.  Not a very efficient design, but it may save the need for extra queries, etc.

Whichever backend design is used in the end, we are still faced with the question, of copying the Company, Phone, and Fax values from the Referenc_Search subform to the Initial Application_Input_Refs Subform.  Hopefully with a pop-up that asks for the Account Number.

Thanks for your help and time…  eric

<Whichever backend design is used in the end, we are still faced with the question, of copying the Company, Phone, and Fax values from the Referenc_Search subform to the Initial Application_Input_Refs Subform.  Hopefully with a pop-up that asks for the Account Number.>

My ENTIRE point in doing this is: You should NOT be copying those values, you should be RELATING the records to each other. You have stated that you will reuse your References - that's fine, but you should RELATE those references to the Applicant, you should not store details of the References with the Applicant (since they have absolutely nothing to do with "describing" an Applicant).

<Not a very efficient design, but it may save the need for extra queries, etc.>

I'm not sure that reducing the need for queries is a valid reason for a denormalized structure. Don't get me wrong - I'm no slave to normalization and will break those rules as needed, but this is most definitely not one of those times. This is a classic M-2-M structure, and as such there is no valid reason I can think of to break that design.

Your user can "search" for References using the built-in AutoComplete function of the combo in the subform of the Applicant form. For example, in the sample data included in my last upload, if I type "C" and then "S" I'm take to the record that begins with those letters. If that's not a sufficient search, then you can certainly build more thorough utility, but in most cases all you need is the ability for your data entry clerks to be able to locate a record, and AutoComplete often provides more than sufficient tools for that.

You can store your Account Number in the Applicant_References table - just add a new Field to that table. It's a simple matter to require the user to enter an Account Number. You can use the Form's BeforeUpdate event to determine if the user has entered that value and disallow the saving of the record if they have not.

OK, point taken, but on the form, we have to input several References.  Let’s say that we use the Reference General Query as the List of Credit References (It is based on the Referenc_Respons table).  So we use the App_Ref_ID field to store the Reference Primary Key.  On the Reference_Input subform, there must be a button that requests the Reference_ID, adds it to the Reference_Response table App_Ref_ID and updates the record.  

Same problem.  Select a Record from the Reference Database and Give it the Applicant_ID and Account Number.

<Let’s say that we use the Reference General Query as the List of Credit References (It is based on the Referenc_Respons table).  >

You shouldn't use that query. It's based on a flawed design. Your Reference table should be nothing but a Lookup table, and should ONLY store data relevant to that reference (i.e. the Name, contact info, etc).

The Reference_Response would relate to the Applicant_References table by storing the value of Applicant_Reference.ID. However, you could make the argument that you don't even need Reference_Response, since that information would be relevant to the Applicant_References, and as you said there will only ever be 1 Response to each unique Applicant_Reference there seems to be little gained from breaking out that data to a separate table.

< On the Reference_Input subform, there must be a button that requests the Reference_ID, adds it to the Reference_Response table App_Ref_ID and updates the record.  >

No - Again, this is based on a flawed design.

An Applicant is not directly related to a Reference, since References are "shared" among Applicants. You should not be storing the ApplicantID or the ReferenceResponseID in the Reference table. The Reference table should do nothing more than provide lookups so that your data entry clerks always choose the correct company. Once that company is chosen, your program should generate a new record in the Applicant_References table, which will relate an Applicant to a Reference.

See above about my thoughts on the Reference_Response table, and whether it's even needed. If, however, you decide to maintain the Reference_Response table, you would need to relate that to a record in the Applicant_Reference table. You'd do this storing the value of Applicant_Reference.ID in the Reference_REsponse table (or vice-versa, since this is a 1-1 relationship).

As to the AccountNumber - that is a "property" of a unique Applicant+Reference, and therefore should be stored in the Applicant_References table. You're currently storing it in the Reference_Response table, which is not correct (since the Reponse of a Reference has nothing to do with the Account number of the Applicant).

Did you review the Relationship diagram in the sample I sent? It should show you what I'm referring to (without the ApplicantAccountNumber field, which would need to be added to the Applicant_References table).

Thank you very much for all of your advice on database design and I have enjoyed it, however, the title and purpose of this question is “Access 2007 Update subform values with another subform”

What I am looking for is either:

A:  A method to extract the values of fields on one subform and insert them into fields in another subform using a button.   A pop-up dialogue box that asks for another value to insert into a field the second subform and then refreshes it would be a bonus.

B: A method to extract the value of the primary key of a subfrom and instert into a subform field and/or a table.  Then refresh a second subform using a button.  Again, A pop-up dialogue box that asks for another value to insert into a field the second subform and then refreshes it would be a bonus.


Thanks for all of your help

I fully understand what the title of your question is and what your end goal is.

However, your design is flawed and that's my ultimate point: You need to change your design in order to attain your goal. You could certainly fudge things and force them to work this time, but in my experience this always leads to more and more troubles down the road, since Access is built around the concept of relational data, and forcing it to do otherwise - that is, store the information from your Reference table into a different table, instead of relating that table correctly - will certain lead you down that road.

Your solution is a simple one - use a Join table, and store the data in the correct locations - but if you'd prefer someone else to assist you with your stated goals I'll gladly step out of the way and let other Experts climb on board.

Thank you for your help and opinions.  I am sure my end design will be somewhat relational, but I still do not see the need for a join table.  I did not intend in getting into a debate over design, but I am looking for some tools I believe I will need to complete my project.  Thank you again for your time...  eric  

I'll ask some of the other Access Experts to assist you.

I agree with LSMConsulting.

Ok, but the same initial problem remains even with the LSMConsulting Model.  On the Initial_Application_Form how does a user select a company in the database in the subform Reference_Input and have it populate the other subform Initial_Application_input_Refs?

You can download the LSMConsulting Model from this thread.

LSMConsulting,

Thank you for your patience and assistance on this project.  You have shared a great deal and I appreciate the effort.  I believe you have given me the tools to complete this phase of my project.  Thank you very much…  eric

Thanks for the help!

LSMConsulting,

The Reference Table will likely hold hundreds of records.  Is it possible to make the ReferenceID field on the subform of the main Applicant form a searchable field instead of a picklist.  This is what I was really going for from the beginning.

Thanks for any help…  eric

You really wouldn't search by ReferenceID (since that's an AutoNumber field and has no meaning to your enduser), but you would search by CompanyName. Is there some reason why the AutoComplete of the Access combo isn't up to the task? As the user types letters, Access will filter the list based on that entry, and the user can quickly narrow down their choices.

If not, I'd encourage you to change the Subform on the Applicant for to a Continuous View setup (you'll have to do some formatting), then expose that Subform's Page Header. Add a search utility there where you can allow users to enter a search phrase (or whatever) and then perhaps a small listbox where the results would be shown. I'd also add a button labeled "Add To References" where you'd do this:

DoCmd.RunCommmand acCmdREcordsGoToNew
Me.ReferenceID = Me.YourLIstbox.Column(0)
Me.Requery

Assuming your Listbox's first column is the ReferenceID, Access will add that record to the Applicant's Reference listing.

You could also do this with a popup form, or with another subform, if it would be more intuitive.

In many cases like this, I've used a Listbox + Textbox to "filter" data for the users to select. Since you're only dealing with the References, you'd add code like to a command button:

Me.Listbox.RowSource = "SELECT * FROM References WHERE CompanyName LIKE '*" & Me.YourSearchTextBox  "*'"

This would show your user all the results that match their Textbox entry.

Great.   thanks again for your help...   e