HasData -- help!

[Chris]

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?

 

[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)

 

[Klatuu]

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]

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?

 

[Chris]

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

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)

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?

 

[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.

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?

 

[Chris]

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

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?

 

[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

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

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?

 

[Chris]

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

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

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

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?

 

[Dirk Goldgar]

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.

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.

 

[Dirk Goldgar]

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?

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?