If/Then type autolookup

[mguy27]

Background: I have written a small program to enter brief reports taken by people on various buildings in our complex. They are categorized by time, date, building, and room number. We then build a report from these entered reports and email them on to the corresponding building supervisors. We have over 100 buildings, and thus have over 100 supervisors. To find out which supervisor to email about which buildings, we have a big long list of names next to buildings and look it up manually. Both the building list and superviser lists, right now, are combo boxes.

Problem: I would like to set it up where when I select a building from the combo box, it automatically enters the name of the person I need to send it to, in order to cut down having to search though the list. I already have a table set up with two fields (1. Building 2. Supervisor) and I have each building and corresponding person listed, but I can't find how to incorporate them in to the form. Each report will only have once building and one supervisor.

Any suggestions?

 

[TheAceMan1]

How are ya mguy27 . . .

[blue]I [purple]already have a table set up[/purple] with two fields (1. Building 2. Supervisor) and I have each building and corresponding person listed . . .[/blue]

A combobox/listbox based on the table above will do just fine. If the [blue]rowsource[/blue] is something like:

[blue]SELECT ID, Building, Supervisor
FROM TableName
ORDER BY Building[/blue]

In the [blue]AfterUpdate[/blue] event of the combobox/listbox, [blue]to access the building selected[/blue] it would be (using listbox as an example):

[blue]   Me.[ListboxName].Column(1)[/blue]

. . . [blue]and to get the supervisor:[/blue]

[blue]   Me.[ListboxName].Column(2)[/blue]

You should have no problem making the control with either the combobox or listbox wizard! . . . Give it a shot!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[mguy27]

TheAceMan1, thanks so much for your help, it's gotten me headed down the right path.

The name of the table where we store our massive list of reports is called 'TR Reports 2007'. The field where we want the name of the supervisor to appear is called 'Send'.

The name of the field in the form where I select which building the report is from (the field is a combo box, not a text box) is 'Building' <--Singular

The name of the table where I have the buildings listed in column one (titled 'Buildings' <--Plural) and the corresponding supervisors in column two (titled 'Supervisor') is 'LookUp'.

Here's what I have in my ListBox:
Control Source: Send
Row Source Type: Table/Query
Row Source: SELECT ID, Supervisor, Buildings FROM LookUp ORDER BY Buildings
After Update: Me.[List79].Column(2)

When I tried it as a ComboBox, it would only display a name in the first record (out of 2027), and none of the following. Not to mention, it was the first name listed in the table LookUp (Ellen B.), not the corresponding name to the selected building (which should be Linda R.).

So, I then tried it as a ListBox. It will now display a name in each record, but it is the same name in each, and still only the first name listed in the table LookUp.

Thoughts? Where am I going wrong?

Thanks again, you're a life saver!

PS - I appologize for my thinkheaded-ness, I've never had any formal training in the program, nor do I know the VisualBasic programming language.

 

[TheAceMan1]

[blue]I appologize for my thinkheaded-ness, [purple]I've never had any formal training in the program, nor do I know the VisualBasic programming language.[/purple][/blue]

Although Tek-Tips does require a minimum knowledge level for the fora, we do take all comers. However . . . [blue]not knowing the software nor VBA[/blue] is too much of a gap . . . way too much to try to explain and get wrong! There are already enough problems with the responses you reveal in your latest post.

Peform a search for combobox and be sure to look at combox in the [blue]FAQ's[/blue] of this forum. You need to [blue]bring yourself up to speed somewhat[/blue] if your gonna make any real progress here!. Otherwise it could easily turn into a nightmare . . .

Post your question again when you think your ready. I'll keep an eye out as [blue]your now flagged in my reminders list.[/blue]

Besure to review and fully understand the following links:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

Good Luck!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[mguy27]

As an addendum (or possibly clarification, rather) to my previous post, I could say that I have a minimal understanding of the program, i.e. how forms, tables, queries, and reports all work together; designing each of the aforementioned; and some other bare basics. As for the "ins-and-outs", not by any stretch of the imagination. I've been playing with the program for about a year now, learning on my own by experimenting, the F1 key, Microsoft Online Help, and a text book for a college Access class that I picked up.

But, I will get back to you once I have finished reading the material you suggested. Thanks!!

 

[mguy27]

Ok TheAceMan1, I've read through the materials and feel much more acquainted with the program.

Without knowing the terminology when I first did it, here's what my first attempt at my problem was:
I have a table wherein I have a field (Building) that indicates a particular building. This field is not unique to this particular to this entry, as several entries will share the same Building entry.
I then created another table with an ID # (ID), a list of possible building entries (Buildings), and a list of the corresponding supervisors (Supervisor).
i.e.: 1, ASB, Ellen R.
2, AKH, Sam W.
3, B-77, Donald G.
etc...

I then created a query and formed a one-to-one relationship between the Building form of my main table, and the Buildings form of my lookup table. The problem I then ran into there was trying to get the corresponding person to automatically appear back in the main table.
Example: When I select ASB in the ComboBox on my main form, I want the box below it to automatically read "Ellen R." without me having to look it up and manually type it in.

I also ran into the problem of not getting a single response. The entry would try to list every single name in the "supervisor" column of the lookup table, regardless of the building I had selected--it was not a dependant.

Ultimately, what I'm looking for is something like: IF I select ASB from ComboBox A, THEN display Ellen R. in ListBox B. OR, IF I select B-77 from ComboBox A, THEN display Donald G. in Box B, and so on.

Ideas?

 

[mguy27]

I forgot to mention that in my main table (where all the reports are entered and recorded) I already have a primary key, which is an autonumber assigned to each report. The autonumber is the only thing that makes each report in the main table unique from all the rest.

 

[TheAceMan1]

mguy27 . . .

Trying to finish a few posts so I can give ya my full attention!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[mguy27]

OK, breakthrough...sort of. The person the report needs to be sent to now shows up in the form automatically. I did this by creating a TextBox with a DLookup function as the ControlSource.

Problem 1: It does not automatically update right away. I have to close the form, then reopen it, and then it will display.

Problem 2: Since the ControlSource is not bound to the main table, it will not record the name in the main table.

I guess my next steps will be to 1) try to get that autolookup to record the name permanently in the main table, and 2) get the name to update immediately in the form.

Ideas? Thoughts?

 

[mguy27]

Anyone have any ideas for me?