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

I am not sure why you have an unbound main form with all of the information on it, and then want to look up a phone number. Where are the results returned?

Normally for something like this, IF my customers shared phone numbers, AND my users wanted to search for customers by phone number, I would create a combo box on a main form, or an intermediate pop-up modal form, and use the combo box wizard to look up values in the customer table. Make sure to change the sort order, remove the ID field, and set the query properties to Unique = Yes. The user would then select the phone number from the combo, and then perhaps click a button that would open a continuous form (not datasheet! Never datasheet!), and the user could then select the customer they really wanted from the continuous form. THEN your frmTest would open, Bound to your customer table, but with the recordset limited to the selected customer (use the WHERE clause of the DoCmd.OpenForm command.)

Did any of that help? I don't think sending the user back and forth between an unbound main form with a phone number look up, then down to a datasheet to select a customer (not sure how that would work) then back up to the main form with the customer data would work too well. I like to send my users through a sequence of forms for this sort of thing.

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

1. I like unbound controls because they never violate consistency and integrity as they are being filled in. Then data can be read and redirected via code at will to any needed location.

2. i have a pizza delivery business so service algorithm goes :

1. answer call
2. get customer phone#
3. find all customers with this #(some regular customers have a few adresses the order from)
4. choose the right adress
5. insert order details
6. make order slip
7. make invoice

3. I delt with the problem yesterday, wrote some code to query and used DAO for manipulation. works fine.

4. I want to use a datasheet view cuase it gives me the best view for the job ( All rows with the info i need to ask the customer which of the locations he is in and fast decide how to proceed). So i would like to know why "Never datasheet" to make a better assesment.

5. is there a way to limit the selection on a data sheet view(or continious form) to an entire row? kind of what you get if you use a comboBox with multiple colmns?

Well that was some Philosophy with a pintch of code:)
thanks for the help and all the best

Normally datasheet view is a bad idea since users can edit the data directly in the view, and sometimes they don't know it has a permanent effect. In any event, for your purposes, now that you SEE the customer in datasheet view, how do you act on it? If you display the data in a continuous form, then you can put a button next to the customer record and select that record by clicking the button.

What is the table structure under this? It sounds like:

Customer
CustID
LastName
FirstName
etc

Address
AddressID
CustID
Street1
Street2
City
State
Zip
Phone Number

This sort of look up is easy, then.

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com