MS Access help!

[winterbadger]

OK, if any of you do MS Access database stuff, you can make my week by explaining what simple thing I'm missing. If you don't do this stuff

I've been a good boy and decomposed my database so it doesn't have dependent information in the same table. But I want to show some of that dependent information on a form.

Let's say my database is about rabbits that belong to college fraternities (why that example occurred to be I have no idea). I have a data table of rabbits, with each record listing the rabbit's name, his breed, his home address, his favourite food, a code for the college he attends, and a code for the fraternity he belongs to. I have a separate table listing all the colleges, and a third table listing all the fraternities. And then I have a table that has the actual Greek letters of each of the frats.

So, now I have a form that allows the user to display or create records for rabbits. And they can choose from a drop-down list which college he belongs to. But after they choose the college, that automatically tells me which frat he belongs to (since each college only has one frat that allows rabbit members), and if I know which frat he is in, I know what the right Greek letters are for his frat. So I want to add two fields to the form that--once the user picks a college--shows the name of the frat and its Greek letters. And I can't for the life of me figure out how to do that.

Because what I need to do is to pull from two other tables values that are determined by a value in the data record I'm viewing in the table the form is bound to. I have relationships set up that link the college key int he rabbit table to the principal key in the table of fraternities, and a relationship that links the fraternity name to the principal key in the Greek letters table. But I can't figure out how to get the form to show the right fields.

Any suggestions?

Comments

[janewilliams20.livejournal.com]

It's been a while since I used the Access interface for anything - I use it as a back-end for web-based things. Let me know what version of Access you use, and I'll see if I can do some experimentation over lunchbreak.

[winterbadger]

Any suggestions you can make would be very welcome. I've been trying to see if I can use a DLookUp query to fill the field, but I'm having trouble getting the syntax for that right.

I'm using Access 2000.

[janewilliams20.livejournal.com]

I'm running Access 2003, so my specific solution won't help (it involves the use of wizards that you probably haven't got).

The general concept, however, will. I bet you're trying to power your drop-down and look-up off a table. Don't. Create yourself a Query, called something like "rabbit-friendly colleges".

The Sql for the query looks a bit like this:
SELECT colleges.college_id, colleges.name, frats.frat_id, frats.name, frats.letter
FROM colleges
INNER JOIN frats ON colleges.college_id = frats.college_id
WHERE (((frats.allows_rabbits)=True));

If you're right about there only being one frat per college that allows rabbits, you can now use that to build a look-up that uses college_id as the key and displays as much other data as you like.

That's a step forwards, at least: sorry, I haven't used the Access interface as a front end for many years, and I can't tell you how to make things any more sophisticated.

[winterbadger]

Thanks! That may be the key I need. I've gotten as far as working out how to get DLookUp to display values from other tables in the current form, but I am still struggling with getting it to pick a response based on the correspondence of a form value. You may have presented me with a way of doing that. :-)