Autofill form using only one table.

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 20, 2005
Messages
24
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.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964

ADVERTISEMENT

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:

Code:
' 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

New Member
Joined
Dec 20, 2005
Messages
24
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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 said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top