Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Duplicate entry check (2002 SP-3)

    On an order entry form there is txtReleaseNo (numeric). This number is unique to the shipper, but not necessarily unique to the record-set (i.e. duplicates allowed). The reason for this is that different shippers may have like release numbers. The problem arises that the same number is re-issued by the same shipper and there is no way for me to "trap" the duplication. Might there be a way to check for a duplicate release number without requiring any action (i.e. BeforeUpdate, msg "The release number for this order has been used previously")? I'm not confident that's the best way to handle the issue, but it at least would alert us to a potential problem. Hmm, might the check for duplication be restricted by the customer account number (txtCustomerNo) also?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    Hi Bryan,
    If I understand...I've done that same thing in the past by doing variants of the following in the BeforeUpdate event:

    <pre>strSQL = "SELECT [CustomerNo], [ReleaseNo] "
    strSQL = strSQL & "FROM YourFormsRecordSource "
    strSQL = strSQL & "WHERE [CustomerNo] = " & Me!txtCustomerNo
    strSQL = strSQL & " AND [ReleaseNo] = " & Me!txt[ReleaseNo]

    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount <> 0 Then
    MsgBox "The release number for this order has been used previously", vbOKOnly, "Dupe"
    Else
    'do nothing
    End If

    rst.Close
    Set rst = Nothing
    </pre>

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    Thanks Gary.
    I'm getting an error in the first line. . .strSQL = "SELECT
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate entry check (2002 SP-3)

    So you should declare strSQL before using it:

    Private Sub ...
    Dim strSQL As String
    strSQL = ...

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    <P ID="edit" class=small>(Edited by gdrezek on 13-Jul-05 14:54. edited to ask questions)</P>Hi Bryan,
    As Hans stated you need to declare the variable strSQL and also don't forget to declare the other variables and set reference to the current database:

    Private Sub ....
    Dim db as Database
    Dim rst As Recordset
    Dim strSQL as String

    Set db = Currentdb()

    strSQL = .....

    End Sub

    Also are the names of your fields really txtCustomerNo and txtRelease1No?...or are they CustomerNo and Release1No?
    If the latter then you need to use CustomerNo and Release1No in your SELECT statement.
    Also I noticed in you picture that you have "AND.......
    There really needs to be a space between the " and the AND; i.e. " AND

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    I'm still messin' something up here.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    Sorry Gary, our posts crossed each other. I corrected the " AND issue. This is the code exactly as entered.
    Private Sub txtRelease1No_Exit(Cancel As Integer)
    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String

    Set db = CurrentDb()

    strSQL = "SELECT [txtCustomerNo], [txtRelease1No] "
    strSQL = strSQL & "FROM [tblMaster] "
    strSQL = strSQL & "WHERE [txtCustomerNo] = " & Me!cboCustomerNo
    strSQL = strSQL & " AND [txtRelease1No] = " & Me!txtRelease1No

    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount <> 0 Then
    MsgBox "The release number for this order has been used previously.", vbOKOnly, "Dupe"
    Else
    'do nothing
    End If

    rst.Close
    Set rst = Nothing
    End Sub

    Now I'm getting:
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    Hi Bryan,
    I think you need to check the actual field name in your query. Are the names of your fields really txtCustomerNo and txtRelease1No?...or are they CustomerNo and Release1No?
    If the latter then you need to use CustomerNo and Release1No in your SELECT statement.
    The too few parameters error message usually indicates that you are asking for something that Access can't relate to; such as a misspelled field name, or a requested field which doesn't exist.
    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    I just double-checked the field names and the values returned; they are both valid.

    txtCustomerNo = SIMAG
    txtRelease1No = 307068.1

    I presume this also means the field names are correct. One thing I wonder about, however, is that the customer number is from a combo box (cboCustomerNo) rather than an actual text box. I tried changing the Me! portion from txtCust... to cboCust..., but there was no difference in the error returned. The table entry (and the source of the cbo) is txtCustomerNo. Thanks for your patience!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    <P ID="edit" class=small>(Edited by gdrezek on 13-Jul-05 15:48. edited to ask about txtRelease1No)</P>Hi Bryan,
    OK. Is there more than 1 column in the Combobox? If there is only one and if that column (txtCustomerNo, correct?) is a text field (SIMAG is text)....then it should be surrounded with quotes.

    That part of the WHERE clause should then be:

    strSQL = strSQL & "WHERE [txtCustomerNo] = '" & Me!cboCustomerNo & "'"

    I also just noticed that txtRelease1No is 307068.1. What is the data type of that field? If it is Double then the rest of the WHERE clause should be:

    strSQL = strSQL & " AND [txtRelease1No] = " & Me!txtRelease1No

    If it is text then the rest of the WHERE clause should be:

    strSQL = strSQL & " AND [txtRelease1No] = '" & Me!txtRelease1No & "'"

    note: '" = single quote followed by double quotes; "'" = Double quotes, single quote, Double quotes

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31> they're BOTH text!
    I'm still getting a "type mismatch" at the "Set rst" line though. Amended code:

    Private Sub txtRelease1No_Exit(Cancel As Integer)
    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String

    Set db = CurrentDb()

    strSQL = "SELECT [txtCustomerNo], [txtRelease1No] "
    strSQL = strSQL & "FROM [tblMaster] "
    strSQL = strSQL & "WHERE [txtCustomerNo] = '" & Me!cboCustomerNo & "'"
    strSQL = strSQL & " AND [txtRelease1No] = '" & Me!txtRelease1No & "'"

    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount <> 0 Then
    MsgBox "The release number for this order has been used previously.", vbOKOnly, "Dupe"
    Else
    'do nothing
    End If

    rst.Close
    Set rst = Nothing
    End Sub
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    <P ID="edit" class=small>(Edited by gdrezek on 13-Jul-05 16:24. added a little explanation for DAO and ADO)</P>Bryan,
    How are your references set? From the VBE Under <Tools><References> You should have Microsoft DAO 3.6 Object Library checked (I think it is 3.6, but it may be 3.5x as I don't have a copy of Access 2002 handy at the moment). If you do and you also have Microsoft ActiveX Data Objects 2.x Library, then the DAO should be placed above the ADO 2.x Library.
    OR.....
    you can change your declarations to:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Both these libraries have similarly named methods, but they are NOT the same method.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    That did it Gary!
    Thanks for all the help (and lesson).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry check (2002 SP-3)

    Hi Bryan,
    You're welcome. Glad I could help.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicate entry check (2002 SP-3)

    DAO 3.5 was for Access 97. All the VBA6 versions of Access use DAO 3.6.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •