SQL String in Form? Please help

[Guest]

Could someone decipher what the last line of this means? I gave you a snippit
so you can see what it is starting to do but I think the last line is giving
me problems. Another developer built this and I am lost. I see a table in a
query called CHECKLIST and there is no table or query in this databse named
Checklist. slo I think the last line references a SQL string somewhere that
will help me out. Any ideas?

Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

blnFormLoading = True

'Determine which tabs to show
Set cn = CurrentProject.Connection
strSQL = "Select groupname from qfrmLoanCheckList where Loanid=" &
Me!LoanID.Value & " Group By GroupName Order By GroupName"
rst.Open strSQL, cn, adOpenDynamic, adLockOptimistic

 

[Jim]

Hi all,

Within the current database open the recordset defined by the SQL code found
in strSQL and lock the recordset optmistically.

I hope that helps

Jim

 

[Guest]

When working with SQL, you have to create a string (which he did with the
strSQL Dim statement). Where you have gone astray is not connecting all of
the lines of the string.

i.e.: "I am a string " & _
"and I am happy " & _
"as a string."

Note beginning quotes and ending quotes (allowing space at end of line), the
ampersand (&) and the underscore (_). Since this thing wraps text, I am am
not sure if I can get your text just right. You need a space after GroupName
before the ending quote. After your &, you will need an underscore (_).
Before Me!, you need a beginning quote. Change your (GroupName") to
(GroupName " & _). Before your rst.Open, you need a beginning quote, as well
as an ending quote after the word Optimistic. There are some other parts of
the Sub which I don't see, but have to assume are there. If you don't have
anything after your SQL, you will need at least an End Sub, but I am not sure
what else. Depends on too many variables.

Hope this helps.

 

[John W. Vinson]

Could someone decipher what the last line of this means? I gave you a snippit
so you can see what it is starting to do but I think the last line is giving
me problems. Another developer built this and I am lost. I see a table in a
query called CHECKLIST and there is no table or query in this databse named
Checklist. slo I think the last line references a SQL string somewhere that
will help me out. Any ideas?

Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

blnFormLoading = True

'Determine which tabs to show
Set cn = CurrentProject.Connection
strSQL = "Select groupname from qfrmLoanCheckList where Loanid=" &
Me!LoanID.Value & " Group By GroupName Order By GroupName"
rst.Open strSQL, cn, adOpenDynamic, adLockOptimistic

You say "you see a table in a query called CHECKLIST" - and I have no idea
what you're talking about. The SQL string here is creating and opening a query
based on a (presumably) already existing query named qfrmLoanCheckList; if
your database does not contain this query, the code won't work. If you don't
know what the original developer intended, I certainly don't either!

One point I'd make is that the rst.Open line is attempting to open a query
datasheet. This is usually neither necessary nor a good idea. To display
records in a query, one would usually set a Form's Recordsource to the query
(which could be a SQL string). The comment line "Determine which tabs to show"
is peculiar as well - is there additional code that deals with a Tab Control?

John W. Vinson [MVP]

 

[Guest]

John and the rest that helped.

Here is the entire code...

rivate Sub ConditionCode_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from [pplCheckListLoanItems] WHERE ConditionCode='" &
Me.ConditionCode.Value & "' AND LoanID=" & Me.LoanID.Value & " AND
CheckListID<>" & Me.CheckListID.Value & " AND GroupName='" &
Me.GroupName.Value & "'"
Set cn = CurrentProject.Connection
rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
If Not rs.EOF Then
MsgBox ("Another item is already labeled #" & Me.ConditionCode.Value &
". You should avoid duplicates whenever possible.")
End If

Exit_Sub:
rs.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
Err_Handler:
MsgBox (Err.Description)
Err.Clear
GoTo Exit_Sub
End Sub

The problem that goes with this form is in the main query it has a table or?
named Checklist. Now there is no table or query in this database named
Checklist. So where did it come from and how is all the fields working on
this form whcih are generated from this so-called table. Thank you for all
your help. Any additional ideas?

 

[Guest]

John,

Thanks again for your help. Yes they have the form based on a query, however
the table they are using in the query does not exist in this databaase. I
know it sounds strange but I can not locate it anywhere. You would have to
see the db to believe it!

The code above is based off a field in the form. What I am trying to do is
filter out certain Conditional Codes for a paticular department. The only
problem is I need to figure out how this thing works first. It has hidden
text boxes behind other text boxes that fire off other code etc, etc.
NIGHTMARE!

 

[John W. Vinson]

The code above is based off a field in the form. What I am trying to do is
filter out certain Conditional Codes for a paticular department. The only
problem is I need to figure out how this thing works first. It has hidden
text boxes behind other text boxes that fire off other code etc, etc.
NIGHTMARE!

Well, I wish I could help, but based on what you've posted here I have no idea
what to suggest.

John W. Vinson [MVP]