ACCESS FEED: Re: [MS_AccessPros] Combo box error

Rabu, 21 November 2012

Re: [MS_AccessPros] Combo box error

Bill-

Add a test for a "blank" combo box:

Private Sub cboPlayer_BeforeUpdate(Cancel As Integer)

' Make sure there's something in the combo box first
If Len(Me.cboPlayer & "") = 0 Then
MsgBox "You have typed something in the player but then cleared
it. " & _
"Press Esc to clear your edit before closing the form."
Cancel = True
Exit Sub
End If
' Check for duplicate player
If Not IsNull(DLookup("tfgTfgID", "t_TeamConfiguration", "tfgPlaID = "
& _
Me.cboPlayer & " AND tfgTnaID <> " &
[Forms]![f_TeamCfgChooser]![cboTeamName] & _
" AND tfgTnaID IN (SELECT tnaTnaID FROM t_TeamName " & _
"WHERE tnaSeaID = " & [Forms]![f_TeamCfgChooser]![cboSeason] &
")")) Then
MsgBox "You have already placed this player on another team " & _
"for the current season. Press ESC and enter new player."
Cancel = True
End If
End Sub

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: agent1of6 <Bill.Singer@at-group.net>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Thursday, November 22, 2012 5:37 AM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Combo box error

This database I put together to organize sports teams is working pretty
well. Only one problem.
My TEAMS to PLAYERS is a ONE to MANY relationship.

On my form the TEAM record is on the main form and the PLAYERS are picked
using combo boxes on a continuous sub form. It works great and is quick,
except when you type a name in the combo box and then change your mind and
try to back space the name out and then hit the close button.

I know if I hit the ESC button first it will back out the record, but if I
try to back space to clear the record and then close the form I get a
runtime error.

Here is the before update code on the combo box. It checks to make sure
the user does not assign a player to two teams. It works great except if
the combo box is blank. Then I get an error.

Any thoughts on how I could intercept a Null value so I do not get that
error message?

Private Sub cboPlayer_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("tfgTfgID", "t_TeamConfiguration", "tfgPlaID = " & _
Me.cboPlayer & " AND tfgTnaID <> " &
[Forms]![f_TeamCfgChooser]![cboTeamName] & _
" AND tfgTnaID IN (SELECT tnaTnaID FROM t_TeamName " & _
"WHERE tnaSeaID = " & [Forms]![f_TeamCfgChooser]![cboSeason] & ")")) Then
MsgBox "You have already placed this player on another team " & _
"for the current season. Press ESC and enter new player."
Cancel = True
End If

End Sub

Thanks,
Speaking of Thanksgiving, I am thankful for this group.

Have a great Thanksgiving.

Bill

------------------------------------

Yahoo! Groups Links