HasData -- help!

G

Guest

I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close the form. I
use HasData and get an error "you entered an expression that has no value."
Well, all the records are gone, but how do I get around this? Works great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do to just
close the form?
 
R

Rob Oldfield

In the form's delete event...

if me.recordsetclone.recordcount=1 then
docmd.close acform,me.name
end if

(=1 because it will run before the delete has taken place)
 
G

Guest

Here is a simpler solution that will do what you want. If any matching
records remain, the message will be presented and the form will not close.
If no matching records are found, the form closes:
Private Sub btnCloseForm_Click()
If Nz(DCount("[CRRefInvNum]","[tbl 1 ClientCreditApplied]", _
"[CRRefInvNum] =" & [CRRefInvNum] ),0) <> 0 Then
MsgBox "something still there."
Else
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

(preaching begings)
A couple of notes:
Don't uses spaces in names. It can create problems. Names should include
Letters, Numbers, and Underscores only.
It appears your field name and your control name are the same - [CRRefInvNum]
Not only is it confusing for us humans to read, it can confuse Access. This
is a good field name, but not a good control name. I would suggest prefixing
all Text Box control names with txt - txtCRRefInvNum. This makes it clear
to the reader and will not be confused with the field name. Also, although
it is not always required, use the Me. prefix to be clear it is a control on
the form and not a variable, so address it as Me.txtCRRefInvNum.

(preaching complete)
 
G

Guest

That looks better, but it leaves the last record. Not on the form (at least I
think not, it closes fast!) but in the table.

What causes that?
--
Thanks for your help,
Chris


Rob Oldfield said:
In the form's delete event...

if me.recordsetclone.recordcount=1 then
docmd.close acform,me.name
end if

(=1 because it will run before the delete has taken place)


Chris said:
I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close the form. I
use HasData and get an error "you entered an expression that has no value."
Well, all the records are gone, but how do I get around this? Works great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do to just
close the form?
Click to expand...
Click to expand...
 
G

Guest

Thanks...preaching appreciated. Maybe that's an issue here.

I pasted your code and get the same message I got with mine. Anything you
see?
--
Thanks for your help,
Chris


Klatuu said:
Here is a simpler solution that will do what you want. If any matching
records remain, the message will be presented and the form will not close.
If no matching records are found, the form closes:
Private Sub btnCloseForm_Click()
If Nz(DCount("[CRRefInvNum]","[tbl 1 ClientCreditApplied]", _
"[CRRefInvNum] =" & [CRRefInvNum] ),0) <> 0 Then
MsgBox "something still there."
Else
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

(preaching begings)
A couple of notes:
Don't uses spaces in names. It can create problems. Names should include
Letters, Numbers, and Underscores only.
It appears your field name and your control name are the same - [CRRefInvNum]
Not only is it confusing for us humans to read, it can confuse Access. This
is a good field name, but not a good control name. I would suggest prefixing
all Text Box control names with txt - txtCRRefInvNum. This makes it clear
to the reader and will not be confused with the field name. Also, although
it is not always required, use the Me. prefix to be clear it is a control on
the form and not a variable, so address it as Me.txtCRRefInvNum.

(preaching complete)

Chris said:
I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close the form. I
use HasData and get an error "you entered an expression that has no value."
Well, all the records are gone, but how do I get around this? Works great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do to just
close the form?
Click to expand...
Click to expand...
 
R

Rob Oldfield

I've just tried it here and it works for me. Do you have SetWarnings
switched off? I'd turn them back on and see if you see any errors that way.


Chris said:
That looks better, but it leaves the last record. Not on the form (at least I
think not, it closes fast!) but in the table.

What causes that?
--
Thanks for your help,
Chris


Rob Oldfield said:
In the form's delete event...

if me.recordsetclone.recordcount=1 then
docmd.close acform,me.name
end if

(=1 because it will run before the delete has taken place)


Chris said:
I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close the
Click to expand...
form.
I
use HasData and get an error "you entered an expression that has no value."
Well, all the records are gone, but how do I get around this? Works great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do to just
close the form?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Don't know SetWarnings. Very amateur here.
--
Thanks for your help,
Chris


Rob Oldfield said:
I've just tried it here and it works for me. Do you have SetWarnings
switched off? I'd turn them back on and see if you see any errors that way.


Chris said:
That looks better, but it leaves the last record. Not on the form (at least I
think not, it closes fast!) but in the table.

What causes that?
--
Thanks for your help,
Chris


Rob Oldfield said:
In the form's delete event...

if me.recordsetclone.recordcount=1 then
docmd.close acform,me.name
end if

(=1 because it will run before the delete has taken place)


