Opening a Form From 'nuter Form

[S Jackson]

Hopefully this is an easy one:

I have a popup form for users to search the db. They select criteria, click
Search and a popup box displays the number of records found and then the
main form displays the search results in a subform. Within the subform, the
user can select individual records to view - when the click the button to
view the record, another form opens with the record selected.

What I want to do is put a button on the main form so the user can browse
all of the search results at once in the other form. Here is what I have so
far:

stLinkCriteria = "[CaseId]=" & Me![fsubResult]![CaseId]
stDocName = "frmMaster"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Unfortunately, the form opens up and displays only the current record
selected within the subform, not all of the records. Do I have to use the
strSQL=String solution and write an entire select query for the form to open
up? I hesitate to do this because of the length of the string - ugh.

TIA
S. Jackson

 

[Albert D. Kallal]

I assume that your popup form that has the search results returns, or uses a
"where" sql clause.right?

So, to populate your sub form, you go:

strMySql = "select * from tblCustomers where " & strWhere

me.MySubForm.Form.recordSouce = strMySql

And, to open up a form, with the same filter, you go:

docmd.OpenForm "frmCustomers",,,strWhere

I mean, the real issue here is how do you populate the sub-form? how is the
sql, or "where" clause formed? Whatever you did to popular the sub-form, do
the same when you open the form in details mode.

 

[S Jackson]

Thanks. I figured that out after I posted, but now I'm having with the
string. It keeps giving me an "invalid syntax" error and it is virtually
the same string that opens the subform. I've studied this and can't figure
out where the error is. Maybe you can pick out the error?

stLinkCriteria = " Like " & "'" & Me.Searchtxt & "*" & "'"
strSQL = "SELECT tblCaseInfo.*, tblStatus.ClosedDate " _
& "FROM tblCaseInfo LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId " _
& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _
& "ORDER BY tblCaseInfo.CaseName;"

TIA
S. Jackson

 

[Albert D. Kallal]

& "WHERE CaseName " & stLinkCriteria & " And ClosedDate Is Null" & _
& " ORDER BY CaseName;"


Often I just put the whole thing into a the query builder, an remove all
conditions, and all order by. I then use:

strSql = CurrentDb.QueryDefs("yourQury").SQL
strSql = Left(strQuerySql, InStr(strSql, ";") - 1)


strSql = strSql & "WHERE CaseName " & stLinkCriteria & _
" And ClosedDate Is Null" & _
" ORDER BY CaseName;"

 

[S Jackson]

I like your solution. Much neater and cleaner. Here's how mine looks:

stLinkCriteria = "Like " & "'" & Me.Searchtxt & "*" & "'"

strSQL = CurrentDb.QueryDefs("qryLTCRAll").SQL
strSQL = Left(strQuerySql, InStr(strSQL, ";") - 1)
strSQL = strSQL & " WHERE CaseName " & stLinkCriteria & " And
ClosedDate Is Null " _
& "ORDER BY CaseName;"

However. . . I keep getting a missing syntax error ("new" is the criteria I
entered when testing - "new" is part of a CaseName as in "New Hope"):

Syntax error (missing operator) in query expression ' WHERE CaseName Like
'new*' And ClosedDate Is Null ORDER BY CaseName;'.

I am still lost here. The only thing I can see with this is that it keeps
plopping in that period at the end of the statement. Is that the problem?
If so, I don't know how to fix that. I can't find a stray period anywhere
in my code - I cut and pasted the exact code into this message.

TIA
S. Jackson

 

[Douglas J. Steele]

What does the content of strSQL look like?

 

[Albert D. Kallal]

Yes, just put:

strSQL = CurrentDb.QueryDefs("qryLTCRAll").SQL
strSQL = Left(strQuerySql, InStr(strSQL, ";") - 1)
strSQL = strSQL & " WHERE CaseName " & stLinkCriteria & " And
ClosedDate Is Null " _
& "ORDER BY CaseName;"

debug.Print strSql <----- this will allow you to bring up the
debugger, and cut/past the text
Msgbox strSql <----- this will display the sql

How does the sql look?, can you cut and past the sql into the query buidler?

Or, paste it here...what does it look like?

 

[S Jackson]

After I posted I realized I had an error in the 2nd line of the code:
strSQL = Left(strQuerySql, InStr(strSQL, ";") - 1)
should have been: strSQL = Left(strSQL, InStr(strSQL, ";") - 1)

But, still got problems with syntax errors. Here is the SQL for the
"qryLTCRall"

