Populating a field from another table using DLookup

[Jamescdawson]

I'm trying to populate a field (we'll call it field#2) on a standard form (Customer Record Card) automatically after I've selected the contents of another field on the same form (we'll call it field#1)



The content of field#1 is a pick-list lookup from a separate table. The other table also holds the look-up list I want to use to populate field#2. The relationship between fileds 1 & 2 is direct. Sounds simple ? It's driving me nuts.

I've tried using DLookup and I'm stuggling.

Can it be done using DLookup or another Built-in function or do I need more complex code.

Can anyone tell me what the syntax for the Dlookup function in the above scenario would be ... and where should I put it ? "on click" "after update".... ?

I'm using Access 2010

Help much appreciated

=Jimbo=

[pbaldy]

If your structure fits, this is is simpler and more efficient:

http://www.baldyweb.com/Autofill.htm

[Jamescdawson]

Good Morning Pbaldy,

Many thanks for your suggestion. It makes sense. I configured the data in the Combo box and played with it to make sure I understand what's going on. However, I'm still not getting the 'autofill' in the text box. I've tried a number of syntax options but I'm doing something wrong.

In your example

=ComboName.column(2)

what does "column(2)" represent ?

I am trying to autofill the content of the text box with the contents of the 3rd column in my table. The 3rd column is labled "Code"

so is this wrong ?

=[County/UA].[Code]

where "Country/UA" is the table name and "Code" is the third column.

(I've also tried using integers 0 - 3.

All options return #Name?

If your structure fits, this is is simpler and more efficient:

http://www.baldyweb.com/Autofill.htm

[Jamescdawson]

That was a typo I do mean "County/UA"

[pbaldy]

It would look exactly as I typed it, replacing the combo name and the appropriate integer. For the third column:

=[County/UA].Column(2)

[Jamescdawson]

Hi Pbaldy,

Thanks for the additional assistance.

I think I'm proceeding in the wrong direction. I'll try another way.

Thanks for your help

Best Regards

=Jimbo=

It would look exactly as I typed it, replacing the combo name and the appropriate integer. For the third column:

=[County/UA].Column(2)

[pbaldy]

No problem, and welcome to the site by the way. I'd like to make sure you beat this, so feel free to post back if you're stuck. Post the db if you want, and we'll sort it out.

[Jamescdawson]

P,

Very kind. I'm reluctant to take advantage of experienced developers on free forums , so I wait until I am just about pulling my hair out before I ask. I'm more of an Operatrions Guy than a coder... I know stuff CAN be done, but I fall down on the "how". I may take you up on the offer.

Best Regards

Jimbo

No problem, and welcome to the site by the way. I'd like to make sure you beat this, so feel free to post back if you're stuck. Post the db if you want, and we'll sort it out.

[pbaldy]

We are here because we choose to be; nobody is forcing us to answer questions against our will. Ask away; nobody wants this:

[Jamescdawson]

Pbaldy... You're a gentleman.....

OK... I'll assume that I can return the good deed at some point in the future... this will be wordy.... apologies...

I started to rethink my problem after your initial post because I'm actually trying to 'autofill' a field on a FORM using the data entered manually (or more accurately from a limited pick list) in another field on the same form... Ideally, I'd like this autofilled field to simple act as a default in the new field because there is a likelihood that the user may want to manually change the data which was initially autofilled to an alternative item from the original underlying pick list. After getting your first advice and suggestion, I tried to use the function you suggested on a slightly simpler problem with only partial success. I could only managed to fill the 2nd field with the ID number.

In the scenario described above, I cannot simply have field #2 use the same source as field #1 because when the user makes a change to the second field, field#1 also changes!

This problem relates to a data entry form which records & controls the scheduling of a small team of 6 drivers (Ken, Keven, Mike, Paul, Garry & Kai) who bring customer items back to our depot and which will be returned to the original customer 7 days later in a weekly cycle - after having been processed. It's necessary for us to populate that "Return Driver" field on the day that the item arrives. (There's good reason why this has to happen so far in advance.) However, the supervisor may decide (in fact it often is necessary) that one of the other drivers will return the item earlier and not wait for the original driver's scheduled visit. At the end of the pickup day, the supervisor generates job cards/drop-off schedules based on driver's names and whilst doing so, is able to re-schule a delivery by an alternative driver. The majority of the returns are made by the same driver on his weekly scheduled round - hence my preference for the 2nd field to be defaulted to the "Pickup Driver" - BUT, it is necessary that the supervisor be able to make that change to an alternative driver. I have two fields in the underlying source table for the form ("pickupdriver" and "ReturnDriver"). They can both populated by drop down pick lists from another table called "drivers". V.simple table (ID, Driver first name, driver Surname)

So here's the process... On the day of pick-up, the data entry clerk selects the pick up driver's name from a drop down pick list on the data entry form together with a range of other data which I don't have problem managing. The item collected by the driver is processed on Day 1 & 2 and availble for return within 3 days but usually returned on the 7th day. Later on the pickupday, the transport manager gets a report based on that data entry form. He will then decide which - if any - of the returns need to be rescheduled. He then changes that schedule and prints the driver lists for all the items collected that day.

He COULD simply bring up each item record and select a driver from the pick list for ever one, but this would be tedious in the extreme. better if he can just change the execeptions.

We process 350-400 items per week - each with its own record in our db and each with a named pick up driver (and exceptionally) an alterntive return driver.

There you have it. I look forward to your comments.

Best Wishes

=Jimbo=

We are here because we choose to be; nobody is forcing us to answer questions against our will. Ask away; nobody wants this:

[pbaldy]

I may be having late-afternoon brain cramps, but wouldn't the second method from that link work? That would set the value into the second field, but still allow it to be changed by the user later. If I'm not understanding, can you post the db here?

[Jamescdawson]

Well if you are having brain cramps, I'm approaching "Brain dead". It's now after midnight here. I'll retry in the morning.

Thanks again.

Jimbo

[chauhan8787]

How to Display combobox releted in other text field

[pbaldy]

Jimbo, you must be over there in the same time zone my daughter lives in (England).

Chauhan, have you reviewed the previous posts?

[Jamescdawson]

Pbaldy,

Just a quick note to say "Thanks" and that I solved the intial problem I asked about in my first post - based on your original suggestion. I didn't get it working until I set the "Bound Column" property in the Combo box to "1" As soon as that parameter was set, the rest worked as you said !! To be honest, I'm not sure why that setting was set to incorrectly in the first place and why that should affect the display in the scondary text box... but it does.

With that in the bag, I can resolve the more detailed problem I posted at the week-end.

Thanks again for pointing me in the right direction.

Best Regards

Jimbo

PS: I'm not quite in England... I am a Scotsman living in Wales... about 5 miles from the border with England.

Jimbo, you must be over there in the same time zone my daughter lives in (England).

Chauhan, have you reviewed the previous posts?