Discussion Locked

Looking for Access 2000 Code(VB)

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a table that looks like a mileage matrix; the first field is labeled as "key" and it is filled down with city names. The rest of the fields across are labeled as the cities in the "key" column, and they are filled down with a number that represents the mileage from the "key" city, to the field city. I want the user to choose two cities from a form, and the result in another field would be the mileage. I don't know how to pull just the mileage from the table. I can send anyone that can help me the table. email marcial.rodriguez@state.nm.us

5 replies

by steved

This code makes some simplifying assumptions about your form, namely that there are three textbox controls. In Text1 and Text2 the user will type the from and to city names. In Text3 will be displayed the mileage. In practice you'll probably want toreplace Text1 and Text2 with comboboxes or listboxes.

Here's code that should do the job:

Dim db As Database
Dim rs As Recordset
Dim strFromCity as String
Dim strToCity as String
Dim strSQL As String
Dim strMiles as String
Set db = CurrentDb
strFromCity = myForm.Text1.Text
strToCity = myForm.Text2.Text
strSQL = "Select " & strToCity & " From MyTable Where Key = '" & strFromCity & "'"
Set rs = db.OpenRecordset(strSQL)
strMiles = CStr(rs.Fields(strToCity))
rs.Close
Text3.Text =strMiles

I hope this helps.

Steve Diamond

by articulated mandible

Steve's answer is good, given your design. But if it?s not too late, I recommend you consider changing the design. There is potential for real problems down the road. Because you have a column for each entry in the ?Key? field, adding even one record is a hassle ?add the new "key" city, add mileage values for every other city already there, add a new column, then update every record to populate the new column. This is a significant amount of coding up front, as well as a a lot of data entry when a city is added.

The fact that the table structure has to be changed every time a record is added has the potential for a huge headaches, not the least of which is the fact that you are limited to 255 fields, and thus to 255 cities. Even if that?s more than you?ll ever need, I wouldn?t want to be the one typing in 254 mileage values. Nor would I want to design the data entry form.

A simpler setup would be to get a map, overlay it with a grid, and enter the x and y coordinates for each city. You can then calculate the distance between any two cities on the fly:

D = Sqrt[(x1-x2)^2 + (y1-y2)^2]

With a map scale of 1 in =100 mi, and half-inch grid, you can get distances to within 100 miles (50 on avg). For more accuracy, use abigger map, or finer grid. The drawback is that the person entering data must have access to that map. Also, if your entries are more than 3-4K mi. apart, the scale of any reasonably sized map, along with the earth's curvature, will raise the marginof error considerably.

For real accuracy, you can use latitude and longitude to calculate the distances. This is trickier than the grid method, but L/L numbers can be had from any atlas, or from the internet. If you?re interested, I?ll give you some sample code(40 lines or so), or you can look at:

http://www.uottawa.ca/academic/arts/geographie/lpcweb/web2320/exercises/extras/greatcircle.htm

This discussion is closed to new replies.