Please Help!

[Guest]

I have some code that works well if there are already records in the table
components1. if the
count returns an empty record set, it gives an error. I need to have the
value
of 1 put into me.numentrytype.value if an empty record set is returned, if
not, the count of recoreds +1 needs to
go into Me.NumEntryType.Value ...
I've tried it in a number of ways, to no avail

the error is "run time error 2113"
The value you entered isn't valid for this field ...

debug shows it bombing out on Me.NumEntryType.Value = r("ct") because the
value is null ...

Private Sub Combo26_AfterUpdate()
Dim r As Recordset
Dim wksp As Workspace
Dim dbcur As Database
Dim sqstr As String
sqstr = "SELECT Count(Components1.EntryType)+1 AS ct "
sqstr = sqstr & "FROM Components1 "
sqstr = sqstr & "WHERE (((Components1.EntryType) = '" & Me.EntryType.Value &
"')) "
sqstr = sqstr & "GROUP BY Components1.EntryType "
sqstr = sqstr & "ORDER BY Components1.EntryType"
Set wksp = CreateWorkspace("", "admin", "", dbUseJet)
Set dbcur = CurrentDb
Set r = dbcur.OpenRecordset(sqstr, dbOpenDynaset, dbReadOnly)

Me.NumEntryType.Value = r("ct")

 

[Guest]

When a query results in no match - -

If you are running your query in code then you could do a Dcount() of that
query before you run it.

If dcount("*","myquery")>0 then
'put it command here for what you want done
end if

 

[Guest]

How would I put that around with what I have coded? The resulting value has
to be written back to the database.

 

[Guest]

your current last line is:

Me.NumEntryType.Value = r("ct")

I'm presuming it is barfing because the ct value is a null....in which case
try modifying to:

If "ct"="" Then
Me.NumEntryType.Value = 1
End If
Me.NumEntryType.Value = r("ct")

try inserting those 3 lines before your last line....if it doesn't work it
is easy enough to remove....if you don't want value changed to a 1 on null
then you can substitue another value

 

[Guest]

Thanks NT ... I appreciate your assistance ... you too Klatuu from a few days
back ...