# Solved: Access 2007 - Continuous form only shows single record



## MuddyTurtle (Jun 30, 2010)

Hello again! I'm continuing to have problems with a form I created in Access 2007. I have a master form with 2 sync'd combo boxes. The 2nd combo box is supposed to display results from a query into a continuous form (sub form). The problem I am having now is that the continuous form is only displaying a single record, even though there should be several resulting from the query. 
Another - possibly related problem - is with the combo boxes. When you open the form, the first box (cboRegion) is empty but the 2nd (cboLocation) still shows the last city name chosen. The continuous form also shows the results of the last search, but still only one record.
Any thoughts are appreciated!


----------



## OBP (Mar 8, 2005)

How are you controlling the Subform's Recordset?
Are you using a query with Criteria or the Master/Child Links of the Main/Subform?


----------



## MuddyTurtle (Jun 30, 2010)

I'm using Master/Child links.


----------



## OBP (Mar 8, 2005)

OK, so what is the master/child link and what do the synced combo boxes select?
If you open the Subform on its own do you get all the records shown?


----------



## MuddyTurtle (Jun 30, 2010)

The master/child link is on an ID field - the same for both - "JobLead_ID" which is in both tables involved in the combo boxes

The 1st combo box (cboRegion) selects a region
It is unbound - no control source
Row Source: tblRegion
Row Source Type: Table/Query
After Update: [Event Procedure]

The 2nd combo box (cboLocation)lists the cities in the chosen region
Control source: Job_Location
Row source: SELECT Job_Location FROM tblLocation WHERE Region_ID=3 ORDER BY Job_Location
Row source type: Table/Query

The sub form is supposed to show all the job leads in the chosen city, but it only shows the first one of several. If I look at the sub form by itself it shows all the job leads for all the cities in all the regions.


----------



## OBP (Mar 8, 2005)

I think the problem is the Master/Child Link.


----------



## OBP (Mar 8, 2005)

The Master Child link is restricting the Subform Records to the JobLead_ID.if you run a query with the JobLead_ID as a Criteria how many records does it show?


----------



## MuddyTurtle (Jun 30, 2010)

JobLead_ID query shows 19 records, which is all I have in the database at the moment.


----------



## OBP (Mar 8, 2005)

Is that 19 records in total?
If so how many records per JobLead_ID or is that 19 JobLead_IDs?


----------



## MuddyTurtle (Jun 30, 2010)

I changed the RecordSetType property for the subform to snapshot and that seems to have helped - I now can get more than one record visible. 
New problems though - or maybe just ones I hadn't noticed before. 
When I choose the region and location from the main form nothing happens, even if I hit the tab or return key. If I close the form then and open it again, the subform displays the correct results - with one exception. The first record will show the correct Location, but the rest of that record will be for JobLead_ID #1. I've attached a screenshot so you can see what I'm working with.
Regardless of what Location I choose in the main form, the first record of the subform will show that Location with the data for JobLead_ID #1. If I go to print or view that record, the report is for JobLead_ID #1, but the location is as I chose it on the form.
I'm sure it is just a simple switch somewhere, but I am stumped.


----------



## MuddyTurtle (Jun 30, 2010)

There are 19 records total. 1 record per ID.


----------



## OBP (Mar 8, 2005)

What happens if you remove the Master Child links and instead use the Region & Location as Criteria in the Query that supplies the Subform with it's data?


----------



## MuddyTurtle (Jun 30, 2010)

OK, I removed the Master/Child links and used the cboLocation and cboRegion fields from the main form in the query as criteria. I also added a button to refresh the form. So now when the user selects the region and then the location and clicks the button, the subform displays the correct city(location) in the subform, without any extraneous records. Hooray! Thank you.
Now the only problem is, the first record (or only record if there is only one) is always the same record, but the location is changed to match the cboLocation field of the main form. It always displays record #2 which is now the first record in the database. It is almost as if the rest of the subform is pulling the data from a different source than what the location field is pulling from, but I don't see where that is happening. I also noticed that the records that are pulled from the master table have the job_region listed as a number - even though they should be text. Somehow the form has changed the data on the first record and added several blank records to the end of the master table, and then only pulls from those records that it added/changed. What am I missing?


----------



## OBP (Mar 8, 2005)

Any chance you can convert it to Access 2003 format.


----------



## MuddyTurtle (Jun 30, 2010)

Ok, here it is. Thanks for all your help with this BTW!


----------



## MuddyTurtle (Jun 30, 2010)

SearchLead form is the major culprit. It no longer seems to be always pulling the first record in the database, but it only pulls the records that it added at the end of the db. The records it does pull have the Job_Region listed as a number instead of text in the master table (tblJobLeadMaster). I'm not sure how those records got there. Records that I added have the text chosen by combo box for the Job_Region, using the New Job Lead form for doing the data entry.


----------



## OBP (Mar 8, 2005)

OK, which Form, Subform?


----------



## OBP (Mar 8, 2005)

Is this doing what you want?
I added some Requery VBA.


----------



## MuddyTurtle (Jun 30, 2010)

It loads results without needing to click the search button, which is helpful, although when you do click the search button it brings up more results. Still, the results are the blank ones that mysteriously got added on to the end of the DB instead of the real data that should be displayed. So in short, no. If you look at the master table you'll see the difference between the records. Maybe if I delete the blank records it would force the form to display the legit ones?


----------



## MuddyTurtle (Jun 30, 2010)

OK, so I deleted the blank records and corrected the first record (ID:2) so that the Region is Other and the Location is Gloucester. Then I opened the SearchLead form and surprise! It only displays the first record, changing the Region and Location to suit the search criteria. I went back to the master table and the corrections that I had made to the first record are gone.


----------



## OBP (Mar 8, 2005)

I see why now the Combos are using the Fields in the table as their Control Sources, which means that they will change the first record, they should be based on a query/table form tblLocation and tblRegion with NO Control Source.


----------



## OBP (Mar 8, 2005)

Try just removing the Control sources of the Combos.


----------



## MuddyTurtle (Jun 30, 2010)

Without the control sources nothing displays in the sub form. 

I'm glad you don't give up easily because this is driving me nuts!


----------



## OBP (Mar 8, 2005)

Mine does, select Peninsula & Newport news.


----------



## MuddyTurtle (Jun 30, 2010)

I see. I deleted the records that didn't have real data in them - so they don't show up in my subform like they do in the one you just sent. There are other records for Peninsula - Newport News, why doesn't the form display them?


----------



## OBP (Mar 8, 2005)

Simple, Key ID & Region


----------



## OBP (Mar 8, 2005)

To elaborate on last nights quick post.
In the tblJobLeadMaster table you have 6 records with the Region ID value 2 & 13 with "Peninsula".
The Form combo Selects Region ID but shows Region, so only those with the value 2 show up for Peninsula in the Query.


----------



## MuddyTurtle (Jun 30, 2010)

Thank you for the elaboration. I fixed the relationships so ID goes to ID and region name goes to region name, not crossed like it had been. Then I fixed the records that had the ID # instead of the Region name. Finally after removing the criteria from the Region field in the JobLeadMaster query it works properly!!!
Thank you so much for your help!!


----------

