Dlookup and change events

[purchaandr]

Hey there guys, just having a little bit of trouble with my forms and I'm sure there isn't a big problem and was hoping one of the guru's on here would be able to lend me a hand.

I have two forms, one to add a new business into the system and another which finds and edits businesses already in the system. There is also another table which holds the address of the business and the AddressID is stored in the business table.

I want to be able to view the address on the business form, and currently I have this working when adding anew business. The way in which I have done this is when the AddressID field is updated,I use the AfterUpdate() event and then use DLookups on some basic text boxes. This works quite well for what I need it for.

When it comes to the second form, the same method using the AfterUpdate doesn't work and it also doesn't work on the onChange even as well.

I'm Just wondering how I can get the Dlookups working when navigating through different records?

Any help would be greatly appreciated,

Cheers

Andrew

 

[HiTechCoach]

I would recommend using a sub form and not dlookups, unless you will have only a few address records. I would probably still use a sub form (NO VBA code required) so that you do not have to work so hard at it.

If you insist on using the Dlookups, then I would us the On Current event to display the address for existing records.

 

[purchaandr]

Hi there Hitech, thanks for the speedy reply.

I think you might be right, there are going to be more than a few address records.

What do you think the best way to use a subform would be? Pretty much I just had a play around after your reply and wrote a query to select the fields I want from address and put the "Like" criteria under the primary key which is AddressID and this seems to bring up the record i need.

Does this sound like a good way to go about it?

Thanks again for your help

 

[WIS]

I have two forms, one to add a new business into the system and another which finds and edits businesses already in the system.

Andrew

Why do you need 2 forms. Have one form with a cmd btn on it with the following code:

DoCmd.GoToRecord , , acNewRec
The above puts up a blank entry for a new record.

You can also put a cbo on the form to find existing records. I can post a routine for this if you so desire.

 

[purchaandr]

thanks for some more input high tech.

I need two forms as there are quite a few fields to fill out for a business. The initial Add has only the basic necessities and the edit form has alot of optional extras. My friend who I am making this for has also requested it this way but was aware I can have a button to add a new record.

Thanks for the extra input though I see exactly where you are coming from