SELECT tblCaseInfo.*, [tblStatus].[ClosedDate], tblRegion.*,
tblRepresentatives.*
FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
[tblRepresentatives].[ID]=[tblCaseInfo].[RepID]) LEFT JOIN tblRegion ON
[tblCaseInfo].[FieldOfc]=[tblRegion].[ID]) LEFT JOIN tblStatus ON
[tblCaseInfo].[CaseId]=[tblStatus].[CaseId];

Using Msgbox strSQL. Here is what I get (which appears to be identical to
above except for the Where clause):

SELECT tblCaseInfo.*, [tblStatus].[ClosedDate], tblRegion.*,
rblRepresentatives.*
FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
[tblRepresentatives].[ID]=[tblCaseInfo].[RepID]) LEFT JOIN tblRegion ON
[tblCaseInfo].[FieldOfc]=[tblRegion].[ID]) LEFT JOIN tblStatus ON
[tblCaseInfo].[CaseId]=[tblStatus].[CaseId] WHERE CaseName Like 'new*' And
ClosedDate Is
Null ORDER BY CaseName;

The error msg reads as follows:

Syntax error . in query expression 'SELECT tblCaseInfo.*,
[tblStatus].[ClosedDate], tblRegion.*,
tblRepresentatives.*
FROM (tblRepresentatives RIGHT JOIN tblCaseInfo ON
[tblRepresentatives].[ID]=[tblCaseInfo].[RepID]) LEFT JOIN tblRegion ON
[tblCaseInfo].[FieldOfc]=[tblRegion].[ID]) LEFT ' .

It stops there at "LEFT"

HTH you with this. This writing SQL makes me batty.

S. Jackson

 

[S Jackson]

Please pardon some of the typos in my previous message. AND, I did not type
the error msg correctly:

1. The 3rd line is -- tblRepresentatives.* not rblRepresentatives
2. In the FROM line it is ((tblRepresentatives . . . Not
(tblRepresentatives

I am so sorry. I know that doesn't help the confusion.
S. Jackson

After I posted I realized I had an error in the 2nd line of the code:
strSQL = Left(strQuerySql, InStr(strSQL, ";") - 1)
should have been: strSQL = Left(strSQL, InStr(strSQL, ";") - 1)

But, still got problems with syntax errors. Here is the SQL for the
"qryLTCRall"

SELECT tblCaseInfo.*, [tblStatus].[ClosedDate], tblRegion.*,
tblRepresentatives.*
FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
[tblRepresentatives].[ID]=[tblCaseInfo].[RepID]) LEFT JOIN tblRegion ON
[tblCaseInfo].[FieldOfc]=[tblRegion].[ID]) LEFT JOIN tblStatus ON
[tblCaseInfo].[CaseId]=[tblStatus].[CaseId];

Using Msgbox strSQL. Here is what I get (which appears to be identical to
above except for the Where clause):

SELECT tblCaseInfo.*, [tblStatus].[ClosedDate], tblRegion.*,
rblRepresentatives.*
FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
[tblRepresentatives].[ID]=[tblCaseInfo].[RepID]) LEFT JOIN tblRegion ON
[tblCaseInfo].[FieldOfc]=[tblRegion].[ID]) LEFT JOIN tblStatus ON
[tblCaseInfo].[CaseId]=[tblStatus].[CaseId] WHERE CaseName Like 'new*' And
ClosedDate Is
Null ORDER BY CaseName;

The error msg reads as follows:

Syntax error . in query expression 'SELECT tblCaseInfo.*,
[tblStatus].[ClosedDate], tblRegion.*,
tblRepresentatives.*
FROM (tblRepresentatives RIGHT JOIN tblCaseInfo ON
[tblRepresentatives].[ID]=[tblCaseInfo].[RepID]) LEFT JOIN tblRegion ON
[tblCaseInfo].[FieldOfc]=[tblRegion].[ID]) LEFT ' .

It stops there at "LEFT"

HTH you with this. This writing SQL makes me batty.

S. Jackson

Yes, just put:

debug.Print strSql <----- this will allow you to bring up the
debugger, and cut/past the text
Msgbox strSql <----- this will display the sql

How does the sql look?, can you cut and past the sql into the query buidler?

Or, paste it here...what does it look like?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

 

[Albert D. Kallal]

note how I gave you the idea to use the debug.print.

After you run,and get the error, you can open up the debug window, and then
look at the sql, and in fact cut/past the sql into a message here, but even
better, is paste it into the sql editor..and work on it...