Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    27

    Question Unanswered: Validate Form Text Box in Multiple Records

    I am trying to use DLOOKUP to verify that data in a text box is contained in a table that isn't "part" of the form. The DLOOKUP works fine on the first record but I would like it to go to the next record and verify that the text box on the next record of the active form also is valid until all records are checked. Any insight would be greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You could place the call to the dlookup function in the forms 'on_current' event, this is fired every time the current record changes.

    However this begs the question why?
    what is it you are checking in the 'other' table?
    I'd suggest that you probably need to revist your table design, and possibly verify your referential links or relationships.

  3. #3
    Join Date
    Feb 2004
    Posts
    27
    I think I wasn’t as detailed as I should have been. I have the Dlookup executing when a command button is clicked . There is an IF THEN statement behind the button that if the dlookup evaluates to “untrue”
    Display a message box and select the record that isn’t valid. I have the dlookup working as it should be but it only verifies the active record. I would like it verify each text box (forms![DATA ENTRY]![CUSIP]) is a member of the field [cusip_no] in the table “dbo_V_Security” for each record on the active form. The entire VB code is below. Thanks for your response.

    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    Dim MYVAR As Variant


    MYVAR = DLookup("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]=forms![DATA ENTRY]![CUSIP]")

    If IsNull(MYVAR) Then
    MsgBox "INVALID CUSIP"
    Else

    End If

    Exit_Command2_Click:
    Exit Sub

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    so you have a handler for when the DLookup is a Nul value, but what about if it's a different value from the one that it's being compared to?

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    DLookup/DCount to check all records

    Hi

    How about using DCount (this does not return Null just zero).

    It will also check for dulicates, if that matters !

    You can check all records in the form with te RecordsetClone object something like this

    Code:
    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    Dim MYVAR As Variant
    Dim rs As Recordset
    Dim BkMrk As Variant
    
    Set rs = Me.RecordsetClone
    
    If rs.RecordCount <= 0 Then Exit Sub
    
    BkMrk = Me.Bookmark
    
    rs.MoveFirst
    
    Do Until rs.EOF
    
        MYVAR = DCount("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]= " & rs("CUSIP"))
    
        If MYVAR <= 0 Then
            MsgBox rs("CUSIP") & " is an INVALID CUSIP"
        Else
            ' ????
        End If
        rs.MoveNext
    Loop
    
    rs.Bookmark = BkMrk
    
    rs.Close
    Set rs = Nothing
    Exit_Command2_Click:
    Exit Sub
    You could also cycle trough the records with the DoCmd.GoToRecord , , acNext

    Just some thoughts.

    HTH

    MTB

  6. #6
    Join Date
    Feb 2004
    Posts
    27
    Thanks for the response. When I click the button I get "Run-Time Error 13 Data Type Mismatch" and the debuger points to .

    Set rs = Me.RecordsetClone.

    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    Dim MYVAR As Variant
    Dim rs As Recordset
    Dim BkMrk As Variant

    Set rs = Me.RecordsetClone

    If rs.RecordCount <= 0 Then Exit Sub

    BkMrk = Me.Bookmark

    rs.MoveFirst

    Do Until rs.EOF

    MYVAR = DCount("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]= " & rs("CUSIP"))

    If MYVAR <= 0 Then
    MsgBox rs("CUSIP") & " is an INVALID CUSIP"
    Else
    ' ????
    End If
    rs.MoveNext
    Loop

    rs.Bookmark = BkMrk

    rs.Close
    Set rs = Nothing
    Exit_Command2_Click:
    Exit Sub
    [/code]

    You could also cycle trough the records with the DoCmd.GoToRecord , , acNext

    Just some thoughts.

    HTH

    MTB[/QUOTE]

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This works in Access 97, but not in 2k (or later versions!!).

    So substitute this

    Dim rs As Recordset

    Set rs = Me.RecordsetClone

    with this

    Dim rs as New ADODB.Recorset

    rs.open Me.RecorsSource, CurrentProject.Connection

    The rest should still be OK (but I havnt tried it). Except pehaps for rs.Bookmark = BkMrk (the RecordsetClone is read only).


    Further investigation reviels that you will need to set a referance to the
    "Microsoft DAO 3.6 Object Library" in VB module Tools-> References.
    This is required to use the "old" DAO record sets as I had in the first example, but it is not requied for the second example.

    My mistake was that this is automatically done when you convert from 97 to 2k, but it is not set by default in 2k or later.

    HTH

    MTB

  8. #8
    Join Date
    Feb 2004
    Posts
    27

    Validate Multiple Records in a Form

    First I want to thank you for taking the time to help we this.

    Now I get run time error-2147217900 (80040e14)

    “Invalid SQL statement; expected DELETE, ISERT,PROCEDURE, SELECT, or UPDATE.”

    And the debugger points to rs.Open Me.RecordSource, CurrentProject.Connection

    Here is the code after I made the change . Did I enter something wrong?

    Dim MYVAR As Variant
    Dim rs As New ADODB.Recordset
    Dim BkMrk As Variant

    rs.Open Me.RecordSource, CurrentProject.Connection

    If rs.RecordCount <= 0 Then Exit Sub

    BkMrk = Me.Bookmark

    rs.MoveFirst

    Do Until rs.EOF

    MYVAR = DCount("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]= " & rs("CUSIP"))

    If MYVAR <= 0 Then
    MsgBox rs("CUSIP") & " is an INVALID CUSIP"
    Else
    ' ????
    End If
    rs.MoveNext
    Loop

    rs.Bookmark = BkMrk

    rs.Close
    Set rs = Nothing
    Exit_Command5_Click:
    Exit Sub


    End Sub

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am totally puzzled by this, I assume that the form where the code is actually written is based on a Table, Query or SQL string !?

    Try adding

    MsgBox Me.RecordSource

    Before rs.Open Me.RecordSource, CurrentProject.Connection

    And see what it displays and let us know.

    Still scratching my head.

    BTW I think you can remove the Bookmark code lines as this (I believe) is not a recordset clone, therefore they probably won’t have the same bookmarks !!??


    Perhaps another way is to declare rs as an Object ie.

    Dim RS as Object

    Set rs = Me.RecordsetClone

    And see if that works !
    However this is late binding and you will not have information available the the rs object when writting the code.

    All thee methods work for me !

    MTB

  10. #10
    Join Date
    Feb 2004
    Posts
    27
    After adding MsgBox Me.RecordSource and making all three changes the message box displays the name of the form where the text box is located “Data Entry” and then I get Run –Time Error 3464 “data Type Mismatch in Criteria Expression” and the debugger points to MYVAR = DLookup("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]= " & rs("CUSIP"))
    The form is not based on a table or query. It has the unbound text box named cusip. I want to verify that the “cusip” a user enters in this text box has a related “cusip” in the table “dbo_V_SECURITY”. The table “dbo_V_SECURITY” is not open or bound to the data entry form. My Code now looks like

    Dim MYVAR As Variant
    Dim rs As Object
    Dim BkMrk As Variant

    MsgBox Me.RecordSource
    Set rs = Me.RecordsetClone

    If rs.RecordCount <= 0 Then Exit Sub
    rs.MoveFirst

    Do Until rs.EOF

    MYVAR = DLookup("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]= " & rs("CUSIP"))

    If MYVAR <= 0 Then
    MsgBox rs("CUSIP") & " is an INVALID CUSIP"
    Else
    ' ????
    End If
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    End Sub

    Thanks again!

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Verifying Unbound Controls

    Hi

    Having finally established that the form with TextBox and Button is unbound, then forget everything I have said previously.

    But I am even more confused as to what you are trying to do (must be my age) !!

    Quote Originally Posted by ATECHNICAL
    I think I wasn’t as detailed as I should have been. I have the Dlookup executing when a command button is clicked . There is an IF THEN statement behind the button that if the dlookup evaluates to “untrue”
    Display a message box and select the record that isn’t valid. I have the dlookup working as it should be but it only verifies the active record. I would like it verify each text box (forms![DATA ENTRY]![CUSIP]) is a member of the field [cusip_no] in the table “dbo_V_Security” for each record on the active form. The entire VB code is below. Thanks for your response.
    If the form is unbound how can you “verify the active record” and do it “for each record on the active form” ??

    If, however, you have a number of TextBoxes each with a potential value for [cusip_no], then you could cycle through the controls collection thus

    Code:
    Private Sub Command2_Click()
    
    Dim ctrl As Control
        
            For Each ctrl In Me.Controls
            If ctrl.ControlType = acTextBox Then
                If DCount("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]=" & ctrl.Value) <= 0 Then
                    MsgBox ctrl.Value & " is not valid" ‘ etc
                     ctrl.SetFocus
                    Exit For
                End If
            End If
        Next ctrl
    
    
    End sub
    Hope that is what you meant ?


    MTB

  12. #12
    Join Date
    Feb 2004
    Posts
    27

    Verify Unbound Form

    You will want to hunt me down and choke me after this post .I have attached my Access 2000 Database. I misspoke previously when I said the text box on the form is unbound. That text box is bound to the field “cusip” in the Data Entry Table and the Data Entry form is based on the data entry table. A user will enter multiple records on this form with however many cusip numbers they want. When they are ready to save their work the Validate button is clicked and I want to verify that the cusip numbers they have entered on the data entry form are in the Table “dbo_V_SECURITY”. If they are not there, there should be a message and the offending record(s)are selected.
    The User would not be allowed to save their work until all cusp’s in the Data Entry Table/ Form were compared to the Table “dbo_V_SECURITY”. I tried your most recent code and I get the error message “data type mismatch in criteria expression” and the debugger points to…. If DCount("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]=" & _
    ctrl.Value) <= 0 Then….
    Again thanks very much for your time and effort. If I don’t hear back I completely understand .


    Quote Originally Posted by MikeTheBike
    Hi

    Having finally established that the form with TextBox and Button is unbound, then forget everything I have said previously.

    But I am even more confused as to what you are trying to do (must be my age) !!



    If the form is unbound how can you “verify the active record” and do it “for each record on the active form” ??

    If, however, you have a number of TextBoxes each with a potential value for [cusip_no], then you could cycle through the controls collection thus

    Code:
    Private Sub Command2_Click()
    
    Dim ctrl As Control
        
            For Each ctrl In Me.Controls
            If ctrl.ControlType = acTextBox Then
                If DCount("[cusip_no]", "dbo_V_SECURITY", "[cusip_no]=" & ctrl.Value) <= 0 Then
                    MsgBox ctrl.Value & " is not valid" ‘ etc
                     ctrl.SetFocus
                    Exit For
                End If
            End If
        Next ctrl
    
    
    End sub
    Hope that is what you meant ?


    MTB
    Attached Files Attached Files

Posting Permissions

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