VBA Query from a string returns adjacent cells

[deafmetal1]

I'm working on a VBA query that gets it's initial query criteria from a string.

i.e., strHULL

Let's say strHULL ends up equaling DDG89. I have a table containing a column with ship's hull # (DDG89), Name (USS MUSTIN), and Homeport (Yokosuka Japan).

I want the query, when invoked, to fetch both the associated name and homeport from that table(we'll call it SHIPS), preferably each as their own string(let's call them strNAME and strHOME), which then each populate a filed in a different table during a separate Public Sub.

So when invoked, the query would pull from the SHIPS table, finding DDG89, and grabbing the next two cells from the same record, USS MUSTIN and Yokosuka Japan. Make sense?

This entire process is a Public Function.

I am not trying to be lazy by not including framework code, because I am still a novice at VBA, reverse-engineering code while learning as I go. All of my project uses DAO recordset functions. And I am just unsure of the best method to execute this query. I've tried DAO stuff and DLOOKUP stuff, no joy.

Thank you for your help as always!!!

 

[dkinley]

Based on what you said, something like this should work although it might require some tweaking.

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryQueryName")
 
rst.FindFirst "[SomeFieldInQueryName] = ' " & Me.txtControlNameToMatch & " '
 
If rst.NoMatch Then
    MsgBox "No match was found."
    Exit Sub
Else
    strHULL = rst.Fields(1)
    strShipName = rst.Fields(2)
    strHomePort = rst.Fields(3)
End If
 
rst.Close
dbs.Close
 
Set rst = Nothing
Set dbs = Nothing

Substitute:
qryQueryName = the name of the query you have set up
SomeFieldInQueryName = the field in the query that you using for the criteria
txtControlNameToMatch = the control on the form where the user inputs the criteria (can be a combo box, can be an input box stored to a variable -drop the Me. if a variable)
rst.Fields(x) = the column number (in the query) of the value you want to assign into the variable. Note, I started with 1 (2nd column in query) even though the first column in the query is 0.

Note: This will return the first record found that matches. So if you have two that meets this criteria, you will need to expand the criteria across several columns to zero in on the record you want.

-dK

 

[deafmetal1]

That is definitely an interesting method, although the query criteria is determined through a Public Sub, not user input, this looks like a great start. I'll work it, and post the findings, and resultant code.

Thank you dk!

 

[dkinley]

If it's through a public sub then the variable used should have sufficient scope in this context .. at least I am guessing it should not seeing your code/layout.

If not, for interim testing, you can have your public sub write to a control that is invisible on the form.

-dk

 

[deafmetal1]

Figured it out, definitely appreciate the point in the right direction dk. I ended up learning Index and Seek in the process, and those worked better for me since I needed to result in two separate strings. Those strings get used later in the parsing to be entered in a separate table.

First is the code from the email parsing procedure:

'get the Ship Name
            strUSS = GetUSS(strToParse:=strFROM)
            If strUSS = "EnterShip" Then
            Exit Sub
            Else
            End If
            
'get the Homeport the ship is assigned to
            strHOME = GetHOME(strToParse:=strFROM)
            If strHOME = "EnterShip" Then
            Exit Sub
            Else
            End If

And then once called, these are the Public Functions that do the query from a table:

Public Function GetUSS(strToParse As String) As String
On Error GoTo Err_GetUSS
       
    Dim db As Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Ships")
        
    rs.Index = "PrimaryKey"
    rs.Seek "=", strToParse
               
    If rs.NoMatch Then
      MsgBox strToParse & " is not in the database, you must add it in Parser DB Utilities below.  Parser will now stop."
      GetUSS = "EnterShip"
    Else
      GetUSS = rs.Fields(1)
      
    End If
    
    rs.Close
    db.Close
    
Set rs = Nothing
Set db = Nothing
 
    
Exit_GetUSS:
    Exit Function
Err_GetUSS:
    MsgBox Err.Description
    Resume Exit_GetUSS

End Function

Public Function GetHOME(strToParse As String) As String
 On Error GoTo Err_GetHOME
        
     Dim db As Database
     Dim rs As DAO.Recordset
     
     Set db = CurrentDb
     Set rs = db.OpenRecordset("Ships")
         
     rs.Index = "PrimaryKey"
     rs.Seek "=", strToParse
                
     If rs.NoMatch Then
      MsgBox strToParse & " is not in the database, you must add it in Parser DB Utilities below.  Parser will now stop."
       GetHOME = "EnterShip"
     Else
       GetHOME = rs.Fields(1)
       
     End If
     
     rs.Close
     db.Close
     
 Set rs = Nothing
 Set db = Nothing
  
     
 Exit_GetHOME:
     Exit Function
 Err_GetHOME:
     MsgBox Err.Description
     Resume Exit_GetHOME
 
 End Function

 

[dkinley]

I ended up learning Index and Seek in the process ...

Good to go, it looks awesome! Required a bit more than just some tweaking than I originally thought. THANKS very much for posting back the resolution, too.

I quoted you since these are the best lessons. A bit hard won, but they stick with you the best. Glad that my notion was at least down the right path.

-dK