Re: access 2003

Tech-Archive recommends: Fix windows errors by optimizing your registry

Geoff:

Results of double-checking the criteria are correct in the query that
retrieves information for the ChooseCust and ChooseJob:

Using the VBA editor:
I followed your instructions with regard to placing the code in the
After_Update event for the ChooseCust comb box and ChooseJob combo box. The
results were correct for both when using the VBA editor. IE, The ChooseCust
result was the correct customer name and the ChooseJob was the correct job id
number.

Using the query record source for the the frmInvoiceSelect :
I removed the parameter expressions and replace with valid values for each
field that had a parameter and the query results were correct.

Testing the =zf function:
The results:
Search text is not found.

Guess this means there is a function in a module somewhere and perhaps it
did not convert over when I imported the database components like a module?

You are correct this is a learning experience especially for me.

Jacksonville, Florida is home so I will not be driving my laptop to your
office.

I can not tell you how much I appreciate your help.

Regards,
doglover





"Geoff" wrote:

See my comments following each of your paragraphs below.

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

I just checked the query that is the record source for the
frmInvoiceSelect and it has the parameters you indicated in
you message. If I understand correctly these parameters
synchronize the combo boxes on the header of the
form and the detail of the form also by using the lablel or name
of the combo boxes which is either ChooseCust or ChooseJob.

Yes, that seems right.

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

I was mistaken when I indicated the
'qd![Forms!frmInvoiceSelect!ChooseCust]=Me!ChooseCust and the same code
except for Job in place of Choose this query creates the PreBilling report
and is executed when you click on the cmdPreBilling (command button) on
the
footer of the form.

OK. Whichever report the above code line helps create doesn't alter the fact
that I do not understand how this code works. Of course, I accept that it IS
working (as you say so), but it doesn't make sense to me at this stage.

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

The combo boxes on the frmInvoiceSelect (header section) seem to be
working ok because they are retrieving the customer name and job name
it is the detail section of the form that does not retrieve any
information
or details for its fields.

If you go to the query that is the record source for the frmInvoiceSelect
and remove the 2 parameters then the query retrieves all records that fit
the other criteria that relates to work orders that have a completed work
date and where the invoice is null, 0 or -1.

The above two paragraphs imply that there is something wrong with one or
both criteria values that are coming from the combo boxes into the query.
Clearly, you have demonstrated that the query is otherwise working because,
when you remove the criteria for the Customer and Job fields, the query
returns all records that meet the other remaining criteria. (Well done!)

I suggest the way forward is to run some tests on exactly what the combo
boxes are returning to the query to ensure that the query is receiving valid
values. Here are some suggestions:

1. First, double-check that the criteria are correct in the query; ie
that the criterion for the Customer field definitely points to the
ChooseCust combo box and that the criterion for the Job field definitely
points to the ChooseJob combo box. Then...

2. In the After_Update event for the ChooseCust combo box, enter the
following code:

Debug.Print "Customer:", Me.ChooseCust.Value

3. In the After_Update event for the ChooseJob combo box, enter the
following code:

Debug.Print "Job:", Me.ChooseJob.Value

4. Open the form, select a customer and a job. Close the form. Switch to
the VBA editor. Open the Immediate window (CTRL-G). The Immediate window
will contain what the combo boxes are sending to the query. Are the combo
boxes sending valid criteria? For example, if the query expects long
integers for the CustomerID and JobID, did you see integers in the Immediate
window? More to the point, did you see the correct integers for the
customer you selected and the job you selected? If not, perhaps the bound
column of one or both combo boxes is wrong.

5. When finished, remove the Debug.Print statements from the After_Update
event code.

6. As an alternative approach, open the query in design view and
temporarily remove the criteria expressions for the Customer and Job; ie
remove:
[Forms]![frmInvoiceSelect]![ChooseCust]
[Forms]![frmInvoiceSelect]![ChooseJob]
Replace these expressions with valid values for a particular customer and
for a particular job. Run the query. Does it return the records you expect?
If not, why not?

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

This form worked fine in access 97 and it actually worked once in access
2003 as created 1 prebilling form and 1 invoice but after that I am not
sure what has happened.

It sounds like something simple changed after it worked once.

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

This is a very large database but I could removed all of the data except
for a few items or just enought that the forms will work and email.

We should try and solve this via the newsgroup if we can. It makes us both
think and communicate clearly, which is a good learning experience for both
of us, if a bit painful sometimes!

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

I am trying to clean up this database as there have been several
developers working on it over the years.

I thought as much.

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

In short the code that retrieves the information to build the prebilling
report is working. The problem is with the frmInvoiceSelect and appears
to be the query source for the form and the parameters. The tables seem
to
be ok as they are linked. I think the =zf codes in the lables are for
sorting the data but I could be wrong.

Usually, when a property begins with an equals sign, the property is calling
a function in a VBA module and that function returns a value to the
property. If you need to clarify what the =zf codes are doing, I suggest:

1. Open the VBA editor.
2. Open the EDIT menu and select FIND.
3. In the FIND dialog, click the "Current Project" option to ensure you
search through all modules.
4. In the Find What box, enter the =zf code you want to find.
5. Click Find Next.

You will probably find:
Function zf....()
...code here...
End Function

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

I have a feeling your office is far away.

I'm in London. Where are you?

Regards
Geoff



.

Quantcast