Simple DMax lookup with "Like" filter

G

Guest

I need to find the record in tblResults (Access database) with the largest
entry in the "SPECIMEN" column and I want to do it from VBscript only. I am
after the largest specimen number so I can chose the next highest value for a
new specimen. Specimen numbers are not distinct on the whole but generally
for any "RESULTS NAME" they are.

Also I want to filter on only those records with "srchString" somewhere in
the entry for "RESULTS NAME" column.

I am using the following code to open the database and add records from
VBscript:

Dim dbE 'As DAO.DBEngine
Dim dbW 'As DAO.Workspace
Dim dbD 'As DAO.Database
Dim rsR 'As DAO.Recordset

Set dbE = CreateObject("DAO.DBEngine.36") 'VBS, late binding
'Set dbE = New DAO.DBEngine 'VB/VBA, early binding

dbE.SystemDB = "C:\Progra~1\Micros~4\Office\CMMDEV.mdw"

Set dbW = dbE.CreateWorkspace("Test", "Master", "pswd123")

Set dbD = dbW.OpenDatabase("H:\...\cmmbackend.mdb")

Set rsR = dbD.OpenRecordset("RESULTS OF MEASUREMENT PLANS")
With rsR
MsgBox .Fields(0).Value 'demo only
'Add a record:
.AddNew
.Fields("RESULTS ID").Value = ResultId
.Fields("PARENT PLAN").Value = chitems(ipl)

' ...other fields if needed...
.Update
End With
rsR.Close

Thanks for any help!
 
B

Bob Barrows [MVP]

You failed to tell us what the problem is.
I need to find the record in tblResults (Access database) with the
largest entry in the "SPECIMEN" column and I want to do it from
VBscript only. I am after the largest specimen number so I can chose
the next highest value for a new specimen. Specimen numbers are not
distinct on the whole but generally for any "RESULTS NAME" they are.

Also I want to filter on only those records with "srchString"
somewhere in the entry for "RESULTS NAME" column.

I am using the following code to open the database and add records
from VBscript:

Dim dbE 'As DAO.DBEngine
Dim dbW 'As DAO.Workspace
Dim dbD 'As DAO.Database
Dim rsR 'As DAO.Recordset

Set dbE = CreateObject("DAO.DBEngine.36") 'VBS, late binding
'Set dbE = New DAO.DBEngine 'VB/VBA, early binding

dbE.SystemDB = "C:\Progra~1\Micros~4\Office\CMMDEV.mdw"

Set dbW = dbE.CreateWorkspace("Test", "Master", "pswd123")

Set dbD = dbW.OpenDatabase("H:\...\cmmbackend.mdb")

Set rsR = dbD.OpenRecordset("RESULTS OF MEASUREMENT PLANS")
With rsR
MsgBox .Fields(0).Value 'demo only
'Add a record:
.AddNew
.Fields("RESULTS ID").Value = ResultId
.Fields("PARENT PLAN").Value = chitems(ipl)

' ...other fields if needed...
.Update
End With
rsR.Close

Thanks for any help!
Click to expand...
 
G

Guest

Bob,

Sorry about that. I was looking for code in VBscript to open the recordset
and only retrieve certain records based on the Like keyword. In fact I have
accomplished this by hacking away at it.

Now I just need to get Dmax working. I get "type mismatch (Dmax)" with the
following:

lastspecid = DMax("[SPECIMEN]", "[RESULTS OF WORK ORDERS]")

Thanks,
 
B

Bob Barrows [MVP]

Stan said:
Bob,

Sorry about that. I was looking for code in VBscript to open the
recordset and only retrieve certain records based on the Like
keyword. In fact I have accomplished this by hacking away at it.

Now I just need to get Dmax working. I get "type mismatch (Dmax)"
with the following:

lastspecid = DMax("[SPECIMEN]", "[RESULTS OF WORK ORDERS]")
Click to expand...

I suspect you are not allowed to use DMax from external applications (I may
be wron).
 
B

Bob Barrows [MVP]

That's how I would have done it (given that I was forced to use dynamic sql
rather than my prefferred method of using a saved parameter query).

Bob Barrows
Bob,

How about this:
Set rsR = dbD.OpenRecordset("select Max([RESULTS OF WORK
ORDERS].SPECIMEN) AS maxSerial from [RESULTS OF WORK ORDERS] where
[RESULTS FILE NAME] like ('" & MeasPlanName & "*')")

After much wrangling this has passed initial testing and seems to
work fine from Vbs.

The end quotes before and open quotes after MeasPlanName were tricky.

:

I suspect you are not allowed to use DMax from external applications
(I may be wron).
Click to expand...
Click to expand...
 
Top