Populating fields from combo box selection (SR2)

Topic

New Reply

I have a form in my database that is going to contain location information. I would like to create a dropdown list for the user to select the location name, and then the remaining 6 fields that pertain to that location would be populated based on the selection in the dropdown list. Make sense?

In other words, the location for McLane Lake is at xxx mile marker, in SectionX, TownshipX, RangeX and consists of a total acreage of x. I want to make the dropdown list so the user would select McLane Lake and the other info would be populated in the appropriate fields (which I have already created).

Preface: I do not know Visual Basic. How can I accomplish this task?

Thanks!

Reply | Quote

Replies

One way to do this is using the Column Property of a Combo box. They normally are Zero based so the second column would be referenced this way
=[comboboxname].Column(1)
If you put this in the Control source for a textbox, it will return the value in the second column of a combo box. To reference the rest, you woul just change the number in the ( ).
Paul

Reply | Quote

If I could jump in here for a minute, I’d like to find out what this Boards opinion is. You can accomplish this problem 2 ways. The Column Property Method is the first way. The second way is to use a query that has two tables (tables that are 1 to many) in it. An example is EmplyeeTable and AttendanceTable. EmployeeTable has a 1 to Many relationship with the AttendanceTable on EmpID. Now in the Query that supports the Attendance Form, you use the EmpID from the Many table and the LName, FName…etc. from the 1 Table. In the Form and Query, this will give you that INFO (LName, FName, Address..etc) but it will not store it back to the Table. That is good. It’s normalized, but I rarely (actually Never) see this method endorced over the Column Method. Is there a reason. It’s purely professional. Thanks
Paul

Reply | Quote

Sorry, Paul, but I can’t quite understand your second method. Are you suggesting populating a combobox from that query or a form? If you’re talking about running a query to retrieve the related values based on the combobox, the reason you don’t see it is that it is inefficient. You’ve already executed one query to populate the combobox, so it’s more efficient to simply grab the information from there and display the related values. If you’re suggesting basing the form itself on that query, you may very well find that it displays just fine but isn’t updateable. Or maybe I totallly misunderstood what you were trying to describe.

However, I would never try to store the additional information in the second table. The normal use of multicolumn comboboxes is to populate controls to *display* additional data but not to store it in the underlying fields, since that would violate the rules of normalization.

Reply | Quote

The gist of this is to populate text boxes on a form using information in an underlying query as opposed to using a ComboBox and it’s Column Properties. In my example of the Attendance Form, I have the EmployeeID in the Combo Box. To populate the LName, FName, Supervisor…ect fields in the Form, I added the Employee Table to my Query and joined on the EmpID. Then i added the LName, FName…fields to the query from the Employee Table. In my form, those fields show up on my Field List so I can add them to the form and the values show for whatever EmpID is selected. It just accomplishes the same thing as the Column Property but a different way. The reason I like it is because I don’t have to add the information to the ComboBox to reference it. It’s right in my query.
Maybe nobody mentions it because it’s so hard to describe.
Thanks for your thoughts.
Paul

Reply | Quote

No, the reason is the one I mentioned. Adding fields to a query just to display the information from another table can very easily make your query/form read-only. That problem isn’t so accute in 97 but in Access 2000 the query engine changed somewhat and doing that will keep you from editing the main fields in the recordset entirely.

Reply | Quote

Could someone look at this database for me? I removed everything but the 2 tables and 2 forms that are giving me grief. I created this database and asked the original question in this post.

All I am trying to do is set this up so the user can open the LandsLocation form, Click on the drop down list for Location and have the fields populate from the LandsLocation dropdown table into the LandsLocation table. Any ideas? I tried a change event on the combo box, but I get an error about Variable not defined. I am almost totally ignorant when it comes to code. I borrowed the idea of this code from MarkJ in a previous post.

I tried attaching it, but I just can’t make it any smaller than 118K.

Thanks!

Reply | Quote

If you only have two forms and 2 tables, you should be able to get it smaller than that unless the tables are huge. In that case, you probably should trim them down anyhow, but did you zip the file before you tried to attach it? If so, try using a decompile and then compact your database before you zip it.

Reply | Quote

If youcan get it attached, I’ll look at it. My e mail is pbricker@mediaone.net
Paul

Reply | Quote

Thanks Charlotte. Your point about making it unupdatable is correct. I haven’t run into it with the simple data entry Forms I’ve built, but I thought getting a second opinion would be good, and it was.
Paul

Reply | Quote

Hi Charlotte. I just looked over Melanie’s Db and we did a bit of chatting. She found that if you go over 6 columns in 97, the Column Method breaks down. I took the lazy way out and decided to ask if you were aware of any limitations on the Column Property? Thanks for any input.
Paul

Reply | Quote

I’m not aware of them, but I don’t try to use comboboxes that way. It makes more sense to do something that extensive with code instead. In Access, there are practical limits on most things, and you usually discover those the hard way.

Reply | Quote