Autofill in a subform

[Dchall_San_Anto]

I manage a self-made database of property deeds for my business. Since it often happens that there are multiple owners and multiple buyers of these properties, and that many of the owners and buyers are the same people from time to time, I think I can manage this database more efficiently by using subforms that autofill when I start to type a person's name. I hope this is possible.

My main table and form are based on the deed description. Each deed will have unique buyers and sellers and each buyer and seller can be on many deeds. Thus far I have created tables and queries for sellers and buyers. The seller subform is set up and can accept entries in the form view. When I scroll from record to record, the names seem to show up where they belong, so I'm happy with that. The problem is that when a name comes up a second (or more often) time, I end up typing the entire name in again. This sort of defeats the purpose of having related tables. How can I get it to recognize that the name is already there and perform an autofill for me? What am I missing?

 

[Pat Hartman]

Buyers and sellers should be in the same table since on any given transaction their roles could switch. You would then need to add role (buyer, seller, agent, lawyer, etc) when you assign each person to a transaction.

To answer your specific question, use a combo box. That will automatically scroll as you type. When you see the name you want you can tab and the rest of the field will fill. The autofill will stop as soon as you type a character that doesn't match something in the list. For example, you have a Smith, Jane and you enter Smith, Jo - as soon as you enter the o, the background of the combo changes and you can see that no matches have been found.

Some words of warning/wisdom.
1. Don't use name as your primary key - use an autonumber. The autonumber will be your bound field in the combo and that is what will be stored with the transaction. Not the name of the person.
2. Include something else in the combo so that people with identical names can be distinguished. For example -
Select PersonID, LastName & " " & FirstName & " -- " & HomePhone As FullName
From YourTable
Order by FullName;

 

[Dchall_San_Anto]

Ooooh, people do have the same names...hadn't thought of that...'til now. I really like the idea of making the phone number part of the full name; however, these deeds go back to before the telephone was invented. Some are in Spanish from before Texas was a state. I need to think about this a bit.

Okay, combo box. Do I create the combo box form and then drag it into the main form? I'll try that and see what I get.

One table for both buyers and sellers? Sometimes a seller will sell to himself, as happens when someone dies leaving an estate to the children and all the children get together to sell the property to one child. That gives a situation where buyer and seller are the same person. Is that a problem? I'm still thinking I'm going to have a combo box subform for sellers and a combo box subform for buyers on the same main form. Is that correct? The role would be a set of radio buttons, right? Except for when buyer = seller. Are you sure I don't want different tables for buyer and seller?

 

[Pat Hartman]

Buyer and Seller are roles of an entity which might actually be a corporate entity, not just an individual. Use a single table. When recording information, you will be creating a basic transfer record that identifies the property and the date of the transaction. The parties to the transaction are added one at a time as separate records along with their roll in the transaction. So, yes, the same person may be both a buyer and seller at the same time. He will be entered twice. Once as the seller and again as the buyer.

 

[Dchall_San_Anto]

I've been working on this for a few days and it seems I haven't made much progress.

1. The dropdown list defaults to display the autonumber and not the name. By fiddling around with the properties I finally got the drop down list to display names, but it still displays the autonumber. When I click the drop down button to select a name, only the number appears. I sort of need the name.

2. Before I went to the combo box I had a data sheet view. It was nice to see the names of the various buyers and sellers without having to scroll through the list. Is it possible to display all the names of the sellers for each deed?

3. The combo box doesn't autofill. Is there something in the properties that needs to switch that on?

 

[boblarson]

