BAH, hit return to soon:
Code should be:
Dim strSQL as String
StrSQL = "Select * from [Drawing History Tbl] Where "
strSQL = StrSQL & " [Drawing Number] = '" & Forms![Drawing
Management Frm]![Drawing Number].Value & "' "
strSQL = strSQL & " AND [Drawing Issue] = '" &
Me.Combo2.Value & "'"
'Note, if [Drawing Number] is a number, remove the '
'Same for [Drawing Issue]
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'If .EOF, then that record doesn't exist
if rst.EOF then
rst.AddNew
rst("Drawing Number") = Forms![Drawing Management Frm]!
[Drawing Number].Value
rst("Drawing Issue") = Me.Combo2.Value
rst("Associated Project") = Me.Combo4.Value
rst.Update
Else
'REcord Exists
MsgBox "This action has been stopped bacause
it would create a duplicate entry of drawing " & Forms!
[Drawing Management Frm]![Drawing Number].Value & " " &
Me.Combo2.Value & "." & vbCrLf & _
"Please validate the drawing issue that you
are trying to create, verify the existing entries in the
database and try again if neccessary.",
vbExclamation, "Duplicate entry warning"
End If
rst.Close
The method you proposed would be very slow on a large
table, especially since it is looping through every record
and checking the value. For future reference, consider
using .Seek or .FindFirst methods, as well as only opening
Forward-Only recordsets.
Chris Nebinger
>-----Original Message-----
>Dim strSQL as String
>StrSQL = "Select * from [Drawing History Tbl] Where "
>strSQL = StrSQL & " [Drawing Number] = '" & Forms!
[Drawing
>Management Frm]![Drawing Number].Value & "' "
>strSQL = strSQL & " AND [Drawing Issue] = '" &
>Me.Combo2.Value & "'"
>
>
>'Note, if [Drawing Number] is a number, remove the '
>'Same for [Drawing Issue]
>
>Dim rst As DAO.Recordset
>Set rst = CurrentDb.OpenRecordset(strSQL)
>'If .EOF, then that record doesn't exist
>if rst.EOF then
>rst.AddNew
>rst("Drawing Number") = Forms![Drawing Management Frm]!
>[Drawing Number].Value
>rst("Drawing Issue") = Me.Combo2.Value
>rst("Associated Project") = Me.Combo4.Value
>rst.Update
>rst.Close
>
>>-----Original Message-----
>>Hello
>>
>>I've created a "pop-up" form that load where a user
>specifies a drawing number and issue and then presses the
>run button which runs the following code:
>>
>> Dim rst As DAO.Recordset
>> Set rst = CurrentDb.OpenRecordset("Drawing
>History Tbl")
>> rst.AddNew
>> rst("Drawing Number") = Forms![Drawing
>Management Frm]![Drawing Number].Value
>> rst("Drawing Issue") = Me.Combo2.Value
>> rst("Associated Project") =
>Me.Combo4.Value
>> rst.Update
>> rst.Close
>>
>>I would like to include this code in an if statement
>that would first verify that there isn't already a
>recordset with those values to avoid duplicate entries.
>Could someone guide me a bit.
>>
>>Thanks
>>
>>Daniel
>>.
>>
>.
>
|
Similar Threads
