Access Combo Boxes

[5556]

I am making a database that tracks the miles an employee puts on his car so he can be compensated for gas. I designed one big table with all the locations he travels to as the rows and also as the columns. On a form I want two combo boxes which he can input the locations he went to and from and then a command button that will calculate the miles and the amount of money he should get and possibly put it into a report. Is this possible to do?

 

[scriverb]

First of all I would suggest that you redesign the table that you indicated. Create a table with three fields:
LocationFrom, LocationTo, Miles

Now each combobox can access the same tble with different queries.
Combo1: Select * from tblTravel as A Group By A.LocationFrom;

The combobox1 Bound column is 1 with the ColumnWidths property set to 2;0;0

Combo2: Select * from tblTravel as A Where A.LocationFrom = FORMS![frmYourFormName].[Combo1];

The combo2 Bound column is 2 with the ColumnWidths property set to 0;2;0

The AfterUpdate Event Procedure of the Combo2 should read:
Dim vMiles as Long
vMiles = Me.Combo2.Column(2)

You can change this code to place the miles value in anohter text control or whereever. The Combo2.column(2) value has the miles between the two locations.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Rolliee]

yes. send me a zipped small copy of your mdb and I will return it with some code to do this. If the tables are big, make a copy and delete much of the data. I am dial-up and will not accept big files.

rollie@bwsys.net

 

[scriverb]

Rollie, pardon my comments here, but taking the solutions behind the scenes and into email communications is really frowned upon here at TT because no one else besides you and the original poster can see the results.

I realize that there are times when things get so confusing after many postings that it is necessary to actually see the db from the poster to get your head straight as to exactly what the problem is. But, you should be making every attempt to solve the problem in the forums so that others can benefit from the conversation and resolution.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[judgehopkins]

Bob, could you please tell me where that policy is posted?

In fact, I would like to be able to read all the rules posters are supposed to follow.

But I just cannot find them on this site...which is, in my never-to-be-humble opinion, the greatest Access forum on the internet.

Thanks!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[scriverb]

I have seen them in different locations and I am quite sure there is one here for ACCESS but the following should give you an indication of what I am referring to:

FAQ222-2244

Item 12 in this FAQ refers to what I posted earlier in this thread. I am still searching around for the one that is posted here in ACCESS. If I can't find it then maybe I will post one myself using the FAQ above as a guide. I know others have posted concerning this issue before.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[judgehopkins]

Bob, thank you for that FAQ which I have, in fact, read quite thoroughly shortly after I started using Tek-Tips. Furthermore, I agree with it wholeheartedly.

But, alas, you have answered a question I did not ask.

The author of the FAQ appears to me to be a private person who is giving some really good advice. Is that correct?

If the poster of the FAQ is "just" a private person (like I am) then his opinion is no better or no worse than mine.

If the poster is a private person (and not an official representative of this site) then you did not answer my original question: Bob, could you please tell me where that policy is posted?

In conclusion, all I am asking is for a cite to a list of rules instituted and maintained by this site so I can read them.

Thanks!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[scriverb]

I understand you concern for the official rules. But, alas, I cannot find them. I have posted a thread in the TT RoundTable forum asking for an official link. Have not received confirmation of such. What I do know that through numerous discussions and threads here on TT there is a unofficial list of do's and don't. They all seem to be on the same wavelength in that they are all attempting to make it easier for the poster as well as the TT member answering the question to arrive at an answer and make that information available to the entire TT community.

I will keep you posted on my progress with this search.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[fneily]

To 5556:

What you are doing is good for an EXCEL spreadsheet that does everything you want, if you want to track it that way. However, it seems you're unfamiliar with Access. Did someone tell you to use Access? As Mr. Scriverb is implying, you should do a process called Normalization before anything. Access is not something you just jump into without QUITE a bit of studying.

Neil

 

[judgehopkins]

Neil...I think we certainly all agree with you!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.