Adding Count Query to Select Query Causes Blank Screen In Subsequent Form

[OztheAussie]

This is my first post so please bear with me.


I have a form based on a query that prompts the user to enter the Task Number. If the Task Number does not match any records in the Task table then the form appears as it should, but with no data in any of the fields and the query can be run again. Just as I wanted it.

However, within the form I wanted to add 3 calculated fields that related to the task number and displayed 3 distinct Counts of parts associated with that specific Task Number.
So I added three Count queries within my query above and it all works fine. I select a Task Number and the information for each task appears in the associated Form also with the data from the 3 count queries.

My problems now start if a Task Number is input that does not match any records in the Task table. Instead of getting a form with blank fields (as I did before I added the 3 count queries) I now get a completely blank screen and I have to ctrl-alt-delete to close Access down.

How can I have the form appear as normal but with empty fields and incorporating the 3 count queries?

I hope this makes sense to anyone reading it. I don't know a lot about VBA, everything is done using standard Access buttons etc.

Any help would be appreciated as I am completely stumped and Googling hasn't solved the problem. This is the first ever problem I've come across that I can't resolve off my own back.

[Robeen]

Could you post the SQL to your query?

[OztheAussie]

Could you post the SQL to your query?

SELECT TabTask.[Task Number], TabTask.[Task Date], TabTask.Description, TabTask.[Supply Chain Impact], TabTask.[Parts Required], TabTask.[Support Required], TabTask.[Task Manager], TabTask.Commercial, TabTask.Engineering, TabTask.Scheduler, TabTask.[Supply Chain], TabTask.Remarks, TabTask.[Last Updated], TabTask.[Task Cleared], QuePartOrderJustPartsOutstandingCount.CountOfMPN, QuePiF250OutstandingCount.[CountOfPI F250 Request ID], QueTiF250OutstandingCount.[CountOfTI F250 Request ID]
FROM ((TabTask LEFT JOIN QuePartOrderJustPartsOutstandingCount ON TabTask.[Task Number] = QuePartOrderJustPartsOutstandingCount.[Task Number]) LEFT JOIN QuePiF250OutstandingCount ON TabTask.[Task Number] = QuePiF250OutstandingCount.[Task Number]) LEFT JOIN QueTiF250OutstandingCount ON TabTask.[Task Number] = QueTiF250OutstandingCount.[Task Number]
WHERE (((TabTask.[Task Number])=[Enter the Task Number:]) AND ((TabTask.[Task Cleared]) Is Null));

[Robeen]

The query on it's own . . . Runs - right?
I'm not sure I needed to see the SQL after all . . . sorry.

What might be happening is that when the Task Number is not found in TabTask - you have Null values across the board . . . and then your Form tries to perform the calculations in the calculated fields using Nulls.

I created a quick and simple Form based on a Parameter query [Student] and then put a text box on it to Add the values in three of the fields on the Form that come from the query.
When I run the Form for a Student that is in the Table - it adds the three marks the student received on three different tests.
When I run the Form for a Student that is in NOT the Table - it shows blanks in all the fields - and a 0 in the calculated field.

I even changed the code to Average the three marks - and it still worked.

I was not able to re-recreate your problem.
Could you tell me what calculations you are doing?

I have to add that I was performing the calculations using VBA Code [very elementary code - fear not!! ] in the OnCurrent Event of the Form.

This is the VBA Code I used:

Private Sub Form_Current()
Dim int1, int2, int3 As Integer

txt1.SetFocus
int1 = Val(txt1.Text)

txt2.SetFocus
int2 = Val(txt2.Text)

txt3.SetFocus
int3 = Val(txt3.Text)

Me.Text20 = (int1 + int2 + int3) / 3
End Sub

Could you perhaps post a stripped-down versoin of your database here?

[OztheAussie]

Robeen,

Looking at what you've posted got me looking at my database in a different way. Like your example above, I have managed to get the Count Query to return a 0 if theTask Number entered did not have a corresponding record in my Task table.

I think the problems lies where I am trying to link (through a relationship) the Task table and the Count query. As soon as I try to do the link I get the blank screen if the Task Number does not exist.


The link is vital. When I click on a button it prompts for a Task Number. Once the Task Number is entered, another form opens, displaying the selected Task Number information and - ideally - the figures from the related count queries. The form that opens is based on a qery which has the Task table in it as well as the Count queries.

Unless I am missing something here, I don't know of any other way of linking the count query to the Task table - apart from the way I am doing it.

[Robeen]

Sorry! I cannot get a clear picture of your database from your description of the problem.
Do you think you could post a copy of the database here so we can open it up and actually see your problem?
Take out sensitive data if there is any - and then 'Go Advanced' . . . and you'll see a 'Manage Attachments' button below the text window.
If you DO post the DB - tell us what to do to see the problem.

[OztheAussie]

Thanks Robeen. I have zipped a copy of the database but every time I try to upload it comes back as failed.

Not sure what I'm doing wrong there. I'll keep persevering.

[Robeen]

If it is a really large database - perhaps you can strip it down to only the portion of the DB that you're having trouble with - and reduce the data as well . . . and then zip it.
Not sure why you'd have trouble uploading the db here.
I'll be on the lookout for it 'maite' . . . 'gidday' . . . . . . there - that's my entire aussie vocabulary!!! I hope you feel at home now!!!!