Re-using a "Door Prize No" - Part two of the questions

[Dennis]

Hi,

I’m on Access 2003 on XP Pro with all the latest updates.

I’m working for a charity that has a membership database. Each member is
assigned a door prize number. The goal is to not have any gaps in the door
prize number. When a member leaves the area, we flag that member as “goneâ€
and I want to re-assign their door prize number to the next new member. I’ve
already beat my head against the wall talking to management about why they
reuse number and they are not going to budge.

I can logically see what I need to do, but I don’t know how to code it. I
would appreciate any help on how to code this or a better way to approach is
issue.

I have a table called tblMember, which is keyed by an auto number field
called AcctNo. I also have the DoorPrizeNo field and Status field. The
status field has one of two values. The values are A for Active or D for
Deleted.

I have a membership form called frmMember. When the user enters a new
member using that form and the press the New Door Price No button, I want to
(in pseudo code):

Select the lowest available door price number from a member with a status of
“Dâ€
If that select works, then
Get the lowest available door prize number from the row with status of “Dâ€
Null out the door price number on the row with status = “D†& upd row
NewNo = LowestAvailableNo
Else
‘ Other wise assign the next highest number
NewNo = DMax(tblMember, DoorPrizeNo) + 1
End if
DoorPrizeNo = NewNo.

By the way, current row is in the tblMaster table and the row with the
deleted member is also in the tblMaster table. I don’t know how to read in
that record, extract the value I want, set that field to null, and write it
back out.

I would greatly appreciated any help.

Thanks,

 

[Daryl S]

Dennis -

Try putting this code behind the button that gets the new door prize number.
Change any field names to match your tables. I haven't tested this in
Access, but the logic should be right.

' This will find the lowest inactive Door Prize Number. If all Door Prize
Numbers are used,
' then it will create a new one that is one higher than the highest current
door price number.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb

' This first SQL finds the lowest inactive door prize number
strSQL = "Select min([DoorPrizeNo]) AS NewDoorPrize from tblMember where
not exists(select [DoorPrizeNo] from tblMember where Status = ""A"" );"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' If no records are returned, then we must find create a door prize
number 1 more than the max.
If rst.RecordCount = 0 Then
rst.Close
strSQL = "Select max([DoorPrizeNo]) + 1 AS NewDoorPrize from tblMember;"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
OpenRecordsetOutput rst
Me.DoorPrizeNo = rst.NewDoorPrize

Else 'We found an unused door prize number - use it and then remove it
from the inactive records.
strSQL = "Select max([DoorPrizeNo]) + 1 from tblMember;"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Me.DoorPrizeNo = rst.NewDoorPrize
strSQL = "Update tblMember set DoorPrizeNo = NULL where DoorPrizeNo =
" & Me.DoorPrizeNo & AND Status <> ""A"" ;"
DoCmd.RunSQL strSQL
End If
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Dennis]

Daryl,

Everything worked great, except for one small problem. When I run the
Update SQL Access pops up a msgbox that informs me that "You are about to
Update 1 Row. If you continue you will not be able to undo.....".

How do I stop this msgbox from appearing. I do not want to have my users
have to answer that questions as they will not know how to answer it.

Here is the final code by the way:


Dim intDoorPrizeNo As Integer
Dim dbCur As DAO.Database
Dim rstNC As DAO.Recordset
Dim strSQL As String
Set dbCur = CurrentDb ‘ Set DB to form's row source

' This first SQL finds the lowest inactive Canteen No
‘ from either Deceased or Term Expired members
strSQL = "Select min([DoorPrizeNo]) AS NewDoorPrizeNo" ‘ Is there to
reuse?
strSQL = strSQL & " FROM qrytblMailingList"
strSQL = strSQL & " WHERE (((MemType) = 'MT-D')) Or (((MemberTerm) =
'MTR-E'))"
Set rstNC = dbCur.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' If a record is returned, then we will re-use it
‘ and then remove it from the inactive record.
' Otherwise we must find generate the next highest canteen no

