Solved

How to populate all fields on subform with combo box data from main form

Posted on 2011-03-02
52
1,047 Views
Last Modified: 2012-05-11
I need to automatically fill in new records in a subform (frmQuality_Review_Subform) based on the selection in a combo box (cboAuditType) on the main form (frmEmployee_Audits).  When the user chooses a selection from the cboAuditType combo box on the main form, it needs to look up values in a table called "tblEnrollment_Dept_Criteria_Codes" and fill in the fields on the subform from this table where the cboAuditType selected from the combo box from the main form,  matches the Audit_Type in the "tblEnrollment_Dept_Criteria_Codes" and poplulate the fields appropriately.

The code needs to populate the subform with all the data that is found in the table where the Audit Types match in both the table and form.  So for instance, if there are 10 records in the table where the Audit Type matches the Audit Type selected on the main form combo box, then the subform needs to be populated with 10 records.

I need to fill in the following fields into the new records on the subform.  The subform is a datasheet form and the main form is a single form.

Fields to fill in:

Field in Table                          POPULATES             Field on subform
Quality_Review_Criteria             ----->                    Subcode
Possible Score                            ----->                    Possible_Score
Audit Score                                 ----->                    Audit_Score
Region                                         ----->                    Region
InquiryNum                                  ----->                    InquiryID
cboEmployee                              ----->                    Employee

Sorry if I repeat myself, I'm just trying to make it clear.

Thanks,
gdunn59
0
Comment
Question by:gdunn59
  • 24
  • 19
  • 4
  • +2
52 Comments
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35024258
You may wish to ry this:

Create a code subroutine for cboAuditType for the AfterUpdate event

Use a series of DLookup statements such as:

     Me!frmQuality_Review_Subform.Form!Subcode = DLookup("[Quality_Review_Criteria]","tblEnrollment_Dept_Criteria_Codes", "[ Audit_Type] = '" & [cboAuditType] & "'")

Note:  '" is ' " without the space and "'" is " ' " without the spaces.

Repeat for the other mapped values.

I haven't tested it but I hope this works (or at least gives you some ideas).

J.
0
 

Author Comment

by:gdunn59
ID: 35024324
Isn't this only going to create 1 record?

As I mentioned in my posting for example:

If there are 10 records in the table where the Audit Type matches the Audit Type selected on the main form combo box, then the subform needs to be populated with 10 records.

Thanks,
gdunn59

0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35024364
gdunn59,

The best approach on this is to create an append query and in the Where Criteria you put the name of your combo box.

Forms!YourForm!cboName

And in the after update event of your combo box you run the query as:

DoCmd.OpenQuery "qryYourQuery"

Sincerely,
Ed
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35024366
Sorry, I missed the 'multiple records' stipulation.

Would it work if a loop examined each record in the table for audit type matches and, if a match were found, to do the series of DLookups on that record?

J.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35024532
Create a query that will display all the necessary data, then as mentioned above, use the criteria in that query to select only the records that match the value of the combo on your form, then set the sub forms record source to the query and requery the subform with the on change event for the combo box.
0
 

Author Comment

by:gdunn59
ID: 35073632
Haven't had a chance to try this, but will when I have some time, and will get  back and accept a solution.

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35097052
I'm with DockieBoy on this.
The recordset of the subform should be

"Select * from tblEnrollment_Dept_Criteria_Codes where Audit_Type = " & Me.Parent!cboAuditType

if cboAuditType returns string values it will need to be

"Select * from tblEnrollment_Dept_Criteria_Codes where Audit_Type = " & chr(34) & Me.Parent!cboAuditType & Chr(34)

Chr(34) is a doublequote -- and a lot easier to debug that all sorts of """ & """

The afterupdate event of the combobox just needs to be

Me.frmQuality_Review_Subform.Requery
0
 

Author Comment

by:gdunn59
ID: 35104907
Nick67:

