How to Extract from Select Query and match results

[CMPaccess]

what i'm trying to acheive is basically done in 3 stages.

1) I have a select query which displays in a list box.

2) I would then like to use that information from the list box as a recordset to allow me to extract a common data from each record.

3) Based on that extracted data I then want to be able to use those to look up a table and retrive associated data.

Fro example. My select query returns

Drg No, Title, Status - For each record

I want to extract all the Drg Nos from that query.

Then using the drg numbers search through a given table to find a match and then retreive the path for each drg number.

Can anyone suggest the best approach for this as I seem to be making it overly complicated.

thanks in advance

 

[PHV]

You may try this:
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 = [highlight]'" & [/highlight]Forms!Issue!DrgLstDiscip[highlight] & "'[/highlight]" & _
"AND [DWG Issues].IssueID = [highlight]'" & [/highlight]Forms!Issue!IssueID[highlight] & "'"[/highlight]
''' If Discip or IssueID is defined as numeric get rid of the corresponding single quotes
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![Path]
SourceFile = DrgPath & DrgNo & ".dwg" ' Define source file name.
DestinationFile = DrgDest & SourceFile ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.
rstWzip.MoveNext
Wend
rstWzip.Close

You may have to check that DestinationFile is correct as it include DrgPath ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CMPaccess]

PHV,

Thanks a mil. That got rid of the parameter problem.

Only thing I need to figure out now is how to get the copy
part to work,

Regards

 

[CMPaccess]

Help again !!!

Now that the query rountine works I cannot get the copyfile command to work in the loop. My code is this now

While Not rstWzip.EOF
SourceFile = rstWzip![Path] + rstWzip![DigitalFile] & ".dwg" ' Define source file name.
DestinationFile = DrgDest + rstWzip![DigitalFile] & "_" + DrgIsID & ".dwg" ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.
rstWzip.MoveNext
Wend
End If

Any ideas how this type of idea can be done. ??

 

[CMPaccess]

AFter debuggin through the code above basically it appears
that the SourceFile remains "empty" as does the DestinationFile !!

What is the reason for this. ??
Is it not possible to have this type of command as part of a loop ??

thanks in advance