Default Memo field value based on seperate table data

[gold007eye]

Is it possible to have a Memo field's default value pulled from data found in another table?

Example:

I have the memo field "Letter Blurb" on a form who's record source is "tblLetter"

What I am trying to do is have the memo field updated based on the "Letter Type" that is chosen. (This is only for certain letters that I want the memo field pre-populated without user intervention.)

For this purpose I am trying to pull data from the table "RTF" to pre-populate the Memo field "Letter Blurb" as the Letter Blurb's value: (see data below)

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial;}}
{\colortbl ;\red0\green0\blue0;}
{\*\generator Riched20 5.50.99.2010;}\viewkind4\uc1\pard\cf1\fs20 Our decision is based on the provisions of the regulations under 42 Code of Federal Regulations (CFR)  section \'a7410.33 (b) ...This section of the Code of Federal Regulations (CFR) states:\~ \i"Each supervising physician must be limited to providing supervision to no more than three IDTF sites.\~ The IDTF supervising physician is responsible for the overall operation and administration of the IDTFs, including the employment of personnel who are competent to perform test procedures, record and report test results promptly, accurately and proficiently, and for assuring compliance with the applicable regulations."\i0\par
\par
In addition the CFR discusses revocation of enrollment and billing privileges in the Medicare program.  Specifically, 42 CFR 424.535(a) (1) lists \i Noncompliance. The provider or supplier is determined not to be in compliance with the enrollment requirements described in this section or in the enrollment application applicable for its provider or supplier type..."\i0   Due to your noncompliance, NHIC, Corp is therefore revoking your Medicare PIN under this statutory provisions.\par
\par
If you believe this revocation is not justified, you may request an on-the-record reconsideration, which is performed by a carrier hearing officer.  The reconsideration is an independent review and will be conducted by a person who was not involved in the initial determination.  You must request the reconsideration in writing to this office within 60 days upon receipt of this notice.  The reconsideration appeal should be sent or delivered to:\par
}

I tried using this code on the AfterUpdate event of the "Letter Types" field:

If Me![Letter Type] = "Revocation Letter" Then
Me![Letter Blurb].Value = Tables![RTF]![RTFField]

When I choose that letter type it gives me an error saying: "Object doesn't support this method"

Is there anyway I can do this in code and if possible have it look at a specific record in the other table "RTF" to get the data from?

 

[KernelK]

I don't know how much programming experience you have, but here is what you could do:

Select Case Me.LetterTypeControlName
    Case "Letter Type 1"
        Me.MemoControlName = dlookup("RTFField", "RTF", "LetterTypeField = 'Letter Type 1'")
    Case "Letter Type 2"
        Me.MemoControlName = dlookup("RTFField", "RTF", "LetterTypeField = 'Letter Type 2'")
End Select

Where LetterTypeControlName is the value for Name in the Letter Type Combo control properties, MemoControlName is the value for Name in the Memo Text control properties, and LetterTypeField is the name of the field in table RTF where you are storing the corresponding letter types. Just add additional Case statements for each letter type.

 

[gold007eye]

KernelK - That did the trick perfectly. I don't know why I didn't think of DLookup. I used a slightly different approach with the same result:

If Me![Letter Type] = "Revocation Letter" Then
Me![Letter Blurb] = DLookup("RTFField", "RTF", "LetterTypeField ='Revocation Letter'")

 

[GaryPanic]

I did this with combo drop down and after update - but I like the look of this better- will change my version to this
g