RecordSource Qry for Subform

B

Bryan Hughes

Hello,

I have a case management form that after case manager selects the case file,
it populates the subform with client information in that case file.

When the user selects case file id from cbo I receive the following error:

Error No. 2580 ; The RecordSource 'SELECT CDID, CFID, CID, [FN] & Chr(32) &
[LN] AS Name,
COD, ARS, CSAPP, FAN, FL, FSP, FSTC FROM tblClient_Details . . .' specified
on this report or form does not exist.

After clicking okay the subform shows Name: errors

Here is the code on the cboCFID change event I am using:
Private Sub cboCFID_Change()
'Some Dim statements and othe code
.....
' Beginning of SQL statements for sub form

str3SQL = "SELECT DISTINCT CDID, COD, ARS, CSAPP, FAN, FL, FSP, FSTC FROM
tblFST;"

str4SQL = "SELECT CDID, CFID, CID, [FN] & Chr(32) & [LN] AS Name, CM, EMPID,
COD, ARS, CSAPP, FAN, FL, FSP, FSTC " & _
"FROM tblClient_Details INNER JOIN str3SQL ON
tblClient_Details.CDID = str3SQL.CDID " & _
"WHERE tblClient_Details.CFID='" & strCFID & "' " & _
"GROUP BY CDID, CFID, CID, [FN] & Chr(32) & [LN], COD, ARS,
CSAPP, FAN, FL, FSP, FSTC;"

Me.fsubFST_Case_File_Clients.Visible = True
Me.fsubFST_Case_File_Clients.Enabled = True
Me.fsubFST_Case_File_Clients.Locked = True
Me.fsubFST_Case_File_Clients.Form.RecordSource = str4SQL
Me.fsubFST_Case_File_Clients.Form.SetFocus

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

What am I doing wrong and how can I fix it?

Thank you
-Bryan
 
M

MacDermott

You need to do your concatenations outside of the string; for example, use
"SELECT CDID, CFID, CID, [FN] " & Chr(32) & " [LN] AS Name"

Although since Chr(32) is a space, it escapes me entirely why you would want
to do this;
it still won't produce valid SQL.

HTH
- Turtle
 
B

Bryan Hughes

Turtle,

Then what would be the best way to do this?

-Bryan

MacDermott said:
You need to do your concatenations outside of the string; for example, use
"SELECT CDID, CFID, CID, [FN] " & Chr(32) & " [LN] AS Name"

Although since Chr(32) is a space, it escapes me entirely why you would want
to do this;
it still won't produce valid SQL.

HTH
- Turtle

Bryan Hughes said:
Hello,

I have a case management form that after case manager selects the case file,
it populates the subform with client information in that case file.

When the user selects case file id from cbo I receive the following error:

Error No. 2580 ; The RecordSource 'SELECT CDID, CFID, CID, [FN] &
Click to expand...
Chr(32)
&
[LN] AS Name,
COD, ARS, CSAPP, FAN, FL, FSP, FSTC FROM tblClient_Details . . .' specified
on this report or form does not exist.

After clicking okay the subform shows Name: errors

Here is the code on the cboCFID change event I am using:
Private Sub cboCFID_Change()
'Some Dim statements and othe code
....
' Beginning of SQL statements for sub form

str3SQL = "SELECT DISTINCT CDID, COD, ARS, CSAPP, FAN, FL, FSP, FSTC FROM
tblFST;"

str4SQL = "SELECT CDID, CFID, CID, [FN] & Chr(32) & [LN] AS Name, CM, EMPID,
COD, ARS, CSAPP, FAN, FL, FSP, FSTC " & _
"FROM tblClient_Details INNER JOIN str3SQL ON
tblClient_Details.CDID = str3SQL.CDID " & _
"WHERE tblClient_Details.CFID='" & strCFID & "' " & _
"GROUP BY CDID, CFID, CID, [FN] & Chr(32) & [LN], COD, ARS,
CSAPP, FAN, FL, FSP, FSTC;"

Me.fsubFST_Case_File_Clients.Visible = True
Me.fsubFST_Case_File_Clients.Enabled = True
Me.fsubFST_Case_File_Clients.Locked = True
Me.fsubFST_Case_File_Clients.Form.RecordSource = str4SQL
Me.fsubFST_Case_File_Clients.Form.SetFocus

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

What am I doing wrong and how can I fix it?

Thank you
-Bryan
Click to expand...
Click to expand...
 
B

Bryan Hughes

Turtle,

Thanks any way I was able to make it work, by making to sub forms and
filtering second subform with first sub form. I was able to Concatenate the
fn and ln fields into Name in SQL string by using the following:

str3SQL = "SELECT DISTINCTROW CDID, CFID, CID, ([FN]+ Chr(32)) & [LN] As
Name FROM tblClient_Details WHERE tblClient_Details.CFID='" & strCFID & "'
GROUP BY CDID, CFID, CID, FN, LN;"

Everything else works great.

Bryan


MacDermott said:
You need to do your concatenations outside of the string; for example, use
"SELECT CDID, CFID, CID, [FN] " & Chr(32) & " [LN] AS Name"

Although since Chr(32) is a space, it escapes me entirely why you would want
to do this;
it still won't produce valid SQL.

HTH
- Turtle

Bryan Hughes said:
Hello,

I have a case management form that after case manager selects the case file,
it populates the subform with client information in that case file.

When the user selects case file id from cbo I receive the following error:

Error No. 2580 ; The RecordSource 'SELECT CDID, CFID, CID, [FN] &
Click to expand...
Chr(32)
&
[LN] AS Name,
COD, ARS, CSAPP, FAN, FL, FSP, FSTC FROM tblClient_Details . . .' specified
on this report or form does not exist.

After clicking okay the subform shows Name: errors

Here is the code on the cboCFID change event I am using:
Private Sub cboCFID_Change()
'Some Dim statements and othe code
....
' Beginning of SQL statements for sub form

str3SQL = "SELECT DISTINCT CDID, COD, ARS, CSAPP, FAN, FL, FSP, FSTC FROM
tblFST;"

str4SQL = "SELECT CDID, CFID, CID, [FN] & Chr(32) & [LN] AS Name, CM, EMPID,
COD, ARS, CSAPP, FAN, FL, FSP, FSTC " & _
"FROM tblClient_Details INNER JOIN str3SQL ON
tblClient_Details.CDID = str3SQL.CDID " & _
"WHERE tblClient_Details.CFID='" & strCFID & "' " & _
"GROUP BY CDID, CFID, CID, [FN] & Chr(32) & [LN], COD, ARS,
CSAPP, FAN, FL, FSP, FSTC;"

Me.fsubFST_Case_File_Clients.Visible = True
Me.fsubFST_Case_File_Clients.Enabled = True
Me.fsubFST_Case_File_Clients.Locked = True
Me.fsubFST_Case_File_Clients.Form.RecordSource = str4SQL
Me.fsubFST_Case_File_Clients.Form.SetFocus

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

What am I doing wrong and how can I fix it?

Thank you
-Bryan
Click to expand...
Click to expand...
 
Top