Using IF THEN to compare textbox value with table name

Question

I'm a newbie to VB and Access, so don't beat me up too much on this one :-P I am trying to take a value from an unbound text box and search the database (Access 2010) for a table name that matches the value. The tricky part is that the value is entered in the text box as ABC-123. I am using the mid(Me.Text... to split the value into two parts. The ABC is a prefix that is also in a table name called ABC-Tools. This prefix changes (MTE, MOLD, MFG, etc.) depending on the required entry. But I seem to keep getting stuck at how to use in the entire function the table name. Once I get that, then I want to take it a step further and go into the table and compare the next part of the textbox entry, the 123 section which is an asset number (auto-generated primary key within the table). I have tried using the DLookup but I ran into the same problem there. FYI, this is a homegrown Calibration Management Database for a company, and this code is within a "Record Data" button on a form. All text boxes are unbound and also export their contents to a pre-formatted Excel spreadsheet for record keeping.

Here is my code so far. Maybe I am WAY off on how to properly do this. Let me know if you need more information.

Cat = Mid(Me.Text4.Value, 1, InStr(Me.Text4.Value, "-") - 1) 'Takes data on the left of the "-" within unbound text4 box for tool asset prefix (eg. MOLD-xxx)

AssetNum = Mid(Me.Text4.Value, InStr(Me.Text4.Value, "-") + 1) 'Takes data on the right of the "-" within unbound text4 box for tool asset number (eg. xxxx-001)

'Check database for tool

If "& Cat &-Tools" <> "{look_for_table_matching & Cat &-Tools}" Then

msg = _
  MsgBox("Tool " & Cat & "-" & AssetNum & " does not exist. Please check that the tool is in the         database and retry.", _
    vbExclamation, "")

Else {continue on with the rest of the script}

End If

Your If statement is comparing different string literals so the If will always be executed. I think you need use the value of the Cat variable rather than the string "Cat". — DeanOC
@DeanOCHow would I tell the script in the IF statement on the right of <> to look for a table with the name I am trying to compare? I keep thinking I have to do ![Table]![tblname] but I don't think that is it. I have been having a hard time finding out how to do that through a Google search. — Mgogan

E Mett E Mett · Accepted Answer · 2014-05-02T07:12:54

To search through the list of tables in the DB:

Dim tdf as TableDef
Dim MatchingTable as String

For each tdf in CurrentDB.TableDefs
   If tdf.Name Like Cat & "*" Then
      MatchingTable = tdf.Name
      Exit For
   End If
Next

If MatchingTable = "" Then 'table not found
   Msgbox...
End If

To then search the table:

'Dlookup returns a null value if nothing found
str = Nz(Dlookup("<Name of field>",MatchingTable,"AssetNumber = " & AssetNum),"")

If str = "" Then 'asset num not found
   Msgbox...
End If

Using IF THEN to compare textbox value with table name

1 Answers