Electronics Firmware Burner, DLookup(x2) to Command Line

[VBA-Dummy]

This project is a firmware loader for a programmable speaker system.
It calls an Exe, and a /parameter using System data scanned by barcode on a form, the form runs Sub RunLoader01()

Sub RunLoader01()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
    rs.Open "SELECT * FROM SYSTEMSPRODUCED01", cn, adOpenStatic, adLockReadOnly
    rs.MoveLast
   Dim systemid As String
   systemid = rs("FILENAME") 'last field named Filename

    Shell ("Ping.exe ") & systemid, 1 'Use Ping.exe for testing
			               'Firmware rev as parameter

    MsgBox "LOADING, " & systemid, vbOKOnly, "LOADING FIRMWARE MATCHED TO:" 'User msg confirms load

    Set rs = Nothing
    Set cn = Nothing
    End Sub

Now the Engineers want me to use a lookup table to get the executable(Ping) and the parameter string (systemid) so they can manage the firmware updates independently in a new table.

I'll need to use systemid (filename) as the lookup string, then retrieve two fields to replace "Ping.exe" and a New field called "Firmware" to replace the old "systemid" in my Shell command.

I was told dlookup was my only option, but if that's untrue I'm wide open to whatever else can work?

I need to bring those 2 fields, "executable" and "parameter", back to my Sub RunLoader01() and insert them where I currently have Ping.exe, and systemid.

This will launch the proper Executable, and load the correct Firmware into the speaker system.

Anyone have ideas how to bring two fields from another table using dlookup and Dim them into my new Shell command??

I’m already getting a field from one table using rs.MoveLast.

 

[WayneRyan]

VBA,

If your table contains the Executable & the SystemID, then you can use
two DLookUps to retrieve the info. I just don't know what the criteria
is. You said that something was scanned in? The "????????" should be
something like --> "[SomeField] = '" & Me.SomeScannedInField & "'"

Sub RunLoader01()
Dim SystemID As String
Dim Executable As String

SystemID   = Nz(DLookUp("[SystemID]",   "SYSTEMSPRODUCED01", "???????"), "")
Executable = Nz(DLookUp("[Executable]", "SYSTEMSPRODUCED01", "???????"), "")

End Sub

btw,

Is your syntax correct for the Shell command?

Wayne

 

[VBA-Dummy]

Thanks Wayne, I'll give that a shot Monday, California Time.
I didn't realize a solution could look so simple.

The Shell is working now, so I suppose the syntax is correct.

I need to study that "Me. " usage, I'm seeing that alot in these posts.

 

[WayneRyan]

VBA,

Look in the FAQ section. Mile & DCX put a couple of threads there that
explain about when to use "!" and ".".

Wayne

 

[VBA-Dummy]

Hey Wayne,
Your solution works perfectly. Wound up using a slight variation with single quotes for text fields.


U R da man!!
Thanks again.

 

[VBA-Dummy]

Spoke too soon...

Had to move around and rename some code to make sense.

Sub RunLoader01()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
    rs.Open "SELECT * FROM LOADERTABLE01", cn, adOpenStatic, adLockReadOnly
    rs.MoveLast
   Dim systemid As String
   systemid = rs("FILENAME")
   Dim executable As String
   Dim firmware As String
   Dim cma As String
   cma = ", "
    executable = Nz(DLookup("[LOADER]", "querysystemstable", "'systemid'"), "")
    firmware = Nz(DLookup("[DSP]", "querysystemstable", "'systemid'"), "")

