SQL Query Prints as string instead of performing Query

[Mr T]

Hopefully this will be an easy fix and I am just missing something stupid.

I have 5 textboxes that I want to populate dependent upon 2 cascaded combo
boxes. Here is copy of the module:

Public Sub Text4_AfterUpdate()

If library = "MF 1" Then
Me.Text6 = " SELECT [TO Title] FROM [MF 1] WHERE [Book Number] = "
& Me.Text2 & " AND [TO Number] = " & Me.Text4 & " "
Me.Text9 = " SELECT [Current Change] FROM [MF 1] WHERE [Book Number]
= " & Me.Text2 & " AND [TO Number] = " & Me.Text4 & " "
Me.Text18 = " SELECT [A-Page Date] FROM [MF 1] WHERE [Book Number] =
" & Me.Text2 & " AND [TO Number] = " & Me.Text4 & " "
Me.Text16 = " SELECT [Man Number] FROM [MF 1] WHERE [Book Number] =
" & Me.Text2 & " AND [TO Number] = " & Me.Text4 & " "
Me.Text14 = " SELECT [Initials] FROM [MF 1] WHERE [Book Number] = "
& Me.Text2 & " AND [TO Number] = " & Me.Text4 & " "
End If

End Sub

The form ends up displaying:
SELECT [TO Title] FROM [MF 1] WHERE [Book Number] = " & Me.Text2 & " AND
[TO Number] = " & Me.Text4 & "

in the first text box etc.

Any help would be greatly appreciated. Thanks all

 

[Rob Parker]

The textboxes are displaying the strings (which happen to be SQL statements)
because that's what you've set them to! A textbox control cannot evaluate a
valid SQL string; it can only display it.

Probably the easiest way to accomplish what you're wanting to do is to use
dLookup statements to get the values you want from the fields in the [MF 1]
table. See the Help file for details if you're unfamiliar with this; the
easiest way to get to help on this is via the Expression Builder dialog:
select Functions - Built-in Functions in the left box, Domain Aggregate in
the centre box, and dLookup in the right box, then click Help.

You'll finish up with an expression such as this for the assignment
statement for each of the textboxes (this should be correct for the first
one):
Me.Text6 = dlookup("[TO Title]","[MF 1]","[Book Number] = " &
Me.Text2 & " AND [TO Number] = " & Me.Text4 )

HTH,

Rob

PS. You'll find life a lot easier if you give your controls meaningful
names, rather than simply accepting defaults such as Text6; a name such as
txtTOTitle is a lot more meaningful. Also, life will be easier if you don't
put spaces into object (tables, fields, ...) names.