Error message aftet update Please Please Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a 2 tables,
table 1 has a field named ProgArea
table 2 has field name ProgArea and RiskArea

I created a form named Risk
with 2 fields ProgramArea and riskArea

in Program afterudpate field I inserted the below.

Private Sub txtProgArea_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!txtProgArea
strSQL = strSQL & " from Program_Area"
Me!cboRiskArea.RowSourceType = "Table/Query"
Me!cboRiskArea.RowSource = strSQL
End Sub

The intent is to have RiskArea list onyl the values that equal the field
name ProgArea from table 1. Then the user would select the the risk area that
equals the program area.

When I select the Prgram ARea then Click on the Risk Area I get the below
error message

The record source 'Select Schedule from Program_Area'specified on this form
or report does not exist.

When I closed the form and look at the Prgram area field the value I
selected is there and when I select the risk area the correct values appear,
However, I changed the program area to another selection and click on risk
area I get the same error message.

I just want to restrict the user to select the correct values from table 2
that equa the value from table 1 program area.. I have search and test all
the links from this discussion group but I can't get it to work..
 
I am no SQL expert but I believe your synthax to be incorrect.

Select Schedule from Program_Area

typically it should be

Select FieldName(s) FROM TableName

You have to ask yourself is Schedule a field in the Program_Area table?
Hence the error.

Also, for terminology sake, a field is in a table and a control is on a
form. Just so we are all talking the same language.

Daniel
 
Sorry I had to run before. Now let see about a solution.

Using the basic synthax Select FieldName FROM TableName

and adding a WHERE clause to the end to perform a filter based on the
selection made by the user in the ProgramArea control.

Select FieldName FROM TableName WHERE ...

Also, you can easily try and develop this synthax by creating queries and
then viewing their SQL value, to learn how to build the correct synthax.

You want to achieve an SQL statement more like

Select RiskArea FROM Table2 WHERE (ProgArea="Me!txtProgArea")

Daniel
 
Back
Top