Ask Your Question

Base change current record from macro [closed]

asked 2018-09-05 01:59:59 +0200

Spun69 gravatar image

updated 2018-09-05 02:11:46 +0200

Version: (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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Spun69
close date 2018-09-05 20:05:10.336013



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).

Ratslinger gravatar imageRatslinger ( 2018-09-05 02:26:13 +0200 )edit

Form has typical text control fields for: Phone#, Firstname, Surname, Address, Email Here is usecase: * User clicks NewRecord, all form fields clear, text cursor in Phone# field as it is first * User enters phone# digits, presses tab key to advance to next field * Control's OnTextChange trigger fires, macro performs lookup to see if phone# already in table * If phone# found, load the customer record such that the form fields reflect the loaded record.

Currently only a msgbox displayed if found.

Spun69 gravatar imageSpun69 ( 2018-09-05 04:30:44 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-09-05 07:58:50 +0200

Ratslinger gravatar image


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.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.

edit flag offensive delete link more


Thanks for the prompt response. I try this and see how it goes.

Spun69 gravatar imageSpun69 ( 2018-09-05 16:09:05 +0200 )edit

This approach worked very well. Discovered need to use 'Refresh Form' operation afterward to restore recordset to using all records without filter.

Spun69 gravatar imageSpun69 ( 2018-09-05 20:04:43 +0200 )edit

Question Tools

1 follower


Asked: 2018-09-05 01:59:59 +0200

Seen: 48 times

Last updated: Sep 05 '18