Autofill form using only one table.

[MrRadio915]

About every 5 years I'm asked to work on a database, and I'm overdue.

I've been asked to modify a DB that is a classical music database containing information like composer, CompBirth, CompDeath, CompNationality. (Comp=composer). The table also contains song title, artist, conductor, CD number, and numerous other fields.

Without splitting the table into Composer info and song info tables, is it possible to "self query" the table and autofill its related form with the Composer name, Date of Birth, Date of Death, and nationality?

Any resources or help would be greatly appreciated.

 

[Norie]

What do you mean by autofill?

If you created a form using a query that only returns unique records with the fields you want as the you can just add the fields to the form.

That's about it, unless I'm missing something.

The query would look something like this.

SELECT DISTINCT Composer, CompBirth, CompDeath, CompNationality
FROM CompTable

 

[MrRadio915]

Wow, Norie. Thanks for the fast response.

By auto fill, I'm wanting to begin typing the Composer's name and possibly have a combo box drop down populated with leading characters and revealing dates and nationality - then populating the new song record with that information for that record only.

Right now, the table has almost 18000 individual song entries. Each record has:
ID Key
Composer
Born
Death
Nationality
Title
Length
CD Number
Conductor
Score
Key
and a few others.

To speed data entry, and improve accuracy, I'd like to populate the fields that don't change for a given composer. (Name, nationality, birth/death)

The form is already designed. I'd like to modify what's happening in the background to make this work (if it's possible).

Had I designed the DB initially, I would have made a composers table and a song table. I inherited this project, and will hand off the use of the DB once it's completed.

More than likely, the users of this DB will be computer novices.

I hope that sheds some light.

 

[Norie]

I don't think this can be done, easily anyway, without modifying something.

Ideally that would be the database - as you realise there should be 2 tables (at least).

If that's not possible then the only alternative I can think would be to change the form to a form/subform.

The form would hold the composer details and the subform the songs associated with the composer.

If set up correctly, and perhaps with a little code, whenever a new entry is made in the song subform a 'complete' record is created in the main table.

 

[boblarson]

I am only giving you this suggestion since it appears that you either can't, or won't, redesign the database to be normalized. So, you can "autofill" the details by having an unbound combo box on the form. You would include all of the composer related fields from the table in it and would use a SELECT DISTINCT to only return the record for the composer once like:

SELECT DISTINCT Composer, CompBirth, CompDeath, CompNationality
FROM TableNameHere
ORDER BY Composer

And set the combo's Column Count property to 4.

Then in the AfterUpdate event of the combo box you could use something like:

' checks to see if there is anything there, and if not it does this adding to the current record
If Len(Me.Composer & vbNullString) = 0 Then
   Me.Composer = Me.ComboNameHere.Column(0)
   Me.CompBirth = Me.ComboNameHere.Column(1)
   Me.CompDeath = Me.ComboNameHere.Column(2)
   Me.CompNationality = Me.ComboNameHere.Column(3)
End If

 

[MrRadio915]

I need to keep it as simple for the end user as possible. To say that the user is a novice, is an understatement.

I'll try the code tomorrow.

Thank you VERY much.

 

[Norie]

Setting up a form/subform might make things easier for the user.

All they would need to do is pick the composer and start inputting songs.

They could enter all the songs for the composer, then move onto the next composer, enter all there songs and so on.

 

[MrRadio915]

I agree that the form/subform format may be intuitive for you and me, but trust me, the end user is challenged - and unmotivated to learn... sigh...

Because by twiddling with Access is so infrequent, Bob, could you point me to the right place to enter the code...

thanks.

 

[boblarson]

I agree that the form/subform format may be intuitive for you and me, but trust me, the end user is challenged - and unmotivated to learn... sigh...

Because by twiddling with Access is so infrequent, Bob, could you point me to the right place to enter the code...

thanks.

Then in the AfterUpdate event of the combo box

Select the combo box when in design view, go to the Event tab in the properties dialog and for BEFORE UPDATE select the drop down which says [Event Procedure] and click the Ellipsis (...) and when the VBA window opens to that event - that is where you put the code. And remember that the combo's columns are zero-based so if you want the second column you refer to Column(1), etc.