I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close the form.
I
use HasData and get an error "you entered an expression that has no
value."
Well, all the records are gone, but how do I get around this? Works great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do to just
close the form?
Click to expand...
Click to expand...
Click to expand...
 
R

Rob Oldfield

Each time you delete, do you get a "You're about to delete a record, are you
sure... yadda, yadda, yadda"?

Anyway, add the following at the start of your routine to switch them on:

docmd.setwarnings true



Chris said:
Don't know SetWarnings. Very amateur here.
--
Thanks for your help,
Chris


Rob Oldfield said:
I've just tried it here and it works for me. Do you have SetWarnings
switched off? I'd turn them back on and see if you see any errors that way.


Chris said:
That looks better, but it leaves the last record. Not on the form (at least I
think not, it closes fast!) but in the table.

What causes that?
--
Thanks for your help,
Chris


:

In the form's delete event...

if me.recordsetclone.recordcount=1 then
docmd.close acform,me.name
end if

(=1 because it will run before the delete has taken place)


I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close
Click to expand...
the
form.
I
use HasData and get an error "you entered an expression that has no
value."
Well, all the records are gone, but how do I get around this?
Click to expand...
Works
great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do
Click to expand...
to
just
close the form?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes I'm getting delete language/confirmations.

I've tried again with only your code at form's OnDelete. The only other
code on this form is the btnDeleteCRApp on click to delete (w/confirms).

The records do delete, but the form remains open.

So....I'm just going to let the user "X" out of the form. That is working.
Thanks so much.
--
Thanks for your help,
Chris


Rob Oldfield said:
Each time you delete, do you get a "You're about to delete a record, are you
sure... yadda, yadda, yadda"?

Anyway, add the following at the start of your routine to switch them on:

docmd.setwarnings true



Chris said:
Don't know SetWarnings. Very amateur here.
--
Thanks for your help,
Chris


Rob Oldfield said:
I've just tried it here and it works for me. Do you have SetWarnings
switched off? I'd turn them back on and see if you see any errors that way.


That looks better, but it leaves the last record. Not on the form (at
least I
think not, it closes fast!) but in the table.

What causes that?
--
Thanks for your help,
Chris


:

In the form's delete event...

if me.recordsetclone.recordcount=1 then
docmd.close acform,me.name
end if

(=1 because it will run before the delete has taken place)


I have a continuous form for the user to review and delete records;
ultimately all the records will be deleted. Then I want to close the
form.
I
use HasData and get an error "you entered an expression that has no
value."
Well, all the records are gone, but how do I get around this? Works
great
when there are records.

My code:

Private Sub btnCloseForm_Click()
If HasData1(CRRefInvNum) Then
MsgBox "something still there."
Exit Sub
End If
DoCmd.Close
End Sub

Function HasData1(CRRefInvNum) As Boolean
'This routine tests for record and suppresses form if no data.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb()

SQL = "SELECT [tbl 1 ClientCreditApplied].CRRefInvNum FROM [tbl 1
ClientCreditApplied] WHERE [CRRefInvNum] =" & [CRRefInvNum] & ";"
Set rst = db.OpenRecordset(SQL)

If rst.EOF And rst.BOF Then
HasData1 = False
Else
HasData1 = True
End If
End Function

I know its because [CRRefInvNum]s are all gone, but what can I do to
just
close the form?
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Klatuu said:
It appears your field name and your control name are the same -
[CRRefInvNum] Not only is it confusing for us humans to read, it can
confuse Access. This is a good field name, but not a good control
name.
Click to expand...

Whether it is confusing to humans or not is a debatable point, but I
would dispute the claim that it confuses Access. Considering that
Access automatically gives controls the same names as their bound fields
when you create them by dragging the fields from the field list, or when
they are created by the Form Wizard, I think you'd be hard put to
demonstrate that Access is confused by such an arrangement. Where you
get into trouble is when you give fields *or* controls the same names as
reserved words; especially the names of properties of the Form object.
Then you have to be very careful in your use of bang vs. dot in building
references to them.
 
D

Dirk Goldgar

Chris said:
Thanks...preaching appreciated. Maybe that's an issue here.

I pasted your code and get the same message I got with mine.
Anything you see?
Click to expand...

Are you sure this proceedure is causing the error? Do you by any chance
have code in the form's Unload or Close event that might be the source
of the error you're getting?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HasData variable not defined 1
Looping Through Records 3
Access Returns different values if code is run from "close" event 8
In joining 2+ records together seperated by commas replace last comma with 'and' 1
HELP - Selet Statement not working, Run-time error '3075' 1
Re-using a recordset 6
Do/Loop Dilemma 6
Why linked sql database is read only 2

Top