Selecting Records via Lookup Problem

[ptaylor-west]

I have created an Order Form and Order Table to store the data. I also have a Main Table which contains all my Names and Adresses.

To make life easy I decided to select the Customer Name by creating that field as a Lookup Combo Box in the Order Table and this looks up a query of Names and Addresses from the Main Table.

So when I open my Order Form I can now scroll through or type in the first few letters for it to select the Customer Name, Great!!

Now how do I get it to bring across the relevant address ? I have tried applyFilter and requery using the unique ID for each record but I can't get it right......please help.

 

[Jack Cowley]

To fill in the Name and Address fields for the record on the form use code similar to this in the After Update event of the Combo box:

Me![LastName] = Me![ComboBoxName].Column(1)
Me![FirstName] = Me![ComboBoxName].Column(2)
The rest of the fields here...

Select the appropriate field names and remember that Access counts the first Column in the Combo box as zero.

HTH,
Jack

 

[ptaylor-west]

Typed in exactly as you stated but the field are still blank, I'm sure it's because I have the fields set up from the fields list from the order table and need to change them to display the results from the columns in the query......but how ?

 

[Jack Cowley]

In the example it says "Me![LastName] =". There must be a Field on your form named "LastName". This field can be bound or unbound, it does not matter but it must be there. Also be sure that you have the right column numbers in your code.

If you can not get this to work and your database is not too huge or contain confidential data then you can zip it and send it to me and I will try and solve the problem.

Jack

 

[ptaylor-west]

Sorry........... I discovered that I didn,t have all the fields named in the Combo Box....it works a treat except that if any of the fields are empty, say Post Code (Zip Code), then I get an error message "Field (name of field} cannot be a zero-length string" with the option to Debug or End, if I choose End it continues OK, can I do anything to make it skip if a field is empty?

 

[R. Hicks]

Open your table in design view. Select the zipcode field (what ever you named it). Change the "Allow Zero Length" property of this field to Yes, the default is No. You'll need to do this to any field that may have to store a zero length output.

HTH
RDH

 

[Jack Cowley]

Ricky -

Good answer! Glad you got here before me as I would have missed that one!

Keep looking over your shoulder....

Jack

 

[ptaylor-west]

Thanks to both of you for all your help.

Paul