How to avoid re-entering info that's already in a table

[creolejazz]

Need an opinion about the best way to do this.

Using a form which puts data into a table, we entered about 1,200 names with addresses, phone numbers, etc. for an annual event a year ago. Now it's time for this year's event. Many of last year's participants will attend again this year and will therefore already be in the table. As we receive written registrations for this year's event, what's the best way to determine if someone is already in the table so we don't duplicate the information?

The current method for entering registrations is to look at a written/printed registration form that the registrant has completed and then using an Access form, enter the data (which goes into the table). Is there a way for Access to let us know -- as the last name is being entered, for example -- that that person is already in the table from last year?

I know I can open the table and alphabetize by last name and search for the name and look to see if they are there, but that seems time-consuming and cumbersome. Anyone know of a better way?

Hope this makes sense. Thanks for any help.

 

[neileg]

If you use a combo box, it will zoom to the value that you are typing. Think about using that to flag up potential duplicates.

 

[gemma-the-husky]

put a unique key on the table, then access will stop you adding duplicates

its tricky with names though, as you can easily get duplicates via slight typo differences

neil's method will help if you do it one at a time, but if your data is all on a spreadsheet say, you can load it to the access table all in one go - then you need access to eliminate the duplicates automatically

 

[Mike375]

I enter 1000s of names/addresses in bulk.

I use & "" & to join the first and last names and sometimes I include phone number.

You can then fiddle about with duplicates or making the joined field a primary key and joining the table to the new data.

If they are one at a time entries then Neil's suggestion is a goer.

 

[creolejazz]

Thanks for the feedback. Generally, we enter one name at a time so I like the sound of this "combo box' concept but I need some help.

Can I just open my existing form, right click on the "last name" field, click "change to", and choose "combo box"? I'm guessing there's something more to it than that because all that seems to accomplish is adding a drop down arrow to that field. How do I make this combo box help me accomplish what I need? Thanks again.

 

[neileg]

The combo box needs a dataset to poulate it. I would use a query based on the existing names. Requery after each new entry to keep the data fresh. Make sure that the Limit to List property is set to No otherwise you won't be able to add new names.

 

[creolejazz]

This is great stuff. I appreciate the help. I'm almost there. Need some help crossing the last hurdle.

I've created my combo box (LAST NAME) based on a query. Sure enough, when I type in a last name and click the down arrow, I see a list of last names (and first names and date of birth - three "columns"). Now I can tell whether or not this person attended last year.

What I would like to do is simply click on a name in the drop down and have all their information appear in the form fields. In other words, when I click on the name in the drop down it would take me straight to that entry and all the info would appear so that I could update any new info such as AGE (which would have changed since last year) or a new address or whatever. Is that possible? How? Right now when I click on the name in the drop down, the last name appears there but that's it.

 

[creolejazz]

So, anyone know how I can get my form to show the data that already exists in my table?

 

[neileg]

Two ways
1) Load the data into the query that populates the combo. Set the column widths to zero for the extra data so it doesn't show. In your text boxes, point the data source to the combo using the Column() property to choose the appropriate column. Use the after update event of the combo to requery the form.
2) Set up another query and bind your text boxes to the query. Use the value in the combo as a criterion for this query. Use the after update event of the combo to requery the form.

 

[creolejazz]

neileg,

Thanks so much for the help. The more you teach me, the more I realize I don't know. Just a few questions regarding your helpful reply.

Regarding solution #1...

When you write, "load the data into the query that populates the combo", do you mean simply to open the query?

I have set the column widths to zero so they don't show.

You wrote: "In your text boxes, point the data source to the combo using the Column() property to choose the appropriate column." Could you explain the proedure for this?

Thanks a lot.

 

[neileg]

Regarding solution #1...

When you write, "load the data into the query that populates the combo", do you mean simply to open the query?

I meant add the relevant fields to your query.

You wrote: "In your text boxes, point the data source to the combo using the Column() property to choose the appropriate column." Could you explain the proedure for this?

Thanks a lot.

Extract from Access help:
Column Property
You can use the Column property to refer to a specific column, or column and row combination, in a multiple-column combo box or list box.
You can use the Column property to assign the contents of a combo box or list box to another control, such as a text box. For example, to set the ControlSource property of a text box to the value in the second column of a list box, you could use the following expression:

=Forms!Customers!CompanyName.Column(1)

 

[creolejazz]

Yes, that makes sense. However, I am not sure of two things....

1. Do I edit the column property of each text box or the column property of the combo box?

2. Given your example, "=Forms!Customers!CompanyName.Column(1)", I'm not sure how that translates to my form. For instance, if my table is named "table" and my form is named "form" and my combo box is named "combo" and my text box is named "textbox", how would that line be written and, again, where exactly would that line go.

Really sorry for the really newbie questions. Thanks again for the time.

 

[neileg]

1. Do I edit the column property of each text box or the column property of the combo box?

Text boxes don't have columns and you can't edit the column property of a combo. So I don't understand the question.

2. Given your example, "=Forms!Customers!CompanyName.Column(1)", I'm not sure how that translates to my form. For instance, if my table is named "table" and my form is named "form" and my combo box is named "combo" and my text box is named "textbox", how would that line be written and, again, where exactly would that line go.

Open the properties sheet for "textbox". In the record source property type =Forms!form!combo.column(whichever column number you want to show)