Null query result returns blank form. Help! 1

[PaulChilds]

Hi,

I have a form which is based on a query. The criteria searches in a field called [Identifier].

This works great and brings up the data I need UNLESS the criteria returns a null result, and then the form comes up completely blank - just the background with no text boxes or buttons.

I was given a code which I was told would work, but doesn't. The code is supposed to close down the form if the [Identifier] field is empty, and then display a message telling the user to try again with a different number. Can any one suggest what I'm doing wrong?

Private Sub Form_Open(Cancel As Integer)
If IsNull(Identifier) Then
DoCmd.Close
End If
End Sub

Private Sub Form_Close()
MsgBox "You have entered an invalid code. Please
try another Identifier"
Response = acDataErrContinue
End Sub

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[Hoving]

I have a similar problem, and here's what it looks like at the query level:

I have some queries that, when the result is Null, show a blank line with "(Autonumber)" in my Autonumber ID field, and Record "1 of 1" at the bottom. Forms based on this query won't appear completely blank. All the fields are visible waiting for the user to begin entering. I like this.

On the other hand, I have other queries that, when the result is Null, show *no* blank line and nothing in the record navigation area. Forms based on this query appear completely blank.

I can't see what is different about my query designs but I'd like to fix it at the query level - not the form level.

Hoving

 

[PaulChilds]

I need to fix this at form level. The database is quite complex and different forms and reports draw uopn one query at different times, so I can't adjust the query without affecting other aspects of the database.

Any ideas on what I should put in my code? (See the top post)

Cheers.

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JeremyNYC]

Show us the code you're using to do this and we can probably show you a way to go about this.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[PaulChilds]

The code is in the post at the top of this page in red.

Cheers.

PC.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

Private Sub Form_Open(Cancel As Integer)
If IsNull(Identifier) Then
DoCmd.Close
End If
End Sub

Private Sub Form_Close()
MsgBox "You have entered an invalid code. Please
try another Identifier"
Response = acDataErrContinue
End Sub

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JeremyNYC]

Paul,

First, yes, the code is clearly there. Sorry.

Second, the fact that more than one object uses this query isn't a problem--you can always just make another query object or alter the sql in the form's recordsource property.

Third, the code you have is going to fire that msgbox every time it's closed, whether or not there is data there.

Fourth, I know I've seen this before, but for some reason I can't get the blank form thing to happen right now.

Fifth, what's happening is that you're not getting a null value in that field, or else the code would close the form(that much I was able to test).

I would open the query at a time when you think it's producing a null set and see what is in there.

Also, I would change your code to this:
Private Sub Form_Open(Cancel As Integer)
If IsNull(Identifier) Then
DoCmd.Close
MsgBox "You have entered an invalid code. Please try another Identifier"
End If
End Sub

And get rid of the code in the Close event.

Hope this helps, and sorry for the earlier blindness.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[PaulChilds]

Thanks Jeremy,

Your code works great and also, the reason why my code didn't work in the first place was because AllowAdditions was set to No, so using your code with that set to No, it does exactly what I want it to!!

Cheers

PC.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

I mean... with AllowAdditions set to Yes it works fine!!!

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

Now I that I can do that, I want to re-open the form.

I tried the following code:

Private Sub Form_Open(Cancel As Integer)
If IsNull(Identifier) Then
DoCmd.Close
MsgBox "You have entered an invalid code. Please try another Identifier"
stDocName = "Client Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub

Which re-opens the form, in turn re-running the query too.
However, if the user decides to cancel the query (by clicking cancel in the crteria box) it brings up Run Time Error 2501. And the debugger highlights this line:

DoCmd.OpenForm stDocName, , , stLinkCriteria

Any ideas how to fix it?

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JeremyNYC]

Something's odd here. Why close it and reopen it? What are you trying to accomplish? A simple me.requery will re run the query. But if there were no records an instant ago, why would there be any records now?

Also, what's the text of the error message?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[PaulChilds]

There are plenty of records - it just depends on what you put in the criteria box. Obviously if you typed in DOG1 in the box, and no records had DOG1 as their identifier it's not going to show anything

The error message reads:

Run time error '2501':

The OpenForm action was canceled.


Basically I want a form that asks for a unique code (Identifier). When the user types this in it brings up only One record (in the form of a switchboard with button to open reports and queries, and then all the reports and queries I open can get their criteria from that particular record's identifier). If the user types in a non-existent identifier, the msgbox appears with the 'please try again...' message. CLick OK, and try again.

If the user then decides to cancel, I don't want any error messages coming up that might scare the living daylights out of any unexperienced users.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

I tried this code:

Private Sub Form_Open(Cancel As Integer)
If IsNull(Identifier) Then
MsgBox "You have entered an invalid code. Please try another Identifier"
Me.Requery
End If
End Sub

And if you click cancel on the criteria button, it brings up the error again but with error code 2001 instead and me.requery is highlighted as the faulty line.

Any ideas?

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JeremyNYC]

Hmmm. I don't use parameter queries because of things like this. It's certainly possible to solve this problem using a parameter query, but I don't know my way around them. I would make the form be the place the parameter query gets its data. That way if there is noting in the control on the form you can just bail before trying to open the object.

If nz(me!ControlName) = "" then
msgbox "Yo, gimme some data."
else
'open the other object
end if

(Assumes it's a text parameter. If not, the first line would be
if me!controlname is null

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[PaulChilds]

I don't think you're understanding what I'm trying to do with my form.

I need to be able to open the form with the data from the query result, not feed the query with data from the form.

The form must only ever display ONE record becuase there are about another twenty queries which geth their data from the form data ONCE the first query has been executed.

If I let there be more than one record at a time in the form, the user could make too many mistakes.

I'm I making myself clear? Or am I still talking gibberish? Maybe I'm not explaining the problem correctly.

Paul C

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JeremyNYC]

OK, it's not gibberish, but I do think we need to get some consistent terms in here. Can you describe the whole thing using the actual names of the forms and queries?

My thought is that the way I would design this is with an unbound form on which you can choose one or more criteria and then click on some buttons to go to other objects.

But your description of what you've got, with the names, will be quite helpful.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[PaulChilds]

I'll have to do it tomorrow. Going home now.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk