Need help with SQL query (1 Viewer)

[jdp103]

I have two tables in my database that are linked. One is a Branch listing that consists of only 3 fields, BranchID (autonumber) BranchName and OpenClosed. The other table is individual account records which are associated to a Branch (in the branch table). I have added code to a button on a form so that when a branch closed, it will mark the current branch account as Inactive, send an email, and update the Branch table for that Branch as closed. The last piece of code that I want to add to the button is to do a search on all of the accounts in the account table to find if there are any other open accounts for that Branch that is being closed...and to just display them. My code works perfect up to that 2nd SQL query, then i get an error. Not sure if the syntax is incorrect or if I am just not doing it right. This is the first time that I've ever used SQL in code. But I don't know of any other way to do this. Any help would be GREATLY appreciated!! Here is my code:

Private Sub Command18_Click()
Dim statuschange As String
Dim emailrec As String
Dim title As String
Dim title2 As String
Dim tmpLoanOfficer As String
Dim tmpBranchName As String
Dim tmpMachID As String
Dim tmpImageID As String
Dim tmptype As String
Dim tmpclosed As String
Dim SQL2 As String
tmpclosed = "Closed"
tmptype = Me![Last Name]
tmpBranchName = Me![BRANCH NAME]
tmpMachID = Me![CUST ID (MACH30)]
tmpImageID = Me![IMAGE ID]
tmpLoanOfficer = Me![LOAN OFFICER]
Const CR = vbCrLf & vbCrLf
Const CR2 = vbCrLf
title = "Deactivate Branch Account"
title2 = "Deactivate Loan Officer Account"
statuschange = "Inactive"
ActiveStatus = statuschange
DEACTIVATION_DATE = Date
DEACTIVATION_DATE.Visible = True
DeactivatedBy = CurrentUser
SQL1 = "UPDATE tblBranch " & _
"SET tblBranch.OpenClosed = '" & tmpclosed & "' " & _
"WHERE tblBranch.BranchName = '" & tmpBranchName & "'"
SQL2 = "SELECT tblCredcoAcct.Loan_Officer, tblCredcoAcct.Branch_Name " & _
"FROM tblCredcoAcct " & _
"WHERE tblCredcoAcct.Branch_Name = '" & tmpBranchName & "'"


If tmptype = "MAIN BRANCH" Then
DoCmd.SendObject acSendNoObject, , , "Tempemail@email.com", , , title, "Please deactivate the following Branch Account as this branch has been closed:" + CR + tmpBranchName + CR2 + "Mach ID: " + tmpMachID + CR2 + "Image ID: " + tmpImageID, True
DoCmd.RunSQL SQL1
DoCmd.RunSQL SQL2


Else
DoCmd.SendObject acSendNoObject, , , "Tempemail@email.com", , , title2, "Please deactivate the following Loan Officer Account as this Loan Officer has been terminated:" + CR + tmpLoanOfficer + CR2 + "Branch: " + tmpBranchName + CR2 + "Image ID: " + tmpImageID, True
End If
End Sub

 

[KenHigg]

Have you examined SQL2 in the debug to see what you're getting?

(Also, if you 'code tag' your code when you post it and indent stuff it would make it easier to read )

 

[jdp103]

Erro message

Here is the SQL2 result in debug:
SQL2 "SELECT tblCredcoAcct.Loan_Officer, tblCredcoAcct.Branch_Name FROM tblCredcoAcct WHERE tblCredcoAcct.Branch_Name = '009 TRI CITIES, WA'"

Here is the error I get:
A RunSQL action requires an argument consisting of a SQL statement.

And sorry about not tagging the code...actually don't know how.

 

[KenHigg]

What exactly is the SQL2 query supposed to be doing?

Edit: To tage the code just highlight it and hit the pound sign.

 

[jdp103]

I'm probably making this harder than it needs to be...i just want it to display a list with the results.

 

[KenHigg]

I think you can only do runsql on an action query where you are updating, appending or deleting data. I would just do the query in the query builder on run it from there with a docmd.openquery statement.

Would this work?

 

[jdp103]

That is what i was originally trying to do, however, I don't know how to automatically put in the filter (i.e. by branch) I don't want someone to have to retype it.

 

[KenHigg]

You can simply ref the form's 'branch name' control as a parameter (or criteria) in the query using something like:

forms!myformName![BRANCH NAME]

 

[jdp103]

I created the query as you suggested and now I keep getting this error...now what am i doing wrong!


The action or method requires a Query Name argument.

I replaced the SQL code with this statement:
DoCmd.OpenQuery OpenLOsforClosedBranch, acViewPreview, acReadOnly

SORRY..I'm practically hopeless... )

 

[KenHigg]

DoCmd.OpenQuery "OpenLOsforClosedBranch", acViewPreview, acReadOnly

???

 

[jdp103]

I can't believe it was just that i was missing quotes..how stupid of me!

Anyway, it brought up the query results, but it didn't filter by the branch name on the original form. I get a blank report when i know for a fact that there are accounts for that branch that are active.

i don't want to take any more of your time...you've helped me tremendously!! i don't want to be a nuisance.

 

[KenHigg]

No problem...

What exacly did you put in the query as criteria? (Cut and paste it if you can...)

Edit:

(I'm quessing you need to replace myFormName with your form name and/or put 'Like' in front:

Like forms!myformName![BRANCH NAME]

I need to bug out - Talk with you tomorrow...(?)

 

[jdp103]

I put the following:
forms!tblCredcoAcct![Branch Name]

I didn't use Like because it should be exact as it is a pick list...not a free form text box and the value should be the same across tables.

I even tried using the variable name for that field thinking that the value would still be store and retrievable.

Maybe I could send you the db to look at? its not big?

And again, thank you for all of your help. I would have spent DAYS just getting to the point i am now as i am SO not a "real" developer!

 

[KenHigg]

Is 'tblCredcoAcct' the name of the form? Looks like the name of the underlying table...

 

[jdp103]

It is the name of the table. The name of the form is frmBranchAccount. I was trying both, but when I typed the reply below I got them mixed up. I tried forms!frmBranchAccount!Branch_Account and got an empty report when there are several open Loan Officer accounts that should have shown up in the report.

 

[KenHigg]

Is 'Branch_Account' a number type data field? You may need:

= forms!frmBranchAccount!Branch_Account

???

 

[jdp103]

No, it is a text field. This can't be hopeless, right?? )