Set Variable to Recordset Value

[CMPaccess]

I have been working on this module for a while now.

I have made progress with help on a previous posting but I seem to have hit a wall.

Basically I have a recordset that I want to loop through.

Within each loop I want to set VARIABLES from certain feilds within the Recordset. I then use the variables in a simple copyfile code.

This is the code thus far :-

Set rstWzip = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly, dbReadOnly)
If rstWzip.EOF Then
MsgBox ("no records selected")
While Not rstWzip.EOF
DrgNo = rstWzip("DigitalFile")
DrgPath = rstWzip.Fields("Path").Value
SourceFile = DrgPath + DrgNo & ".dwg" ' Define source file name.
DestinationFile = DrgDest + DrgNo & "_Issue_" + DrgIsID & ".dwg" ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.
'If FileCopy = Err Then
'SourceFile = DrgPath + DrgNo & ".dgn"
'FileCopy SourceFile, DestinationFile

rstWzip.MoveNext
Wend
End If


AS you can see I have tried different approaches of retreiving the data for Drg No and DrgPath. When I debug the code the values are appearing but the variable returns as EMPTY.

How Can I cure this ???

Thanks in advance.

 

[TheAceMan1]

Ho are ya CMPaccess . . . . .

At the very least you need to post [purple]strSQL[/purple]! . . . .

See Ya! . . . . . .

 

[CMPaccess]

Ok here is the full code.


Private Sub Command178_Click()
On Error GoTo Err_Command178_Click

Dim SourceFile, DestinationFile As String
Dim rstWzip As DAO.Recordset
Dim DrgNo As Variant
Dim DrgPath As Variant
Dim DrgDest
Dim DrgIsID
Dim strSQL

DrgDest = "C:\Test Issue\"
DrgIsID = [Forms]![issue]![IssueID]
strSQL = "SELECT [DWG Issues].DigitalFile, Drawings.Path, [DWG Issues].Discip " & _
"FROM Drawings INNER JOIN [DWG Issues] ON Drawings.DrawingID = [DWG Issues].DrawingID " & _
"WHERE [DWG Issues].Discip = '" & Forms!issue!DrgLstDiscip & "'" & _
"AND [DWG Issues].IssueID = " & Forms!issue!IssueID & " "

Set rstWzip = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly, dbReadOnly)
If rstWzip.EOF Then
MsgBox ("no records selected")
While Not rstWzip.EOF
DrgNo = rstWzip(0)
DrgPath = rstWzip.Fields("Path").Value
SourceFile = DrgPath + DrgNo & ".dwg" ' Define source file name.
DestinationFile = DrgDest + DrgNo & "_Issue_" + DrgIsID & ".dwg" ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.
rstWzip.MoveNext
Wend
End If
rstWzip.Close


Exit_Command178_Click:
Exit Sub

Err_Command178_Click:
MsgBox Err.Description
Resume Exit_Command178_Click

End Sub

 

[TheAceMan1]

OK CMPaccess . . . . .

Without going thru a full explanation, try this:

[blue]Private Sub Command178_Click()
On Error GoTo Err_Command178_Click

   Dim db As DAO.Database, rstWzip As DAO.Recordset, SQL As String
   Dim SourceFile As String, DestinationFile As String
   Dim DrgNo, DrgPath, DrgDest, DrgIsID
   
   DrgDest = "C:\Test Issue\"
   DrgIsID = [Forms]![issue]![IssueID]
   SQL = "SELECT [DWG Issues].DigitalFile, Drawings.Path, [DWG Issues].Discip " & _
         "FROM Drawings INNER JOIN [DWG Issues] ON Drawings.DrawingID = [DWG Issues].DrawingID " & _
         "WHERE [DWG Issues].Discip = '" & Forms!issue!DrgLstDiscip & "'AND " & _
               "[DWG Issues].IssueID = " & Forms!issue!IssueID & ";"
   Set rstWzip = db.OpenRecordset(SQL, dbOpenDynaset, dbReadOnly, dbReadOnly)
   
   If rstWzip.EOF Then
      MsgBox ("no records selected")
   Else
      Do
         DrgNo = rstWzip!DigitalFile
         DrgPath = rstWzip!Path
         SourceFile = DrgPath & DrgNo & ".dwg" '
         DestinationFile = DrgDest & DrgNo & "_Issue_" & DrgIsID & ".dwg"
         FileCopy SourceFile, DestinationFile
      rstWzip.MoveNext
      Loop Until rstWzip.EOF
   End If
   
   rstWzip.Close

Exit_Command178_Click:
    Exit Sub
    
Err_Command178_Click:
    MsgBox Err.Description
    Resume Exit_Command178_Click

End Sub[/blue]

See Ya! . . . . . .

 

[CMPaccess]

Thanks your a star !!!