About to lose my job

[elloydtke401]

Hello,
I have been to just about every forum under the sun trying to have this question answered for me, and I think the reason it is not being answered is because it is too "easy" and people think I am not trying to find the information on my own. Not true. I am very good with MS Excel, unfortunately, our company database has grown to the size that Excel has become too cumbersome an avenue to travel anymore. I am now trying to learn MS Access. I took an introduction course hoping to learn things I needed to design our company database and I have, although one minor snag has occurred. The boss wants a "drop-down" box (I believe they are called "combo-boxes" in Access) to display Company Names he can choose from when filling out quotes to customers. Upon choosing a company name, he wants 3 other boxes on the form to display contact, phone, and fax information. I have tables designated with Company Names, Contacts, Phones, and Faxes. The problem is, Each company has multiple contacts, phones, and faxes, so each box has to be a combo box, that updates itself with only the Contacts for the chosen company, only the phone and faxes for the chosen Contact. Through my travels around discussion boards I have found out that I need 2 Tables, One with just company names in it, and the other with company names, contacts, phones, and faxes in it. I'm not really sure how I should relate them at all? I have read tutorial after tutorial, but it seems like they were all written by the same guy/girl...one that doesn't speak English. I have a hard time understanding the terms they use and really need someone to lay it out for me. By that I mean, assume when replying to this post that I don't know anything about computers. I also understand that I have to put a code in the "AfterUpdate Event" of the boxes...whatever that means??? I am thoroughly frustrated and if I sound short in this post I really don't mean to. Also, any help that could be given in this matter would be EXTREMELY appreciated. Thank you for listening to my rant and I hope that I can repay this some day.

 

[elloydtke401]

My God! Someone finally helped me with this. It is really easy now that I understand it. I was making the mistake of limiting the number of columns shown in the "Company Name" drop-down. And I finally understand the use of "AfterUpdate".

 

[Garyj]

Did someone answer all of the questions?

 

[elloydtke401]

Yes, I am a fool. But thank you to all that read this thread. And especially to BlueClaw. It is a very simple answer. I was thinking about it the wrong way...sometimes you just need someone to help you see the play from a different camera angle I needed one table to list the Company Names, and one table to list Company, Contact, Phone, and Fax. Then, I just needed to direct the form as to which boxes to display the information. I believe that I am duplicating the Company Names and some of the phone and faxes too much in the Contacts table...but you know what? It works and I have been mulling over this problem for way too long to try to fix that right now. Thanks Again!

 

[EVP]

Congratulations!
Can you share your solution in more detail? I am an amateur db builder, but unfortunately know too much about what access can do, without being able to do it.

When a user types in a value in one field, I want the following combo boxes in the fields to only display the values that I dictate are available. (i.e. I'm dealing with sites, doctors, and specific Provider Id's for one doctor varying with multiple sites).

Thanks!

 

[elloydtke401]

More detail

Well, as you can tell, I haven't figured out a way to not repeat values in the database, I'm still working on that, but to start, I can tell you how I did my form.

1. Create a table with every doctor's name followed by their Provider ID for each location they could be at.

IE:
Name ID
Issac Bruce Location1 ID
Issac Bruce Location2 ID
Kevin Johnson Location1 ID
Kevin Johnson Location2 ID
Kevin Johnson Location3 ID
Terrell Owens Location1 ID
Ed McCaffrey Location1 ID
Ed McCaffrey Location2 ID
etc...

2. Create a form with a combobox which pulls it's info from the table above based upon the name category. Then go to the properties for the combobox and click the "All" tab. Scroll down to the "Column Count" column and set it to 2. Then scroll to the "Bound Column" column and set it to 1.

3. Create a regular textbox and place it on the form in the place where you want the ID info for the doctor selected to appear and name it ID.

4. Go back to the properties for the ComboBox and click on the "Event" tab. Click on the "After Update" selection and then click the button with the 3 dots on it (...). That will bring up the event builder. On the screen you should see

Private Sub Combo(a number generated by the program)_After Update()
End Sub


Place your cursor in the line between the "Private Sub..." line and the "End Sub" line. (Hit Enter on the keyboard after "Update()" if there isn't a space between those lines already.) Type in the code

Me.ID=Me.Combo"Whatever#".column(1)


When you are all done, your code should look like this:

Private Sub Combo#_AfterUpdate()
Me.ID=Me.Combo#.column(1)
End Sub

The end result for all of this is when you click the drop-down for the Name field, you will be given a list of names followed by corresponding ID #'s based upon the table you created. Although the names will be repeated for each ID, when you choose a name and corresponding ID, the ID will appear in the textbox labled ID. That's how I solved my problem. I really wish I knew a way to be able to choose a name in one field and only have corresponding ID#s appear in the next drop-down, but this is the next best way. I hope this is of some help to you!

 

[modest]

I dont think I would ever help anyone out, when they title a thread like this. If I ever see something similar, I won't even look at it.

 

[LisaRae007]

Not to confuse matters, but I am wondering if your database would be more powerful and flexible for future uses of the data if you used some of the relational perks of using the db rather than excel. For example, have a table of doctors, a table of locations, and a table in the middle somewhere tying it together (ie-Dr.Man works at location 1 and 2 while Dr.Woman only can be found at location 1). You will have to rework your queries, etc but I think in the end, managing it all will be much easier for you.

 

[Rich]

The thread's over two years old,why answer it now?

 

[LisaRae007]

Good point. I realized afterwards that it was old. I suppose me putting that out there was almost as useful as posting a question like yours. Goes back to the old expression, "why ask why?". I guess maybe rhetorical question postings are a good way to get 10K postings out there. I have seen your postings around, finally I get personally razzed too! Yae!

 

[Rich]

Good point. I realized afterwards that it was old. I suppose me putting that out there was almost as useful as posting a question like yours. Goes back to the old expression, "why ask why?". I guess maybe rhetorical question postings are a good way to get 10K postings out there. I have seen your postings around, finally I get personally razzed too! Yae!

If you think that's how the post count got to 10k+ then you need to do some more research here, I was just curious as to why you picked the post out.
Oh and the true post count is over 20k by the way

 

[LisaRae007]

Obviously, judging by your reaction, you have placed a lot of value in your post rank. You shouldn't talk down to people though. The only reason people come to the forum is to get help and find answers. I know, you have lots of postings in other portions of this site. And yes, I did look you up before I sent that comment. All I'm saying is, leave the unhelpful comments and condescending remarks for private messages or the Watercooler.

 

[Rich]

Skip the lecture, I've been posting help on the main boards for years and I didn't bring up the post count, you did.
I didn't talk down either, again I merely asked a question, something I've done for years and intend to continue with especially if it helps get a clearer picture of the posters intentions

 

[chrisd49]

Hi All
It's now nearly 4 years for me reading this post and the answers given have helped me with exactly the same problem, so I for one am grateful for any advice which helps. And it was the title of the post that made me look. I would like to thank whoever helped Elloydtke401 with his post because they have now helped two people.