access 2003

[doglover]

Geoff:
From my previous e-mail I noticed a mistake in the code I relayed.
The code below is missing a piece
1.) after Me!ChooseJob.Requery
add: Me.Requery
End Sub

2.) Something else that is strange. I was reviewing the frmInvoiceSelect
2003 version compared to 97 version. Specifically, the query resource behind
the unbound combo box on the form header Name: ChooseJob. This query has the
criteria Like nz([forms]![frmInvoiceSelect]![ChooseCust],"*") and I decided
to run the query in the design view and I received an error message ...
invalid syntax refering TO brackets. I looked and looked at the query in
design view but no error with the syntax was visible. So I changed the
view to SQL and the code was: PARAMETERS
[[forms]]!frmInvoiceSelect![[ChooseCust]] Text (255); The balance of the code
was equal to the current text that follows in the next paragraph.
So I removed the double brackets around forms and ChooseCust and this is the
code that is now in the SQL view for this query which runs with no error
message but the form still has the same problem as before the detail is not
synchronized with the combo boxes on the form header.

PARAMETERS [forms]!frmInvoiceSelect![ChooseCust] Text ( 255 );
SELECT DISTINCT tblJob.jobID, tblJob.jobnm, tblJob.jobdt_start
FROM tblJob INNER JOIN tblWorkOrder ON tblJob.jobID=tblWorkOrder.wrkky_jobid
WHERE (((tblWorkOrder.wrkdt_comp) Is Not Null) And ((tblJob.jobKy_CusNm)
Like nz(forms!frmInvoiceSelect!ChooseCust,"*")) And
((tblWorkOrder.wrkky_invno) Is Null Or (tblWorkOrder.wrkky_invno)=0 Or
(tblWorkOrder.wrkky_invno)=-1))
ORDER BY tblJob.jobID;

Code for version 97 is:

PARAMETERS forms!frmInvoiceSelect!ChooseCust Text;
SELECT DISTINCT tblJob.jobID, tblJob.jobnm, tblJob.jobdt_start
FROM tblJob INNER JOIN tblWorkOrder ON tblJob.jobID = tblWorkOrder.wrkky_jobid
WHERE (((tblWorkOrder.wrkdt_comp) Is Not Null) AND ((tblJob.jobKy_CusNm)
Like nz([forms]![frmInvoiceSelect]![ChooseCust],"*")) AND
((tblWorkOrder.wrkky_invno) Is Null Or (tblWorkOrder.wrkky_invno)=0 Or
(tblWorkOrder.wrkky_invno)=-1))
ORDER BY tblJob.jobID;

In case you are wondering, I copied and pasted the code from each module
rather than trying to type with no errors.

I am wondering if there is a problem deeper than just the forms but the code
source that provides instructions to the expressions, etc. If you recall I
indicated that the expression in the on click event is different from version
97 database.

version 2003: =zfSortLabel("tktno",Screen.ActiveForm)
version 97: =zfSortLabel("tktno",[Screen].[ActiveForm])

If you type in the missing brackets on the zfSortLabel, etc for version 2003
and close the screen the replaed bracket go away but no error message ever
appears.

The database I am working with uses the execution file from a retail version
of access 2003 but I noticed under the option, Default File Format "Access
2000" is selected rather than the other option of "Access 2002-2003."

This might be more information than needed I just seemed important, so I am
sending to you.

Once again, I thank you for your help.

Regards,
doglover

Geoff:
1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

The code behind the After_update event on the ChooseJob combo box:
Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery
End Sub

frmInvoiceSelect allows filters and has the following code:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True

End Sub


Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.


2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed that
an access built in function directs the expressions to fire. I was wondering
if perhaps a built-in function could be missing or a predefined property?
Just a thought.

I am attempting to look for missing modules in the Access 97 version will
advise in a seperate post.

I am a Florida native and unfortunately I do not speak French. I am
curious, why French?

Regards,
doglover

Correction (apologies):

Clearly, you should have a function in a standard module called
"zfSortLabel".

In a standard module, the function should appear something like this:

Public Function zfSortLabel(Argument1, Argument2)
... Code here...
End Function

Therefore, I should have advised you to search for "zfSortLabel" (not "=zf")
in the VBA editor.

Notice, there is no leading equals sign (=) when you search for
"zfSortLabel". This is because there is no leading equals sign in the
function's definition (see above - "Public Function zfSortLabel"). As you
know, you're trying to find the function to establish its existence or
absence.

In contrast, the leading equals sign is used in the OnClick event property
of the 5 labels in the form header ("btnktkno", "btntktfl_invoice", etc).
This is because the equals sign tells Access to call a function when the
OnClick event is fired. Therefore, the OnClick event property calls the
function as "=zfSortLabel(arg1, arg2). Obviously, the function does
something using the arguments that are passed to it. As you indicate, it
probably sorts something on the form. It may sort something different
depending on which label is clicked - which may send different arguments to
the function.

Sorry if this is all obvious to you - I'm just correcting an earlier
mistake.

Geoff.

 

[doglover]

Geoff:

Today I went on line with Microsoft to troubleshoot this form via a live
meeting and so far they have not been able to discover the problem. The code
is working correctly but the detail section of the form is not synchronizing
with the form header combo boxes.

I will keep you posted as the details are revealed.

Thanks again for all of your help.

Regards,
doglover

Geoff:
From my previous e-mail I noticed a mistake in the code I relayed.
The code below is missing a piece
1.) after Me!ChooseJob.Requery
add: Me.Requery
End Sub

2.) Something else that is strange. I was reviewing the frmInvoiceSelect
2003 version compared to 97 version. Specifically, the query resource behind
the unbound combo box on the form header Name: ChooseJob. This query has the
criteria Like nz([forms]![frmInvoiceSelect]![ChooseCust],"*") and I decided
to run the query in the design view and I received an error message ...
invalid syntax refering TO brackets. I looked and looked at the query in
design view but no error with the syntax was visible. So I changed the
view to SQL and the code was: PARAMETERS
[[forms]]!frmInvoiceSelect![[ChooseCust]] Text (255); The balance of the code
was equal to the current text that follows in the next paragraph.
So I removed the double brackets around forms and ChooseCust and this is the
code that is now in the SQL view for this query which runs with no error
message but the form still has the same problem as before the detail is not
synchronized with the combo boxes on the form header.

PARAMETERS [forms]!frmInvoiceSelect![ChooseCust] Text ( 255 );
SELECT DISTINCT tblJob.jobID, tblJob.jobnm, tblJob.jobdt_start
FROM tblJob INNER JOIN tblWorkOrder ON tblJob.jobID=tblWorkOrder.wrkky_jobid
WHERE (((tblWorkOrder.wrkdt_comp) Is Not Null) And ((tblJob.jobKy_CusNm)
Like nz(forms!frmInvoiceSelect!ChooseCust,"*")) And
((tblWorkOrder.wrkky_invno) Is Null Or (tblWorkOrder.wrkky_invno)=0 Or
(tblWorkOrder.wrkky_invno)=-1))
ORDER BY tblJob.jobID;

Code for version 97 is:

PARAMETERS forms!frmInvoiceSelect!ChooseCust Text;
SELECT DISTINCT tblJob.jobID, tblJob.jobnm, tblJob.jobdt_start
FROM tblJob INNER JOIN tblWorkOrder ON tblJob.jobID = tblWorkOrder.wrkky_jobid
WHERE (((tblWorkOrder.wrkdt_comp) Is Not Null) AND ((tblJob.jobKy_CusNm)
Like nz([forms]![frmInvoiceSelect]![ChooseCust],"*")) AND
((tblWorkOrder.wrkky_invno) Is Null Or (tblWorkOrder.wrkky_invno)=0 Or
(tblWorkOrder.wrkky_invno)=-1))
ORDER BY tblJob.jobID;

In case you are wondering, I copied and pasted the code from each module
rather than trying to type with no errors.

I am wondering if there is a problem deeper than just the forms but the code
source that provides instructions to the expressions, etc. If you recall I
indicated that the expression in the on click event is different from version
97 database.

version 2003: =zfSortLabel("tktno",Screen.ActiveForm)
version 97: =zfSortLabel("tktno",[Screen].[ActiveForm])

If you type in the missing brackets on the zfSortLabel, etc for version 2003
and close the screen the replaed bracket go away but no error message ever
appears.

The database I am working with uses the execution file from a retail version
of access 2003 but I noticed under the option, Default File Format "Access
2000" is selected rather than the other option of "Access 2002-2003."

This might be more information than needed I just seemed important, so I am
sending to you.

Once again, I thank you for your help.

Regards,
doglover

Geoff:
1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

The code behind the After_update event on the ChooseJob combo box:
Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery
End Sub

frmInvoiceSelect allows filters and has the following code:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True

End Sub


Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.


2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed that
an access built in function directs the expressions to fire. I was wondering
if perhaps a built-in function could be missing or a predefined property?
Just a thought.

I am attempting to look for missing modules in the Access 97 version will
advise in a seperate post.

I am a Florida native and unfortunately I do not speak French. I am
curious, why French?

Regards,
doglover

Correction (apologies):

Clearly, you should have a function in a standard module called
"zfSortLabel".

In a standard module, the function should appear something like this:

Public Function zfSortLabel(Argument1, Argument2)
... Code here...
End Function

Therefore, I should have advised you to search for "zfSortLabel" (not "=zf")
in the VBA editor.

Notice, there is no leading equals sign (=) when you search for
"zfSortLabel". This is because there is no leading equals sign in the
function's definition (see above - "Public Function zfSortLabel"). As you
know, you're trying to find the function to establish its existence or
absence.

In contrast, the leading equals sign is used in the OnClick event property
of the 5 labels in the form header ("btnktkno", "btntktfl_invoice", etc).
This is because the equals sign tells Access to call a function when the
OnClick event is fired. Therefore, the OnClick event property calls the
function as "=zfSortLabel(arg1, arg2). Obviously, the function does
something using the arguments that are passed to it. As you indicate, it
probably sorts something on the form. It may sort something different
depending on which label is clicked - which may send different arguments to
the function.

Sorry if this is all obvious to you - I'm just correcting an earlier
mistake.

Geoff.

 

[Geoff]

This is a very long reply!
I could not finish it yesterday.
I have tried to comprehensively answer your questions.

I would focus on the queries behind the combo boxes. Examine in detail why
they list invalid customers or jobs or both. I think they are passing
invalid criteria to the query behind the form.

I'll be working flat out next week, so if you have any further questions,
post back soon.

Sorry, this hasn't been simple.
Geoff

------------------------------------------------------------------

1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.

Apologies - the code is already there; ie:

Me!ChooseJob = Null
Me!ChooseJob.Requery

One of your first posts said so. I missed it!

------------------------------------------------------------------

2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed
that an access built in function directs the expressions to fire. I was
wondering if perhaps a built-in function could be missing or a predefined
property?
Just a thought.

As you imported the form "frmInvoiceSelect" and the associated queries from
the Access 97 database, I wouldn't have thought any expressions would be
missing.

------------------------------------------------------------------

So far No missing modules found in the program database or the database
that stores data.

I intended you search the Access 97 database and I believe you've done that
now. That's where the hidden modules might be and where that elusive
"zfSortLabel" function might be.

If you find hidden modules in the Access 97 database, then you need to
import them into your Access 2003 Program database.

LIBRARY DATABASE

If the Access 97 does not contain any hidden modules, then the "zfSortLabel"
function must exist somewhere else. Perhaps the Access 97 database had a
reference to a library database. To check this follow these steps:

1. Open the Access 97 database.
2. Open the VBA editor.
3. Open the Tools, References menu.
4. Examine the References dialog.
5. Is anything checked that might be another Access database or even a
customised DLL file?

------------------------------------------------------------------

It seeems to be me that the expressions in the queries are not
communicating with the combo boxes on the form.

I don't think that's right. The tests you've done so far have demonstrated
that the queries do communicate with the combo boxes.

Let me know if you agree or disagree with the following summary:

1. Essentially, there are three queries in contention. These are:

(a) The query that selects rows in the ChoostCust combo box.

(b) The query that selects rows in the ChooseJob combo box.

(c) The query that selects records for the form "frmInvoiceSelect".

2. Query (b) relies on the ChooseCust combo box.

3. Query (c) relies on both the ChooseCust and ChooseJob combo boxes.

4. When you select a customer and a job in the two combo boxes, sometimes
records will be listed in the Detail Section and sometimes records will not
be listed.

(a) When records are listed, it is possible to create a PreBilling
report and records will be updated correctly.

(b) When records are not listed, this indicates that something is
wrong, because the queries should function so that records are always listed
in the Detail Section.

Q: Is that right?

------------------------------------------------------------------

If there was a problem with the relationship then the queries would not
retrieve the correct data, right?

The test you did earlier proved that the relationship between the combo
boxes and the "qryworkorders_uninvoiced" is working. In the test, you
selected a customer and job number, got some records listed in the Detail
Section, and produced a PreBilling report. Right?

It seems that, sometimes there are records in the Detail Section and
sometimes there aren't. Therefore, I think that, when there are no records
in the Detail Section, the two combo boxes are pumping invalid criteria into
"qryworkorders_uninvoiced" that result in no records.

This makes me think that the two combo boxes are listing customers, or job
numbers, or both, that are invalid. In other words, there might be something
wrong with queries "qlbCustomerOpenInvoice" and
"qlbJobByCustomer_frmInvoice" (which act as the row sources for the combo
boxes). The criteria in these queries might be wrong. They need very careful
examination.

I would suggest you find a customer and job number that result in no records
in the Detail Section and then examine in minute detail why this customer
and job number are being listed in the combo boxes. Clearly, they should not
be listed in the combo boxes.

------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
End Sub

The above code suggests that, when the form opens, a form filter is used to
prevent records from showing in the Detail Section. The code in the
After_Update event of the ChooseJob combo box (see next two code blocks)
removes the form's filter (Me.FilterOn = False) and requeries the form
(Me.Requery), so that records are (or should be) shown in the Detail Section
for the selected customer and job number:

It seems that one problem with the above code is that the user could select
a job number when no customer has been selected.

If you want to prevent this from happening then the solution is:

(1) to disable the ChooseJob combo box when the form opens and
(2) enable it after a customer has been selected, as follows:

(1) The Form_Open event procedure needs to include an extra line of code
as follows:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
Me.ChooseJob.Enabled = False
End Sub

(2) The ChooseCust_AfterUpdate event procedure needs an extra line of
code as follows:

Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me.ChooseJob.Enabled = True
Me.ChooseJob = Null
Me.ChooseJob.Requery
Me.Requery
End Sub

------------------------------------------------------------------

CHECK EVENTS ARE FIRING

Check that you see [Event Procedure] in the appropriate property of the
property sheet for each object that has an event. For example, you have code
for a Form_Open event. Therefore, check the property sheet for the form and
ensure that the OnOpen property has [Event Procedure].

------------------------------------------------------------------

2.) ...
So I changed the view to SQL and the code was: PARAMETERS
[[forms]]!frmInvoiceSelect![[ChooseCust]] Text (255);

It seems odd that there were two square brackets before and after [[Forms]]
and [[ChooseCust]] in the Parameters clause. Perhaps this happened when the
database was converted.

I think the square brackets are only absolutely necessary when the parameter
contains a space. I'm in the habit of including them anyway.

------------------------------------------------------------------

I am wondering if there is a problem deeper than just the forms but the
code source that provides instructions to the expressions, etc.

The code source could be library database or a DLL (dynamic link library)
file. As mentioned above, you need to open the Access 97 database and check
References in the VBA editor to determine if there is another place where
code could be stored.

------------------------------------------------------------------

If you recall I indicated that the expression in the on click event is
different from version 97 database.

version 2003: =zfSortLabel("tktno",Screen.ActiveForm)
version 97: =zfSortLabel("tktno",[Screen].[ActiveForm])

If you type in the missing brackets on the zfSortLabel, etc for version
2003 and close the screen the replaed bracket go away but no error
message ever appears.

I don't think the brackets are needed (see following test).

I created a dummy zfSortLabel function in a standard module. As an
experiment, you could do the same. Copy and paste the following code into a
standard module:

Public Function zfSortLabel(Param1 As Variant, Param2 As Form)
MsgBox "The active form in the active screen is:" _
& vbNewLine & vbNewLine _
& CStr(Param2.Name)
End Function

Notice Param2 is defined "As Form", which means the function expects a form
object to be passed to it as Param2.

Now, open form "frmInvoiceSelect" and click one of your five labels whose
OnClick property is set to =zfSortLabel("sometext", Screen.ActiveForm). The
above function should now run and display a message box saying that the
active form is "frmInvoiceSelect". If the above function does not run,
double-check that the OnClick property for the label contains [Event
Procedure].

CONCLUSION:

If the above dummy function works, then you have demonstrated that your
labels can successfully call the zfSortLabel function. The fact that the
labels don't call the real zfSortLabel function is odd. Either the function
can't be found, or does nothing, or [Event Procedure] is missing from the
OnClick property.

Delete the dummy function (or rem it out).

------------------------------------------------------------------

The database I am working with uses the execution file from a retail
version of access 2003 but I noticed under the option, Default File
Format "Access 2000" is selected rather than the other option of
"Access 2002-2003."

Unfortunately, I'm not an Access 2003 user. However, the above information
suggests that Access 2003 is set to create files in Access 2000 format. But
this is probably irrelevant. You started by using Access 2003 to create a
blank database. This would (presumably) have been in Access 2000 file
format. You then imported database objects from the Access 97 version of the
database. So, Access 2003 is now working with an Access 2000 file-format
database. If Access 2003 isn't complaining, I don't see that as a problem.
It should be just like upgrading a Word 97 document to Word 2000 and then
opening it in Word 2003.

------------------------------------------------------------------

I'm not sure what code you think should exist, so the answer is Yes and No:

Yes - there should be code for the zfSortLabel function. See above - if
you've not found the function in a hidden module in the Access 97 database,
then it might be in a library database or DLL file (maybe).

No - there won't be any code to make the queries work, other than
(Me.Requery), which you already have in the After_Update event procedure for
the ChooseJob combo box.

As I said above, I think the queries behind the combo boxes need a thorough
examination.

------------------------------------------------------------------

I'm not sure what expressions you mean.

If you mean the criteria expressions in the queries, then, as the criteria
expressions refer to the form "frmInvoiceSelect", the form must be open or
the query will fail. But no "built-in function" would be needed to make the
query get the criteria expression from the form. You simply need to ensure
that the form is open and then run the query. You are, in effect, doing this
already. The After_Update event procedure of the ChooseJob combo box
includes:

Me.Requery

Thereby, you are forcing the form to display the latest set of records in
the Detail Section, depending on the customer and job number selected in the
two combo boxes.

As mentioned above, I think that the Detail Section sometimes does not
display records because the Customer or Job Number or both are wrong -
meaning the query supplying records to the form returns no records.

------------------------------------------------------------------

No - all built-in VBA functions would be automatically available. I think
the zfSortLabel function (written by a former developer) is missing.

Not sure what you mean by "predefined". The only thing I can think of is
that the phrase [Event Procedure] is missing from the OnClick property of
the 5 labels. This would apply to any event property that should be running
code. Even if you have code for an event, it won't run if you don't have
[Event Procedure] in the relevant property.

 

[Geoff]

Corrections.

You need to make three deletions from my previous post. The deletions all
relate to the fact that you do not neet [Event Procedure] in the OnClick
property of the 5 labels that call the =zfSortLabel function.

Sorry for the confusion.
Geoff

Deletion 1. Delete from my reply:

If the above function does not run, double-check that the OnClick property
for the label contains [Event Procedure].

Deletion 2. Delete from my reply (under Conclusion):

... or [Event Procedure] is missing from the OnClick property.
Not sure what you mean by "predefined".

Deletion 3. Delete from my reply:

The only thing I can think of is that the phrase [Event Procedure] is
missing from the OnClick property of the 5 labels.

------------------------------------------------------------------

 

[doglover]

Sorry the reply was so long I was trying to explain how the code acctually
appears. I believe you are correct the problem is behind the queries (most
likely the criteria) in the combo boxes.

When I said predefined I really meant functions, operators, etc.

Will post when I get more feed back form Microsoft.

Doglover

This is a very long reply!
I could not finish it yesterday.
I have tried to comprehensively answer your questions.

