Dlookup from a Query

[jzalar]

I am using Access 97 (No choice) and I trying to get the VBA to do this.

1. Open a query "X-Misses-1"
2. Pull the value from the field "total misses"
3. Use that value in a loop to run an append query that # of times.

Here is where I am, and I am stuck on

    Dim stDocName As String
    Dim Temp As Integer
    Dim Count As Integer
 
    stDocName = "X-Misses-1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Temp = DLookup("[total misses]")
 
    Count = 1
    Do While Count <= Temp
 
 
    stDocName = "X-Missesappend"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
    Count = Count + 1
    Loop
 
Exit_Command54_Click:
    Exit Sub
 
Err_Command54_Click:
    MsgBox Err.Description
    Resume Exit_Command54_Click
 

[MikeTheBike]

I am using Access 97 (No choice) and I trying to get the VBA to do this.

1. Open a query "X-Misses-1"
2. Pull the value from the field "total misses"
3. Use that value in a loop to run an append query that # of times.

Here is where I am, and I am stuck on

    Dim stDocName As String
    Dim Temp As Integer
    Dim Count As Integer
 
    stDocName = "X-Misses-1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Temp = DLookup("[total misses]")
 
    Count = 1
    Do While Count <= Temp
 
 
    stDocName = "X-Missesappend"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
    Count = Count + 1
    Loop
 
Exit_Command54_Click:
    Exit Sub
 
Err_Command54_Click:
    MsgBox Err.Description
    Resume Exit_Command54_Click
 

Hi

Assuming 'X-Misses-1' is a stored query then have you tried this

Temp = DLookup("[total misses]","X-Misses-1") ??

Note: if the query 'X-Misses-1' has more that one record, then it will return the value of 'total misses' from the first record (i believe?), otherwise you need to use the optional third element 'WHERE' clause in DLookUp.


MTB