Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013

    Question Unanswered: Validating SQL record with Access


    I am not employed as a database specialist, however I did have Access/VB6 courses in college (15 years ago), so I'm taking on this task at my manufacturing/assembly company. I believe it to be fairly simple, but I'm not sure of the easiest or simplest way to go about it.

    We are needing a simple form that a production worker can scan a barcode into. The form would then take the number that is entered into the text box, and verify that the number exists somewhere in a certain field of a certain table. If it exists, the form would then indicate so by presenting a checkmark, or filling the box green, or any other method that doesn't require the user much effort to repeat the process.

    The SQL Server is 2000, the Access version could be 2007 or 2010. I'm not looking for extremely detailed samples of code, only a broad overview of what would likely be the easiest method. My initial research is telling me there are many ways to go about this, and some of them are ringing my 15-year-old-bells from school and others are not. Any thoughts/suggestions/outlines would be GREATLY appreciated!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    1. If you work with attached tables:
    Private Sub Text_Barcode_AfterUpdate()
        If DCount("*", "TableName", "ColumnName = " & Me.Text_Barcode.Value) > 0 Then
            ' present a checkmark, or fill the box green, or any other method ...
        End If
    End Sub
    2. If you work with pass-through queries:
    Public Sub Text_BarCode_AfterUpdate()
        Dim qdf As DAO.QueryDef
        Dim var As Variant
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = "ODBC;driver={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
            .SQL = "SELECT COUNT(*) FROM TableName WHERE ColumnName = " & Me.Text_Barcode.Value
            var = qdf.OpenRecordset.GetRows
        End With
        If var(0, 0) > 0 Then
            ' present a checkmark, or fill the box green, or any other method ...    
        End If
        Set qdf = Nothing
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

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