I would focus on the queries behind the combo boxes. Examine in detail why
they list invalid customers or jobs or both. I think they are passing
invalid criteria to the query behind the form.

I'll be working flat out next week, so if you have any further questions,
post back soon.

Sorry, this hasn't been simple.
Geoff

------------------------------------------------------------------

1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.

Apologies - the code is already there; ie:

Me!ChooseJob = Null
Me!ChooseJob.Requery

One of your first posts said so. I missed it!

------------------------------------------------------------------

2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed
that an access built in function directs the expressions to fire. I was
wondering if perhaps a built-in function could be missing or a predefined
property?
Just a thought.

As you imported the form "frmInvoiceSelect" and the associated queries from
the Access 97 database, I wouldn't have thought any expressions would be
missing.

------------------------------------------------------------------

So far No missing modules found in the program database or the database
that stores data.

I intended you search the Access 97 database and I believe you've done that
now. That's where the hidden modules might be and where that elusive
"zfSortLabel" function might be.

If you find hidden modules in the Access 97 database, then you need to
import them into your Access 2003 Program database.

LIBRARY DATABASE

If the Access 97 does not contain any hidden modules, then the "zfSortLabel"
function must exist somewhere else. Perhaps the Access 97 database had a
reference to a library database. To check this follow these steps:

1. Open the Access 97 database.
2. Open the VBA editor.
3. Open the Tools, References menu.
4. Examine the References dialog.
5. Is anything checked that might be another Access database or even a
customised DLL file?

------------------------------------------------------------------

It seeems to be me that the expressions in the queries are not
communicating with the combo boxes on the form.

I don't think that's right. The tests you've done so far have demonstrated
that the queries do communicate with the combo boxes.

Let me know if you agree or disagree with the following summary:

1. Essentially, there are three queries in contention. These are:

(a) The query that selects rows in the ChoostCust combo box.

(b) The query that selects rows in the ChooseJob combo box.

(c) The query that selects records for the form "frmInvoiceSelect".

2. Query (b) relies on the ChooseCust combo box.

3. Query (c) relies on both the ChooseCust and ChooseJob combo boxes.

4. When you select a customer and a job in the two combo boxes, sometimes
records will be listed in the Detail Section and sometimes records will not
be listed.

(a) When records are listed, it is possible to create a PreBilling
report and records will be updated correctly.

(b) When records are not listed, this indicates that something is
wrong, because the queries should function so that records are always listed
in the Detail Section.

Q: Is that right?

------------------------------------------------------------------

If there was a problem with the relationship then the queries would not
retrieve the correct data, right?

The test you did earlier proved that the relationship between the combo
boxes and the "qryworkorders_uninvoiced" is working. In the test, you
selected a customer and job number, got some records listed in the Detail
Section, and produced a PreBilling report. Right?

It seems that, sometimes there are records in the Detail Section and
sometimes there aren't. Therefore, I think that, when there are no records
in the Detail Section, the two combo boxes are pumping invalid criteria into
"qryworkorders_uninvoiced" that result in no records.

This makes me think that the two combo boxes are listing customers, or job
numbers, or both, that are invalid. In other words, there might be something
wrong with queries "qlbCustomerOpenInvoice" and
"qlbJobByCustomer_frmInvoice" (which act as the row sources for the combo
boxes). The criteria in these queries might be wrong. They need very careful
examination.

I would suggest you find a customer and job number that result in no records
in the Detail Section and then examine in minute detail why this customer
and job number are being listed in the combo boxes. Clearly, they should not
be listed in the combo boxes.

------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
End Sub

The above code suggests that, when the form opens, a form filter is used to
prevent records from showing in the Detail Section. The code in the
After_Update event of the ChooseJob combo box (see next two code blocks)
removes the form's filter (Me.FilterOn = False) and requeries the form
(Me.Requery), so that records are (or should be) shown in the Detail Section
for the selected customer and job number:

It seems that one problem with the above code is that the user could select
a job number when no customer has been selected.

If you want to prevent this from happening then the solution is:

(1) to disable the ChooseJob combo box when the form opens and
(2) enable it after a customer has been selected, as follows:

(1) The Form_Open event procedure needs to include an extra line of code
as follows:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
Me.ChooseJob.Enabled = False
End Sub

(2) The ChooseCust_AfterUpdate event procedure needs an extra line of
code as follows:

Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me.ChooseJob.Enabled = True
Me.ChooseJob = Null
Me.ChooseJob.Requery
Me.Requery
End Sub

------------------------------------------------------------------

CHECK EVENTS ARE FIRING

Check that you see [Event Procedure] in the appropriate property of the
property sheet for each object that has an event. For example, you have code
for a Form_Open event. Therefore, check the property sheet for the form and
ensure that the OnOpen property has [Event Procedure].

------------------------------------------------------------------

2.) ...
So I changed the view to SQL and the code was: PARAMETERS
[[forms]]!frmInvoiceSelect![[ChooseCust]] Text (255);

It seems odd that there were two square brackets before and after [[Forms]]
and [[ChooseCust]] in the Parameters clause. Perhaps this happened when the
database was converted.

I think the square brackets are only absolutely necessary when the parameter
contains a space. I'm in the habit of including them anyway.

------------------------------------------------------------------

I am wondering if there is a problem deeper than just the forms but the
code source that provides instructions to the expressions, etc.

The code source could be library database or a DLL (dynamic link library)
file. As mentioned above, you need to open the Access 97 database and check
References in the VBA editor to determine if there is another place where
code could be stored.

------------------------------------------------------------------

If you recall I indicated that the expression in the on click event is
different from version 97 database.

version 2003: =zfSortLabel("tktno",Screen.ActiveForm)
version 97: =zfSortLabel("tktno",[Screen].[ActiveForm])

If you type in the missing brackets on the zfSortLabel, etc for version
2003 and close the screen the replaed bracket go away but no error
message ever appears.

I don't think the brackets are needed (see following test).

I created a dummy zfSortLabel function in a standard module. As an
experiment, you could do the same. Copy and paste the following code into a
standard module:

Public Function zfSortLabel(Param1 As Variant, Param2 As Form)
MsgBox "The active form in the active screen is:" _
& vbNewLine & vbNewLine _
& CStr(Param2.Name)
End Function

Notice Param2 is defined "As Form", which means the function expects a form
object to be passed to it as Param2.

Now, open form "frmInvoiceSelect" and click one of your five labels whose
OnClick property is set to =zfSortLabel("sometext", Screen.ActiveForm). The
above function should now run and display a message box saying that the
active form is "frmInvoiceSelect". If the above function does not run,
double-check that the OnClick property for the label contains [Event
Procedure].

CONCLUSION:

If the above dummy function works, then you have demonstrated that your
labels can successfully call the zfSortLabel function. The fact that the
labels don't call the real zfSortLabel function is odd. Either the function
can't be found, or does nothing, or [Event Procedure] is missing from the
OnClick property.

Delete the dummy function (or rem it out).

------------------------------------------------------------------

The database I am working with uses the execution file from a retail
version of access 2003 but I noticed under the option, Default File
Format "Access 2000" is selected rather than the other option of
"Access 2002-2003."

Unfortunately, I'm not an Access 2003 user. However, the above information
suggests that Access 2003 is set to create files in Access 2000 format. But
this is probably irrelevant. You started by using Access 2003 to create a
blank database. This would (presumably) have been in Access 2000 file
format. You then imported database objects from the Access 97 version of the
database. So, Access 2003 is now working with an Access 2000 file-format
database. If Access 2003 isn't complaining, I don't see that as a problem.
It should be just like upgrading a Word 97 document to Word 2000 and then
opening it in Word 2003.

------------------------------------------------------------------

I'm not sure what code you think should exist, so the answer is Yes and No:

Yes - there should be code for the zfSortLabel function. See above - if
you've not found the function in a hidden module in the Access 97 database,

 

[doglover]

Geoff:

Just completed reading your e-mail and since it is late here I am going to
defer my response until Saturday.

I did want to share this information with you. I disassembled the form
piece by piece and ended up with a form that had:
form header
detail
properties for the form set to continuous
form query source (same as before less parameters)

I removed the parameters from the form query source. Removed both combo
boxes from the form header, events, code, etc and ran the form query source
and it worked fine. I opend the form in form view and it produced records in
the detail section just like a continuous form should. Obviously, it did not
synchronize combo boxes to the detail section or the parameter form query
source because I had removed these items.

I placed the combo boxes back on the form header and the form stopped
working like a continuous form even though the properties of the form
indicated continuous.

I also believe there is a problem with the parameter in at least one of the
forms queries and the SQL because syntax of the SQL will change randomly.
What I mean is the form works and still produces the same problem we have
been discussing but an additional error message shows up indicating that a
syntax problem exist. If you look at the design view of the query things
look ok but if you flip over to SQL view you notice that additional brackets
have been placed around "forms" and "ChooseCust" in the PARAMETERS LINE OF
THE SQL VIEW. Remove the extra brackets and the query runs with no error
message. Specifically, the query with the parameters that start off "LIKE
nz" which is the parameter placed in the query row source of the combo box
ChooseJob.

I realize this has been increadbly time consuming for you and I really do
appreciate your help. I decided yesterday to go on line with Microsoft to
try and help the situation. My code was confirmed to be valid and they
really do not understand what is creating this problem.

The information about the detail section I mentioned earlier was made
available to Microsoft today and their response will be interesting. I will
share with you their results because you have more than gracious with your
time and knowledge.

Regards,
doglover

Sorry the reply was so long I was trying to explain how the code acctually
appears. I believe you are correct the problem is behind the queries (most
likely the criteria) in the combo boxes.

When I said predefined I really meant functions, operators, etc.

Will post when I get more feed back form Microsoft.

Doglover

This is a very long reply!
I could not finish it yesterday.
I have tried to comprehensively answer your questions.

I would focus on the queries behind the combo boxes. Examine in detail why
they list invalid customers or jobs or both. I think they are passing
invalid criteria to the query behind the form.

I'll be working flat out next week, so if you have any further questions,
post back soon.

Sorry, this hasn't been simple.
Geoff

------------------------------------------------------------------

1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.

Apologies - the code is already there; ie:

Me!ChooseJob = Null
Me!ChooseJob.Requery

