Help needed with Duplicate Check subroutine

[trekgoes2malaysia]

Hello,

I have created a subroutine in VBA that checks to see if a user's text
entry ( a new sport) already exists in a table (Sport). My problem is
how to refer to a table field in VBA when that table is not already
open. I have included my code below for viewing. Access returns
error messages for my Dmax & Dmin statements as well as my table
reference in my 'if' statement but I can't figure out my problem even
though I followed my reference books instructions. Any help is
appreciated.

Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

Dim formname As String
Dim newsport As String
Dim minrec As Integer
Dim maxrec As Integer
Dim intcounter As Integer

newsport = Me.txtsport
formname = "frmsport"
minrec = DMin("[Sportid]", "Sport")
maxrec = DMax("[Sportid]", "Sport")


For intcounter = minrec To maxrec
If newsport = Sport.Sport Then
MsgBox "Sport already exists, try another"
Exit Sub
Next
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, formname
End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

Thanks,
Patrick

 

[Andy Hull]

Hi

Try the following...

Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

if nz(Me.txtsport)="" then
msgbox "Sport can not be blank"
exit sub
end if

if dcount("*", "Sport", "Sport = '" & Me.txtsport & "'") <> 0 then
MsgBox "Sport already exists, try another"
Exit Sub
end if

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,acMenuVer70
DoCmd.Close acForm, Me.Name

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub


Note: The dcount line uses double quotes (") and single quotes (') so I show
it again below spaced out so you can more easily see where each type of quote
is used...

if dcount( " * " , " Sport " , " Sport = ' " & Me.txtsport & " '
" ) <> 0 then

hth

Andy Hull

 

[Klatuu]

You must either have a recordset open or use a Domain Aggragate Function to
reference a field. Here is a rewrite that would improve your code:

Private Sub SaveRec_Click()

On Error GoTo Err_SaveRec_Click

If Not IsNUll(DLookup("[Sportid]", "Sport") Then
MsgBox "Sport already exists, try another"
Else
If Me.Dirty Then
Me.Dirty = False
End If
End If
DoCmd.Close acForm, "frmsport", acSaveNo

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

I noticed another poster suggested the Count function. It is much slower to
execure than the DLookup.

 

[Pieter Wijnen]

one comment, for the statement
DoCmd.Close acForm, "frmsport", acSaveNo
acSaveNo has nothing to do with the record beeing discarded, it simply tells
not to save any design changes to the form itself. (if anybody was
wondering)

Pieter

You must either have a recordset open or use a Domain Aggragate Function
to
reference a field. Here is a rewrite that would improve your code:

Private Sub SaveRec_Click()

On Error GoTo Err_SaveRec_Click

If Not IsNUll(DLookup("[Sportid]", "Sport") Then
MsgBox "Sport already exists, try another"
Else
If Me.Dirty Then
Me.Dirty = False
End If
End If
DoCmd.Close acForm, "frmsport", acSaveNo

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

I noticed another poster suggested the Count function. It is much slower
to
execure than the DLookup.
--
Dave Hargis, Microsoft Access MVP

Hello,

I have created a subroutine in VBA that checks to see if a user's text
entry ( a new sport) already exists in a table (Sport). My problem is
how to refer to a table field in VBA when that table is not already
open. I have included my code below for viewing. Access returns
error messages for my Dmax & Dmin statements as well as my table
reference in my 'if' statement but I can't figure out my problem even
though I followed my reference books instructions. Any help is
appreciated.

Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

Dim formname As String
Dim newsport As String
Dim minrec As Integer
Dim maxrec As Integer
Dim intcounter As Integer

newsport = Me.txtsport
formname = "frmsport"
minrec = DMin("[Sportid]", "Sport")
maxrec = DMax("[Sportid]", "Sport")


For intcounter = minrec To maxrec
If newsport = Sport.Sport Then
MsgBox "Sport already exists, try another"
Exit Sub
Next
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, formname
End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

Thanks,
Patrick

 

[Klatuu]

Correct; however, you comment is not pertinent. There was no mention of
saving a record in the question.
--
Dave Hargis, Microsoft Access MVP

one comment, for the statement
DoCmd.Close acForm, "frmsport", acSaveNo
acSaveNo has nothing to do with the record beeing discarded, it simply tells
not to save any design changes to the form itself. (if anybody was
wondering)

Pieter

You must either have a recordset open or use a Domain Aggragate Function
to
reference a field. Here is a rewrite that would improve your code:

Private Sub SaveRec_Click()

On Error GoTo Err_SaveRec_Click

If Not IsNUll(DLookup("[Sportid]", "Sport") Then
MsgBox "Sport already exists, try another"
Else
If Me.Dirty Then
Me.Dirty = False
End If
End If
DoCmd.Close acForm, "frmsport", acSaveNo

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

I noticed another poster suggested the Count function. It is much slower
to
execure than the DLookup.
--
Dave Hargis, Microsoft Access MVP

Hello,

I have created a subroutine in VBA that checks to see if a user's text
entry ( a new sport) already exists in a table (Sport). My problem is
how to refer to a table field in VBA when that table is not already
open. I have included my code below for viewing. Access returns
error messages for my Dmax & Dmin statements as well as my table
reference in my 'if' statement but I can't figure out my problem even
though I followed my reference books instructions. Any help is
appreciated.

Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

Dim formname As String
Dim newsport As String
Dim minrec As Integer
Dim maxrec As Integer
Dim intcounter As Integer

newsport = Me.txtsport
formname = "frmsport"
minrec = DMin("[Sportid]", "Sport")
maxrec = DMax("[Sportid]", "Sport")


For intcounter = minrec To maxrec
If newsport = Sport.Sport Then
MsgBox "Sport already exists, try another"
Exit Sub
Next
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, formname
End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub

Thanks,
Patrick