Programmer but new to access programming

[pdbowling]

Hi all, I'm trying to write a macro or module that will:

1) Run a query and
2) Step through the record set using the results as the basis for the next set of queries.

Any suggestions?

The macro builder builds the first one fine but I didn't recognize any options in the drop down lists for stepping through the record set. I do know SQL if this info helps. Maybe a little nudge in the right direction finding the right keywords in VB.???? Much appreciated.

PB

 

[Rob.Mills]

Don't think you can do it with a macro.

First setup the query.

Then setup this function:

Public Function FunctionName()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst as DAO.Recordset
Dim str As String
Dim strInsert as string

Set db=CurrentDb
set qdf=db.Querydefs("Query Name")
Set rst=qdf.OpenRecordset

strInsert=" or "

rst.MoveFirst

Do Until rst.EOF
str=str & rst.Fields("FieldName") & strInsert
rst.MoveNext
Loop

'Now you need to eliminate the last 'or'
str=left(str,len(str)-len(strInsert))


Now str will equal the criteria for your query.

 

[raskew]

Here's another sample (written in Access 97) that you can copy/paste to a new module in Northwind. It uses an existing query so you need only to type:
? totalMyOrder("order subtotals")<Enter>
in the debug window to see it in action.

Function TotalMyOrder(strQryName As String) As String
'*******************************************
'Name:      TotalMyOrder (Function)
'Purpose:   Sample function to process a
'           recordset
'Inputs:    From debug window (ctrl+g):
'           ? totalMyOrder("order subtotals")
'Output:    See debug window
'*******************************************

Dim db As Database
Dim rs As Recordset
Dim i As Integer, n As Integer, j As Integer
Dim amtHold As Long
Dim fmt As String

Set db = CurrentDb
fmt = "$###,###,##0.00"  ' Define money format.

Set rs = db.OpenRecordset(strQryName)

'get the total number of records
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
'following for|next cycles thru the entire recordset
For i = 0 To n - 1
      amtHold = 0
'following for|next processes records in blocks of 10
   For j = 1 To 10
      amtHold = amtHold + rs!Subtotal
      rs.MoveNext
      'next line precludes error message following last record
      If rs.EOF Then Exit For
   Next j
   Debug.Print i + 1 & "-" & i + 10 & "= " & Format(amtHold, fmt)
   i = i + 9
Next i

'cleanup -- release memory
rs.Close
db.Close
Set db = Nothing
End Function

 

[pdbowling]

Nice, thank you

Thank you, looks similar to ASP. This is almost a rheatorical question, Why do I need to declare a database and set it to current when I'm "IN" that database to begin with??? Just a Microsoft quandery....... Thanks so much for your help.
PB

 

[raskew]

PB-
Afraid I can't provide the technical answer to the Dim db as database
Set db = currentDB

...dilemma.

In my mind, it's just an Access idiocyncracie (close, but no cigar!). Every programming language has them. Just do it--you'll be happy with the results.

Bob

 

[pono1]

Perhaps somewhat relevant to the CurrentDB question and of some interest in general: You can, from code, work with other Access files. For example, the following sample deletes a table in a remote Access DB.

Const ThePath = "\\server2\dir1\DB1.mde"

Const Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThePath

Dim Connection As New ADODB.Connection
Call Connection.Open(Connect_String)
    
    Dim strSQL As String
    strSQL = "DROP TABLE TblTxt"
    
    Connection.Execute strSQL

Connection.Close
Set Connection = Nothing

Regards,
Tim

 

[Rich]

Wasn't CurrentDB supposed to be obsolete after Access2 ?

 

[pono1]

Rich,

CurrentDB is still around in A2K, since DAO is also available in A2K (just not checked -- installed -- by default).

Regards,
Tim