Version: 6.0.5.2 (x64)
I have a form with customer information. First field is phone number, and I would like the customer’s record to be loaded and become the current record if the phone number entered is found in the underlying table. I am unsure how to replace the current record from within a macro after phone# lookup returns ‘hit’. Suggestions?
Hello,
The question is kind of murky. Is this “First field” used as a lookup field? What kind of control? If it is a lookup and the record is not found, aren’t you concerned it was incorrectly entered? What if it was entered correctly but not found? Maybe more on what you are trying to accomplish rather than how to accomplish something (ie purpose of the form & its’ use).
Form has typical text control fields for: Phone#, Firstname, Surname, Address, Email
Here is usecase:
Currently only a msgbox displayed if found.
Hello,
It appears this form in not used to scroll through records so this should be fairly easy. My preference is to use the When losing focus event but that is ultimately your choice.
When you enter your macro you check if the record exists already (most likely with an SQL statement to see if record returned based upon # entered). If not already existing just exit the sub.
If record does exist, you can Filter the table and reload it:
sFilter = "(""Phone#"" = '" & phone_number_from_form & "')"
oForm.Filter = sFilter
oForm.Reload()
oForm.Filter = ""
With phone_number_from_form this is the field you already have & used for the SQL.
The result will be only one record with the cursor in the next field if Tab was used. Because the filter was cleared in the last statement of the macro, a New record can then be selected & the process started once again.
Thanks for the prompt response. I try this and see how it goes.
This approach worked very well. Discovered need to use ‘Refresh Form’ operation afterward to restore recordset to using all records without filter.