Using a multiple field index in a form

[Guest]

Hello,

I am in the process of entering thousands of employees into our database. I am using a form, with a subform that brings up matching last names. I have gotten this far by using the form-wizard, which seems a little limited.

For the sake of preventing duplicate entries, I would like the subform to narrow down the list of matching last names to matching first names.(due to the large # of smiths and rodriguez') I have created a multiple field index I guess, not that I can figure out how to do anything with it.

(tangent - While tinkering with the multiple-field index dialogue box I deleted the primary key index. I haven't seen any difference, but if I'd like to make any necessary corrections before entering 1000's of records)

Also, I would like some kind of alarm/warning for duplicate SSN's. I have seen where I can select an option of 'Allow Duplicates' yes/no, but the problem is there are already duplicates. I think so anyways.

Thanks

 

[John Vinson]

On Mon, 14 Jun 2004 08:21:01 -0700, "venus as a boy" <venus as a

Hello,

I am in the process of entering thousands of employees into our database. I am using a form, with a subform that brings up matching last names. I have gotten this far by using the form-wizard, which seems a little limited.

For the sake of preventing duplicate entries, I would like the subform to narrow down the list of matching last names to matching first names.(due to the large # of smiths and rodriguez') I have created a multiple field index I guess, not that I can figure out how to do anything with it.

I'd suggest - instead of a Subform - using a Combo Box. Create a Query

SELECT EmployeeID, [LastName] & ", " & [FirstName], <other identifying
fields>
FROM yourtable
ORDER BY LastName, FirstName;

Use this as the Row Source for your combo box. The combo can then
directly stoer the EmployeeID in your table.

You don't need to "do" anything with the Index - Access will just use
it to speed the sorting of the data specified by the Order By clause.

(tangent - While tinkering with the multiple-field index dialogue box I deleted the primary key index. I haven't seen any difference, but if I'd like to make any necessary corrections before entering 1000's of records)

An ABSOLUTELY NECESSARY correction is to restore your primary key! If
you don't have a primary key *there is no way to determine* which
record you're updating. It need not be an Autonumber but you must - no
options! - have some field or fields that define the record uniquely;
this is the Primary Key. I called it EmployeeID above.

Also, I would like some kind of alarm/warning for duplicate SSN's. I have seen where I can select an option of 'Allow Duplicates' yes/no, but the problem is there are already duplicates. I think so anyways.

You will need a bit of VBA code in the Form. I'm not sure just what
you're doing - are you entering names from an existing table into a
new table (selectively), editing an existing table, or what?

In addition to duplicate SSN's you very well may have duplicate names.
I once worked with Dr. Lawrence David Wise and his colleague, Dr.
Lawrence David Wise. You very will may have two Maria Rodriguez's. How
do you plan to handle the ambiguities?

 

[Guest]

John - Thanks

Creating a query: I went to create query using wizard.
Selected fields:
New ID (employee ID # as you put it mr. astute)
Last
First
"that's all the info we need..."|Finish
Close query
Open form, design view
Insert combo box
"I want the combo box to look up values in a table or query."|Next>
"Which table or query..." |'Employee Main: Query' |Next>
"Which fields contain the values you want included in your combo box?" |New ID, Last, First |Next
"You can sort records by up to four fields..." |Last-ascending, first-ascending |next
Column Width| Next>
"When you select a row in the combo box, you can store a value from that row in your database... Choose a field that uniquely identifies the row. Which column in your box contains the value you want to store or use in your database?" |Last |Next>
"When you select a value in your combo box, what do you want Access to do?" |Remember the value for later use. |Next

Where does that land me? The New ID is the catalyst for the combo box, which serves no purpose since I wouldn't type in 1196 to find Robert Smith.

What am I doing...
I took a database from Excel and imported (copy/paste actually) to Access. That database had just over 3500 entries. It was a halfway completed database, in that there are about 7000 job applications lying around this office that need to be recorded and addressed. (New ID) The people I work for are not computer literate, so I am left creating a database from the ground up. Having never used database software, your help is much appreciated.

The duplicate name problem is not so ambiguous. There are obviously many duplicate names, but we have the SSN's and/or birthdates to tell them apart. There shouldn't be more than one person with any given social security number, so I'd prefer to be warned of duplicates. It could be useful for the sake of data entry so that I don't have to look at the monitor to compare all the names to what I'm typing. It's not completely reliable as not all records have SSN's but most do.


Thanks for the advice on my primary index

Jason

 

[John Vinson]

John - Thanks

Creating a query: I went to create query using wizard.
Selected fields:
New ID (employee ID # as you put it mr. astute)
Last
First

Add one more field by typing into an empty Field cell:

FullName: [Last] & ", " & [First]

Put "Ascending" under Last and First, and uncheck their Show checkbox.
This will give you a query showing the full name, sorted
alphabetically.

"that's all the info we need..."|Finish
Close query
Open form, design view
Insert combo box
"I want the combo box to look up values in a table or query."|Next>
"Which table or query..." |'Employee Main: Query' |Next>
"Which fields contain the values you want included in your combo box?" |New ID, Last, First |Next
"You can sort records by up to four fields..." |Last-ascending, first-ascending |next
Column Width| Next>

Ignore this

"When you select a row in the combo box, you can store a value from that row in your database... Choose a field that uniquely identifies the row. Which column in your box contains the value you want to store or use in your database?" |Last |Next>

You probably want to store the EmployeeID instead, though maybe I
don't understand what you're doing here! If you already have an
employee table with names in it, you should probably NOT be storing
those names redundantly in some other table.

"When you select a value in your combo box, what do you want Access to do?" |Remember the value for later use. |Next
Where does that land me? The New ID is the catalyst for the combo box, which serves no purpose since I wouldn't type in 1196 to find Robert Smith.

Set the Column Widths property of the combo box to 0;1.5 so you will
STORE the ID, but DISPLAY the full name. This will let you store a
unique EmployeeID in this table.

What am I doing...
I took a database from Excel and imported (copy/paste actually) to Access. That database had just over 3500 entries. It was a halfway completed database, in that there are about 7000 job applications lying around this office that need to be recorded and addressed. (New ID) The people I work for are not computer literate, so I am left creating a database from the ground up. Having never used database software, your help is much appreciated.

I'm still confused then. If you have 3500 names to add, what's with
the form? Just run an Append query and add them!

The duplicate name problem is not so ambiguous. There are obviously many duplicate names, but we have the SSN's and/or birthdates to tell them apart. There shouldn't be more than one person with any given social security number, so I'd prefer to be warned of duplicates. It could be useful for the sake of data entry so that I don't have to look at the monitor to compare all the names to what I'm typing. It's not completely reliable as not all records have SSN's but most do.

Again... I'm not quite clear what you are doing. Are you retyping the
records you have already entered? Are you manually adding records from
paper? What was the function of your subform? Were you using it to add
data (to what?) or just to display possible dups?

 

[Guest]

John,

Minus the month.5 of working here, I have 0 experience with Access and databases in general. Why did a business hire a completely inexperienced person to do their job? Because they can pay me $8.50/hour, much less than someone qualified.(maybe not after all the hours I put into learning) So if you think I can adopt a better system feel free to advise.

During my initial exploration, I stumbled onto the fact that by using a form and a subform that correlates the last name field in my form with records in my main table, I can display all the records with the same last name, which with the exceptions of what I'll call mega-lastnames (johnson etc.) is very efficient at determining if a record has been previously entered.

Narrowing down my list to matching last names and first names would be outstanding.

Otherwise, I would like a setup in which I enter the SSN first, and if it's a duplicate then the validation text would display the matching record. The problem I have here is that several records are already duplicated, due to faulty practice by previous holders of my job I'd guess.

Thanks for your time.

 

[John Vinson]

John,

Minus the month.5 of working here, I have 0 experience with Access and databases in general. Why did a business hire a completely inexperienced person to do their job? Because they can pay me $8.50/hour, much less than someone qualified.(maybe not after all the hours I put into learning) So if you think I can adopt a better system feel free to advise.

:-{( My sympathies and apologies if I sounded patronizing. Not intended!

During my initial exploration, I stumbled onto the fact that by using a form and a subform that correlates the last name field in my form with records in my main table, I can display all the records with the same last name, which with the exceptions of what I'll call mega-lastnames (johnson etc.) is very efficient at determining if a record has been previously entered.

Narrowing down my list to matching last names and first names would be outstanding.

It's a sneaky trick, but you can use TWO fields in the Master/Child
Link Fields property. Set these two properties to

[Last];[First]

(or whatever the fieldnames are) and you'll have the two-field link.

Otherwise, I would like a setup in which I enter the SSN first, and if it's a duplicate then the validation text would display the matching record. The problem I have here is that several records are already duplicated, due to faulty practice by previous holders of my job I'd guess.

First suggestion: create a new Query, and use the "Find Duplicates"
wizard to locate and (if possible) fix the duplicate SSN's - then set
a unique index.

Second suggestion: put the following VBA code in the BeforeUpdate
event of the SSN field. You'll need to adapt the fieldnames and
control names; if you have trouble please post back with the actual
names of the fields in your table, the name of the table itself, and
the names of the form controls:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindFirst "[SSN] = '" & Me!txtSSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
strMsg = "This SSN already exists!" & _
" Click Yes to go to it, Cancel to retry"
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.txtSSN.Undo ' just erase the SSN
End If
End If
End Sub