When you say that the recordset of the subform should be:

"Select * from tblEnrollment_Dept_Criteria_Codes where Audit_Type = " & Me.Parent!cboAuditType

Where/how do I incorporate this on the subform?

Thanks,
gdunn59


0
 
LVL 26

Expert Comment

by:Nick67
ID: 35110437
With the subform in design view, on the data tab of the properties window, set the record source to the SQL select statement.
You could also conceivably save the select statement as a query.
The syntax for the parameter changes, because Me.Parent doesn't play as a role
In a saved query it would be
"Select * from tblEnrollment_Dept_Criteria_Codes where Audit_Type = " & forms!frmEmployee_Audits!cboAuditType.

See the two attached images

And I know that the properties window is from a report -- but I don't have a handy example of a form that uses a bare SQL statement.
For forms  I always use a saved query.
The idea is the same though


Properties.jpg
query.jpg
0
 

Author Comment

by:gdunn59
ID: 35111258
Nick67,

I have tried this, but just can't seemed to get it to work.

Not sure what I am doing wrong.  Doing exactly what you're stating above.

HELP!!!!!!

Thanks,
gdunn59

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35111580
OK

First things first when troubleshooting.
Have frmEmployee_Audits open in regular form view.

Now, what kind of data does cboAuditType return.
String data needs quotes around it, while number data doesn't
Date data -- if you ever use it, needs to be surrounded by # -- as in #1-Mar-11#

So, if you created a query, like the one in my image, select a value in cboAuditType on the open form, and then open the query.
Is it returning data?
Is it returning the right data?

If not, take the forms!frmEmployee_Audits!cboAuditType parameter out.
Now does it return data?
Just too much --- not filtered--data?

Now, put in a value for a parameter for Audit_Type.
You haven't said what kinds of values Audit_Type holds
Is some filtered data returned?

At this point, we then have to figure out the right syntax for the parameter, if you can get properly returned data for a manually entered parameter.
Post screenshots for the design of tblEnrollment_Dept_Criteria_Codes, the query you just created, and the format and data tabs of the properties window for cboAuditType
0
 

Author Comment

by:gdunn59
ID: 35112379
Ok.  I have to work on something else for probably the next hour.  I will get back to it after that and try the suggested troubleshooting.

I'm not getting any errors, its just not doing anything.

Thanks for your time in this matter.

gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35112411
Not a problem
Life happens :)
0
 

Author Comment

by:gdunn59
ID: 35113764
Ok.  I will be trying it hopefully here soon or with in the next hour.

Will you be checking this posting tonight and/or this weekend?

Thanks,

gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113775
Generally not.
if you can post the screenshots we can try and get it licked here and now :)
0
 

Author Comment

by:gdunn59
ID: 35113784
Ok.  I need to take a look at things before I do the screen shots.

How long will you be available tonight?

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113790
Another 20 minutes or so
0
 

Author Comment

by:gdunn59
ID: 35113796
Ok.  I will try to get the info to you soon.

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113866
Hey, I'm heading home.
I'll check up on this since you're grinding away at it
It might be a dumb question, but you do know the simple way to post screen shots?
ALT-PRNT SCREEN and then paste into MSPaint.
Select the part you care about, undo, and paste the selected chunk
Save and post.

I'll check later tonight.

Nick67
0
 

Author Comment

by:gdunn59
ID: 35113877
Not a stupid question, but yes I know how to do screen shots.

I will work on getting this information.

Thanks much,

gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35113884
No Problem :)
Later
0
 

Author Comment

by:gdunn59
ID: 35113944
Hey if I have the frmEmployee_Audits form open in view mode, and select an audit type from the combo box on that form, and then run the query manually, it is giving me the correct results.  But if I do it in the frmEmployee_Audit form and don't run the query manually, nothing happens (nothing is entered in the attached subform).

Thanks,
gdunn59
0
 

Author Comment

