PLEASE help: Autofill based on one field

[Suzanne]

Please forgive me - I do not speak Access which is making it difficult to research this issue. It took me several days to figure out I should be searching autofill instead of autopopulate...and yet now I am not sure.

I need to create a conference registration form that upon entering a member ID, certain fields (name, address, etc) would autofill.



I managed to create a form that autofilled all the fields () but it also created a form for every member in my database () - I only want to create a form as each member registers.

I know it can be done as my predecessor did this. Any help would be appreciated and please keep in mind my very basic understanding of Access (ie please explain it to me like I'm a 3 year old. Maybe 6. )

I've spent days on this and am at my wit's end. Please help!

Thank you in advance for your assistance.

[jzwp11]

Welcome to the forum!

The easiest way to display the name and address details of a registrant is to use a combo box. The combo box would be based on your member table, you will need to include the fields you want to display later in the combo box.

Then create some unbound textboxes on your form to display the name and address info. All you have to do is use an expression that references a different column of the combo box's row source. For example, let's say that the row source of the combo box looks like this:

SELECT memberID, membername, memberaddress FROM tblMembers

Create a textbox control on the form and set its control source to the following expression:

=comboboxname.column(1)

When you select a memberID from the combo box, the created text box will display the membername info. Access starts counting at zero not 1 (memberID is column(0); membername is column(1) etc.)

If you do not want to use a combo box, then you will probably need use a DLookup() function that uses the MemberID.

[Suzanne]

Forgive me, I'm not quite sure that is what I am looking for. We have over 1000 members so I don't want to scroll through a dropdown box for the right one...

The previous year's form has a member ID box. When the member ID is entered (based on the Master Database), the name, address, etc fills in. My boss is rather adamant that I replicate the previous form.

[jzwp11]

Is the memberID the primary key of the member table?

What is the datatype of the memberID field: text or number?

Can you provide a little more detail on the table structure you have?

[Suzanne]

The memberID is the primary key of the member table.

It is an autonumber.

Not sure if this is what you are asking or not: the member table is very basic - it contains the member ID, name, title, address, organization, phone, email, etc as well as if the person is a current member or sponsor.

The form(table?) I want to create would have parts of that information as well as details specific to the conference.

Thanks for trying to help! I appreciate it!

[jzwp11]

So your member table looks like this:

Members
-member ID (autonumber primary key)
-name (text)
-title (text)
-address (text)
-organization (text)
-phone (text)
-email (text)
-current member (unsure of data type, maybe a yes/no field?)
-sponsor (unsure of data type, maybe a yes/no field?)

In terms of the above table, if you have several members that belong to the same organization, it would be better to have a table to hold all of the organization names and then just use a foreign key field in the member table to reference the organization.

Is the member ID field name memberID or member ID? The space matters. In fact, it is generally recommended not to have spaces or special characters in your table or field names. Additionally, it is best not to use words that are reserved words in Access. The most common reserved words are: name, date, month, year

So in your table, I would recommend changing the field name called name to something else.

As to the rest of your tables, I assume that you have multiple events/conferences and that each conference can have many members that attend. Since a conference can have many attending members, that describes a one-to-many relationship. Also, a member may attend many conferences, so you have another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables, you have a many-to-many relationship which requires a junction table.

First a table to hold the conference info

tblConferences
-pkConferenceID primary key, autonumber
-txtConferenceName (text)
-dteDate (date/time field)

now the junction table which holds the members attending each conference or in other words a table to hold the registration:

tblRegistration
-pkRegistrationID primary key, autonumber
-fkConferenceID foreign key to tblConferences (long number integer field)
-fkMemberID foreign key to Member table (long number integer field)

Without using the combo box method, you will need some code to bring in the details of the member. I have attached an example database with a form (frmConferences) that has that code. The code needs to be in the after update event of the textbox where the memberID is entered as well as the on current event of the registration subform.

BTW, if you use the combo box method, you can type the ID in and the combo box will automatically move to that ID #, so you do not have to scroll through a bunch of records. In the attached example database, I illustrate the combo box approach in frmConferences2.