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]

What about a single query joining drg and path ?

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 that is alot simplier.

I have the query working fine.

How can I then create an action for each entry in the
query once it hase been run.

Eg. I hit the command button, query returns three drawing files and a path.

I then one to code it so it creates a copy of the file in a new folder.

Hope that makes sense !!! Not sure how to go through the query. Do I use a loop ??

Thanks

 

[PHV]

Take a look either at the ListBox or the DAO.Recordset objects and at the FileCopy instruction.

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,

I need your help with this I'm afraid. Had a look at the help files as you suggested and came up with this thus far.

Dim rstWzip As Recordset
Dim DrgNo As Variant
Dim DrgPath As Variant

Set rstWzip = CurrentDb.OpenRecordset("Winzip Query", dbopentable, dbappendonly, dbreadonly)
With rstWzip
Do While Not .EOF

Loop
End With

Where I'm not sure to go now is how to split the information within the recordset. How do you differentiate between records and fields etc.

With the loop I want to use the file copy function with the path and drgno data taken from the recordset.

Is that possible ??

thanks

 

[CMPaccess]

I have now got to this point.

This seems to run through but gives an error of

"Too Few parameters: Expected 1"

Dim rstWzip As Recordset
Dim DrgNo As Variant
Dim DrgPath As Variant

Set rstWzip = CurrentDb.OpenRecordSet("Winzip Query")
If Not (rstWzip.RecordCount) Then
Do
rstWzip.MoveFirst
DrgNo() = rst!DigitalFile
DrgPath() = rst!Path
Loop
End If

I'm probably miles off at the moment I guess !!!

 

[Crowley16]

the error is because you've not used a valid query name or sqlstatement in the .openrecordset() function, either use the name of a saved query, or a complete sql statement...

I've not looked at anything else though, so no idea if this will actually do what you want it to do...

--------------------
Procrastinate Now!

 

[CMPaccess]

WinZip Query" is the correct name of a query.

Have I used it in the wrong way perhaps ??

thanks

 

[Crowley16]

Oh, it's a query...

yeah, you have to first load the querydef, and then open the query, then can you load the query into a recordset...

something like:
dim qry as dao.querydef
set qry = currentdb.querydefs("queryName")

set rs = qry.openrecordset

--------------------
Procrastinate Now!

 

[Crowley16]

oh, almost forgot...

you can just you the sql statement directly in the .openrecordset() function instead of having to use a saved query and stuff...

set rs = currentdb.openrecordset("SELECT BLAH BLAH...")

--------------------
Procrastinate Now!

 

[CMPaccess]

Ok will try again cheers

 

[hkaing79]

Do you have DAO referenced? Check in Tools - > References.

There's nothing wrong with

Set rstWzip = CurrentDb.OpenRecordSet("Winzip Query")

Although you may want to try Dim rstWzip AS DAO.RecordSet.

 

[CMPaccess]

Thanks people,

I tried all suggestions but stilll get the same error message. "Too Few Parameters. Expected 1""
This is my code.

I would be grateful if someone could point me in the right direction here. Thanks

CODE :

Dim SourceFile, DestinationFile
Dim rstWzip As DAO.Recordset
Dim DrgNo As Variant
Dim DrgPath As Variant
Dim DrgDest
Dim qry As DAO.QueryDef

DrgDest = "C:\Test Issue\"

Set qry = CurrentDb.QueryDefs("WinZip Query")
Set rstWzip = qry.OpenRecordset
If Not (rstWzip.RecordCount) Then
Do
rstWzip.MoveFirst
DrgNo = rstWzip!DigitalFile
DrgPath = rstWzip!Path
SourceFile = DrgPath + DrgNo ' Define source file name.
DestinationFile = DrgDest + DrgNo ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.
Loop
End If


Exit_Command178_Click:
Exit Sub

 

[hkaing79]

Try replacing "Set qry..." with "Set rstWzip..." and comment out "Set rstWzip..."

You do realize, by including MoveFirst in your loop, you're going to be caught in an infinite loop. You also don't have a MoveNext command anywhere.

I use the following code to traverse through records:

Dim rs AS DAO.Recordset

Set rs = CurrentDB.OpenRecordset("QueryName") 'or use SQL

If rs.EOF Then
  msgbox("No records selected")
Else
  rs.MoveFirst
  While Not rs.EOF
    [code]
    rs.MoveNext
  WEND
End If

rs.Close

 

[PHV]

What is the SQL code of "WinZip Query" ?
You may have to play with the Parameters collection.

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

 

[CMPaccess]

Firstly, thanks again.

I tried changing the loop as suggested but as I suspected got the same error message.

I had a feeling it was connected to the query as PHV suggested.

This is the code for the query :

SELECT [DWG Issues].DigitalFile, Drawings.Path
FROM Drawings INNER JOIN [DWG Issues] ON Drawings.DrawingID = [DWG Issues].DrawingID
WHERE ((([DWG Issues].IssueID)=[forms]![Issue]![IssueID]));

I tried actually adding parameters to the query. Not really sure what I was trying to be honest. Howver I got the same error but the number had changed.

"too few parmeters expected 3." etc.

What parameters do I need for this to work. ??

 

[PHV]

The Issue form must be opened when the query is launched.

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

 

[PHV]

The Issue form must be opened when the the query is launched.

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

 

[peterl999]

You can do code like this so the form doesn't have to be open:

Dim qd1 As QueryDef

Set qd1 = CurrentDb().QueryDefs("q Query") 'Your query
qd1.Parameters("[Forms]![f formName]![txtBoxName]") = "stuff"
Set rs = qd1.OpenRecordset(dbOpenDynaset)

Peter

 

[CMPaccess]

I apologise for the delay holidays and that.

The form is open when the command is run. That is what confused my.

If I do a run query command button whilst the form is open it works fine.

This is my code now. Now I get an object required.

I think I'm biting of more than I can chew with this and would appreciate the help in putting the code right.

thanks


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.value " & _
"AND [DWG Issues].IssueID = forms!Issue.IssueID.value ; "

'Set qry = CurrentDb.QueryDefs("WinZip Query")
strSQL.Parameters("[Forms]![Issue]![DrgLstDiscip]") = "[DWG Issue].Discip"
strSQL.Parameters("[Forms]![Issue]![IssueID]") = "[DWG Issue].IssueID"
Set rstWzip = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly, dbReadOnly)
'If Not (rstWzip.RecordCount) Then
'Do
'rstWzip.MoveFirst
If rstWzip.EOF Then
MsgBox ("no records selected")
While Not rstWzip.EOF
DrgNo(0) = rstWzip![DigitalFile]
DrgPath(0) = rstWzip![Path]
SourceFile = DrgPath + DrgNo & ".dwg" ' Define source file name.
DestinationFile = DrgDest + SourceFile ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.
'If FileCopy = Err Then
'SourceFile = DrgPath + DrgNo & ".dgn"
'FileCopy SourceFile, DestinationFile
'End If
rstWzip.MoveNext
Wend
End If

rstWzip.Close