Multi Field SQL Where Clause

[Guest]

I have seven key fields in a notes table so the table can be accessed from
any application within mutiple systems. I have tried everything I know to
perform a DoCmd.OpenForm with a mutiple field Filter/Where/Args statement.
When the open is performed, the module being opened always is at the first
record in the file and has ignored the selection criteria. Example:
SysCd/AplCd/CoNo/DvNo/LcCd/ApLkNo/ApLkLn are the key fields and the work
fields being passed are all prefixed with sys and defined in a base module at
the front end of each Application Project. The Notes Maintenance Form is
copied to each Application Project as a common module to all Application
Projects. Is Access/VB unable to do multi field comparisons via the open
args/filter and/or SQL Where. If anyone has a good example of a multi Field
SQL Where selection string please fwd. Thanks Hank

 

[SteveS]

It would be helpful to see the DoCmd.OpenForm statements you tried.

What code are you using to build the where clause?


Steve S.

 

[Guest]

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

 

[SteveS]

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.)

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.

 

[Guest]

Hello Again Steve;
I haven't given up yet of course so here goes.
The triple quotes cause a syntax error. When I remove the two additional
quotes the syntax error goes away but the functionality is still absent.
Do the definitions have to be DIM rather than PUBLIC as in
Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc.
Note the co/dv & lc are single fixed fields. I don't know if that makes a
difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct values
are displayed so it has to be close to correct. Just can't identify the
obvious probably.
Thanks Again for all your help!!!
hank

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

 

[Guest]

Hello Again Steve;
I haven't given up yet of course so here goes.

Note: These are VB modules in MS Access 2000...
Note1: An additional double quote appears before the last field name in
the string. That I presume is the source of the syntax error.

The triple quotes cause a syntax error. When I remove the two additional
quotes the syntax error goes away but the functionality is still absent.
Do the definitions have to be DIM rather than PUBLIC as in
Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc.
Note the co/dv & lc are single fixed fields. I don't know if that makes a
difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct values
are displayed so it has to be close to correct. Just can't identify the
obvious probably.
Thanks Again for all your help!!!
hank

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

 

[SteveS]

Hank,

Yes, it should have been a single (double) quote, not three (double) quotes.
And the MsgBox should have been "MsgBox strWhere".

The variables should be Dim'd. Public makes them available to every module.




In this section of code, what/where are

sysPUOHCoNo
sysPUOHDvNo
sysPUOHLcCd
sysPUOHNo


Are they control names? On a form?



Where is this function? Code behind a form or a standard module? If a form,
what is the form name?
How are you calling the function? (a button?)

Why are you using a Function instead of a Sub?

Have you traced (stepped thru) the code? Where is the error?

What exactly doesn't work?

Are the fields

[NtCoNo]
[NtDvNo]
[NtLcCd]
[NtApLkNo]
[NtApLkLn]


in the recordsource for the form "NtMaint"?



Sorry for all the questions, but since I can't see your mdb, I have to be a two
year old...... <grin>

Hang in there...

Steve S.

 

[SteveS]

Also, add a line below the line

Public Function DisplayNotes()
' add this line
Dim sysform As String

Dim sysNtSysCd As String
Dim sysNtAplCd As String

'this was 'NotesSelectCriteria'
Dim strWhere As String
'------snip----------


Another question....

Have you tried compiling the code?


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hank,

Yes, it should have been a single (double) quote, not three (double)
quotes.
And the MsgBox should have been "MsgBox strWhere".

The variables should be Dim'd. Public makes them available to every module.





In this section of code, what/where are

sysPUOHCoNo
sysPUOHDvNo
sysPUOHLcCd
sysPUOHNo


Are they control names? On a form?



Where is this function? Code behind a form or a standard module? If a
form, what is the form name?
How are you calling the function? (a button?)

Why are you using a Function instead of a Sub?

Have you traced (stepped thru) the code? Where is the error?

