FindRecord problem (1 Viewer)

[cjherrick]

In Access 2000, I use the AfterUpdate event in the first field of a customer input form to call a function (not an Event Procedure) which checks if a new customer has the same ID as an existing customer (by using DCount > 0), to prevent typing more data. If so, I display a warning message, save the new input ID, delete the new partial record, then display the existing customer record with the same ID. When the user clicks the correct button on the warning box, here's the code I'm using:

NewID = Forms![Customer Form]![Customer ID]
DoCmd.DoMenuItem acFormBar, acEditMenu, A_SELECTRECORD
DoCmd.DoMenuItem acFormBar, acEditMenu, A_DELETE
DoCmd.FindRecord NewID, , , , True

The warning dialog works fine and the value in NewID becomes, for example, BROWNJO2. The input record gets deleted and the exisiting record with ID = BROWNJO2 is then displayed.

I want to add an additional check for duplicate street address. I tried using essentially the same code:

Street = """" & Forms![Customer Form]![Street Address] & """"
DoCmd.DoMenuItem acFormBar, acEditMenu, A_SELECTRECORD
DoCmd.DoMenuItem acFormBar, acEditMenu, A_DELETE
DoCmd.FindRecord Street, , , , True

The value in Street becomes, for example, "123 Main St" including the quotes (I tried omitting quotes but got the same result). The input record is deleted, but a blank record is displayed instead of the existing record corresponding to the address in Street. I also tried using specific constants instead of the comma defaults, as in: DoCmd.FindRecord Street, acAnywhere, False, acSearchAll, True, acAll, True.

The only differences I can see in the two fields is that the ID gets the focus when the record is opened and it has no spaces or special characters, whereas the Street field is reached by tabbing in, and it does have spaces or special characters. Both fields are defined as String.

I'd be grateful if any of you good folks can spot what I'm doing wrong.

Charles Herrick

 

[doulostheou]

If street is not the first field to gain focus, you will need to set the focus to that field before finding the record. Try adding this line immediately before using FindRecord:

Street.SetFocus

 

[cjherrick]

Thanks for the tip, but pre-setting the focus to the problem control didn't help.

 

[cjherrick]

Through much trial and error I finally got this thing to work. The Access Help example for FindRecord shows the search argument enclosed in quotes, but this did not work in my case. In desparation, I tried enclosing it in parentheses and that worked like a charm.

Thanks to all who pondered this problem.