none
Queries and forms RRS feed

  • Question

  • I have a couple of questions:
    1. If a user enters a loan number into a form, how do I see if the loan number is already in the database and ask the user whether they would like to update the entry, and if yes, then update the existing entry rather than added a new one. How do I do that?

    2. How do I get the previous date before 3pm in the query? (the actual dates in the database would be 1/12/13 15:00 for instance)

    Thanks so much for your help!
    Thursday, March 14, 2013 5:46 PM

All replies

  • 1.  There are two ways . First declare the database field as unique and you will get an automatic pop that no dublicate valued r allowed. Other method is a record set one. i am pasting You a code which will help you to solve both the queries programatically.

    It consists of fields CompanyId and departname, The form has a text box named txtDeptname

    Private Sub SaveData()
         '  On Error Resume Next

       'Conn is the connection string      
        
        If mbAddNewFlag = True Then
           Set RsDept = New Recordset
           RsDept.Open "Select * from mstDepartment where Department='0' ", Conn, adOpenStatic, adLockOptimistic
             'MaxValue  a function can be called
           RsDept.AddNew
             'RsCategory.Fields("CompanyId").Value = mlngId
        Else
           If Len(mlngId) < 1 Then
              MsgBox "Choose the record Properly", vbInformation
              Exit Sub
           End If
          
           Set RsDept = New Recordset
           RsDept.Open "(Select * from mstDepartment where Department='" & mlngId & "' )", Conn, adOpenStatic, adLockOptimistic
        End If
        
        With RsDept
             
              RsDept.Fields("Department").Value = txtDeptName.Text
            
          RsDept.Update
          RsDept.Close
        End With
       
        Set RsDept = Nothing
        'UpdateInv
                     
      
      MsgBox "Saved ", vbInformation, "Dept Information Entry"
     
    End SubPrivate Sub CtlValidate()
      
            If Len(txtDeptName.Text) < 1 Then
               txtDeptName.SetFocus
            End If
     
      
       
            If Len(txtDeptName.Text) > 0 Then
             If mbAddNewFlag = True Then
               Set RsDubChk = New Recordset
               RsDubChk.Open "select Department from mstDepartment where Department= '" & UCase(txtDeptName) & "' ", Conn, adOpenStatic, adLockOptimistic
                If RsDubChk.RecordCount > 0 Then
                     MsgBox "Dublicate Entry", vbCritical, "Dublicate Entry"
                     flgValidate = False
                End If
              End If
            
               If mbEditFlag = True Then
                      If UCase(txtDeptName.Text) <> UCase(strDub) Then
                          Set RsDubChk = New Recordset
                          RsDubChk.Open "select Department from mstDepartment where Department = '" & UCase(txtDeptName) & "'", Conn, adOpenStatic, adLockOptimistic
                          If RsDubChk.RecordCount > 0 Then
                             MsgBox "Dublicate Entry", vbCritical, "Dublicate Entry"
                             flgValidate = False
                          End If
               End If
               End If
              
               If RsDubChk.State <> 0 Then
                   RsDubChk.Close
               End If
        End If
    End Sub

    Tuesday, April 16, 2013 3:46 AM