What exactly doesn't work?

Are the fields

[NtCoNo]
[NtDvNo]
[NtLcCd]
[NtApLkNo]
[NtApLkLn]


in the recordsource for the form "NtMaint"?



Sorry for all the questions, but since I can't see your mdb, I have to
be a two year old...... <grin>

Hang in there...

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hello Again Steve;
I haven't given up yet of course so here goes.
The triple quotes cause a syntax error. When I remove the two
additional quotes the syntax error goes away but the functionality is
still absent. Do the definitions have to be DIM rather than PUBLIC
as in Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc. Note the co/dv & lc are single fixed fields. I don't know if
that makes a difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct
values are displayed so it has to be close to correct. Just can't
identify the obvious probably.
Thanks Again for all your help!!!
hank

 

[Guest]

Steve...Per the below:
The working variables starting with sys are publicly defined because I need
them to be available to all the forms in this application stream. If a user
elects to enter/view/delete a note at the PO header/PO Detail/Receiving
Header or Detail I want them to use the same Public Function to display the
Form with data from the notes table based on the key field info indicated.
The Notes table fields which are also the controls on the NtMaint form are
the NtSysCd/NtAplCd/NtCoNo/NtDvNo/NtLcCd/NtApLkNo/NtApLkLn/NtLn... the
associated sys fields are dynamically refreshed by a private routine
assoicated with a text button (not command button) on the form from which the
user requests to view any associated notes. Once Refreshed the DisplayNotes
Function is called/executed and should present and allow
editing/adding/deletion of only the records from the notes table on the notes
forms for the application form from which the user chose the notes display
function. This is a simple treed architechture normalized data base with
each application using the link table function to access only the necessary
files for that application. The Notes function provides a pool of all notes
for all applications through which a user can scan and then backward tree
back to the actual application detail to which the note is attached.
I hope this helps. When the access application is executed I still get the
same results I did with my original select/filter statement. I also created
the DIM statements as you indicated and that didn't make any difference.
Thanks for Letting Me bang your brain around!!!
hank

Also, add a line below the line

Public Function DisplayNotes()
' add this line
Dim sysform As String

Dim sysNtSysCd As String
Dim sysNtAplCd As String

'this was 'NotesSelectCriteria'
Dim strWhere As String
'------snip----------


Another question....

Have you tried compiling the code?


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hank,

Yes, it should have been a single (double) quote, not three (double)
quotes.
And the MsgBox should have been "MsgBox strWhere".

The variables should be Dim'd. Public makes them available to every module.

sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo

In this section of code, what/where are

sysPUOHCoNo
sysPUOHDvNo
sysPUOHLcCd
sysPUOHNo


Are they control names? On a form?



Where is this function? Code behind a form or a standard module? If a
form, what is the form name?
How are you calling the function? (a button?)

Why are you using a Function instead of a Sub?

Have you traced (stepped thru) the code? Where is the error?

What exactly doesn't work?

Are the fields

[NtCoNo]
[NtDvNo]
[NtLcCd]
[NtApLkNo]
[NtApLkLn]


in the recordsource for the form "NtMaint"?



Sorry for all the questions, but since I can't see your mdb, I have to
be a two year old...... <grin>

Hang in there...

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hello Again Steve;
I haven't given up yet of course so here goes.
The triple quotes cause a syntax error. When I remove the two
additional quotes the syntax error goes away but the functionality is
still absent. Do the definitions have to be DIM rather than PUBLIC
as in Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc. Note the co/dv & lc are single fixed fields. I don't know if
that makes a difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct
values are displayed so it has to be close to correct. Just can't
identify the obvious probably.
Thanks Again for all your help!!!
hank

 

[Guest]

