Help with Dlookup

[pmangan]

Hi all,
Looking for help with displaying unrelated table fields on a form. My 3 tables are: PatientT, LocationT, and DoctorT. PatientT is controlled with form PatientF for new patient entry; PatientF has a lookup column field (LocationLookup) which prompts the user to pick from an exhaustive list of location names from LocationT. LocationT and DoctorT are related on a one to many basis via an autonumber field from LocationT.
I would like to make it so that when the user selects a location from the LocationLookup field on PatientF, certain information will populate from LocationT (address, phone number, etc.).
The expression I've come up with (using expression builder) to populate the address field from LocationT onto PatientF is:
=DLookUp([LocationT]![Address],[LocationT],([PatientT]![LocationLookup]=[LocationT]![Location]))

I'm not sure if the expression is right, since I don't know how to now add the field to the form. Can anyone advise?

Also, not sure if it's worth noting or not but PatientT and LocationT are not related. I'm not sure if they need to be since PatientF pulls location information directly from LocationT via the lookup column field.



Once I can populate all of the dlookup fields I'm looking for from LocationT, I would then like to add another field which will display the various doctors which relate to a given location from DoctorT. One location may have 4 doctors, for example. Ideally, I would like the user to select a location and then choose from a filtered list of only the doctors who operate out of the selected location.

I realize that this is sort of convoluted so please let me know if I'm not explaining this well. Any help is greatly appreciated.

Access 2016, Windows 10

[pbaldy]

Here's help on the DLookup() syntax:

DLookup Usage Samples

But this may be simpler:

BaldyWeb - Autofill

[pmangan]

Here's help on the DLookup() syntax:

DLookup Usage Samples

But this may be simpler:

BaldyWeb - Autofill

Thank you, I'm not sure I understand yet though. I think I can figure out the syntax of the expression, but I don't know how to actually get the information to display on the form once I build the expression. Once I can get the information to populate somewhere on the form, I can make any changes to the expression that I need.
The link showing autofill methodology looks like it would accomplish what I'm looking for but unfortunately I have too many records for a traditional combobox, and I don't have the VBA expertise to work around that limitation; hence the lookup column field. Any ideas on how to produce a similar autofill based on a selection from a lookup column field?
I do not understand how to change the column width to 0 for the additional fields I'm looking to display.

[pbaldy]

How many records is too many? I don't/won't use lookup fields:

http://www.theaccessweb.com/lookupfields.htm

You change the column width to 0 with the column widths property of the combo.

[pmangan]

Thanks for the heads up re: lookup fields. I think I'll stay with the lookup field though since the information I'm looking up will not be queried or manipulated in any way. I'm only adding them for ease of use from an operation standpoint (the user needs the information displayed on the same screen)
I got the column widths to work properly, thank you for that.

Do you have any ideas on how to address the doctor lookup that I mention in the first post?

[pbaldy]

This kind of thing?

Baldy-Cascading Combos

[pmangan]

This kind of thing?

Baldy-Cascading Combos

Something like that, yes. Unfortunately I've never used VBA so I'm not sure I can follow these instructions.
Can this be done by manupulating the Row Source/Type properties of the field?

EDIT: Row Source - SELECT DoctorT.Doctor, DoctorT.Doctor FROM DoctorT WHERE DoctorT.ID=LocationT.LocID ORDER BY DoctorT.Doctor;
Row Source Type - Table/Query

When I do this it prompts me to enter the LocationID autonumber in a dialogue box and then the filtered list of doctors that I'm looking for will populate. Is there a way to not have to enter the autonumber and to just have the field display the relevant doctors?

[pbaldy]

Your criteria would refer to the form control, as shown in one of the methods. Then the only code is a requery.

[pbaldy]

Plus I would expect the criteria to be on the location field.

[pmangan]

Your criteria would refer to the form control, as shown in one of the methods. Then the only code is a requery.

Sorry, I do not understand; I've only been using access for a couple months and am self-trained so I'm not up on much of the terminology. Are you saying there is another step, or that what I've done so far is incorrect?

[pbaldy]

Did you look at the sample db?

[pmangan]

Did you look at the sample db?

Yes, but since I don't know any VBA I don't understand what the After Update event is doing or how to reproduce it in my db. I don't even know what an After Update event is. I'm not sure an example will help me much without the context.
Also, I'm not using combo boxes, I'm using lookup column fields. Not sure if that makes a difference or not.

[Bulzie]

The event methods trigger code or processes to happen inside the database. If you go into design mode of a form or click on a field, if you open the properties window, you will see a tab for events and listed are all the events where you can trigger something to happen. In an AfterUpdate event say on a form, after a record is saved, whatever code or macro you have in the AfterUpdate box(could be VBA code or a macro, etc.) will trigger. In and OnOpen event, once the form is opened, that code would trigger. In a field, AfterUpdate will trigger after you have entered a value into that field and hit enter or moved to a new field, etc.

[pmangan]

The event methods trigger code or processes to happen inside the database. If you go into design mode of a form or click on a field, if you open the properties window, you will see a tab for events and listed are all the events where you can trigger something to happen. In an AfterUpdate event say on a form, after a record is saved, whatever code or macro you have in the AfterUpdate box(could be VBA code or a macro, etc.) will trigger. In and OnOpen event, once the form is opened, that code would trigger. In a field, AfterUpdate will trigger after you have entered a value into that field and hit enter or moved to a new field, etc.

That makes sense, thank you. So in this case the After Update event in my LocationLookup field will dictate the Row Source in my DoctorLookup field? I'm still unclear on how the two fields will communicate with one another or how the DoctorLookup field will become populated with the doctors which relate to the location.

[Bulzie]

Paul's Post 6 helps give code and explains I think. Basically in your doctors table and combo box you have a field for their Location right? So in the Row Source query for Doctor combo box in the field for LocationID, in the criteria you want to point to the Location field on your form. Then in the AfterUpdate of the Location field on the form, you put me.Doctor.Requery. So after they select a location, the Doctor combo box list gets redone using the new value in the location box as criteria for the doctor query. Also if you are not experienced with VBA, use the query builder/designer to help you do the processes you need instead of straight vba.