by:gdunn59
ID: 35113948
or it just displays the query results on a separate tab (I'm using MS Access 2007).
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114242
Perfect.

The query is working right.
Now the *Subform's* record source.
What is it?
It should be the query that works correctly when run manually.

Then the combobox afterupdate code should requery the subform
The afterupdate event of the combobox just needs to be

Private sub cboAuditType_AfterUpdate
Me.frmQuality_Review_Subform.Requery
end sub
0
 

Author Comment

by:gdunn59
ID: 35114429
Ok, I'm finally getting somewhere with it.  First, it should have been an "Update" query, because I need it to append the records to the a table for the subform, and then I determined that I was using the wrong table for the subform append.  I knew once I could get away from all the other things I was trying to today, and work at my own pace tonight, things would look clearer.

Now when it trys to append, I am getting an error (which I have attached as a screen shot).

Thanks,
gdunn



Error-With-Append.bmp
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114470
An update query is not an append query.
One changes the values of existing records.
The other one adds records to a table.

Needless to say, you now have me confused.
My understanding of your workflow was, shall we say, lacking.

Nevertheless.
Your query -- append or update?
Probably append.

Now, for an append query to work the data types and validation rules that map for each field HAVE to match.

So if in Table A, field 1 is integer, and you try to append it to Table B, field 1, which is text -- BANG!
--that dog don't hunt

If Table C, field 1 has a value of 1-Jan-1999 and Table D Field 2 has a validation rule > 1-Jan-2005 -- BANG!
-that dog don't hunt

Look carefully at the fields you're appending in both the source and destination tables.  They have to match.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:gdunn59
ID: 35114481
Ok. Figured out the error.  It was because I had a couple of fields set to "required".

It is appending the records, but it still doesn't appear to be requerying the subform and putting the data there to see.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114491
So, to what table is it appending the records?
And how is the append query firing?
I don't think an append query can be the recordset of a form
And, in the table that you appended the records to, what distinguishes the new records from the ones that existed before
0
 

Author Comment

by:gdunn59
ID: 35114539
The table that it is appending records to is:
    tblEmployee_Quality_Review_Info

The append query is fired from the 'After Update' event of the cboAuditType combo box on the main form (frmEmployee_Audits).

The recordset for the subform is:
    SELECT tblEmployee_Quality_Review_Info.*, * FROM tblEmployee_Quality_Review_Info WHERE ((([Audit_Type])=" & Me.Parent!cboAuditType"));
     
As far as how the appended records are distinguished, I need to somehow get the InquiryNum from the Main form to populate the InquiryID in the subform/table.

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114581
Easy enough.  How are the main form and subform related?  They should link on master and child fields of InquiryNum --> InquiryID
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114604
Or have the subform' SQL be

"SELECT tblEmployee_Quality_Review_Info.*, * FROM tblEmployee_Quality_Review_Info WHERE ((([Audit_Type])=" & Me.Parent!cboAuditType & " And [InquiryID] = " & Me.parent!InquiryNum));
0
 

Author Comment

by:gdunn59
ID: 35125610
Still doesn't seem to show the records on the subform.

Is it possible to do a loop through a set of dlookups with an IF Statement, and have all of the matching rows up in the subform (see my code below).

Thanks,
gdunn59