OK........ I dropped the Dim's and went back to the original sequene of
events. I recompiled the project and discovered another error in two
undefined entries. Once corrected and recompiled IT WORKS EXACTLY LIKE I
WANT IT TOOOO!!!
Thanks Again for Your Help Determining the SQL Statement errors.
By the way, is there any difference in the stating of integers/long & single
fixed fields when addressing them in and SQL statement. I know the
alpha/symbol variances but I am not sure if the different numeric types
require any special tweaking within and SQL statement.

Steve...Per the below:
The working variables starting with sys are publicly defined because I need
them to be available to all the forms in this application stream. If a user
elects to enter/view/delete a note at the PO header/PO Detail/Receiving
Header or Detail I want them to use the same Public Function to display the
Form with data from the notes table based on the key field info indicated.
The Notes table fields which are also the controls on the NtMaint form are
the NtSysCd/NtAplCd/NtCoNo/NtDvNo/NtLcCd/NtApLkNo/NtApLkLn/NtLn... the
associated sys fields are dynamically refreshed by a private routine
assoicated with a text button (not command button) on the form from which the
user requests to view any associated notes. Once Refreshed the DisplayNotes
Function is called/executed and should present and allow
editing/adding/deletion of only the records from the notes table on the notes
forms for the application form from which the user chose the notes display
function. This is a simple treed architechture normalized data base with
each application using the link table function to access only the necessary
files for that application. The Notes function provides a pool of all notes
for all applications through which a user can scan and then backward tree
back to the actual application detail to which the note is attached.
I hope this helps. When the access application is executed I still get the
same results I did with my original select/filter statement. I also created
the DIM statements as you indicated and that didn't make any difference.
Thanks for Letting Me bang your brain around!!!
hank

Also, add a line below the line

Public Function DisplayNotes()
' add this line
Dim sysform As String

Dim sysNtSysCd As String
Dim sysNtAplCd As String

'this was 'NotesSelectCriteria'
Dim strWhere As String
'------snip----------


Another question....

Have you tried compiling the code?


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hank,

Yes, it should have been a single (double) quote, not three (double)
quotes.
And the MsgBox should have been "MsgBox strWhere".

The variables should be Dim'd. Public makes them available to every module.




sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo


In this section of code, what/where are

sysPUOHCoNo
sysPUOHDvNo
sysPUOHLcCd
sysPUOHNo


Are they control names? On a form?



Where is this function? Code behind a form or a standard module? If a
form, what is the form name?
How are you calling the function? (a button?)

Why are you using a Function instead of a Sub?

Have you traced (stepped thru) the code? Where is the error?

What exactly doesn't work?

Are the fields

[NtCoNo]
[NtDvNo]
[NtLcCd]
[NtApLkNo]
[NtApLkLn]


in the recordsource for the form "NtMaint"?



Sorry for all the questions, but since I can't see your mdb, I have to
be a two year old...... <grin>

Hang in there...

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hank@ieultd wrote:

Hello Again Steve;
I haven't given up yet of course so here goes.
The triple quotes cause a syntax error. When I remove the two
additional quotes the syntax error goes away but the functionality is
still absent. Do the definitions have to be DIM rather than PUBLIC
as in Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc. Note the co/dv & lc are single fixed fields. I don't know if
that makes a difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct
values are displayed so it has to be close to correct. Just can't
identify the obvious probably.
Thanks Again for all your help!!!
hank

 

[SteveS]

"I wondered all night where the sun went when it set, and finally it dawned on me"

Hank,

So if I understand right, the variables that begin with "sys" are globally
defined variables. You are using them in a function declared Public.

Trying to DIM variables in a public function when the variables are already
DIM'd will generate an error (of course!!). OK, I got it. (better later than...)

For the SQL string, text needs to be delimited with quotes:

strWhere = "[NtSysCd] = '" & [sysNtSysCd] & "' And "


