Need Help Changing Existing VBA Code to an event procedure

[MsGeek]

I have recently inherited a DB that I need to make some changes to. There is a button on an unbound form that when clicked, launches some VBA code below ( I have only included the code I am having trouble with and not the entire copy of code):

Function make_select()
Dim sql_select As String
Dim throw_error As Integer
Dim fans_agents_moniker As String
Dim fans_agents_2_moniker As String
Dim common_agent_moniker As String
Dim email_address_moniker As String

throw_error = 0
sql_select = "SELECT "
fans_agents_moniker = "A."
fans_agents_2_moniker = "D."
common_agent_moniker = "B."
email_address_moniker = "C."

If Me.chk_data_name.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "first_name, " & fans_agents_moniker & "last_name "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "first_name, " & fans_agents_moniker & "last_name "
End If


throw_error = 1
End If
If Me.chk_data_id.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "agent_id "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "agent_id "
End If
throw_error = 1
End If
If Me.chk_data_addr_office.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & common_agent_moniker & "office_addr_1, " & common_agent_moniker & "office_addr_2, " & _
common_agent_moniker & "office_addr_city, " & common_agent_moniker & "office_state, " & _
common_agent_moniker & "office_zip "
Else
sql_select = sql_select & ", " & common_agent_moniker & "office_addr_1, " & common_agent_moniker & "office_addr_2, " & _
common_agent_moniker & "office_addr_city, " & common_agent_moniker & "office_state, " & _
common_agent_moniker & "office_zip "
End If
throw_error = 1
End If
If Me.chk_data_addr_personal.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & common_agent_moniker & "home_addr_1, " & common_agent_moniker & "home_addr_2, " & _
common_agent_moniker & "home_addr_city, " & common_agent_moniker & "home_state, " & _
common_agent_moniker & "home_zip "
Else
sql_select = sql_select & ", " & common_agent_moniker & "home_addr_1, " & common_agent_moniker & "home_addr_2, " & _
common_agent_moniker & "home_addr_city, " & common_agent_moniker & "home_state, " & _
common_agent_moniker & "home_zip "
End If
throw_error = 1
End If
If Me.chk_data_distr_key.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "distr_key "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "distr_key "
End If
throw_error = 1
End If
If Me.chk_data_email.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & email_address_moniker & "email_addr "
Else
sql_select = sql_select & ", " & email_address_moniker & "email_addr "
End If
throw_error = 1
End If
If Me.chk_data_phone_office.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & common_agent_moniker & "office_area_code, " & common_agent_moniker & "office_telephone "
Else
sql_select = sql_select & ", " & common_agent_moniker & "office_area_code, " & common_agent_moniker & "office_telephone "
End If
throw_error = 1
End If
If Me.chk_data_phone_personal.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & common_agent_moniker & "home_area_code, " & common_agent_moniker & "home_telephone "
Else
sql_select = sql_select & ", " & common_agent_moniker & "home_area_code, " & common_agent_moniker & "home_telephone "
End If
throw_error = 1
End If
If Me.chk_data_CRD_No.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "fans_CRD_NO "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "fans_CRD_NO "
End If
throw_error = 1
End If
If Me.chk_data_rvp_id.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "fans_rvp_no "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "fans_rvp_no "
End If
throw_error = 1
End If
If Me.chk_data_rvp_name.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_2_moniker & "first_name AS RVP_FIRST_NAME, " & fans_agents_2_moniker & "last_name AS RVP_LAST_NAME "
Else
sql_select = sql_select & ", " & fans_agents_2_moniker & "first_name AS RVP_FIRST_NAME, " & fans_agents_2_moniker & "last_name AS RVP_LAST_NAME "
End If
throw_error = 1
End If
If Me.chk_data_status.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "agent_status "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "agent_status "
End If
throw_error = 1
End If
If throw_error = 0 Then
MsgBox "You must select at least one display field."
Me.tmp_txt_sql = ""
Else
Me.tmp_txt_sql.Value = sql_select
End If

End Function


The text denoted in green I have added to add the CRD No. field to the table that is generated from this procedure - it is an unbound checkbox on the unbound form. However, I think I have missed something, because I get an error when I try to run the process. Any help would be great, as I am a complete novice when it comes to programming VBA code.

[Bob Fitz]

What error do you get?
Which line of code causes th error?

[MsGeek]

I suppose it's not an 'error' per se, but I get a parameter value prompt for the CRD_NO field, so I am pretty sure that how I referenced it in the code is probably incorrect or there is somewhere else in the remaining code that the reference has been left out of.

I honestly don't even know where to look - I am very unfamiliar with VBA coding/programming for Access.

[MsGeek]

Also, if I select 'debug' option the code window opens and the following line is highlighted (red text - i am including excerpts of the code also):

sSql2 = Me.tmp_txt_sql & " INTO [tbl_tmp_agt_list] " & _
"FROM (((DB2PROD_FANS_AGENTS AS A Left JOIN DB2PROD_FANS_AGENTS AS D on A.FANS_RVP_NO = D.AGENT_ID) " & _
" INNER JOIN DB2PROD_COMMON_AGENT AS B ON A.AGENT_ID = B.AGENT_ID) LEFT JOIN [qry_primary_emails] AS C " & _
" ON C.EMAIL_OWNER_ID = A.AGENT_ID) " & _
Me.tmp_txt_where.Value & ";"
' " AND A.FANS_RVP_NO = D.AGENT_ID;"
'MsgBox sSql
DoCmd.RunSQL sSql2

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me.txt_tbl_name.Value, thedir & "/" & file_name & ".xls", True
'StartDocApp "./" & file_name & ".xls", "EXCEL.Application", "XLMAIN"

[Bob Fitz]

Hi
In the code that you first posted, CRD_NO appears twice. (See below in red)

If Me.chk_data_CRD_No.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & "fans_CRD_NO "
Else
sql_select = sql_select & ", " & fans_agents_moniker & "fans_CRD_NO "
End If
throw_error = 1
End If

I think that "fans_CRD_NO " needs a space before the word fans, as below.

If Me.chk_data_CRD_No.Value = True Then
If throw_error = 0 Then
sql_select = sql_select & fans_agents_moniker & " fans_CRD_NO "
Else
sql_select = sql_select & ", " & fans_agents_moniker & " fans_CRD_NO "
End If
throw_error = 1
End If

I think that may solve your problem. Please try that and post back with the result.

Regards
Bob