Having Trouble w/Data Validation Code (Acc97) 
Author Message
 Having Trouble w/Data Validation Code (Acc97)

I am trying to write a subproc in one of my data entry forms to prevent
duplication of records.  The field I'm using as the "test" of duplicate
is BKNumber; if a user tries to enter a BKNumber that already exists, db
should pop up message box with information from the matching record.
(Code listing below.)

Two questions:
1.  The subproc is{*filter*} up at the "Set rs = db.OpenRecordset"
statement - I get an error dialog stating "Too few parameters.  Expected
1."  Once I close the error dialog, it still permit me to tab past the
BKNumber field, allowing the duplicate entry.  For instance, I have a
test record, in which BKNumber = 3456; entering a new record with
BKNumber = 3456 causes the error, and I can continue with entry via the
form, without having to go back and edit the value of BKNumber.

I've determined that the "Set rs =" statement is, in fact, where it's
{*filter*} up...

2.  How do I grab some of the information on the record that already
exists (f'rinstance, the record for John Smith, whose BKNumber = 3456),
and display my MsgBox to say something like, "This record already exists
for [LastName], [FirstName], [KindredID]"?  (something along the lines
of Dim'ing a few more procedure variables, to grab the .Text properties
of the fields included in the recordset found by the pass through the
db?)

Many thanks for any/all input...

Terry

CODE LISTING:

Private Sub BKNumber_BeforeUpdate(Cancel As Integer)

    On Error GoTo Err_BKNumberCheck
    Dim db As Database    'we need a handle to refer to our own DB
    Dim strSQL As String  'we need a string to build a SQL query
    Dim rs As Recordset   'we need a handle for the records returned
from query

    'If BKNum not null, check count in db
    If BKNumber.Text <> "" Then
        'grab the db, write SQL to get all the rows with the same
BKNumber, run the query
        Set db = CurrentDb()
        strSQL = "SELECT BKNumber FROM Individuals WHERE BKNumber = " &
BKNumber.Text
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot,
dbSQLPassThrough, dbReadOnly)

        'if count >0 then Error: you have dupe, try again
        If rs.RecordCount > 0 Then
            MsgBox "Record already exists; please try again."  'want to
display more detailed info here
            Cancel = True
        End If

    End If

Exit_BKNumber_BefUpdate:
    Exit Sub

Err_BKNumberCheck:
    'If action cancelled by user, don't display error message
    Const conErrDoCmdCancelled = 2501
    If (Err = conErrDoCmdCancelled) Then
        Resume Exit_BKNumber_BefUpdate
    Else
        MsgBox Err.Description
        Resume Exit_BKNumber_BefUpdate
    End If
End Sub



Sat, 14 Dec 2002 03:00:00 GMT  
 Having Trouble w/Data Validation Code (Acc97)
Hello Terry, I hope I get your question right. Did you try setting the
field's Index property to Yes (No duplicates) in the table Design View?

Second. The error message you get often appear when you try to open a
recordset with parameter query. You cannot run a parameter query so easily
from VBA. You would have to inquire about the Parameters collection of the
QueryDef object. If case I'm wrong, I won't go there.

Tell me it this helped! Daniel


Quote:
> I am trying to write a subproc in one of my data entry forms to prevent
> duplication of records.  The field I'm using as the "test" of duplicate
> is BKNumber; if a user tries to enter a BKNumber that already exists, db
> should pop up message box with information from the matching record.
> (Code listing below.)

> Two questions:
> 1.  The subproc is{*filter*} up at the "Set rs = db.OpenRecordset"
> statement - I get an error dialog stating "Too few parameters.  Expected
> 1."  Once I close the error dialog, it still permit me to tab past the
> BKNumber field, allowing the duplicate entry.  For instance, I have a
> test record, in which BKNumber = 3456; entering a new record with
> BKNumber = 3456 causes the error, and I can continue with entry via the
> form, without having to go back and edit the value of BKNumber.

> I've determined that the "Set rs =" statement is, in fact, where it's
>{*filter*} up...

> 2.  How do I grab some of the information on the record that already
> exists (f'rinstance, the record for John Smith, whose BKNumber = 3456),
> and display my MsgBox to say something like, "This record already exists
> for [LastName], [FirstName], [KindredID]"?  (something along the lines
> of Dim'ing a few more procedure variables, to grab the .Text properties
> of the fields included in the recordset found by the pass through the
> db?)

> Many thanks for any/all input...

> Terry

> CODE LISTING:

> Private Sub BKNumber_BeforeUpdate(Cancel As Integer)

>     On Error GoTo Err_BKNumberCheck
>     Dim db As Database    'we need a handle to refer to our own DB
>     Dim strSQL As String  'we need a string to build a SQL query
>     Dim rs As Recordset   'we need a handle for the records returned
> from query

>     'If BKNum not null, check count in db
>     If BKNumber.Text <> "" Then
>         'grab the db, write SQL to get all the rows with the same
> BKNumber, run the query
>         Set db = CurrentDb()
>         strSQL = "SELECT BKNumber FROM Individuals WHERE BKNumber = " &
> BKNumber.Text
>         Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot,
> dbSQLPassThrough, dbReadOnly)

