[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1074
  • Last Modified:

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

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
gdunn59
Asked:
gdunn59
  • 24
  • 19
  • 4
  • +2
1 Solution
 
JAMcDoCommented:
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
 
gdunn59Author Commented:
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
 
MINDSUPERBCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
JAMcDoCommented:
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
 
DockieBoyCommented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
Not a problem
Life happens :)
0
 
gdunn59Author Commented:
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
 
Nick67Commented:
Generally not.
if you can post the screenshots we can try and get it licked here and now :)
0
 
gdunn59Author Commented:
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
 
Nick67Commented:
Another 20 minutes or so
0
 
gdunn59Author Commented:
Ok.  I will try to get the info to you soon.

Thanks,
gdunn59
0
 
Nick67Commented:
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
 
gdunn59Author Commented:
Not a stupid question, but yes I know how to do screen shots.

I will work on getting this information.

Thanks much,

gdunn59
0
 
Nick67Commented:
No Problem :)
Later
0
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
or it just displays the query results on a separate tab (I'm using MS Access 2007).
0
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
Easy enough.  How are the main form and subform related?  They should link on master and child fields of InquiryNum --> InquiryID
0
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
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
 
DockieBoyCommented:
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
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
Also, the main/subforms are being linked master and child fields of InquiryNum --> InquiryID
0
 
DockieBoyCommented:
I think it would be much easier if you could upload the db, I only use 2003, so .mdb would be good :)

0
 
MINDSUPERBCommented:
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
 
gdunn59Author Commented:
Ok.  I have attached my database.

Thanks everyone for your assistance on this.

gdunn59
Audit-Database--3-11-2011--for-E.mdb
0
 
gdunn59Author Commented:
I have an append query created already (qryAppendSubCodes) that you can look at to see if it is right.

Thanks,
gdunn
0
 
MINDSUPERBCommented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
Nick67Commented:
Dumb question perhaps.
Was the file you uploaded saves as an mdb?
Or renamed to one?
My Access 2003 won't open it
0
 
Nick67Commented:
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
 
Nick67Commented:
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
 
Nick67Commented:
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
 
gdunn59Author Commented:
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
 
Nick67Commented:
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
 
MINDSUPERBCommented:
gdunn59,

You may try to see the file attached as well.

Sincerely,
Ed
Audit-Database--3-11-2011--for-E.mdb
0
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
Thanks!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 24
  • 19
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now