ComboBox Problems

[rexmorgan]

I have two problems with my database. The primary table of concern is the table named 'Spotter'. The primary form of concern is named 'SpotterForm'. There are a few primary queries of concern also they are, 'qrycboStatesOnCountry', 'qrycboCountyFromState', and 'qrycboCityFromCounty'.

The first issue I am having is that when I select info from the combo boxes on the SpotterForm some of the values diplayed in the Spotter table are numbers and not the Text from the combo box. The country and state appear fine in the table 'Spotter' however the County and City appear as numbers. I am sure that there is a setting somewhere that I have not corrected. Just not sure where that setting is. I tried adjusting the BoundColumn property of the County combo box, but that seemed to make the the City combo box fail to populate.

The second issue is that the City combo box seems to not retain its value. Not sure what the reason for this is.

Any help is greatly appreciated. I am self taught using access. I have learned quite a bit from my mistakes and the comments on this site.

On the form I have cleaned up my actual database and will include it as an attachment.

 

[Kiwiman]

Howzit

Look at your bound column - this is what determines what is written to the table. Your bound column for the county is your 1st column (county ID) (which is hidden in your combobox as you have set the column width to 0) but the county id is stored in the table not the county name.

 

[Kiwiman]

Howzit

Try this in your Form_Current event procedure

Private Sub Form_Current()
Me.cboCountry.Requery
Me.cboState.Requery
Me.cboCounty.Requery
Me.cboCity.Requery

End Sub

This will still leave blanks on the odd record but this is because of an incorrect city selections based on the state. For example Spotter id is in state MT and has county 132 and city 3075 selected . However county 132 belongs to state ND

 

[rexmorgan]

Thanks for the quick replies. I thought that perhaps it had something to do with the 'Bound Column' setting. However when I change the 'Bound Column' value to 2 instead of 1. The cboCity will not populate. Any ideas on why that would be.

Any thoughts as to why the cboCity will not retain its value after moving to the next record. The very first record will keep the data but all sequential cboCity values drop off. They are still in the table 'Spotter' but not on the form 'SpotterForm'.

I am going to try to whittle down the database and still leave some things in it. I realized that I removed too much for it to work properly. I was having problems getting the file size small enough. After using the Compact Database tool it shrank the file size way down.

This has been a work in progress for over a year. It was working fine then someone decided they needed to add some extra fields and I thought that while I was at it I would try to add the combo boxes instead of a text field in an effort to reduce errors and save time.

Thanks again for the help/suggestions.

 

[Kiwiman]

Howzit

Did you add the code in the Form_Current section - this works for me. Changing the bound column to 2 will write the city name into the spotter table, but you need to form_current to refresh the combo boxes to view the "record" when cycling through records

 

[rexmorgan]

I did add the code. I also have the cboCounty and the cboCity 'Bound Column' property set to 2. The initial problem seems that the cboCity will not populate after changing the cboCounty 'Bound Column' property to 2. Not sure why that is.

I am not sure I put the code in the correct place but I think so.

 

[Kiwiman]

Howzit

Can you check that you see [Event Procedure] in teh Event tab of your form properties in teh On Current field

 

[rexmorgan]

Yeah that shows up. I have included a screen capture attachment. Thanks again for all the help.

 

[Kiwiman]

Howzit

Part of the problem is you are using lookups at the table level (which is commonly frowned upon). So what you see is not necessarily what is stored in the table. As you do all your data entry (assumption here) in forms there is no reason to have lookups at the table level - in my opinion anyway.

You have changed the bound column to 2 for the county, but now your combobox will not work as it is filtering the city table by county (which in your city table is a number field) but in your spotter is a text. So in fact you will be searching for Daniels in the city table but the value is 1 - the value stored.

 

[Kiwiman]

Howzit

This works for me. I have taken the liberty of removing the lookups so I could see the actual values in the tables. Please feel free to reinstate them if you wish - it is your db after all.

I needed to change the cityfromcounty query to reflect the new bound column. I have changed couple of records only to see them cycle over. The others will need to changed to write the city county values to the table.

 

[rexmorgan]

Do you have any suggestions on fixing the problem? You mentioned that lookups at the table level are frowned upon, what would be the better way to go about accomplishing this? Any pointers on re-arranging the data?

As I noted in my original post, I know enough about access to be dangerous. I am still learning and would appreciate any help in pointing me in the right direction. I really do not wish to completely rework this database but if that is what is necessary then I will. Much of what we have is way more than what we had before I began this project. All we have before this was a spreadsheet with the basic info. It has come a long way but since it has been done piece meal I am sure some things I would have planned a little better.

Sorry about the long rambling. I have been awake for quite a while now trying to get this darn thing sorted out.

 

[Kiwiman]

Howzit

Changing the tables so it does not use lookups is straightforward. You just change in Table Design, on the lookup tab from combobox to text box. This will not change the design of the table or lose you any data, it just changes how you see the data at table level.

No real re-arranging is needed. The Spotter table could be a small problem and depends on

1. What you currently have showing in this table for the City and County
2. What you want to show in these fields
3. How many records you have

If you want to show the county and city names in the spotter table as your initial post indicates and you have the id curretly stored then you will need to run some updates.

If you can live witht he id's being stored in the spotter table, I would change the datatype from Text to be the same as the datatype from the source table(s) and use the form combobox to view the city and county names.

At the end of the day you need to decide what you want stored in the tables and then decide how best a user will view the data to make it meaningful.

Prior to making any changes, make sure you make a backup.

 

[Kiwiman]

Howzit

By adding the Form_Current code (without changing anything else) to your initial database you uploaded requeries the combobox when cycling through your form records, but does not solve the text v number issue you have

 

[rexmorgan]

Thanks for all the help. You have given me much to consider and work on. Total records are about 1200, it fluctuates some from about 1180 to 1250. I try to keep the database as up to date as possible.

I used your earlier prompt about changing the qrycboCityFromCounty and it seems to function as expected. I see that you used an INNERJOIN. Although I used a couple of these I am still not real certain on the best use of them. But again something to ponder and keep in mind for the future.

Thank you again for all you help thus far and giving me things to consider about possibly starting over from scratch sometime in the not too distant future.

 

[Kiwiman]

Howzit

I don't see there being all that much to change really, even with 1200 records.

Good luck with your project I'm sure you'll get there in the end.

JOINS

When you make a query based on multiple tables you need to join the two tables together. If you don't you get something called a cartesion join where the query returns the sum of both tables. I.e. if you have 2 tables one with 10 records and one with 20 records and you do not join them your query will return 200 records (10 x 20)

An INNER JOIN just says that your query will return records in tableA but only where a record exits in TableB

A LEFT JOIN says your query will return all records from TableA regardless if it has a record in TableB.

There are also Right and Full Joins which I don't really use so I won't comment on them.

 

[Simon_MT]

Can you upload a Access 2007 version, sorry I don't have Access 2010. I create dependencies within the queries for the cascading combiboxes. I start with global regions UK, Europe, US Rest of the World. So if you choose "E" for Europe then only the Countries in the EU are available for selection. And so on ...

All this is done by Functions and dropdown lists.

Simon

 

[rexmorgan]

Access 2007 should be able to open access 2010 files. Unless of course you meant access 2003. I have used some features in the new file format that are not available in 2003 and older versions of access.

 

[Simon_MT]

I may look stupid, but I get an unrecogned format.

Simon