Member Avatar for froy001

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

  • 2 Contributors
  • 1 Reply
  • 92 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by timothybard
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.