Problems with FindFirst, Access 2002

[Armando]

Hello everyone,

In an Access 2002 database I have the following setup:

On a Form there is a List Box on which records can be viewed and selected. When a user selects a record it can be modified by using Combo Boxes or Text Boxes on the Form to modify the record.

On the table associated with the Form I had to change the properties of 1 column. The properties changed were:

• From the column being Required to not being Required

• From the column being Indexed Yes (No Duplicates) to not being indexed

• Removing the Primary Key and re-assigning the Primary Key to a new column with AutoNumber

The change was done to allow duplicate data on the column. After the change when a user enters duplicate data it is entered into the table just fine but when the new record with duplicate data is selected again (ie: for editing) the data displayed in the Combo and Text boxes is not of the record selected in the List box but rather the first record to have had original data entered. Because of this behavior the user cannot edit the record they have selected.

Here is the code:

Sub List35_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[License] = '" & Me![List35] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I have tried replacing the table with a new identical table but the behavior still occurs. I am guessing there is something wrong with the Form but I am not sure where else to look.

Any ideas?

Thanks.

[Stewart Ross]

As you have found, FindFirst (as its name suggests) just finds the first occurrence of the given value. That was fine when license values were unique, but you have now changed this so that duplicates are allowed.

How do you distinguish the 'correct' record from a user's point of view? Forgetting the autonumber primary key for a moment (as sequence numbers are not normally meaningful from a user's point of view), what can you supply to your FindFirst that will pick up the record you want to find? Even if you were to use 'movenext' to walk through each record in the recordset after using findfirst to find the very first match, you would need some criterion to know which record to stop upon.

If you can identify an additional field to search upon you can modify the findfirst to find the combination of fields accordingly, like this (with a renewal date assumed in this example):

Me.RecordsetClone.FindFirst "[License] = '" & Me![List35] & "' AND [Last Renewal Date] = #" & Format(Me.[List35].Column1, """mm/dd/yyyy""" & "#"

Of course, if you can identify the proper record from a user's point of view you can then devise a query that would include the proper primary key, which can be fed to the FindFirst in place of the License number field used at present.

If you could advise what potential field or fields you can use in combination with the license number we can advise further on how to search for the combination (and how to include the field concerned in your list box).

-Stewart

[Armando]

Thank you Stewart for helping me with this.

After reading your response I decided to change the Form again so that FindFirst would be focused on a column with unique data like you suggested. Unfortunately the only unique data per record is the column with AutoNumber set. I agree with you this column will be meaningless to the user’s entering data but if it will make the form work properly then I think it’s worth to try to use it. It is also the column to which the Primary Key is set to.

So on the List Box properties, in the “Format” Tab, I increased the “Column Count” by 1, I specified the width of the column to 0.01” (this way it just looks like double lines to the user), and I added the name of the column under the “Data” Tab in the “Row Source” Field with the flowing text: SELECT DISTINCTROW [MTO Fax].[Record], [MTO Fax].[License], … (more non-unique columns follow). The “Bound Column” is 1 for the number 1 column. Finally in the Form’s code I changed the FindFirst criteria from “License” to “Record”.

When I test I get the following message:

Run-time error ‘3464’:

Data type mismatch in criteria expression.

Can you tell me what's going on now after this new change?

Also, just in case FindFirst does not like the column that contains the Primary Key, I tried to create a new column with AutoNumber set but Access says it only allows one column with AutoNumber set per table.

The table by the way, which is for inventorying software, does not contain records that are unique. The fields include Name, Description, License and PC Name. Data will be repeated in multiple records, for example if 2 people share a license for one piece of software.

Any ideas? Thanks again for helping out.

[Stewart Ross]

Hi. The License Number field was previously a text type; the autonumber field is a whole number. The type mismatch arises from the use of the text delimiter apostrophes that were formerly used for the license number - remove these as in the example below:

Me.RecordsetClone.FindFirst "[Record] = " & Me![List35]

-Stewart

[Armando]

Thank you Stewart, that took care of it. It works perfect now.

Thank you for your help.

-Armando.