Shell ("C:\" & executable & " - " & firmware), 1 ' Work This Syntax Later

MsgBox "LOADING, " & systemid & cma & executable & cma & firmware, vbOKOnly, "LOADING FIRMWARE MATCHED TO:"

Set rs = Nothing
Set cn = Nothing
End Sub

Now, when I use the Nz(dlookup... it always returns only the first record's data, not the data for the Lookup value. It does return the correct fields, but only from the first record.

"'SYSTEMID'" is coming from a table using rs, then used as the criteria for dlookup in a query, could that be creating a problem?

I also had to "' '" dbl.sgl quote systemid to negate runtime error "you cancelled the previous operation", was that a problem ?

I'm stumped

 

[pbaldy]

Don't want to speak for Wayne, but it doesn't look like he's online, so I'll try to help. You haven't told it which record you want, which Wayne noted you would need to:

The "????????" should be
something like --> "[SomeField] = '" & Me.SomeScannedInField & "'"

A proper DLookup might look like

DLookup("[LOADER]", "querysystemstable", "systemid = 5")

or something, though more than likely you'll be needing to refer to a form field or variable containing the value representing the record you want? Here's a good reference on the structure of DLookup's:

http://www.mvps.org/access/general/gen0018.htm

 

[VBA-Dummy]

You haven't told it which record you want, which Wayne noted you would need to:

A proper DLookup might look like

DLookup("[LOADER]", "querysystemstable", "systemid = 5")

or something, though more than likely you'll be needing to refer to a form field or variable containing the value representing the record you want?

I guess I'm confused. "systemid" IS the value I want to lookup in "querysystemtable", field LOADER is the value to return. In that query, the text for systemid should be found in the first column, it's there.

I'm old school Excel, maybe I'm rationalizing in the wrong terms?

I'll go ahead and experiment with your solution, I'm sure your correct, I just cant visualize it right now.

Thanks pbaldy.

 

[pbaldy]

Perhaps if you posted an example of what the table data looks like. The structure of a dlookup is

First section: the name of the field you want to see returned
Second section: the name of the table or query containing it
Third section: a criteria that identifies which record within the table that you want that field from

The third section seems to be where we're stuck, the criteria. It is basically an SQL WHERE clause without the word WHERE. Therefore, it won't just be 'systemid'. It has to look like

FieldName = Something

If you're saying that systemid is the value to lookup, which field contains that value? Here's an example. If I had a login table that had 2 fields, UserName & Password. I enter my name & password in fields on a form, and I want to find my password from the table to verify it against what I enter on the form. The criteria section of the DLookup needs to specifically identify which record in the table I want the password for, in this case Paul. The DLookup would look like

DLookup("Password","tblUsers", "UserName = '" & Forms!FormName.txtUser & "'")

Access would evaluate the contents of the form and internally it would look like:

DLookup("Password","tblUsers", "UserName = 'Paul'")

If you post a sample of your data, maybe I can make it clearer.

 

[VBA-Dummy]

Thanks for the fantastic lesson Paul.

I suppose all of you feel like you're beating a dead horse by now. Thanks for your patience!!

Here's a sample of the tables and query being called on from this Sub:
I tried changing around the Criteria = and ended up with runtime errors.

Uggg.

 

[pbaldy]

Am I correct that the value of systemID you're looking to match is the FileName from the recordset you opened? If so try this:

DLookup("[LOADER]", "querysystemstable", "systemid = '" & rs!FileName & "'")

 

[WayneRyan]

VBA,

OK, I understand now.

Sub RunLoader01()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SystemID As String
Dim executable As String
Dim firmware As String
Dim cma As String
'
' Get SystemID from the recordset
'
Set cn = CurrentProject.Connection
rs.Open "SELECT * FROM LOADERTABLE01", cn, adOpenStatic, adLockReadOnly
rs.MoveLast
SystemID = rs("FILENAME")

cma = ", "

'
' Now, get the LOADER and DSP from the query
'
executable = Nz(DLookup("[LOADER]", "querysystemstable", "systemid = '" & SystemID & "'"), "")
firmware   = Nz(DLookup("[DSP]",    "querysystemstable", "systemid = '" & SystemID & "'"), "")
'
' Now shell ...
'
Shell ("C:\" & executable & " - " & firmware), 1 

MsgBox "LOADING, " & systemid & cma & executable & cma & firmware, vbOKOnly, "LOADING FIRMWARE MATCHED TO:"

Set rs = Nothing
Set cn = Nothing
End Sub

btw,

When you open the recordset and do a .MoveLast, it has no predictable result. I take it you
mean the last record entered. Tables are just a random collection of rows. To ensure that
you get the latest record, you should probably reference the Index field in LOADERTABLE01:

"SELECT * FROM LOADERTABLE01 Order By Index"

or

"SELECT * FROM LOADERTABLE01 Order By Index DESC"

The second way, you can get rid of the .MoveLast

Wayne

 

[VBA-Dummy]

You guys are great, hands down!

Paul,
You are correct assuming rs.filename is what I'm after.
I got a compile error with that, but with taht example, I FINALLY get the idea, Duh!!

DLookup("[LOADER]", "querysystemstable", "systemid = '" & rs!FileName & "'")

Wayne,
You've outdone yourself. I bet you just got tired of looking at this thread.
I can't thank you enough for re-writing, re-organizing the whole Sub.

I'm about 1.75 hours driving home, thinking about that systemid, systemid all the way. I summized the structure was wrong, therefor canceling out the 2nd systemid usage. Now that I see the correct layout, there's no way I could have done that without you

I'll keep trying... but I'm sure I'll be back as this project moves into full production. The structure was changed three times already, ruining forms, queries, reports, switchboard, etc. I sure hope all future changes are coding!!

Thanks again guys.