One of your first posts said so. I missed it!

------------------------------------------------------------------

2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed
that an access built in function directs the expressions to fire. I was
wondering if perhaps a built-in function could be missing or a predefined
property?
Just a thought.

As you imported the form "frmInvoiceSelect" and the associated queries from
the Access 97 database, I wouldn't have thought any expressions would be
missing.

------------------------------------------------------------------

So far No missing modules found in the program database or the database
that stores data.

I intended you search the Access 97 database and I believe you've done that
now. That's where the hidden modules might be and where that elusive
"zfSortLabel" function might be.

If you find hidden modules in the Access 97 database, then you need to
import them into your Access 2003 Program database.

LIBRARY DATABASE

If the Access 97 does not contain any hidden modules, then the "zfSortLabel"
function must exist somewhere else. Perhaps the Access 97 database had a
reference to a library database. To check this follow these steps:

1. Open the Access 97 database.
2. Open the VBA editor.
3. Open the Tools, References menu.
4. Examine the References dialog.
5. Is anything checked that might be another Access database or even a
customised DLL file?

------------------------------------------------------------------

It seeems to be me that the expressions in the queries are not
communicating with the combo boxes on the form.

I don't think that's right. The tests you've done so far have demonstrated
that the queries do communicate with the combo boxes.

Let me know if you agree or disagree with the following summary:

1. Essentially, there are three queries in contention. These are:

(a) The query that selects rows in the ChoostCust combo box.

(b) The query that selects rows in the ChooseJob combo box.

(c) The query that selects records for the form "frmInvoiceSelect".

2. Query (b) relies on the ChooseCust combo box.

3. Query (c) relies on both the ChooseCust and ChooseJob combo boxes.

4. When you select a customer and a job in the two combo boxes, sometimes
records will be listed in the Detail Section and sometimes records will not
be listed.

(a) When records are listed, it is possible to create a PreBilling
report and records will be updated correctly.

(b) When records are not listed, this indicates that something is
wrong, because the queries should function so that records are always listed
in the Detail Section.

Q: Is that right?

------------------------------------------------------------------

If there was a problem with the relationship then the queries would not
retrieve the correct data, right?

The test you did earlier proved that the relationship between the combo
boxes and the "qryworkorders_uninvoiced" is working. In the test, you
selected a customer and job number, got some records listed in the Detail
Section, and produced a PreBilling report. Right?

It seems that, sometimes there are records in the Detail Section and
sometimes there aren't. Therefore, I think that, when there are no records
in the Detail Section, the two combo boxes are pumping invalid criteria into
"qryworkorders_uninvoiced" that result in no records.

This makes me think that the two combo boxes are listing customers, or job
numbers, or both, that are invalid. In other words, there might be something
wrong with queries "qlbCustomerOpenInvoice" and
"qlbJobByCustomer_frmInvoice" (which act as the row sources for the combo
boxes). The criteria in these queries might be wrong. They need very careful
examination.

I would suggest you find a customer and job number that result in no records
in the Detail Section and then examine in minute detail why this customer
and job number are being listed in the combo boxes. Clearly, they should not
be listed in the combo boxes.

------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
End Sub

The above code suggests that, when the form opens, a form filter is used to
prevent records from showing in the Detail Section. The code in the
After_Update event of the ChooseJob combo box (see next two code blocks)
removes the form's filter (Me.FilterOn = False) and requeries the form
(Me.Requery), so that records are (or should be) shown in the Detail Section
for the selected customer and job number:

Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery
End Sub

It seems that one problem with the above code is that the user could select
a job number when no customer has been selected.

If you want to prevent this from happening then the solution is:

(1) to disable the ChooseJob combo box when the form opens and
(2) enable it after a customer has been selected, as follows:

(1) The Form_Open event procedure needs to include an extra line of code
as follows:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
Me.ChooseJob.Enabled = False
End Sub

(2) The ChooseCust_AfterUpdate event procedure needs an extra line of
code as follows:

Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me.ChooseJob.Enabled = True
Me.ChooseJob = Null
Me.ChooseJob.Requery
Me.Requery
End Sub

------------------------------------------------------------------

2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed
that
an access built in function directs the expressions to fire. I was
wondering
if perhaps a built-in function could be missing or a predefined property?
Just a thought.

CHECK EVENTS ARE FIRING

Check that you see [Event Procedure] in the appropriate property of the
property sheet for each object that has an event. For example, you have code
for a Form_Open event. Therefore, check the property sheet for the form and
ensure that the OnOpen property has [Event Procedure].

------------------------------------------------------------------

2.) ...
So I changed the view to SQL and the code was: PARAMETERS
[[forms]]!frmInvoiceSelect![[ChooseCust]] Text (255);

It seems odd that there were two square brackets before and after [[Forms]]
and [[ChooseCust]] in the Parameters clause. Perhaps this happened when the
database was converted.

I think the square brackets are only absolutely necessary when the parameter
contains a space. I'm in the habit of including them anyway.

------------------------------------------------------------------

I am wondering if there is a problem deeper than just the forms but the
code source that provides instructions to the expressions, etc.

The code source could be library database or a DLL (dynamic link library)
file. As mentioned above, you need to open the Access 97 database and check
References in the VBA editor to determine if there is another place where
code could be stored.

------------------------------------------------------------------

If you recall I indicated that the expression in the on click event is
different from version 97 database.

version 2003: =zfSortLabel("tktno",Screen.ActiveForm)
version 97: =zfSortLabel("tktno",[Screen].[ActiveForm])

If you type in the missing brackets on the zfSortLabel, etc for version
2003 and close the screen the replaed bracket go away but no error
message ever appears.

I don't think the brackets are needed (see following test).

I created a dummy zfSortLabel function in a standard module. As an
experiment, you could do the same. Copy and paste the following code into a
standard module:

Public Function zfSortLabel(Param1 As Variant, Param2 As Form)
MsgBox "The active form in the active screen is:" _
& vbNewLine & vbNewLine _
& CStr(Param2.Name)
End Function

Notice Param2 is defined "As Form", which means the function expects a form
object to be passed to it as Param2.

Now, open form "frmInvoiceSelect" and click one of your five labels whose
OnClick property is set to =zfSortLabel("sometext", Screen.ActiveForm). The
above function should now run and display a message box saying that the
active form is "frmInvoiceSelect". If the above function does not run,
double-check that the OnClick property for the label contains [Event
Procedure].

CONCLUSION:

If the above dummy function works, then you have demonstrated that your
labels can successfully call the zfSortLabel function. The fact that the
labels don't call the real zfSortLabel function is odd. Either the function
can't be found, or does nothing, or [Event Procedure] is missing from the
OnClick property.

Delete the dummy function (or rem it out).

------------------------------------------------------------------

The database I am working with uses the execution file from a retail
version of access 2003 but I noticed under the option, Default File
Format "Access 2000" is selected rather than the other option of
"Access 2002-2003."

Unfortunately, I'm not an Access 2003 user. However, the above information
suggests that Access 2003 is set to create files in Access 2000 format. But
this is probably irrelevant. You started by using Access 2003 to create a
blank database. This would (presumably) have been in Access 2000 file
format. You then imported database objects from the Access 97 version of the
database. So, Access 2003 is now working with an Access 2000 file-format
database. If Access 2003 isn't complaining, I don't see that as a problem.
It should be just like upgrading a Word 97 document to Word 2000 and then

 

[Pieter Wijnen]

I Always run through all Qdef's Removing [] before exporting from '97 to
avoid this kind of thing
I'm a good boy <g> and stay away from spaces etc it Object Names, so it's
safe for me to do so

Sub FindQueryText(ByVal t As String, Optional ByVal ReplaceBy As String =
VBA.vbNullString)
On Error Resume Next
Dim QDef As DAO.QueryDef
Dim Db As DAO.Database
Set Db = Access.CurrentDb()
For Each QDef In Db.QueryDefs
If VBA.InStr(QDef.SQL, t) > 0 Then
Debug.Print QDef.Name
End If
If VBA.Len(ReplaceBy) Then
Replace QDef.SQL, t, ReplaceBy 'This is '97 so my own Replace
End If
Qdef.Close
Next
Set Db = Nothing
End Sub

Pieter

Geoff:

Just completed reading your e-mail and since it is late here I am going to
defer my response until Saturday.

I did want to share this information with you. I disassembled the form
piece by piece and ended up with a form that had:
form header
detail
properties for the form set to continuous
form query source (same as before less parameters)

I removed the parameters from the form query source. Removed both combo
boxes from the form header, events, code, etc and ran the form query
source
and it worked fine. I opend the form in form view and it produced records
in
the detail section just like a continuous form should. Obviously, it did
not
synchronize combo boxes to the detail section or the parameter form query
source because I had removed these items.

I placed the combo boxes back on the form header and the form stopped
working like a continuous form even though the properties of the form
indicated continuous.

I also believe there is a problem with the parameter in at least one of
the
forms queries and the SQL because syntax of the SQL will change randomly.
What I mean is the form works and still produces the same problem we have
been discussing but an additional error message shows up indicating that a
syntax problem exist. If you look at the design view of the query things
look ok but if you flip over to SQL view you notice that additional
brackets
have been placed around "forms" and "ChooseCust" in the PARAMETERS LINE OF
THE SQL VIEW. Remove the extra brackets and the query runs with no error
message. Specifically, the query with the parameters that start off
"LIKE
nz" which is the parameter placed in the query row source of the combo box
ChooseJob.

I realize this has been increadbly time consuming for you and I really do
appreciate your help. I decided yesterday to go on line with Microsoft to
try and help the situation. My code was confirmed to be valid and they
really do not understand what is creating this problem.

The information about the detail section I mentioned earlier was made
available to Microsoft today and their response will be interesting. I
will
share with you their results because you have more than gracious with your
time and knowledge.

Regards,
doglover

Sorry the reply was so long I was trying to explain how the code
acctually
appears. I believe you are correct the problem is behind the queries
(most
likely the criteria) in the combo boxes.

When I said predefined I really meant functions, operators, etc.

Will post when I get more feed back form Microsoft.

Doglover

This is a very long reply!
I could not finish it yesterday.
I have tried to comprehensively answer your questions.