If Me.cboRegion Like "East*" And [cboAuditType] = "BAE" Then
    Me!frmQuality_Review_Subform.Form!Code = DLookup("[Quality_Review_Criteria]", "tblEnrollment_Dept_Criteria_Codes", "[Audit_Type] = '" & [cboAuditType] & "'")
    Me!frmQuality_Review_Subform.Form!Possible_Score = DLookup("[Possible Score]", "tblEnrollment_Dept_Criteria_Codes", "[Audit_Type] = '" & [cboAuditType] & "'")
    Me!frmQuality_Review_Subform.Form!Audit_Score = DLookup("[Audit Score]", "tblEnrollment_Dept_Criteria_Codes", "[Audit_Type] = '" & [cboAuditType] & "'")
    Me!frmQuality_Review_Subform.Form!txtSection = DLookup("[Section]", "tblEnrollment_Dept_Criteria_Codes", "[Audit_Type] = '" & [cboAuditType] & "'")
    Me!frmQuality_Review_Subform.Form!SectionDesc = DLookup("[SectionDesc]", "tblEnrollment_Dept_Criteria_Codes", "[Audit_Type] = '" & [cboAuditType] & "'")
    Me!frmQuality_Review_Subform.Form!cboAssoc = "N"
    Me!frmQuality_Review_Subform.Form!cboOpers = "N"
    Me!frmQuality_Review_Subform.Form!cboOpers = "N"
End If

Open in new window

0
 

Author Comment

by:gdunn59
ID: 35125640
In regards to my last question regarding looping through an if statement with dlookups, based off of the "if statement", I have attached a file that shows what should be populating in the fields on the subform. (see attached file).

Thanks,
gdunn59
 What-should-show-up--for-EE-.docx
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35125670
Sorry if someone has asked already, but are you able to upload the db or at least a basic version of what you are trying to achieve?

I've been reading through the posts and I'm a litttle confused, lol.

Just for a quick re-cap here, You have a main form with a combo box, when a selection is made from that combo box you want the subform to display the data from a table where the data in a field in that table matches the data in the combo box on your main form?

With the sub form, are you only displaying data or is that then updateing a seperate table?

0
 

Author Comment

by:gdunn59
ID: 35125790
I'm more than a little confused at this point - lol.

What you recapped is exactly right.  I was doing an append query to a table and then was requerying the subform to show the records that matched the AuditType and InquiryNum from the Main form and the subform.  The inquiry num in the subform is being populated by the inquiry num in the main form in the "after update" event for the inquiry num field on the main form.

The subform should display data and that data should be appending to a table called tblEmployee_Quality_Review_Info.

If you still need me to upload a version of the db, please let me know.

Thanks,
gdunn59
0
 

Author Comment

by:gdunn59
ID: 35125804
Also, the main/subforms are being linked master and child fields of InquiryNum --> InquiryID
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35125820
I think it would be much easier if you could upload the db, I only use 2003, so .mdb would be good :)

0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35126026
gdunn59,

My post at ID: 35024364 surely achieves what you want. What you only need is an append query.

Post your sample db and we can assist you how to create the append query.

Ed
0
 

Author Comment

by:gdunn59
ID: 35126611
Ok.  I have attached my database.

Thanks everyone for your assistance on this.

gdunn59
Audit-Database--3-11-2011--for-E.mdb
0
 

Author Comment

by:gdunn59
ID: 35126627
I have an append query created already (qryAppendSubCodes) that you can look at to see if it is right.

Thanks,
gdunn
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35126655
I got a slow connection. It took time for me to download the db. Can you post the query statement for qryAppenSubCodes?

Sincerely,
Ed
0
 

Author Comment

by:gdunn59
ID: 35129092
Here is the query statement for qryAppendSubCodes:

INSERT INTO tblEmployee_Quality_Review_Info ( Quality_Review_Criteria, Possible_Score, Audit_Score, [Section] )
SELECT tblEnrollment_Dept_Criteria_Codes.Quality_Review_Criteria, tblEnrollment_Dept_Criteria_Codes.[Possible Score], tblEnrollment_Dept_Criteria_Codes.[Audit Score], tblEnrollment_Dept_Criteria_Codes.Section
FROM tblEnrollment_Dept_Criteria_Codes
WHERE (((tblEnrollment_Dept_Criteria_Codes.Audit_Type)=[forms]![frmEmployee_Audits]![cboAuditType]))
ORDER BY tblEnrollment_Dept_Criteria_Codes.Code_Sort;
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35129202
Ok.

