dlookup function (1 Viewer)

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
Please forgive my ignorance on this subject - I am relatively new to MS Access and appreciate any advice.

I am developing a database to record student data.

One of the fields in the table is named LTSCHOOL and data entry is via a combo linked to a table called 'LN20 - School last attended'. 'LN20 - School last attended' consists of two fields, Code and Description.

The LTSCHOOL combo stores the school Code in the table as the Description (school name) is not required.

I have created an unbound textbox called txtLTSCHOOL in which I want the Description to appear based on the Code in the 'School last attended' textbox.

I have used the following VBA code on the AfterUpdate event on the combobox:

txtLTSCHOOL = DLookup("[description]", "LN20 - School last attended", "
Code:
=Form![LTSCHOOL]")
 
This does give me the Description of the school as per the Code but does not change when I move through the records. The Code does change however as this is bound to the table.
 
Can someone tell me how I can have a unique Description for each record instead of the same Description throughout?
 
Many thanks
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
Have you tried joining the two tables in a query and set the query as the RecordSource of your form?
 

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
Hi RuralGuy

This is one example of many fields for which I will only need to record the code but view the description on the form.

If I use the query as the form RecordSource I don't think I will be able to do this with all of the other fields - although I may be wrong?

Thanks
 

rainman89

I cant find the any key..
Local time
Today, 09:22
Joined
Feb 12, 2007
Messages
3,016
have you tried requerying the textbox on each record move? the
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
Hi RuralGuy

This is one example of many fields for which I will only need to record the code but view the description on the form.

If I use the query as the form RecordSource I don't think I will be able to do this with all of the other fields - although I may be wrong?

Thanks
As long as the joins are correct, I believe it should work and still be updateable even though there are many joins.
 

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
Rainman89 - Apologies for sounding like a complete amateur (the truth is I am) but how would I do that?

RuralGuy - Can I use more than one query as a form RecordSource? Maybe I haven't understood - apologies if this is the case.

Thanks both
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
RuralGuy - Can I use more than one query as a form RecordSource? Maybe I haven't understood - apologies if this is the case.
Thanks both
Sorry, but only one RecordSource per form. Have you tried the additional joins? How many fields are we talking about here?
 

rainman89

I cant find the any key..
Local time
Today, 09:22
Joined
Feb 12, 2007
Messages
3,016
Rainman89 - Apologies for sounding like a complete amateur (the truth is I am) but how would I do that?

you might be able to get away with me.yourtextboxname.refresh in the onclick of the record button... are you using your own buttons to move between records?

RuralGuy - Can I use more than one query as a form RecordSource? Maybe I haven't understood - apologies if this is the case.

Thanks both
No. You would create one query that would incorporate all the information that you would need.
 

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
Rainman89 - Many thanks I will try this.

RuralGuy - There's between 30-40 fields, all codes. I work for a government-funded training provider. As you can imagine there is a lot of data that we have to forward, all in code format. I would like to have the description for each code on the form though for my own benefit.

Thanks both
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
Well the DLookup's will make the form seem like it is stuck in syrup but I would probably use Ray's idea but put it under a special button so the user separates the events. Something like "Supply Names" button. That way the user can move from record to record with relative speed and only get the names on the record they are interested in.
 

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
Sorry - I'm not getting this. Will I have to insert a button to refresh every field on the form?

Could I change the DLookup to something else or use the Me.Refresh as an event procedure behind the combo or unbound textbox?

Apologies.
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
You can have code that will refresh all and only the controls that you wish. How is the user moving from record to record?
 

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
At the moment, in the drafts that I've put together, I'm using the scroll button on the mouse to move between records. I do find this frustrating however and will be looking for a code to remove this function.

I will be the only user of this database so I can add buttons to navigate between records if I need to - I'm open to whatever you think is most effective.

Thanks
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
Have you tried turning on the Nav Buttons for the form to see how they work?
 

matg

Registered User.
Local time
Today, 13:22
Joined
Feb 4, 2009
Messages
19
Hi RuralGuy

Again, apologies but how would I do this? My knowledge of Access if very limited indeed.

Thanks
 

RuralGuy

AWF VIP
Local time
Today, 07:22
Joined
Jul 2, 2005
Messages
13,826
Open the properties sheet for the form. The Navigation Buttons are on the Format tab.
 

Users who are viewing this thread

Top Bottom