Can't change RecordSource or ControSource with a VBA Sub

[Roger Withnell]

The following code is run from a form:

Private Sub cmdSetupComplaint_Click()
Dim strSQL As String

strSQL = "SELECT Ingredients.Ingredient, " & txtSetupComplaint & " FROM
Ingredients"
strSQL = strSQL & " LEFT OUTER JOIN Complaints ON"
strSQL = strSQL & " (Ingredients.Ingredient = Complaints.Ingredient)"
strSQL = strSQL & " ORDER BY Ingredients.Ingredient"

DoCmd.OpenForm "Set up Complaint"
Forms("Set up Complaint").Caption = "Set up the Ingredient for the
Complaint: " & txtSetupComplaint
Forms("Set up Complaint")!Label22.Caption = "Set up the Ingredient for
the Complaint: " & txtSetupComplaint
Forms("Set up Complaint").RecordSource = strSQL
Forms("Set up Complaint")!Complaint.ControlSource = txtSetupComplaint

End Sub

The Form's Caption is changed to txtSetupComplaint, but Label22,
RecordSource and ControlSource do no change to the new values.

Any help very much appreciated.

 

[Arvin Meyer [MVP]]

Try using this syntax:

[Forms]![Set up Complaint]![Label22].Caption = "Set up the Ingredient for
the Complaint: " & txtSetupComplaint

[Forms]![Set up Complaint].RecordSource = strSQL

I'm not sure about the following. It doesn't look right, so maybe you can
explain what it does:

Forms("Set up Complaint")!Complaint.ControlSource = txtSetupComplaint
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dirk Goldgar]

The following code is run from a form:

Private Sub cmdSetupComplaint_Click()
Dim strSQL As String

strSQL = "SELECT Ingredients.Ingredient, " & txtSetupComplaint &
" FROM Ingredients"
strSQL = strSQL & " LEFT OUTER JOIN Complaints ON"
strSQL = strSQL & " (Ingredients.Ingredient =
Complaints.Ingredient)" strSQL = strSQL & " ORDER BY
Ingredients.Ingredient"

DoCmd.OpenForm "Set up Complaint"
Forms("Set up Complaint").Caption = "Set up the Ingredient for the
Complaint: " & txtSetupComplaint
Forms("Set up Complaint")!Label22.Caption = "Set up the
Ingredient for the Complaint: " & txtSetupComplaint
Forms("Set up Complaint").RecordSource = strSQL
Forms("Set up Complaint")!Complaint.ControlSource =
txtSetupComplaint

End Sub

The Form's Caption is changed to txtSetupComplaint, but Label22,
RecordSource and ControlSource do no change to the new values.

Any help very much appreciated.

Similar code works for me. What is the value of txtSetUpComplaint?
What is strSQL?

Do you get any error message?

 

[Dirk Goldgar]

I'm not sure about the following. It doesn't look right, so maybe you
can explain what it does:

Forms("Set up Complaint")!Complaint.ControlSource = txtSetupComplaint

It looks okay to me, Arvin. It would seem to be setting the
ControlSource of a control named "Complaint" on the form named "Set up
Complaint".

 

[Guest]

You may be having a timing issue. Try moving the code to
the Load event of the "Set up Complaint" form.

(david)

 

[Roger Withnell]

Fixed it!

When I set up the "Set up Complaint" form originally, I bound it to a table,
which set RecordSource and the related ControlSource's.

When I set them (RecordSource and ControlSource's) to blank, the sub
worked.

Many thanks for all your help. Focussed me on the right track.