I would focus on the queries behind the combo boxes. Examine in detail
why
they list invalid customers or jobs or both. I think they are passing
invalid criteria to the query behind the form.

I'll be working flat out next week, so if you have any further
questions,
post back soon.

Sorry, this hasn't been simple.
Geoff

------------------------------------------------------------------

1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

Tell me exactly where you want the code placed in the ChooseJob combo
box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions
correctly.

Apologies - the code is already there; ie:

Me!ChooseJob = Null
Me!ChooseJob.Requery

One of your first posts said so. I missed it!

------------------------------------------------------------------

2.) Could the code be missing that tells the expressions placed in
form
frmInvoiceSelect and and the associated queries be missing. I
assumed
that an access built in function directs the expressions to fire. I
was
wondering if perhaps a built-in function could be missing or a
predefined
property?
Just a thought.

As you imported the form "frmInvoiceSelect" and the associated queries
from
the Access 97 database, I wouldn't have thought any expressions would
be
missing.

------------------------------------------------------------------

So far No missing modules found in the program database or the
database
that stores data.

I intended you search the Access 97 database and I believe you've done
that
now. That's where the hidden modules might be and where that elusive
"zfSortLabel" function might be.

If you find hidden modules in the Access 97 database, then you need to
import them into your Access 2003 Program database.

LIBRARY DATABASE

If the Access 97 does not contain any hidden modules, then the
"zfSortLabel"
function must exist somewhere else. Perhaps the Access 97 database had
a
reference to a library database. To check this follow these steps:

1. Open the Access 97 database.
2. Open the VBA editor.
3. Open the Tools, References menu.
4. Examine the References dialog.
5. Is anything checked that might be another Access database or even
a
customised DLL file?

------------------------------------------------------------------

It seeems to be me that the expressions in the queries are not
communicating with the combo boxes on the form.

I don't think that's right. The tests you've done so far have
demonstrated
that the queries do communicate with the combo boxes.

Let me know if you agree or disagree with the following summary:

1. Essentially, there are three queries in contention. These are:

(a) The query that selects rows in the ChoostCust combo box.

(b) The query that selects rows in the ChooseJob combo box.

(c) The query that selects records for the form
"frmInvoiceSelect".

2. Query (b) relies on the ChooseCust combo box.

3. Query (c) relies on both the ChooseCust and ChooseJob combo
boxes.

4. When you select a customer and a job in the two combo boxes,
sometimes
records will be listed in the Detail Section and sometimes records will
not
be listed.

(a) When records are listed, it is possible to create a PreBilling
report and records will be updated correctly.

(b) When records are not listed, this indicates that something is
wrong, because the queries should function so that records are always
listed
in the Detail Section.

Q: Is that right?

------------------------------------------------------------------

If there was a problem with the relationship then the queries would
not
retrieve the correct data, right?

The test you did earlier proved that the relationship between the combo
boxes and the "qryworkorders_uninvoiced" is working. In the test, you
selected a customer and job number, got some records listed in the
Detail
Section, and produced a PreBilling report. Right?

It seems that, sometimes there are records in the Detail Section and
sometimes there aren't. Therefore, I think that, when there are no
records
in the Detail Section, the two combo boxes are pumping invalid criteria
into
"qryworkorders_uninvoiced" that result in no records.

This makes me think that the two combo boxes are listing customers, or
job
numbers, or both, that are invalid. In other words, there might be
something
wrong with queries "qlbCustomerOpenInvoice" and
"qlbJobByCustomer_frmInvoice" (which act as the row sources for the
combo
boxes). The criteria in these queries might be wrong. They need very
careful
examination.

I would suggest you find a customer and job number that result in no
records
in the Detail Section and then examine in minute detail why this
customer
and job number are being listed in the combo boxes. Clearly, they
should not
be listed in the combo boxes.

------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
End Sub

The above code suggests that, when the form opens, a form filter is
used to
prevent records from showing in the Detail Section. The code in the
After_Update event of the ChooseJob combo box (see next two code
blocks)
removes the form's filter (Me.FilterOn = False) and requeries the form
(Me.Requery), so that records are (or should be) shown in the Detail
Section
for the selected customer and job number:

Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery
End Sub

It seems that one problem with the above code is that the user could
select
a job number when no customer has been selected.

If you want to prevent this from happening then the solution is:

(1) to disable the ChooseJob combo box when the form opens and
(2) enable it after a customer has been selected, as follows:

(1) The Form_Open event procedure needs to include an extra line of
code
as follows:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True
Me.ChooseJob.Enabled = False
End Sub

(2) The ChooseCust_AfterUpdate event procedure needs an extra line
of
code as follows:

Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me.ChooseJob.Enabled = True
Me.ChooseJob = Null
Me.ChooseJob.Requery
Me.Requery
End Sub

------------------------------------------------------------------

2.) Could the code be missing that tells the expressions placed in
form
frmInvoiceSelect and and the associated queries be missing. I
assumed
that
an access built in function directs the expressions to fire. I was
wondering
if perhaps a built-in function could be missing or a predefined
property?
Just a thought.

CHECK EVENTS ARE FIRING

Check that you see [Event Procedure] in the appropriate property of the
property sheet for each object that has an event. For example, you have
code
for a Form_Open event. Therefore, check the property sheet for the form
and
ensure that the OnOpen property has [Event Procedure].

------------------------------------------------------------------

2.) ...
So I changed the view to SQL and the code was: PARAMETERS
[[forms]]!frmInvoiceSelect![[ChooseCust]] Text (255);

It seems odd that there were two square brackets before and after
[[Forms]]
and [[ChooseCust]] in the Parameters clause. Perhaps this happened when
the
database was converted.

I think the square brackets are only absolutely necessary when the
parameter
contains a space. I'm in the habit of including them anyway.

------------------------------------------------------------------

I am wondering if there is a problem deeper than just the forms but
the
code source that provides instructions to the expressions, etc.

The code source could be library database or a DLL (dynamic link
library)
file. As mentioned above, you need to open the Access 97 database and
check
References in the VBA editor to determine if there is another place
where
code could be stored.

------------------------------------------------------------------

If you recall I indicated that the expression in the on click event
is
different from version 97 database.

version 2003: =zfSortLabel("tktno",Screen.ActiveForm)
version 97: =zfSortLabel("tktno",[Screen].[ActiveForm])

If you type in the missing brackets on the zfSortLabel, etc for
version
2003 and close the screen the replaed bracket go away but no error
message ever appears.

I don't think the brackets are needed (see following test).

I created a dummy zfSortLabel function in a standard module. As an
experiment, you could do the same. Copy and paste the following code
into a
standard module:

Public Function zfSortLabel(Param1 As Variant, Param2 As Form)
MsgBox "The active form in the active screen is:" _
& vbNewLine & vbNewLine _
& CStr(Param2.Name)
End Function

Notice Param2 is defined "As Form", which means the function expects a
form
object to be passed to it as Param2.

Now, open form "frmInvoiceSelect" and click one of your five labels
whose
OnClick property is set to =zfSortLabel("sometext", Screen.ActiveForm).
The
above function should now run and display a message box saying that the
active form is "frmInvoiceSelect". If the above function does not run,
double-check that the OnClick property for the label contains [Event
Procedure].

CONCLUSION:

If the above dummy function works, then you have demonstrated that your
labels can successfully call the zfSortLabel function. The fact that
the
labels don't call the real zfSortLabel function is odd. Either the
function
can't be found, or does nothing, or [Event Procedure] is missing from
the
OnClick property.

Delete the dummy function (or rem it out).

------------------------------------------------------------------

The database I am working with uses the execution file from a retail
version of access 2003 but I noticed under the option, Default File
Format "Access 2000" is selected rather than the other option of
"Access 2002-2003."

Unfortunately, I'm not an Access 2003 user. However, the above
information
suggests that Access 2003 is set to create files in Access 2000 format.
But
this is probably irrelevant. You started by using Access 2003 to create
a
blank database. This would (presumably) have been in Access 2000 file
format. You then imported database objects from the Access 97 version
of the
database. So, Access 2003 is now working with an Access 2000
file-format
database. If Access 2003 isn't complaining, I don't see that as a
problem.
It should be just like upgrading a Word 97 document to Word 2000 and
then

 

[Geoff]

Here are my comments.

You may want to experiment with Section 2 below first.

-------------------------------------------------------------

SECTION 1:

I removed the parameters from the form query source. Removed both combo
boxes from the form header, events, code, etc and ran the form query
source and it worked fine. I opend the form in form view and it produced
records in the detail section just like a continuous form should.
Obviously,
it did not synchronize combo boxes to the detail section or the parameter
form query source because I had removed these items.

Q1: At this first stage in your experiment, how many records did the
Detail Section list?
(The number of records displayed in the Detail Section will be shown in the
Record Navigation buttons at the bottom of the form.)

I placed the combo boxes back on the form header and the form stopped
working like a continuous form even though the properties of the form
indicated continuous.

Q2: At this second stage in your experiment, how many records did the
Detail Section list?
(Again, look at the Record Navigation buttons.)

Q3: At this second stage, was the Detail Section in Single Form view?

I repeated your experiment. The form did not stop displaying in Continuous
Forms view.

The answers to the above questions may throw some light on the problem.

-------------------------------------------------------------

SECTION 2:

I also believe there is a problem with the parameter in at least one of
the forms queries and the SQL because syntax of the SQL will change
randomly. What I mean is the form works and still produces the same
problem we have been discussing but an additional error message shows
up indicating that a syntax problem exist. If you look at the design view
of the query things look ok but if you flip over to SQL view you notice
that additional brackets have been placed around "forms" and
"ChooseCust" in the PARAMETERS LINE OF THE SQL VIEW.
Remove the extra brackets and the query runs with no error
message. Specifically, the query with the parameters that start off
"LIKE nz" which is the parameter placed in the query row source of the
combo box ChooseJob.

(1) Query "qlbJobByCustomer_frmInvoice"

As you know, you are referring to the query "qlbJobByCustomer_frmInvoice".
This query acts as the Row Source for the ChooseJob combo box and contains
the criterion:

Like Nz([Forms]![frmInvoiceSelect]![ChooseCust],"*")