If rstNC.RecordCount >= 1 Then ‘ There is one that can be
re-used!
intDoorPrizeNo = rstNC!NewDoorPrizeNo ‘ Reuse number
strSQL = "Update qrytblMailingList"
strSQL = strSQL & " SET DoorPrizeNo = NULL"
strSQL = strSQL & " WHERE (((DoorPrizeNo)= " & intDoorPrizeNo & "))"
DoCmd.RunSQL strSQL ' Erase the No from Old Member
Else
intDoorPrizeNo = DMax("DoorPrizeNo", "tblMailingList") + 1 ‘ No #
to reuse, get next highest Canteen No
End If
rstNC.Close ' Clean up the
table variables
Set rstNC = Nothing
Set dbCur = Nothing
txtDoorPrizeNo = CStr(intDoorPrizeNo)

 

[Mike Painter]

Place docmd.setwarnings False before the operation and
docmdserwarnings true after.

 

[John W. Vinson]

Everything worked great, except for one small problem. When I run the
Update SQL Access pops up a msgbox that informs me that "You are about to
Update 1 Row. If you continue you will not be able to undo.....".

How do I stop this msgbox from appearing. I do not want to have my users
have to answer that questions as they will not know how to answer it.

An alternative to toggling SetWarnings is to use

CurrentDb.Execute strSQL, dbFailOnError

instead of

DoCmd.RunSQL strSQL

If you do so be sure to put error trapping code in the subroutine.

 

[Dennis]

John, Mike,

Thanks for the information.

John,

When you say "be sure to put error trapping code " are you refering to the
On Error statement or is there something else?

Thanks,

 

[Dennis]

All,


I have the following SQL statement:

Set dbCur = CurrentDb
strSQL = "Select min([DoorPrizeNo]) AS NewDoorPrizeNo"
strSQL = strSQL & " FROM qrytblMailingList"
strSQL = strSQL & " WHERE (((MemType) = 'MT-D')) Or (((MemberTerm) =
'MTR-E'))"
Set rstNC = dbCur.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

It is working fine. I know this because I copied the actual SQL statement
and pasted it into the Design New Query and ran it and it works fine.

The only issues is if there is no records that meet the criteria the SQL
statement still returns one record that is NULL. If there is a record that
meets the criteria, the SQL statement also returns one record that has a
value in the

In both cases (fails or works) rstNC.RecordCount is equal to 1.

Why does it return a null record is the SQL failed? To determine if I have
a record, I'm testing "IsNull(rstNC!NewDoorPrizeNo)".

 

[David W. Fenton]

An alternative to toggling SetWarnings is to use

CurrentDb.Execute strSQL, dbFailOnError

instead of

DoCmd.RunSQL strSQL

If you do so be sure to put error trapping code in the subroutine.

Or just use my SQLRun functions so you can search and replace
"DoCmd.RunSQL" with "SQLRun" and not have to write the error
handlers.

Code after my .sig.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String, Optional db As Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler

If db Is Nothing Then Set db = CurrentDb
'DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected
'DBEngine.Workspaces(0).CommitTrans

exitRoutine:
SQLRun = lngRecordsAffected
'Debug.Print strSQL
Exit Function

errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & err.Number & ": " _
& err.Description, vbExclamation, "Error in SQLRun()"
Debug.Print "SQL Error: " & strSQL
'DBEngine.Workspaces(0).Rollback
Resume exitRoutine
End Function

 

[John W. Vinson]

Or just use my SQLRun functions so you can search and replace
"DoCmd.RunSQL" with "SQLRun" and not have to write the error
handlers.

Thanks David! Nice utility...

 

[Dennis]

David,

I like your utility, I will be using it.

Thanks,


Dennis

 

[David W. Fenton]

Thanks David! Nice utility...

Not a utility, just a function.

And please, for anyone who uses it and finds anything wrong, post
back to the newsgroup with problems/fixes. I've been using it (or
some version of it) in production for years, but it still
occasionally causes hiccups and I'm sure there are issues I've never
encountered that could be addressed.

 

[David W. Fenton]

I like your utility, I will be using it.

Good! I'm glad others can get benefit from the function (it's not a
utility).

Just let me know in the newsgroups if you encounter any problems.
I'm constantly enhancing it and it's only in the last couple of
years that it's reached a stable and full-featured state.

 

[Daryl S]

Dennis -

When you query for a Minimum value, it always returns a record. Your test
for null should work for you.