Using a List Box to Select Two values (Access 2000)

BlueChicken · Jul 9, 2009

Ok, I am becoming increasingly thankful for this site!

I few days ago I made a report that uses sub forms and expressions to 'report' in letter form an employee's trip information. My boss was really happy and liked it, and I thought that was that but late yesterday afternoon she asked for the hotel's phone number to be displayed along with the hotel's information.

I have already went back into the table I made for the 'Booked Hotels' to add the 'PhoneNumber' field - then using relationships linked the phone number in booked hotels table to the phone number in the Master Hotel list.

Then, I added in the 'Phone number' column to my query, added it to my form and added it to my sub form in the final report.

And NOTHING shows up... On the form I was able to make the original list box for the Hotel name be directly linked to the phone number but everywhere else the hotel name and phone numbers are not linked... and that could mean the wrong phone number will be chosen from the drop down list when entering the information...

I am completely at a loss here.

pbaldy · Jul 9, 2009

BlueChicken said:
I thought that was that

You're so naive. That is NEVER that; bosses always think of new things they want added. Think of it as job security.

I would not expect the phone number to be stored in more than one place. I would have it in the master hotel table, along with their other info. You would get the phone number by joining the two tables together on the HotelID field, or whatever it's called. More info about your table structure might clarify things.

BlueChicken · Jul 9, 2009

pbaldy said:
You're so naive. That is NEVER that; bosses always think of new things they want added. Think of it as job security.

Well to be fair I knew on some level she would want more... but I really just wanted to hope that the insanely problematic form was all complete and done with... I had to get lots of help just to get it working in the first place

I attached some pictures of the tables and forms and wrote all over them... hopefully that will help. Sorry about the zip though, the files were too big to post like normal.

Kryst51 · Jul 9, 2009

One thing I would suggest, is making the primary key of your hotels table an autonumber. Then do the lookup in the other table with the auto number.

What if you had a form that had hotels as the main form with a subform for the bookings? Then it would show all the info you needed.

pbaldy · Jul 9, 2009

Again, I wouldn't have the hotel's phone number in the booked employee hotels table. Your combo box on the form would have something like this as its row source:

SELECT HotelName, HotelPhoneNumber FROM Hotels

The bound column would be 1, so the hotel name would save to the booked employees table. If you wanted to display the related phone number on the form, this in a textbox:

=ComboBoxName.Column(1)

At some point you may regret separating the date and time fields. I did the same thing in my first database, but now always put them together. Probably depends on how much querying of the data you have to do.

BlueChicken · Jul 9, 2009

Kryst51 said:
What if you had a form that had hotels as the main form with a subform for the bookings? Then it would show all the info you needed.

The sub form would not work because of the way I have all my forms linked together. I do not want to have the Hotels form show up at all. The only reason that the Hotels form should be open is to add another hotel to our list or update the information in in.

BlueChicken · Jul 9, 2009

pbaldy said:
Again, I wouldn't have the hotel's phone number in the booked employee hotels table. Your combo box on the form would have something like this as its row source:

SELECT HotelName, HotelPhoneNumber FROM Hotels

The bound column would be 1, so the hotel name would save to the booked employees table. If you wanted to display the related phone number on the form, this in a textbox:

=ComboBoxName.Column(1)

Ok... I really hate to sound dumb but you lost me...

Kryst51 · Jul 9, 2009

Something Like this?

BlueChicken · Jul 9, 2009

Kryst51 said:
Something Like this?

Ok, 1 - That is amazing.

2 - How on Earth did you do it?

Kryst51 · Jul 9, 2009

First, on your form you have the Hotel ID, which is a foreign key in the table "Booking" (Primary key in the hotel table) pointing to your hotel table. That is the most important thing. Oh, by the way, Use autonumbers as primary keys for all of your tables. This keeps life easier when your database gets more complicated.

When you create a form using the wizard from the "Booking" table go to design view. Delete the field "Hotel ID" and create a combo box. This will start a lookup wizard. See Attachments for sequence.

Then Create another combo box for the phone number follow the second set of screen shots.

Then Save.

BlueChicken · Jul 9, 2009

Ok, I copied the code you used in yours and adapted it to fit my naming order... however I only end up the the ID numbers instead of the words... what did I do?

SELECT [Hotels].[HotelListAutoID], [Hotels].[HotelName] FROM Hotels ORDER BY [HotelName];

Kryst51 · Jul 9, 2009

Try following my screen shots.

BlueChicken · Jul 9, 2009

Kryst51 said:
Try following my screen shots.

Sorry I didn't refresh in enough time to see your newest post.

I followed your screen shots and now have it working... but I am a little confused.

How would I get them to display on my report? I can't just used the HoteListAutoID right? Am I going to have to do the same thing on the report or will I have to do something different?

Kryst51 · Jul 9, 2009

Here are some screen shots and my example. The phone number combo is created the same way as on the form.

BlueChicken · Jul 9, 2009

Thank you for all your help. Your screen shots and sample databases were insanely helpful!

I have it working perfectly... now hopefully nothing else will have to meander its way onto my forms.

Thanks!

Kryst51 · Jul 9, 2009

You are so very welcome! I am glad it helped.

Using a List Box to Select Two values (Access 2000)

BlueChicken

Usually Confused

pbaldy

Wino Moderator

BlueChicken

Usually Confused

Attachments

Kryst51

Singin' in the Hou. Rain

pbaldy

Wino Moderator

BlueChicken

Usually Confused

BlueChicken

Usually Confused

Kryst51

Singin' in the Hou. Rain

Attachments

BlueChicken

Usually Confused

Kryst51

Singin' in the Hou. Rain

Attachments

BlueChicken

Usually Confused

Kryst51

Singin' in the Hou. Rain

BlueChicken

Usually Confused

Kryst51

Singin' in the Hou. Rain

Attachments

BlueChicken

Usually Confused

Kryst51

Singin' in the Hou. Rain

Similar threads

Users who are viewing this thread