When you view this query in SQL view, it should NOT have a Parameters
clause. It does not need a Parameters clause because you are not opening
this query from VBA code; this query is opened by the ChooseJob combo box
when the "frmInvoiceSelect" form opens. Therefore, I think you're getting a
syntax error because Access doesn't expect to evaluate a Parameters clause
in your circumstances. See "(4) Parameters Clause" below for more
information.

To remove the Parameters clause, follow these steps:

1. Open the query in design view.
2. Open the Query menu and select Parameters.
3. Delete the above parameter (and any other parameters) from the Query
Parameters dialog.
4. Switch to SQL view to make sure the parameters clause has been
removed.
5. Save the query.

(2) Query "qlbCustomerOpenInvoice"

The same applies to the query "qlbCustomerOpenInvoice". This query acts as
the Row Source for the ChooseCust combo box. Likewise, this query is not
opened by VBA code. Therefore, the query should not have a Parameters
clause. If it has a Parameters clause, remove it using the above technique.

(3) Query "qryWorkOrders_uninvoiced"

The same applies to the query "qlbCustomerOpenInvoice". This query acts as
the Record Source for the form "frmInvoiceSelect". Likewise, this query is
not opened by VBA code. Therefore, the query should not have a Parameters
clause. If it has a Parameters clause, remove it using the above technique.

(4) Parameters Clause

A query needs a Parameters clause when you open the query in VBA code. Then
your code can give the parameters values - in other words, the Parameters
clause is a means of putting criteria into the query when the query is run
from VBA code. If you're not opening a query in code, then you don't need
the Parameters clause because the query can obtain the criteria directly
from the form "frmInvoiceSelect" (in your case) or, more generally, directly
from the user by displaying a prompt for the user to enter the criteria.

Let me know how things work after the Parameters clauses have been removed.

-------------------------------------------------------------

SECTION 3:

I realize this has been increadbly time consuming for you and I really do
appreciate your help. I decided yesterday to go on line with Microsoft to
try and help the situation. My code was confirmed to be valid and they
really do not understand what is creating this problem.

The information about the detail section I mentioned earlier was made
available to Microsoft today and their response will be interesting. I
will share with you their results because you have more than gracious
with your time and knowledge.

I'd like to hear what they come up with.

Regards
Geoff

 

[Geoff]

Here are some follow-up issues.
Regards
Geoff

--------------------------------------------------------

SECTION 1:

Just to be sure we're not talking at cross purposes:

(1) Here is an example of a query in SQL view without a parameters
clause:

SELECT Table1.*
FROM Table1
WHERE (((Table1.WorkDate) Is Not Null) AND ((Table1.InvoiceNumber) Is Not
Null));

(2) And here is the same query in SQL view with a parameters clause:

PARAMETERS [WorkDate] DateTime, [InvoiceNumber] Long;
SELECT Table1.*
FROM Table1
WHERE (((Table1.WorkDate) Is Not Null) AND ((Table1.InvoiceNumber) Is Not
Null));

In my previous posts, when I used the phrase "Parameters clause", I was
referring to the first line of the SQL statement starting with the word
PARAMETERS (and ending just before the word SELECT).

In contrast, I tend to refer to the text in the WHERE clause (last line) as
a "criteria expression".

So, the two phrases I use are "parameters clause" and "criteria
expressions".

--------------------------------------------------------

SECTION 2:

ADO/DAO

The following issue may not have a direct bearing on the problems you've
been experiencing. However, it is an issue I think you should consider as it
may cause problems. I'll mention it now while I have time.

As you know, your database was developed using Access 97 with a reference to
DAO.

When you converted the database to Access 2003, you started with a blank
database and imported objects from the Access 97 database. The new blank
database would have had a reference to:

Microsoft ActiveX Data Objects 2.8 Library

(or to a later version of ADO).

As you have code in your database that would only run using DAO, you must
have added a reference to the:

Microsoft DAO 3.6 Object Library

(or to a later version of DAO if it exists).

As you may know, if you have references to both ADO and DAO, then some
object variables may not be created correctly. For example, the ADO and DAO
object libraries both contain a Recordset object.

Therefore, take your code for "Private Sub cmdInvoice_Click()". In this code
you declare a recordset object variable as:

Dim rs As Recordset

The question is, if you have references to both ADO and DAO, which recordset
object will Access create - an ADO recordset or a DAO recordset? The answer
is that Access will create a recordset object for whichever object library
appears first in the References dialog.

It seems clear that you want your code to create a DAO recordset. Therefore,
if you have references to both ADO and DAO, you have two choices:

(1) You can move the DAO object library up the References dialog (using
the up and down Priority buttons) so that the DAO library appears before the
ADO library.

(2) Alternatively (I prefer this method), you can disambiguate the
variable declaration like this:

Dim rs As DAO.Recordset

Then there is no ambiguity and future developers can see exactly what's
going on.

If I were you, I would disambiguate the declarations of all DAO object
variables. Although this is not strictly necessary for objects that exist in
the DAO object library but not in the ADO library, I think it makes the code
easier to read. Here are some examples:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim rs as DAO.Recordset
Dim tbl as DAO.TableDef

When you edit the variable declarations, you'll know you're on the right
track because, after you type DAO followed by a full stop, a pop-up list of
objects will appear. The object you are declaring must appear in that pop-up
list.

Incidentally, as you know, references are made and removed by opening the
Tools > References menu in the VBA editor. If you have a reference to ADO,
then it may be quite safe to remove it, but I'm not sure about that. You
might like to remove it and see if any problems result.

 

[Geoff]

I have - at long last - replicated the behaviour you are experiencing and I
have found the cause of the problem.

The problem is caused by the Filter/FilterOn commands, which appear in the
Form_Open event and the ChooseJob_AfterUpdate event:

Private Sub Form_Open(Cancel As Integer)
' If you rem out the filter lines, the problem disappears:
' Me.Filter = "[wrkid]=0"
' Me.FilterOn = True
End Sub

Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
' Rem out the FilterOn line:
' Me.FilterOn = False
Me.Requery
End Sub

Your Original Code:

When the form opens, the word "(Filtered)" appears after the Record
Navigation buttons at the bottom of the form. After the ChooseJob combo box
is used to select a job, the word "(Filtered)" correctly disappears.
However, the Detail Section does not wake up and display the records for the
selected customer and job number.

With the Filter Commands Remmed Out:

If you rem out the code lines relating to the Filter commands (as shown
above), then, "(Filtered)" does not appear after the Record Navigation
buttons when the form opens. In my simulation of your database, the form
opens without showing any records in the Detail Section (which made me
wonder why the Filter was needed). After a customer and job number are
selected, the Detail Section shows the appropriate records for the customer
and job number.

According to online help, your original code appears correct; namely,
(Me.FilterOn = False) should indeed remove the effect of the filter.
However, although the word "(Filtered)" disappears from after the Record
Navigation buttons, the Detail Section does not show the appropriate
records. I have not yet found a command that will make the Detail Section
wake up and display the records. It does appear there is something wrong
with Access.

I thought I'd report this to you now as it's getting late here.

If the filter is important to you (and you might like to consider whether it
is), then I would suggest you might have some better luck if you designed an
unbound main form containing the combo boxes and a bound subform (to replace
your Detailed Section). If I had time, I'd experiment a bit to see if that
approach might work. However, if you don't need the Filter, then your
problem is solved with your existing form.

This has been quite a deal!

It has made me re-evaluate my understanding of parameterized queries and how
they work at the user-interface and in VBA..I do now believe that your
following code is very suspicious:

