same database in Access 2000 and '97

  • Thread starter Thread starter Robert Solomon
  • Start date Start date
R

Robert Solomon

Hi. I have to use a database created in Access 97 and now used in both
Access '97 and 2000. On a form I have a button that is to search a
table and insert the selected record into a field. This works fine in
Access 97 but the same button does not work at all in Acc 2000 where it
always retrieves all blanks. The event procedure is

Private Sub SearchICD9_Click()
On Error GoTo Err_SearchICD9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "srhICD9Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SearchICD9_Click:
Exit Sub

Err_SearchICD9_Click:
MsgBox Err.Description
Resume Exit_SearchICD9_Click

End Sub


Why does this event procedure not work in Access 2000?

Thanks
 
Hi. I have to use a database created in Access 97 and now used in both
Access '97 and 2000. On a form I have a button that is to search a
table and insert the selected record into a field. This works fine in
Access 97 but the same button does not work at all in Acc 2000 where it
always retrieves all blanks. The event procedure is

Private Sub SearchICD9_Click()
On Error GoTo Err_SearchICD9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "srhICD9Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SearchICD9_Click:
Exit Sub

Err_SearchICD9_Click:
MsgBox Err.Description
Resume Exit_SearchICD9_Click

End Sub


Why does this event procedure not work in Access 2000?
Click to expand...
It won't work in A97 either, at least the way you might be thinking.
You defined the variable stLinkCriteria, but haven't assigned a value
to it.

- Jim
 
It won't work in A97 either, at least the way you might be thinking.
You defined the variable stLinkCriteria, but haven't assigned a value
to it.
Click to expand...
I may not have described the problem accurately. The stLinkCriteria is
entered on the form.

Is this code accurate for Access 97 and Access 2000? I know it works in
Acc97, but it may not in Acc2000

Public Function IsLoaded(ByVal strFormName As String) As Integer
' Returns true if the specified form is open in form view or datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function


Thanks again
 
Yes, it does work, Jim.

It simply means that strLinkCriteria defaults to an empty String and there
is no WhereCondition specified in the OpenForm. The CommandButton Wizard
does this all the time, probably from a code template.
 
(Newsgroups trimmed. Please restrict to max. 3 relevant newsgroups)

IsLoaded() should work fine AFAIK in A2K. OTOH, for A2K and later, you can
use:

CurrentProject.AllForms("FormName").IsLoaded
 
Maybe I'm approaching this from the wrong way. I have a query that gets
its param from a form. This is not working in Acc2000. The query is:
SELECT [ICD9-93].ICD9Desc, [ICD9-93].ICD9Code, [ICD9-93].ICD9Id
FROM [ICD9-93]
WHERE ((([ICD9-93].ICD9Desc) Like "|[Forms]![srhICD9Form]!
[SearchCriteria]|"))
ORDER BY [ICD9-93].ICD9Code;

This works in Acc97, why not in Acc2000?

Thanks
 
Unless something went wrong when you copied and pasted the SQL String, I am
very surprised that the Query / SQL String works correctly in A97. The SQL
should be something like:

SELECT [ICD9-93].ICD9Desc, [ICD9-93].ICD9Code, [ICD9-93].ICD9Id
FROM [ICD9-93]
WHERE [ICD9-93].ICD9Desc
Like "*" & [Forms]![srhICD9Form]![SearchCriteria] & "*"
ORDER BY [ICD9-93].ICD9Code;

(you need to use wildcards with "Like". Not sure what the "pipe" character
was doing in the posted SQL.)
 
Yes, I understand that. What I was pointing out was that it would not
work the way that he was likely thinking it would work. It really made
no sense in view of the way he was describing the problem. The version
he was using was irrelevant to the issue. That does look like standard
wizard generated code.

- Jim
 
Unless something went wrong when you copied and pasted the SQL String, I am
very surprised that the Query / SQL String works correctly in A97. The SQL
should be something like:

SELECT [ICD9-93].ICD9Desc, [ICD9-93].ICD9Code, [ICD9-93].ICD9Id
FROM [ICD9-93]
WHERE [ICD9-93].ICD9Desc
Like "*" & [Forms]![srhICD9Form]![SearchCriteria] & "*"
ORDER BY [ICD9-93].ICD9Code;

(you need to use wildcards with "Like". Not sure what the "pipe" character
was doing in the posted SQL.)
Click to expand...
I designed this table when Acc97 was current, and I found I needed the
vertical pipe characters for it to work. In the searchcriteria field I
would type the * chars myself. I didn't think of moving those into the
query as you did. But my kludge did not work in Acc2000 or AccXP. When
I changed the query as you indicate, it now works.

Thanks.
 
Van said:
Unless something went wrong when you copied and pasted the SQL String, I am
very surprised that the Query / SQL String works correctly in A97. The SQL
should be something like:

SELECT [ICD9-93].ICD9Desc, [ICD9-93].ICD9Code, [ICD9-93].ICD9Id
FROM [ICD9-93]
WHERE [ICD9-93].ICD9Desc
Like "*" & [Forms]![srhICD9Form]![SearchCriteria] & "*"
ORDER BY [ICD9-93].ICD9Code;

(you need to use wildcards with "Like". Not sure what the "pipe" character
was doing in the posted SQL.)
Click to expand...


Just to clarify for future reference, the pipe character was
an archaic expression evaluation operator, kind of like an
Eval that worked inside of quoted strings. For example:
the string "a|2+3|z" would end up as a5z. The last
documentation I saw that included this operator was in A2
and I believe it was finally removed in A2XP.
 
OK. The description wasn't entirely clear and O.P.
changed the question twice.

Van T. Dinh
MVP (Access)
 
Thanks, Marsh.

I only used A2 (with Macros) for a short time before I
switched to A94 so I never knew the pipe.

Cheers
Van T. Dinh
 
Back
Top