Hello all

I am a total newbee to access so please bear with me if this is a dumb one.

I have an unbound main form : frmTest. With fields : [CustomerId](autonumber), [PhoneNum](str), [CustomerName](str), [CustomerSurName](str), [StreetName](str),[StreetNum](str), [aptNum](str), [floor](str), [Notes](str), [RegDate](date)

and a subform field called [FrmQryCustomerSearch] which uses DataSheet view which has as source object a form called FrmQryCustomerSearch which has as a source a simple qry named qrySearchCustomer .

What i want to do is have the user enter a phone# in [PhoneNum] control, press the Tab to go to the next control .
On Tab (BeforUpdate as i understand) have the subform produce all the customers with the same Phone(if any) in the subform .
If there are more than one customers with same number the user selects one and the info updates from the qry form to the relevant controls in the main form.

My problem is : Coding the Sub PhoneNum_BeforeUpdate(Cancel As Integer) so as to achieve the end described above. I cant seem to be able to reference [PhoneNum] for the qry or use it as a criteria for the sub form and refresh the subform.

Thanks in advance

9 Years
Discussion Span
Last Post by timothybard

First, I would have use the LostFocus event of the phonenum control. Second, the code you need has to change the record source of the child form and add a WHERE clause to it. The WHERE clause would be something similar to

"WHERE [Table].[PhoneNum] = " & me![PhoneNum]

Notice that the code actually updates the record source to include the PhoneNum field and does not "link" the record source to the field, if that makes sense.

I can give you more details if needed.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.