Set qd = db.CreateQueryDef("", "SELECT wrkky_invno FROM
qryWorkOrders_uninvoiced WHERE wrkky_invno = true;")
qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob

The SELECT clause and WHERE clause do not contain the required parameters:

[Forms!frmInvoiceSelect!ChooseCust]
[Forms!frmInvoiceSelect!ChooseJob]

I think it would be easier, when running a query in VBA (as the above code
does), to use a simple name for the Parameter, rather than a criteria
expression that would only be needed at the user-interface. But that's
another story.......

Regards.
Geoff

 

[Geoff]

All is clear after a sleep!
No doubt you realised that the parameters are in "qryWordOrders_uninvoiced",
so are hidden from the code below. I didn't see it, working from this
distance without the database. Another good learning experience!
Regards
Geoff

 

[doglover]

Sorry for the dealy it has been a very hectic two weeks with other
commitments that are not computer related.

You nailed the problem regarding the filter issue and relalized the hidden
parameter in the query "qryWorkOrders_uninvoiced."

I commented the line of code in the "Open Event" on the form
"Me.FilterOn=True" per Microsoft's instructions. For now, there do not seem
to be any related issues with the commenting of this code. However, the
"ChooseJob_AfterUpdate Event" uses this filter "Me.FilterOn = False" before
the Me.Requery portion of this event.

My help with the conversion of the project is volunteer as I made a 360
degree career change a few years ago ... most likely I am not current on all
of the changes that affect Access 2003 from Access 97.

I will continue to post information that relates to this project as received.

Thank you for your time, knowledge, interest, and help with this enormously
time consuming project.
Regards,
doglover

All is clear after a sleep!
No doubt you realised that the parameters are in "qryWordOrders_uninvoiced",
so are hidden from the code below. I didn't see it, working from this
distance without the database. Another good learning experience!
Regards
Geoff

I do now believe that your following code is very suspicious:

Set qd = db.CreateQueryDef("", "SELECT wrkky_invno FROM
qryWorkOrders_uninvoiced WHERE wrkky_invno = true;")
qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob

The SELECT clause and WHERE clause do not contain the required parameters:

[Forms!frmInvoiceSelect!ChooseCust]
[Forms!frmInvoiceSelect!ChooseJob]

 

[doglover]

Is there a text version of the options regarding implementing system security
in access 2003. I assumed the best plan is to load the converted or new
databases (data & program) then set up the security. The data will go on the
server and the program database will go on each workstation. IE,
administorator and user with login and password or just not allow changes on
the forms, queries, reports, etc and forget the login password, etc.

Suggestions or pointing me toward text to read will be greatly appreciated.
There is a lot of material available but nothing replaces experience.

Regards,
doglover

Sorry for the dealy it has been a very hectic two weeks with other
commitments that are not computer related.

You nailed the problem regarding the filter issue and relalized the hidden
parameter in the query "qryWorkOrders_uninvoiced."

I commented the line of code in the "Open Event" on the form
"Me.FilterOn=True" per Microsoft's instructions. For now, there do not seem
to be any related issues with the commenting of this code. However, the
"ChooseJob_AfterUpdate Event" uses this filter "Me.FilterOn = False" before
the Me.Requery portion of this event.

My help with the conversion of the project is volunteer as I made a 360
degree career change a few years ago ... most likely I am not current on all
of the changes that affect Access 2003 from Access 97.

I will continue to post information that relates to this project as received.

Thank you for your time, knowledge, interest, and help with this enormously
time consuming project.
Regards,
doglover

All is clear after a sleep!
No doubt you realised that the parameters are in "qryWordOrders_uninvoiced",
so are hidden from the code below. I didn't see it, working from this
distance without the database. Another good learning experience!
Regards
Geoff

I do now believe that your following code is very suspicious:

Set qd = db.CreateQueryDef("", "SELECT wrkky_invno FROM
qryWorkOrders_uninvoiced WHERE wrkky_invno = true;")
qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob

The SELECT clause and WHERE clause do not contain the required parameters:

[Forms!frmInvoiceSelect!ChooseCust]
[Forms!frmInvoiceSelect!ChooseJob]

 

[Geoff]

It's been a hectic week here too.

I was sorry that my earlier responses to your posts were a messy progression
towards enlightenment, particularly since you had often already posted
information that had clarified the situation but which simply wasn't
registering with me! Sometimes, it takes a while for the penny to drop!

I commented the line of code in the "Open Event" on the form
"Me.FilterOn=True" per Microsoft's instructions. For now, there do not
seem to be any related issues with the commenting of this code.
However, the "ChooseJob_AfterUpdate Event" uses this filter
"Me.FilterOn = False" before the Me.Requery portion of this event.

You probably already realise this but, given your above comment, I would say
this. I believe the purpose of the original two code lines in the
ChooseJob_AfterUpdate event (ie Me.FilterOn = False, Me.Requery) was as
follows: By the time the AfterUpdate code runs, the user has by now
selected a customer and a job in the ChooseCust and ChooseJob combo boxes,
so the query to which the form is bound now needs to be refreshed to enable
the form to show a new set of records: For, as you know, the query points to
the two combo boxes for criteria information. Therefore, Me.FilterOn=False
should make the form ignore the filter and allow the query to successfully
use the combo box criteria. I think the filter was intended to effectively
over-ride the criteria from the combo boxes so no records would show in the
form until after a customer and job number had been selected. I think the
original database developer(s) felt the filter needed to be turned off so
that Me.Requery will (or should) pull new records into the form based on the
query's combo box criteria. As we know, of course, that used to happen in
Access 97 but does not happen in Access 2003.

The reason I make this point is because your comment above implies that you
have not remmed out the Me.FilterOn=False code from the AfterUpdate event.
This doesn't do any harm, but equally, I don't see that it does any good
either. Assuming for one moment that you have not remmed out
Me.FilterOn=False, then I don't understand why you need to leave it in: For,
if you've remmed out Me.FilterOn=True in the Form_Open event, then the form
doesn't have a filter that needs to be turned off - not unless I'm still
missing something. (If my assumption is wrong and you have remmed out
Me.FilterOn=False, then just ignore this comment.)

My help with the conversion of the project is volunteer as I made a 360
degree career change a few years ago ... most likely I am not current on
all of the changes that affect Access 2003 from Access 97.

We're all in the same boat! Things change so fast, it's a constant battle to
keep up-to-date. You might like to search the Microsoft website from
time-to-time. You cannot be the only person who has (or will) experience
this problem. Sooner or later, an article is bound to appear on the website,
detailing a work-around or patch or service pack or something.

I will continue to post information that relates to this project as
received.

Many thanks. I'd like to hear what solution Microsoft come up with. I feel
pretty certain that Access should still work that way the original
developers of your database expected it to work.

You might like to re-post your query into a newsgroup specifically dealing
with Access Forms. A guru somewhere might know the solution.

By the way, in a previous post, I suggested that, if you had to retain the
filter, your form might work if you re-did it as an unbound main form with
combo boxes and a bound sub-form to replace your current detail section. If
you experiement in this direction, you would need to requery the control
that contains the sub-form. The reason I suggested an unbound main form is
because it would allow you to display the sub-form in datasheet view,
instead of continuous forms view. I don't know that that would necessarily
make any difference - it's just that I often prefer to see things in
datasheet view. At present, you cannot use datasheet view on the bound form
because, if you did, the combo boxes would disappear. That wouldn't happen
with an unbound main form.

Regards
Geoff

 

[Geoff]

Is there a text version of the options regarding implementing system

security in access 2003. I assumed the best plan is to load the
converted or new databases (data & program) then set up the
security. The data will go on the server and the program database
will go on each workstation. IE, administorator and user with login
and password or just not allow changes on the forms, queries, reports,
etc and forget the login password, etc. Suggestions or pointing me
toward text to read will be greatly appreciated. There is a lot of
material available but nothing replaces experience.

I have found that setting up user-level security is complex because there is
so much to know and, sometimes, things don't work the way you expect them
to. If you unwittingly omit a crucial piece of the jigsaw, you can
compromise your security plan.

I seem to remember being caught out when Microsoft changed the security
system between Access 97 and Access 2000. If I remember right, in Access 97,
you can apply user-level security to modules (to prevent users getting into
modules) but, in Access 2000, user-level security doesn't work for modules.
I thought this was a pretty ghastly change for Microsoft to have made. I
hadn't noticed the change and, as a consequence, when I upgraded a secured
database from Access 97 to Access 2000, my code, which I had understood was
secure in Access 97, simply became insecure in Access 2000 without warning.
I would still prefer to be able to secure modules using user-level security,
than the alternative of creating an mde file. You may be confronted with
this issue. Check the Access 97 version of the database and see if the
modules have user-level security implemented. If they did, then you're going
to have a nice time sorting out how to implement security on the modules in
Access 2003 (remembering that the file format is Access 2000 for your
current database).

I would qualify the following comment by saying that, as always, my
information may be out-of-date (as I don't use Access 2003). However, I
think that, the only current way to make modules secure is to compile the
database to create an mde file. If your code modules are in the Program
database, then at least your Program database should be converted to an mde
file (assuming you need to secure the code modules from user interference).

However, creating MDE files has a number of implications, which may put you
off this approach. For example, you absolutely MUST keep the mdb version in
order to do further development work and you must compile any new mdb
version and release it as an mde version to the users. I believe an mde
version will not allow changes to forms and reports by the user. This is
because the code behind the forms and reports (as well as code in any
modules) has been compiled. This means that, in the mde version of the
database, all program text you write in the VBA editor has been removed from
the database and all your programs have been converted to a (kind of)
machine code. Therefore, even you cannot change the programming in the mde
version. This could be significant if your present database deliberately
allows users to change forms or reports, or if the code in your present
database changes the design of forms or reports at run-time to meet
different situations.

I've created one database in the past with serious user-level security. Like
you, initially, I set up security manually, which took a lot of effort.
However, I ended up using VBA to program the security, which took even more
effort! I seem to recall I did this because it was too easy to overlook
something when doing things manually. When I did things manually (in Access
97), I documented every single database object - tables, queries, forms,
reports and modules - and the permissions I had given to each object. In
fact, I gave different permissions to a number of different groups and made
users members of appropriate groups depending on what access to the
database they should have. I had a limited number of generic user names so
they didn't keep changing as time went by. I documented all permissions to
database objects, groups and users. It took ages and ages. Most important of
all, I wrote down all the security information I used when creating the MDW
file (the workgroup admin file) and kept the written record safe. The MDW
file is the key that unlocks the database. If you lose the key (say the MDW
file gets trashed), then you must restore a backup MDW file or recreate the
MDW file from the written record. If you plan to use the existing MDW file,
then my advice is to keep several copies of it very safe, both on- and
off-site. This is a big subject, so give yourself plenty of time - or the
frustration will drive you nuts!

As requested, here are a few suggestions for learning texts:

1. The book I originally used was:
"Microsoft Access 97 Developer's Handbook"
by Timothy M. O'Brien, Steven J. Pogge, and Geoffrey E. White
ISBN 1-57231-358-7
Publisher: Microsoft Press
Despite a number of minor errors (which most computer books have and which
you, the reader, will easily spot), I still very much admire the consistent
and clear language style of this book. They must have had a very good
editor. The book gives concise and clear explanations of a good selection of
topics, including DAO security. I imagine the book is out-of-print - but you
could search the Intenet (eg Amazon) and see what comes up. A bookseller
somewhere might still have a copy.

2. The other book I mostly use these days (but there is a more up-to-date
version now) is:
"Access 2000 Developer's Handbook"
"Volume 1: Desktop Edition"
by Ken Getz, Paul Litwin, Mike Gilbert
ISBN 0-7821-2370-8
Publisher: Sybex
There is a "Volume 2, Enterprise edition", and a "VBA Language Reference"
book. I bought all three books as a package at a reduced price. These books
are clear, authoritative and indefatigable in their explanations. These
books will set you back quite a bit, so I recommend you browse them in a
good computer bookstore before buying to see if you feel comfortable with
them. (They are much cheaper than a computer course and it's great to have a
good permanent reference on your bookshelf.) Check out the authors' website:

http://www.developershandbook.com/

The above website suggests that the book has been updated for Access 2002,
but not yet for Access 2003. Double-check with a bookstore.

3. You could search the Microsoft website to see what papers on security
you can find there.


I wish you the very best of luck for your journey through the quagmire of
vast, new and changing knowledge.

Regards
Geoff

Why French?
You used "Prive" for "Private".

 

[Geoff]

2. The other book I mostly use these days (but there is a more

up-to-date
version now) is:
"Access 2000 Developer's Handbook"
"Volume 1: Desktop Edition"
by Ken Getz, Paul Litwin, Mike Gilbert
ISBN 0-7821-2370-8
Publisher: Sybex
There is a "Volume 2, Enterprise edition"...

Chapter 8 of Volume 2 deals with security.

Geoff

 

[doglover]

Geoff:

Since Microsoft did not instruct me to remove the Me.FilterOn = False,
Me.Requery I was puzzled and could not understand the need for this portion
and wondering if I had missed something. In short, I believed more was to
yet to be revealed as a result of discovery or further explaination with
regard to upgrade or corrections from Acess 97 to Access 2000 or Access 2003.
I expected a patch from Microsoft to solve this problem. Your synopsis of
the filters actions and goals confirmed my thought process.

It's been a hectic week here too.

I was sorry that my earlier responses to your posts were a messy progression
towards enlightenment, particularly since you had often already posted
information that had clarified the situation but which simply wasn't
registering with me! Sometimes, it takes a while for the penny to drop!


You probably already realise this but, given your above comment, I would say
this. I believe the purpose of the original two code lines in the
ChooseJob_AfterUpdate event (ie Me.FilterOn = False, Me.Requery) was as
follows: By the time the AfterUpdate code runs, the user has by now
selected a customer and a job in the ChooseCust and ChooseJob combo boxes,
so the query to which the form is bound now needs to be refreshed to enable
the form to show a new set of records: For, as you know, the query points to
the two combo boxes for criteria information. Therefore, Me.FilterOn=False
should make the form ignore the filter and allow the query to successfully
use the combo box criteria. I think the filter was intended to effectively
over-ride the criteria from the combo boxes so no records would show in the
form until after a customer and job number had been selected. I think the
original database developer(s) felt the filter needed to be turned off so
that Me.Requery will (or should) pull new records into the form based on the
query's combo box criteria. As we know, of course, that used to happen in
Access 97 but does not happen in Access 2003.

The reason I make this point is because your comment above implies that you
have not remmed out the Me.FilterOn=False code from the AfterUpdate event.
This doesn't do any harm, but equally, I don't see that it does any good
either. Assuming for one moment that you have not remmed out
Me.FilterOn=False, then I don't understand why you need to leave it in: For,
if you've remmed out Me.FilterOn=True in the Form_Open event, then the form
doesn't have a filter that needs to be turned off - not unless I'm still
missing something. (If my assumption is wrong and you have remmed out
Me.FilterOn=False, then just ignore this comment.)

It has been two years since I've attempted to work with Access or any other
software related issues ... things do change and fast.

We're all in the same boat! Things change so fast, it's a constant battle to
keep up-to-date. You might like to search the Microsoft website from
time-to-time. You cannot be the only person who has (or will) experience
this problem. Sooner or later, an article is bound to appear on the website,
detailing a work-around or patch or service pack or something.


Many thanks. I'd like to hear what solution Microsoft come up with. I feel
pretty certain that Access should still work that way the original
developers of your database expected it to work.

Geoff:

Great idea to re-post as I am sure your are correct and other will surely
appreciate this information. Actually, I should copy and post all of your
responses as I thought they were right-on the target and considering that
fact that you did not have the database in your possession you were very
intutive. You are not the guru, sure could have fooled me.

You might like to re-post your query into a newsgroup specifically dealing
with Access Forms. A guru somewhere might know the solution.

Your idea or suggestion about the main form and a subform as a datasheet
view is a great one! In fact, that is my preferred choice of forms that
require a subform with multiple lines of information.

By the way, in a previous post, I suggested that, if you had to retain the
filter, your form might work if you re-did it as an unbound main form with
combo boxes and a bound sub-form to replace your current detail section. If
you experiement in this direction, you would need to requery the control
that contains the sub-form. The reason I suggested an unbound main form is
because it would allow you to display the sub-form in datasheet view,
instead of continuous forms view. I don't know that that would necessarily
make any difference - it's just that I often prefer to see things in
datasheet view. At present, you cannot use datasheet view on the bound form
because, if you did, the combo boxes would disappear. That wouldn't happen
with an unbound main form.

Regards
Geoff


My goal is to respond back to the Microsoft prior to Monday, 8/07 and ask if they have posted a patch to deal with this type of a filter. I honestly did not get the impression this particular individual had experienced this problem or was aware. Trial and error (elimination of code, piece by piece) until the problem was re-created seemed to be the solution or way of discovery. That is exactly how I discovered what was creating the problem. As previously mentioned, I felt out of touch with technology and change and must surely be missing something very important that might have an affect that I was not thinking of. I normally like to start at the very end but in this case was missing too many pieces.

What a time consuming project his has been and such a tremendous help. Can
not possibly thank you enough for all of your efforts.

If more information develops I will post.

Regards,
doglover

 

[doglover]

I have found that setting up user-level security is complex because there is
so much to know and, sometimes, things don't work the way you expect them
to. If you unwittingly omit a crucial piece of the jigsaw, you can
compromise your security plan.

I seem to remember being caught out when Microsoft changed the security
system between Access 97 and Access 2000. If I remember right, in Access 97,
you can apply user-level security to modules (to prevent users getting into
modules) but, in Access 2000, user-level security doesn't work for modules.
I thought this was a pretty ghastly change for Microsoft to have made. I
hadn't noticed the change and, as a consequence, when I upgraded a secured
database from Access 97 to Access 2000, my code, which I had understood was
secure in Access 97, simply became insecure in Access 2000 without warning.
I would still prefer to be able to secure modules using user-level security,
than the alternative of creating an mde file. You may be confronted with
this issue. Check the Access 97 version of the database and see if the
modules have user-level security implemented. If they did, then you're going
to have a nice time sorting out how to implement security on the modules in
Access 2003 (remembering that the file format is Access 2000 for your
current database).

I would qualify the following comment by saying that, as always, my
information may be out-of-date (as I don't use Access 2003). However, I
think that, the only current way to make modules secure is to compile the
database to create an mde file. If your code modules are in the Program
database, then at least your Program database should be converted to an mde
file (assuming you need to secure the code modules from user interference).

However, creating MDE files has a number of implications, which may put you
off this approach. For example, you absolutely MUST keep the mdb version in
order to do further development work and you must compile any new mdb
version and release it as an mde version to the users. I believe an mde
version will not allow changes to forms and reports by the user. This is
because the code behind the forms and reports (as well as code in any
modules) has been compiled. This means that, in the mde version of the
database, all program text you write in the VBA editor has been removed from
the database and all your programs have been converted to a (kind of)
machine code. Therefore, even you cannot change the programming in the mde
version. This could be significant if your present database deliberately
allows users to change forms or reports, or if the code in your present
database changes the design of forms or reports at run-time to meet
different situations.

I've created one database in the past with serious user-level security. Like
you, initially, I set up security manually, which took a lot of effort.
However, I ended up using VBA to program the security, which took even more
effort! I seem to recall I did this because it was too easy to overlook
something when doing things manually. When I did things manually (in Access
97), I documented every single database object - tables, queries, forms,
reports and modules - and the permissions I had given to each object. In
fact, I gave different permissions to a number of different groups and made
users members of appropriate groups depending on what access to the
database they should have. I had a limited number of generic user names so
they didn't keep changing as time went by. I documented all permissions to
database objects, groups and users. It took ages and ages. Most important of
all, I wrote down all the security information I used when creating the MDW
file (the workgroup admin file) and kept the written record safe. The MDW
file is the key that unlocks the database. If you lose the key (say the MDW
file gets trashed), then you must restore a backup MDW file or recreate the
MDW file from the written record. If you plan to use the existing MDW file,
then my advice is to keep several copies of it very safe, both on- and
off-site. This is a big subject, so give yourself plenty of time - or the
frustration will drive you nuts!

As requested, here are a few suggestions for learning texts:

1. The book I originally used was:
"Microsoft Access 97 Developer's Handbook"
by Timothy M. O'Brien, Steven J. Pogge, and Geoffrey E. White
ISBN 1-57231-358-7
Publisher: Microsoft Press
Despite a number of minor errors (which most computer books have and which
you, the reader, will easily spot), I still very much admire the consistent
and clear language style of this book. They must have had a very good
editor. The book gives concise and clear explanations of a good selection of
topics, including DAO security. I imagine the book is out-of-print - but you
could search the Intenet (eg Amazon) and see what comes up. A bookseller
somewhere might still have a copy.

2. The other book I mostly use these days (but there is a more up-to-date
version now) is:
"Access 2000 Developer's Handbook"
"Volume 1: Desktop Edition"
by Ken Getz, Paul Litwin, Mike Gilbert
ISBN 0-7821-2370-8
Publisher: Sybex
There is a "Volume 2, Enterprise edition", and a "VBA Language Reference"
book. I bought all three books as a package at a reduced price. These books
are clear, authoritative and indefatigable in their explanations. These
books will set you back quite a bit, so I recommend you browse them in a
good computer bookstore before buying to see if you feel comfortable with
them. (They are much cheaper than a computer course and it's great to have a
good permanent reference on your bookshelf.) Check out the authors' website:

http://www.developershandbook.com/

The above website suggests that the book has been updated for Access 2002,
but not yet for Access 2003. Double-check with a bookstore.

3. You could search the Microsoft website to see what papers on security
you can find there.


I wish you the very best of luck for your journey through the quagmire of
vast, new and changing knowledge.

Regards
Geoff

Why French?
You used "Prive" for "Private".

Thank you for all of the information regarding implementing security and the
related issues. I certainly have another huge project ahead of me and you
certainly have pointed me in the right direction. Simple is my choice. The
journey begins.

Regards,
doglover

 

[Geoff]

Since Microsoft did not instruct me to remove the

Me.FilterOn = False, Me.Requery I was puzzled and could not
understand the need for this portion and wondering if I had missed
something. In short, I believed more was to yet to be revealed as a
result of discovery or further explaination with regard to upgrade or
corrections from Acess 97 to Access 2000 or Access 2003.
I expected a patch from Microsoft to solve this problem. Your
synopsis of the filters actions and goals confirmed my thought process.

I think your expectation is spot on - that a patch will, eventually, be the
order of the day. (But we'll have to wait and see.)

As you know, I'm getting the same behaviour in Access 2002. Therefore, it
seems Microsoft have inadvertently introduced a product change sometime
after Access 97 and before Access 2002. It must be inadvertent because the
Access 2002 online help system continues to assert (as expected) that:

"You can remove a filter by ... using Visual Basic to set the FilterOn
property to False."

If this is a general issue, then I guess Microsoft will need to consider
what customer advice or patches they need to provide and for what products.

Very, very interesting. Well done. Please keep the newsgroup posted.

Regards
Geoff