Hi Hank,
First, open a code window, then in the menu bar, click on TOOLS/OPTIONS.
Click on the Editor tab, then UNCHECK "Auto Syntax Check". This option just
causes a dialog to appear when there is a syntax error. The error turns red,
so there is no need for the additional warning (in my opinion).
Next, CHECK "Require Variable Declaration". This adds a line to any NEW module
created that requires variables to be DIM'ed. Any new module will have as the
first two lines:
Option Compare Database
Option Explicit
Now for the code.
I can tell that "sysNtSysCd" and "sysNtAplCd" are strings and "sysNtApLkLn" and
"sysNtLn" are integers.
What data types are these variables: (String, Integer, Long, etc.)
sysNtCoNo
sysNtDvNo
sysNtLcCd
sysNtApLkNo
You almost have it. The where clause needs a little different.
Look at the variable "strWhere" in the following modified code. (I changed
'NotesSelectCriteria' to 'strWhere')
(watch for line wrap)
'************************************
Option Compare Database
Option Explicit
Public Function DisplayNotes()
Dim sysNtSysCd As String
Dim sysNtAplCd As String
'this was 'NotesSelectCriteria'
Dim strWhere As String
Dim sysNtCoNo As Integer
Dim sysNtDvNo As Integer
Dim sysNtLcCd As Integer
Dim sysNtApLkNo As Integer
Dim sysNtApLkLn As Integer
Dim sysNtLn As Integer
'initialize
sysNtSysCd = "PU"
sysNtAplCd = "OHNT" '< removed a training space
sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo
sysNtApLkLn = 0
sysNtLn = 1
'create the Where clause
'these are strings
strWhere = "[NtSysCd] = '" & [sysNtSysCd] & "' And "
strWhere = strWhere & "[NtAplCd] = '" & [sysNtAplCd] & "' And "
'and these are numeric
strWhere = strWhere & "[NtCoNo] = " & [sysNtCoNo] & " And "
strWhere = strWhere & "[NtDvNo] = " & [sysNtDvNo] & " And "
strWhere = strWhere & "[NtLcCd] = " & [sysNtLcCd] & " And "
strWhere = strWhere & "[NtApLkNo] = " & [sysNtApLkNo] & " And """
strWhere = strWhere & "[NtApLkLn] = " & [sysNtApLkLn]
'------ for debugging only ------------
MsgBox NotesSelectCriteria
'---------------------------------
sysform = "NtMaint"
'syntax
'DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]
DoCmd.OpenForm sysform, , , strWhere
End Function
'************************************
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Hank@ieultd said:
Steves, Thanks for replying!!!
Below is and example of one of the formats I tried. I have also used long
form field designations quoting the forms etc., etc., etc... to no avail.
This is by no means the only form attempted. Anyway, here is a sample:
Public Function DisplayNotes()
sysNtSysCd = "PU"
sysNtAplCd = "OHNT "
sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo
sysNtApLkLn = 0
sysNtLn = 1
' NotesSelectCriteria = ([NtSysCd] = [sysNtSysCd] And [NtAplCd] =
[sysNtAplCd] And [NtCoNo] = [sysNtCoNo] And [NtDvNo] = [sysNtDvNo] And
[NtLcCd] = [sysNtLcCd] And [NtApLkNo] = [sysNtApLkNo] And [NtApLkLn] =
[sysNtApLkLn])
sysform = "NtMaint"
DoCmd.OpenForm sysform
End Function
I tried the above "NotesSelectionCriteria" string as a filter, where clause
and args both in the openform command and the gotorecord command. None work
and I get the invalid type error on some occasions.
thanks for your help!!!
hank
:
It would be helpful to see the DoCmd.OpenForm statements you tried.
What code are you using to build the where clause?
Steve S.