Change a memo unbound text box when combo box changes

[Ruegen]

I have



an unbound combo box
an unbound text box (made to resemble a memo field)

a continuous form with many schooltype

the form is filtered by schooltype when the unbound combo box is changed i.e. high school, primary etc

I have made a field in the tblSchoolType called emMessage1 (there is 2, 3 etc)

the idea is that you choose to filter the form and then the relevant type emMessage1 appears in the text box

so if you choose high school then the high school emmessage1 appears (filled with the text)

because all the high schools are tied to tblschooltype - when you select high you are getting the high emmessage1

When I select all records I am guessing that the text box will be invisible until you select a school type in the combo box (to avoid problems)

when you have selected a type, the text gets swapped accordingly to the corresponding id of school type id thus changing emMessage1 to suit and make the textbox visible

now I can type in the field and it saves it in the table however:

I do not know how to do this.

I thought about dlookup but not sure if that will allow me to change or whether it will do this

any suggestions?

[June7]

What is the RecordSource for the form? Is it tblSchoolType or another table? Do these tables have a relationship? Can tblSchoolType be included in the form RecordSource?

Textbox can have an expression with DLookup.

Need to know more about structure of tblSchoolType. There are fields named emMessage1, emMessage2, emMessage3? What criteria determines which record is needed?

[Ruegen]

What is the RecordSource for the form? Is it tblSchoolType or another table? Do these tables have a relationship? Can tblSchoolType be included in the form RecordSource?

Textbox can have an expression with DLookup.

Need to know more about structure of tblSchoolType. There are fields named emMessage1, emMessage2, emMessage3? What criteria determines which record is needed?

recordsource for the form has tblSchoolType in it (it's a linked table to tblSchools). Yes related. Yes can be included.

If it can make the textbox have a dlookup - can I edit what it shows?

structure is:

SchoolTypeID	SchoolType	emMessage1	emMessage2	emMessage3
1	High	Dear Sir	To the Lib	To the DP

[Ruegen]

I have added

=DLookUp("EmMessage1","tblSchoolType","SchoolTypeID=" & [Text38]) 'text38 is the combo box

I have made clicking in the textbox open to another form that has a box to type (of the same field emMessage1)

when you swap the combo it changes however

on null combo box I get #error

I could make it invisible until you choose a type... unless you have a better suggestion?

[June7]

One issue at a time. First you were just populating a textbox, now opening a form? I am confused. If the table is in the form RecordSource, there is no need for DLookup.

If 'high' should always use emMessage1, why does it have values in the other two fields? Maybe table structure should be like:

ID	Type	Message
1	high	Dear Sir
2	middle	To the Lib
3	elem	To the DP

[Ruegen]

One issue at a time. First you were just populating a textbox, now opening a form? I am confused. If the table is in the form RecordSource, there is no need for DLookup.

If 'high' should always use emMessage1, why does it have values in the other two fields? Maybe table structure should be like:

ID	Type	Message
1	high	Dear Sir
2	middle	To the Lib
3	elem	To the DP

The original table was always without the emMessage1

Each school had an id number depending on what they were e.g.

High school is ID 1
Primary school is ID 2

and so on

each type has up to 3 email fields (this I added on recently)

So if you have chosen to view high schools via a combo box, then automatically it will display the stored email message body for high school category - up to 3 different kinds which I hope to toggle later using an option box and swapping the control source (for the text field that displays the message body on the form)

so primary, high etc could each have a Dear Sir/Madam however each one could be varied and customized to suit.

Because I did a dlookup I can't edit it so I made another form that pops up when you enter the displayed text field allowing you to edit what you see in the dlookup.

[June7]

I am still fuzzy on the three fields and when each should be selected, why DLookup is needed if fields are in the RecordSource, and how the tables are related.

The tblSchoolType offers users some 'canned' email text but you want to allow them to change the text? Where is this changed text saved to?

VBA code can populate textbox and that will leave it user editable.

Is the form in continuous view?

[Ruegen]

ok I managed to get it to work the way I wanted

I used on the button, several of these:

ElseIf Me.Frame63 = 1 And Me.Command47.Caption = "Blank Emails" Then
Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "") & "And [1ContactEmail] Is Null"
Me.FilterOn = True
Me.Command47.Caption = "Show All"
ElseIf Me.Frame63 = 1 And Me.Command47.Caption = "Show All" Then
Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "")
Me.Command47.Caption = "Blank Emails"

on the option box I used (several)

Case 1
Me.txtSchoolEmail.ControlSource = "[1ContactEmail]"
Me.Label29.Caption = "Contact Email"
fieldemailchange = "1ContactEmail"
Me.Label184.Caption = fieldemailchange
Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "")
Me.Command47.Caption = "Blank Emails"
Me.FilterOn = True

it all works

thanks for your help guys