I'm back at work.
I got a few things to look after and then we'll see if we can finish this.

Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35129489
Dumb question perhaps.
Was the file you uploaded saves as an mdb?
Or renamed to one?
My Access 2003 won't open it
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35129801
Alright.
Opened it with Access 2010 and saved it as an mdb.
Now I have it open in Access 2003.

Now my friend.  InquiryNum is your Primary Key in tblEmployee_Audits.
But it is type text.  Highly unusual.  Does that text come from something external?
What value should it have if I was blowing in some dummy data to test?

And your append query, while it blows in the records, needs to add the InquiryNum to InquiryID field.
Not that a query can append and update at the same time.

Give me a bit to poke harder.
But do tell me about your primary keys, or lack thereof.
Those monsters are critical to the success of your db
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35129911
Now this sub

Private Sub cboEmployee_AfterUpdate()
    'Me.frmQuality_Review_Subform.Employee = Me.Employee
    Me.frmQuality_Review_Subform.Form.Employee = Me.Employee
   
End Sub

Is already adding a record, or changing a record in the subform when it changes
Is that required/desired?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35130057
Ok,

A query can append and update at the same time if you jury-rig it a little.
But the SQL stateemt powering the subform isn't going to work.

Tricky syntax things to remember
Me.Anything works only in VBA code -- it does NOT work in the query editor
In the query editor you need [forms]![someform]![somecontrol]
If its coming from a subform, you need a more tortured syntax
[forms]![someform]![somesubform].Form![somecontrol]
0
 

Author Comment

by:gdunn59
ID: 35130246
The primary key (Inquiry Num) is a field that is entered by the user and identifies each record for that employee.  The field has to be text because it contains letters and numbers.

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35130587
Happier design is to have an AutoNumber key and then, if user input is required and cannot be duplicated, have that user entered field be required and indexed (No Duplicates).  Let Access handle Primary Keys, numbering them uniquely and dealing with the consequences of key violations.

Indexing works better then.
So do your relationships.
No matter.

The altered file has been uploaded.
I have commented out all the AfterUpdate code
I would recommend that you put a cmd button on the form 'cmdStartJob' and put some user confirmation code in there
Then put what is now in cboAuditType_AfterUpdate in there.

You can also put you Data validation stuff in there

if nz(me.employee,"") = "" then
    Msgbox "you need an employee"
    Me.employee.setfocus
    exit sub
end if

...

Make sure your end-users got everything RIGHT before you do the append query
The final append query is below
INSERT INTO tblEmployee_Quality_Review_Info ( Quality_Review_Criteria, Possible_Score, Audit_Score, [Section], InquiryID, Employee )
SELECT tblEnrollment_Dept_Criteria_Codes.Quality_Review_Criteria, tblEnrollment_Dept_Criteria_Codes.[Possible Score], tblEnrollment_Dept_Criteria_Codes.[Audit Score], tblEnrollment_Dept_Criteria_Codes.Section, forms!frmEmployee_Audits!InquiryNum AS InquiryNum, forms!frmEmployee_Audits!Employee AS Employee
FROM tblEnrollment_Dept_Criteria_Codes
WHERE (((tblEnrollment_Dept_Criteria_Codes.Audit_Type)=[forms]![frmEmployee_Audits]![cboAuditType]))
ORDER BY tblEnrollment_Dept_Criteria_Codes.Code_Sort;

Open in new window

Audit-Database.mdb
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 35130858
gdunn59,

You may try to see the file attached as well.

Sincerely,
Ed
Audit-Database--3-11-2011--for-E.mdb
0
 

Author Comment

by:gdunn59
ID: 35167688
MINDSUPERB,

This appears to be working.  I still need to do some more testing, just haven't had much time for that lately.

Once I get it throughly tested, I will respond and accept a solution.

Thanks much,

gdunn59
0
 

Author Closing Comment

by:gdunn59
ID: 35749272
Thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now