1. Your combo box can be set to display whatever you want, yet be set to store the value of a different item in the same rowsource. To do this, click on the combo box (while in design mode) and go to PROPERTIES. The rowsource is usually a query, but can also be set to a table (not really recommended as a query gives you more options, such as sorting, concatenated fields, etc. Next, you can change the number of columns (see COLUMN COUNT in the properties window), the BOUND COLUMN (the column that the value of the combo box will return without having to reference the columns), and the COLUMN WIDTHS (which lets you have the combo box bound to column 1 as the id of the item but have it display column 2 (for example) as the item you select and display within the combo box. To hide columns set the width to 0" and the columns you wish to show as anything but 0" (example 1.5"). Separate the column widths by a semi-colon ";" so it would look like this with two columns selected and the first not shown: 0";1.5"

2. If you wish to show multiple names at the same time without having to have a user click the combo box, use a list box. The same rowsource things I said above apply to the list box as well.

3. In order for the combo box to "auto-fill," you must set the LIMIT TO LIST property to YES.

 

[Dchall_San_Anto]

When I set LIMIT TO LIST property to YES, then I can't enter new names. Is this an either/or thing or can I have both?

When I change to a list box, then I see all the entries for the entire table on each record. That's not right. I just want to see the buyers and sellers for that deed.

 

[boblarson]

1. I'm afraid that it's an either/or thing.

2. Your rowsource for your list box is not set properly for the way you want it to work then. Set the rowsource query to only those on that deed. You can set the rowsource to have criteria that is based on the deed ID (or whatever key you are using in the record).

 

[Dchall_San_Anto]

Your rowsource for your list box is not set properly for the way you want it to work then. Set the rowsource query to only those on that deed. You can set the rowsource to have criteria that is based on the deed ID (or whatever key you are using in the record).

After trying a few things, I think I don't understand what this means. I wasn't using a query, I was using the table. That is (and was) easy enough to fix, but how do I set up the query to show only the appropriate buyers/sellers? I'm assuming I need to build something in the Criteria row of the query where the autonumber value from the master table comes in. What I tried and didn't work is the following. I have a field in the query named ENTRY which is linked from the main table called tblMain. ENTRY is the autonumber ID for each deed in tblMain. In the Critera row I have the following, [tblMain]![ENTRY]. The results is an empty listbox. Am I getting close?

 

[Dchall_San_Anto]

And another thing. I tried setting the LimitToList property to No and got a warning:

The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first, and then set the LimitToList property.

When I make the first visible column the ENTRY column, the only thing I see in my ComboBox (I'm back to combo boxes) is the ENTRY number, not the name.

 

[boblarson]

Can you zip a copy of your db and upload here? It has to be under 393k for you to do so. If necessary strip some of the non-essential items.

 

[Dchall_San_Anto]

I deleted 70 records and repaired it down to 288k. Hope this helps. If nothing else it should demonstrate my ignorance of databases

 

[Dchall_San_Anto]

boblarson: did I lose you with the db?

 

[boblarson]

Sorry, I got sidetracked and forgot about this.

I think I'm confused by this. I see that your database design has a bit of a problem as you have Grantor and Grantee in your tblMain, yet they are not set up properly. You are storing multiple people in the same field (one record for example: Robert Henderson Coquat and Ruth Frances Collins) which isn't really good database design. Each field should contain ONLY a single entity.

I think some looking at the Northwind database and some posts here on Normalization would be useful to you.

For example, in your main table, you have listed the Grantor and Grantee names, which should not be there. They should only have the ID number (as a foreign key) from the Grantee_Grantor table. The same goes with the InstType. By having the text there it makes maintenance more difficult as you might have to fix a typo in the InstrumentTypeTable and since you used the text in the main table, then you would have to go fix the data there too.

Unfortunately I don't have time to put together a more detailed explanation. I hope that this helps.

 

[Dchall_San_Anto]

If nothing else it should demonstrate my ignorance of databases

Didn't I tell you!!

Thank you so much for the help so far. I should have said this earlier. And thank you for looking at the db and giving me the quick overview of the bigger picture issues. However...

Please let me explain what I sent and what I've been doing and maybe this won't seem so daunting. I think I'm closer to what you are suggesting than that db appears. The ultimate use of this database is to report ownership data to the people who hire me. My expertise in MS Word far (FAR) exceeds any capability I might pretend to have in Access, so what I am most comfortable doing is formatting my report in Word and mail merging the data from Access. So far, even with the errors and limitations you have pointed out, that process works flawlessly. What I am trying to do by changing the db is to make the data input easier.

By and large my industry coworkers (except one that I know of) write their reports using either Excel or they type it all in in Word. One key requirement makes either of these programs unacceptable to me - the deeds have to be listed in chronological order. I am quite experienced in using Excel so I know well the danger of trying to use a spreadsheet as a sorted database. Thus I am using Access and learning along the way.

My one co-worker who uses Access developed the db you see in my attachment. The format of the database was totally flat file when he gave it to me. tblMain was the only table, no queries, and one form. I have been trying to improve it to eliminate much of the typing we do.

Getting back to my immediate concern: I am only asking for help with the Grantor part of this. Yes, I understand the db has lots of warts and stuff I already know isn't correct, but those stinky old "legacy fields" are all helpful to me for diagnostics as I develop it. I think that once I understand how to use the subform with separate tables and queries, I will be off to the races on all the other issues you mention - in other words, I think everything is linked to my understanding of how to make the tblNames work with subfrmXXXX. Here's what I think I want:

1. Two subforms on my main form to input and view the names of (1) the buyers (Grantees) and (2) the sellers (Grantors). All I am asking for help on is the Grantors part and the Grantees should be the same.

a. If the names have already been typed in, it would be nice to not have to type them again.

b. I would also like to be able to see at least some of the names all on the same subform if there are multiple names in the field.

c. I have never had a field for addresses and phone numbers in the db, but I would like to make room for that now. I'm thinking of keeping it with the tblNames​

And to remind what I have with the current design, the best I can get in the subform is a listbox that shows numbers, not names. Or I can get a listbox that shows all the names in the table but not the ones in individual records. Or I can get the datasheet view showing exactly what I want but not telling me if I have already input the same name before.

I have found some script for an event that alerts when I try to input a name that is not found in the table. The script pops up a box asking if I want to add the name to the table. If that is a good work around for what I want to do, please let me know. I would rather do it right the first time than do it wrong and have to fix it later when I want to change something else.

Does this help or did I lose you about six paragraphs ago?

 

[Dchall_San_Anto]

Or, to summarize, I am going exactly where you suggest I go. This thread is all about me being stuck getting there.

 

[Dchall_San_Anto]

The Northwind db doesn't do what I'm asking about. I want to do data entry via a subform. I would think this would be a common use and there would be more than one person familiar with the process.

I've looked up Normalization. Normalization is what I'm trying to accomplish by asking my question. Nothing I've read about it; however, has been helpful to the level of actual practice.