Dates are delimited with hashs (#):


strWhere = "[dteSysDate] = #" & Me.SomeDate & "# And " _ ...


Numbers do not require delimiters

strWhere = "[lngCd_ID] = " & Me.A_Number_ID & " And " _ ...



And....., its great you were able to find the problems and get it to work. I
also struggled with creating the SQL string when I first started. It does get
easier with practice. <grin>


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

OK........ I dropped the Dim's and went back to the original sequene of
events. I recompiled the project and discovered another error in two
undefined entries. Once corrected and recompiled IT WORKS EXACTLY LIKE I
WANT IT TOOOO!!!
Thanks Again for Your Help Determining the SQL Statement errors.
By the way, is there any difference in the stating of integers/long & single
fixed fields when addressing them in and SQL statement. I know the
alpha/symbol variances but I am not sure if the different numeric types
require any special tweaking within and SQL statement.

:

Steve...Per the below:
The working variables starting with sys are publicly defined because I need
them to be available to all the forms in this application stream. If a user
elects to enter/view/delete a note at the PO header/PO Detail/Receiving
Header or Detail I want them to use the same Public Function to display the
Form with data from the notes table based on the key field info indicated.
The Notes table fields which are also the controls on the NtMaint form are
the NtSysCd/NtAplCd/NtCoNo/NtDvNo/NtLcCd/NtApLkNo/NtApLkLn/NtLn... the
associated sys fields are dynamically refreshed by a private routine
assoicated with a text button (not command button) on the form from which the
user requests to view any associated notes. Once Refreshed the DisplayNotes
Function is called/executed and should present and allow
editing/adding/deletion of only the records from the notes table on the notes
forms for the application form from which the user chose the notes display
function. This is a simple treed architechture normalized data base with
each application using the link table function to access only the necessary
files for that application. The Notes function provides a pool of all notes
for all applications through which a user can scan and then backward tree
back to the actual application detail to which the note is attached.
I hope this helps. When the access application is executed I still get the
same results I did with my original select/filter statement. I also created
the DIM statements as you indicated and that didn't make any difference.
Thanks for Letting Me bang your brain around!!!
hank

:

Also, add a line below the line

Public Function DisplayNotes()
' add this line
Dim sysform As String

Dim sysNtSysCd As String
Dim sysNtAplCd As String

'this was 'NotesSelectCriteria'
Dim strWhere As String
'------snip----------


Another question....

Have you tried compiling the code?


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

SteveS wrote:

Hank,

Yes, it should have been a single (double) quote, not three (double)
quotes.
And the MsgBox should have been "MsgBox strWhere".

The variables should be Dim'd. Public makes them available to every module.





sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo


In this section of code, what/where are

sysPUOHCoNo
sysPUOHDvNo
sysPUOHLcCd
sysPUOHNo


Are they control names? On a form?



Where is this function? Code behind a form or a standard module? If a
form, what is the form name?
How are you calling the function? (a button?)

Why are you using a Function instead of a Sub?

Have you traced (stepped thru) the code? Where is the error?

What exactly doesn't work?

Are the fields

[NtCoNo]
[NtDvNo]
[NtLcCd]
[NtApLkNo]
[NtApLkLn]


in the recordsource for the form "NtMaint"?



Sorry for all the questions, but since I can't see your mdb, I have to
be a two year old...... <grin>

Hang in there...

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hank@ieultd wrote:


Hello Again Steve;
I haven't given up yet of course so here goes.
The triple quotes cause a syntax error. When I remove the two
additional quotes the syntax error goes away but the functionality is
still absent. Do the definitions have to be DIM rather than PUBLIC
as in Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc. Note the co/dv & lc are single fixed fields. I don't know if
that makes a difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct
values are displayed so it has to be close to correct. Just can't
identify the obvious probably.
Thanks Again for all your help!!!
hank

 

[Guest]

Ayh Steve;
Thanks again for the Help. I ran a recompile over the other projects and
found the same two missing module problems which this little exercise
exposed. Fortunately most of the SQL statements werre right on and didn't
need further tweeking.
me best,
hank