Access Newbie - 1 Form, 2 Tables Combobox Hell!

[pointacula]

Hi all.

I've just begun to use Access (2003, Win XP) to generate school reports for my wife's job (teacher). It was all going so well:

Created table 1 with fields for Forename, Surname, Class and Comments. This is the table that will be filled in from the form.

Created another table with the Form Class, Curriculum and Report term (eg autumn 08 or summer 09). This is a store of certain information that will not change, but it is a bit repetitive and applies to different kids. It is to be displayed on the form to help in completing it and will be displayed on the report when it is printed.

I managed to create a form that allows for the input of names, selecting a Class from the drop down menu (combo box) and input of Comments.
The combo box takes the Form Class data (eg. J3M) from table 2 and fills the Class field in table 1 in an attempt to link the tables together to correlate the data.

Then... my ingenuity failed me and I've been stuck for days!

Ideally, I'd like the combobox in the form that selects the Form Class to auto fill 2 other fields in the form with the corresponding Report term and Curriculum data from table 2 that it relates to.

I've tried searching this forum, and nearly got the right answer a few times but every one of those seemed to be a step ahead of me!
Like this one.

Any help that you could offer would be gratefully received.

Cheers

Mark

 

[jepoysaipan]

Hi,

Attached is a DB, hope this helps. Although I get confused on your requirement since you want to fill 2 fileds based on selected Class but where do you plan to store the info.?

Just let me know in details the concept you have and I will try to help you.

Jeff

 

[pointacula]

Thanks a lot jepoysaipan!

That's pretty much exactly what I wanted, It's that visual basic stuff that foxed me (I didn't do it that way...). That's the bit I couldn't do!

I have to admit, I'm a total beginner (as in 3 or 4 days experience) and I was waaaay off if your solution is anything to go by.

I think I can make Access do my bidding now with a bit of fiddling with the db you sent me.

Cheers again

Mark

PS> I'm hoping that creating the report to print the data is a similar process to making the form, but might need a bit of help if I continue to work so cack handedly! In other words, keep your eye out for any further replies to this post!

 

[jepoysaipan]

Hi,

I've updated the attached DB and include a Query and Report.

Changes:

1. Created a Query named "Query1" merging all the fields in both Table.
2. Created a Report based on "Query1".

Let me know if you need more assistance!

Happy Halloween!

 

[pointacula]

Thanks again but your first db more than sorted me out!

I've got it working perfectly now. All credit to you, obviously...

I can tell you that I'm glad I don't have to deal with this program every day!

Mark

 

[jepoysaipan]

Just bringing back all the help I got from this forum, I remembered the days when I was starting on Access 97.

Glad to be of help.

Cheers