Autofill without combo box?

[thewrightdesign]

Using Access 2003. I'm fairly new to coding in Access/VBA, my background is web design not development but I've been thrown headfirst into this project. I have a database of student information. Main form/table is StudentInfo. Also have forms/tables for Enrollment, Classes, Grades etc. I'm learning as I search that I shouldn't store the same information in more than one table but that's exactly how our current database does it so at some point I will probably need help to fix that issue.

Right now though, what I'd like to have happen is when I go into Enrollment or Grades forms and I add a new record, I want to enter the student ID# and have the first name and last name fields automatically populate with their information. I will then enter the enrollment and grade information in other fields in the record.

I've searched this forum, googled, and searched other forums and most of the answers I see for this involve combo boxes. It isn't really practical for us to use that option since we have thousands of students and don't want to have to choose their ID# from a drop down list. Current database lets us enter the ID# and it populates the name fields for us. I want to recreate that in Access 2003.

I've gotten great help on this forum while I'm struggling through the process of re-creating this database in Access (current one is Lotus Approach). Hoping this issue is solved as easily as the others have been

 

[pbaldy]

You can still type an ID into a combo box; you don't have to scroll/select. I'd use a combo unless you were running into performance issues given the number of choices.

If you want to use a textbox, you'd either open a recordset or use DLookup to get the names. Since you have 2 fields, the recordset would be the winner performance-wise (single trip to the data).

 

[thewrightdesign]

We currently have in excess of 4k students, each with a unique ID# and record. Maybe I don't understand how combo boxes are more efficient when dealing with a high number of records like this. I keep seeing combo box offered as a solution and truly, I don't get it LOL. I don't understand the allure of them when it is so much faster to just type in the exact information you want. But that's me, and remembering I'm new to building databases so maybe I just don't yet understand.

Keeping in mind I am fairly new to this, can you explain how I'd open a recordset for this issue? That's a term I have not yet heard. I have two fields for enrollment that need to autopopulate, and three on grades (last name, first name, and institution if that helps).

 

[thewrightdesign]

Still looking for help on this issue if anyone can explain to me about how to open a recordset to get these fields to autopopulate.

Hoping someone can help, really hitting a wall on this one. If you need more information from me I'm happy to provide it.

Thanks to anyone that responds

 

[missinglinq]

Comboboxes have a Property named AutoExpand. When set to Yes (which is the default setting) as a user starts to type in info, such as your ID number, Access automatically moves to the first matching record, moving as each subsequent character is entered, until the job is complete with the entire ID entered. This does away with the need to scroll at all.

Linq ;0)>

 

[thewrightdesign]

Thanks Linq, the scrolling really isn't my main issue though I'll play with it and try to do it with a combo box I guess since that seems to be what everyone thinks is best. I don't care if I have to use one or not, I just want these other fields to autopopulate, but as of yet no one has given me an idea of how to make that happen. I don't know how to do it with the combo box any more than I know how to do it with DLookup. I'm a beginner at Access, this is my first experience using it and it's not like other coding I have done. I'm not sure where anything goes half the time.

Thanks.

 

[boblarson]

First off, you should not store the first name and last name in the other table but only the ID of the student. You can DISPLAY the other information and here's a sample on how to do that with a combo box and extra fields in its row source:

http://downloads.btabdevelopment.com/Samples/combos/Sample-FillInFields.zip

 

[thewrightdesign]

Thanks Bob I'll check that out. Yeah I knew storing the info in separate tables was bad, I'm working with an existing database though so re-vamping it as I go. It's a nightmare so far. I think they did that for reporting purposes, related to the other issues I'm having with searches. The approach database we are working from is actually a different database for every form we store information in all tied together, rather than one database with multiple tables. I'm having to fix that as well.

I really appreciate the help I've gotten in this forum.