New to VBA, Access, and SQL. How do i write an SQL query that will search a table in my AccessDB and give me back a True/False if it finds it?

Question

I haven't messed with VBA much before, or Access or SQL. But i have a little... and I'm working on a project. So, the goal here is to enter a name, take that name and search for that name in a linked table. I'm told I can do this with an SQL Query? But how? Do I need to use an SQL Query?

I've tried quite a few things, but I never get much closer to my answer. I've tried this...

'strTable1 = "FAC_List"

'Dim cellFind As Object
'cellFind = Workbooks("FAC List.xlsx").Sheets("Sheet1").Range("A2").Find(what:=txtLDcode)
    'Dim rowNum As Integer
    'Dim cellFind As Object
    'rowNum = 0
    'Do
    '    rowNum = rowNum + 1
    '    Set cellFind = Workbooks("FAC List.xlsx").Sheets("Sheet1").Range("A" & rowNum)
    '
    '    If InStr(cellFind, txtLDcode) Then
    '        txtLDcode = True
    '
    '    End If
    'Loop Until cellFind = ""

    'If Not cellFind Is Nothing Then
    '    Set txtLDcode = True

    'End If

The "Set cellFind" part always returns an error with Workbooks...I've also tried this below...

'Dim objRecordset As ADODB.Recordset
'Set objRecordset = New ADODB.Recordset
'Dim i As Integer
'Dim value As Variant

'objRecordset.ActiveConnection = CurrentProject.Connection
'objRecordset.Open "FAC_List"

'While objRecordset.EOF = False
    'check for match
'    If objRecordset.Fields.Item(0).value = txtLDcode Then

'        txtLDcode = True
'    Else
        'txtLDcode = False
'    End If
'    objRecordset.MoveNext

'Wend

But the problem with the last part is it always returns False... My linked-Table name is FAC_List. Once the user inputs some information, that info is suppose to be searched in a table and basically only return True/False. How can I achieve this, and am I going in the right direction?

Which field should be searched? A simple DLookup should accomplish. Or try: `If objRecordset!fieldname = txtLDcode Then`. Or instead of looping recordset, use DAO recordset and FindFirst method. Or open recordset filtered to the input and check RecordCount. Anything better than looping. — June7, Jun 20 '19 at 20:52
Why are you messing with workbooks when you are in Access. This is not Excel, right? And yes a SQL query is the way to go here. I'm not even certain that VBA is the solution to employ here since this is basic SQL for your database. Like `SELECT nameField FROM yourLinkedTable WHERE nameField = 'somename';` — JNevill, Jun 20 '19 at 20:52
txtLDcode is a variable that needs to be searched. I'll have to check out DLookUp to see how to use it. I've come across someone recommending that also — br34k, Jun 20 '19 at 20:53
I'm basically trying to get the string, that's in my variable txtLDcode, to be searched in a linked table, which is the excel file. Since i haven't messed with SQL much before, I'm personally not entirely sure how to get the query to run and only return True/False if txtLDcode is found or not. It should be a very simple search and return, and if I was in C or c++ shell then i could do this. — br34k, Jun 20 '19 at 20:57
Thank you guys. After researching on using DLookup, i was able to get that to work quite easily. — br34k, Jun 20 '19 at 21:20

June7 · Accepted Answer · 2019-06-20T21:27:25.230

An expression in textbox can execute domain aggregate function (DCount, DSum, DLookup) - no query and no VBA. Substitute fieldname with actual field name. Assume field is text data type; if not, remove apostrophe delimiters.

=IIf(DCount("*", "FAC_List", "fieldname='" & [txtLDcode] & "'") = 0, "False", "True"))

If you must use VBA, then it can execute domain aggregate.

txtLDcode = IIf(DCount("*", "FAC_List", "fieldname='" & Me.txtLDcode & "'") = 0, "False", "True"))

Other VBA options would involve opening a recordset.
1. open recordset filtered by the input and check RecordCount
2. open DAO recordset unfiltered and use FindFirst method

All these avoid looping a recordset which is least efficient.

Thank you. DLookup, after further research, was able to complete my work in what I needed to do. So much easier than anything i was trying to find or do. Just needed a little guidance. — br34k, Jun 20 '19 at 21:30

New to VBA, Access, and SQL. How do i write an SQL query that will search a table in my AccessDB and give me back a True/False if it finds it?

1 Answers1