>         'if count >0 then Error: you have dupe, try again
>         If rs.RecordCount > 0 Then
>             MsgBox "Record already exists; please try again."  'want to
> display more detailed info here
>             Cancel = True
>         End If

>     End If

> Exit_BKNumber_BefUpdate:
>     Exit Sub

> Err_BKNumberCheck:
>     'If action cancelled by user, don't display error message
>     Const conErrDoCmdCancelled = 2501
>     If (Err = conErrDoCmdCancelled) Then
>         Resume Exit_BKNumber_BefUpdate
>     Else
>         MsgBox Err.Description
>         Resume Exit_BKNumber_BefUpdate
>     End If
> End Sub



Sat, 14 Dec 2002 03:00:00 GMT  
 Having Trouble w/Data Validation Code (Acc97)
Daniel:

Thanks much for the reply; I attempted to reset the index property on the
field, but Access is giving me an error (the usual "would create duplicate
value in index, primary key or relationship").   Actually, I'm thinking I
_can't_ create a "No Duplicates" index on this field, since in many records,
BKNumber will be null (some records I have values for the field, some I don't
- but I need to make sure that if I do have a value for that field, it doesn't
duplicate a previous value).

Ideas?  Suggestions?  Many thanks!

Terry

Quote:

> Hello Terry, I hope I get your question right. Did you try setting the
> field's Index property to Yes (No duplicates) in the table Design View?

> Second. The error message you get often appear when you try to open a
> recordset with parameter query. You cannot run a parameter query so easily
> from VBA. You would have to inquire about the Parameters collection of the
> QueryDef object. If case I'm wrong, I won't go there.

> Tell me it this helped! Daniel



> > I am trying to write a subproc in one of my data entry forms to prevent
> > duplication of records.  The field I'm using as the "test" of duplicate
> > is BKNumber; if a user tries to enter a BKNumber that already exists, db
> > should pop up message box with information from the matching record.
> > (Code listing below.)

> > Two questions:
> > 1.  The subproc is{*filter*} up at the "Set rs = db.OpenRecordset"
> > statement - I get an error dialog stating "Too few parameters.  Expected
> > 1."  Once I close the error dialog, it still permit me to tab past the
> > BKNumber field, allowing the duplicate entry.  For instance, I have a
> > test record, in which BKNumber = 3456; entering a new record with
> > BKNumber = 3456 causes the error, and I can continue with entry via the
> > form, without having to go back and edit the value of BKNumber.

> > I've determined that the "Set rs =" statement is, in fact, where it's
> >{*filter*} up...

> > 2.  How do I grab some of the information on the record that already
> > exists (f'rinstance, the record for John Smith, whose BKNumber = 3456),
> > and display my MsgBox to say something like, "This record already exists
> > for [LastName], [FirstName], [KindredID]"?  (something along the lines
> > of Dim'ing a few more procedure variables, to grab the .Text properties
> > of the fields included in the recordset found by the pass through the
> > db?)

> > Many thanks for any/all input...

> > Terry

> > CODE LISTING:

> > Private Sub BKNumber_BeforeUpdate(Cancel As Integer)

> >     On Error GoTo Err_BKNumberCheck
> >     Dim db As Database    'we need a handle to refer to our own DB
> >     Dim strSQL As String  'we need a string to build a SQL query
> >     Dim rs As Recordset   'we need a handle for the records returned
> > from query

> >     'If BKNum not null, check count in db
> >     If BKNumber.Text <> "" Then
> >         'grab the db, write SQL to get all the rows with the same
> > BKNumber, run the query
> >         Set db = CurrentDb()
> >         strSQL = "SELECT BKNumber FROM Individuals WHERE BKNumber = " &
> > BKNumber.Text
> >         Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot,
> > dbSQLPassThrough, dbReadOnly)

> >         'if count >0 then Error: you have dupe, try again
> >         If rs.RecordCount > 0 Then
> >             MsgBox "Record already exists; please try again."  'want to
> > display more detailed info here
> >             Cancel = True
> >         End If

> >     End If

> > Exit_BKNumber_BefUpdate:
> >     Exit Sub

> > Err_BKNumberCheck:
> >     'If action cancelled by user, don't display error message
> >     Const conErrDoCmdCancelled = 2501
> >     If (Err = conErrDoCmdCancelled) Then
> >         Resume Exit_BKNumber_BefUpdate
> >     Else
> >         MsgBox Err.Description
> >         Resume Exit_BKNumber_BefUpdate
> >     End If
> > End Sub



Sun, 15 Dec 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Newbie having trouble with VBA Code in Access

2. Having trouble with source code

3. Hi, I am having trouble with bar code font not working

4. Having trouble converting SQL Server encrypted field in VB code

5. Having trouble getting the Alias (Account) field data from a global address

6. Please help! Having trouble with data control

7. New user to VB5 having trouble with data controll

8. Code driven validation and data manipulation against DBMS

9. Troubles with Acc97 and password-protected Back-end database

10. Having Trouble populating an array from table values

11. getting reference path errors, having trouble fixing.

12. Having trouble programatically switching between fax and pdf report output

 

 
Powered by phpBB® Forum Software