Using SQL in VBA to Load a Control

[Guest]

I want to use a query to load a control. I have minimal SQL and VBA know
how. This is what I am trying to do:

I am starting off in Form![Chart]. Using AfterUpdate in Form![Chart].[24D1
CPT] I need to do a query: I want to select from Table![Insurance Company]
where Table![Insurance Company] .ID is equal to the value in the
Form![Chart]. [InsuranceCompany] then select the field in
Table![Chart].[Insurance Company] who’s name is equal to the value in
Form!Chart.[24D1 CPT]. I want to use the results of this query to fill
Form!Chart.[ 24F1 Charges]. Code Follows:

Private Sub Ctl24D1_CPT_AfterUpdate()
Dim SQL As String

SQL = "SELECT [Insurance Companies].ID, [Insurance Companies]." & _
Me![24D1 CPT] & "FROM [Insurance Companies]" & _
"WHERE ((([Insurance
Companies].ID)=[Forms]![Chart].[InsuranceCompany]));"

DoCmd.RunSQL SQL

End Sub
I have gotten this far and do not know if I am on the right track or what to
do next. I know that the program stops at DoCmd.RunSQL SQL so something is
not right and I am not sure how to get my results into the control after the
query is done.

 

[SteveS]

I want to use a query to load a control. I have minimal SQL and VBA know
how. This is what I am trying to do:

I am starting off in Form![Chart]. Using AfterUpdate in Form![Chart].[24D1
CPT] I need to do a query: I want to select from Table![Insurance Company]
where Table![Insurance Company] .ID is equal to the value in the
Form![Chart]. [InsuranceCompany] then select the field in
Table![Chart].[Insurance Company] who’s name is equal to the value in
Form!Chart.[24D1 CPT]. I want to use the results of this query to fill
Form!Chart.[ 24F1 Charges]. Code Follows:

Private Sub Ctl24D1_CPT_AfterUpdate()
Dim SQL As String

SQL = "SELECT [Insurance Companies].ID, [Insurance Companies]." & _
Me![24D1 CPT] & "FROM [Insurance Companies]" & _
"WHERE ((([Insurance
Companies].ID)=[Forms]![Chart].[InsuranceCompany]));"

DoCmd.RunSQL SQL

End Sub
I have gotten this far and do not know if I am on the right track or what to
do next. I know that the program stops at DoCmd.RunSQL SQL so something is
not right and I am not sure how to get my results into the control after the
query is done.

It stops at DoCmd.RunSQL because the SQL string must be an action query,
which is defined as:

"A query that copies or changes data. Action queries include append,
delete, make-table, and update queries; they are identified by an
exclamation point (!) next to their name in the Database window. Append
and make-table queries copy existing data; delete and update queries
change existing data. These queries are also known as DML (Data
Manipulation Language)."


Also, to get the *value* from [Forms]![Chart].[InsuranceCompany] into
the string, you have to concatenate it to the string:

strSQL = "SELECT [Insurance Companies].ID, " & _
"[Insurance Companies].[24D1 CPT] " & _
"FROM [Insurance Companies] " & _
"WHERE [Insurance Companies].ID = " & _
[Forms]![Chart].[InsuranceCompany] & ";"




If Form![Chart].[24F1 Charges] is the name of the combo box, the
afterupdate code would look something like:


'******* untested code *******
Private Sub Ctl24D1_CPT_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [Insurance Companies].ID, " & _
"[Insurance Companies].[24D1 CPT] " & _
"FROM [Insurance Companies] " & _
"WHERE [Insurance Companies].ID = " & _
[Forms]![Chart].[InsuranceCompany] & ";"


Form![Chart].[24F1 Charges].RowSource = strSQL
End Sub
'******* end code *******

 

[Guest]

Fantastic. This works. Thank you.

I want to use a query to load a control. I have minimal SQL and VBA know
how. This is what I am trying to do:

I am starting off in Form![Chart]. Using AfterUpdate in Form![Chart].[24D1
CPT] I need to do a query: I want to select from Table![Insurance Company]
where Table![Insurance Company] .ID is equal to the value in the
Form![Chart]. [InsuranceCompany] then select the field in
Table![Chart].[Insurance Company] who’s name is equal to the value in
Form!Chart.[24D1 CPT]. I want to use the results of this query to fill
Form!Chart.[ 24F1 Charges]. Code Follows:

Private Sub Ctl24D1_CPT_AfterUpdate()
Dim SQL As String

SQL = "SELECT [Insurance Companies].ID, [Insurance Companies]." & _
Me![24D1 CPT] & "FROM [Insurance Companies]" & _
"WHERE ((([Insurance
Companies].ID)=[Forms]![Chart].[InsuranceCompany]));"

DoCmd.RunSQL SQL

End Sub
I have gotten this far and do not know if I am on the right track or what to
do next. I know that the program stops at DoCmd.RunSQL SQL so something is
not right and I am not sure how to get my results into the control after the
query is done.

It stops at DoCmd.RunSQL because the SQL string must be an action query,
which is defined as:

"A query that copies or changes data. Action queries include append,
delete, make-table, and update queries; they are identified by an
exclamation point (!) next to their name in the Database window. Append
and make-table queries copy existing data; delete and update queries
change existing data. These queries are also known as DML (Data
Manipulation Language)."


Also, to get the *value* from [Forms]![Chart].[InsuranceCompany] into
the string, you have to concatenate it to the string:

strSQL = "SELECT [Insurance Companies].ID, " & _
"[Insurance Companies].[24D1 CPT] " & _
"FROM [Insurance Companies] " & _
"WHERE [Insurance Companies].ID = " & _
[Forms]![Chart].[InsuranceCompany] & ";"




If Form![Chart].[24F1 Charges] is the name of the combo box, the
afterupdate code would look something like:


'******* untested code *******
Private Sub Ctl24D1_CPT_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [Insurance Companies].ID, " & _
"[Insurance Companies].[24D1 CPT] " & _
"FROM [Insurance Companies] " & _
"WHERE [Insurance Companies].ID = " & _
[Forms]![Chart].[InsuranceCompany] & ";"


Form![Chart].[24F1 Charges].RowSource = strSQL
End Sub
'******* end code *******

 

[david epsom dot com dot au]

Also, to get the *value* from [Forms]![Chart].[InsuranceCompany] into the
string, you have to concatenate it to the string:

Both RunCmd and RowSource run 'in' Access, and
unlike SQL run in ADO or DAO, sql run in Access
can contain references to [Forms]:


ctl.RowSource = "select * from tbl where i=[Forms]![C].;"

So macros and forms can refer to the current value
of a control, and no VB code is required to resolve
the value and dynamically construct SQL.


(david)