search table for location

[steve111]

hi,

I use this bit code to look at a table for the path for pdf file to go into

varFolder = DLookup("Folderpath", "pdfFolder")

this looks at the table pdffolder

what I would like to do if possible is for this to look at the first line
in the table but I want to use this part of the code for 4 or 5 different paths
Is it possible to modify the code so when I use it 5 times each code looks at different lines in the table

steve

 

[MarkK]

DLookup() takes three parameters . . .

DLookup(<field>, <table>, <criteria>)

. . . so when I use DLookup(), it usually looks something like . . .

dim var
var = DLookup("Field1", "MyTable", "IDField = " & Me.SomeIDToFind)
if not isnull(var) then
[COLOR="Green"]   'do something with var[/COLOR]
end

 

[steve111]

hi

I am a little confused can you please explain a little more

varfolder = DLookup("folderpath", "pdffolder", "ID = 2")

I have put an id field in the table and I want it to look at id 2 which is a folder called purchasing where the first line is sales in the table

thanks
steve

 

[pr2-eugin]

So you say you have a table (pdfFolder) like,

ID |    folderPath
---+------------
1  |    C:\Temp\
2  |    D:\Test\
3  |    E:\Sales\

With your DLookup, the value in varFolder should be "D:\Temp\".

If the answer is 'Yes', then what is your problem? If the answer is 'No', what is your problem?

 

[steve111]

hi

the table is pdffolder in that table I have this

c:\users\user\documents\sales id number 1
c:\users\user\documents\purchasing id number 2

when the macro runs I would like it to see which one of the above is to be used by means of the " ID " NUMBER if that is possible . this then will put my pdf repprt in that folder
but don't know how to tell it to look at the id number

steve

 

[pr2-eugin]

As MarkK, has suggested mimic the format. where the category look up the ID from the form.

 

[steve111]

hi

I have really no idea how to do it this is the code for the whole process could you please look at it and alter it to suit

Private Sub cmdPDF_Click()
On Error GoTo Err_Handler

Const FOLDER_EXISTS = 75
Const MESSAGE_TEXT1 = "No current invoice."
Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
Dim strFullPath As String
Dim varFolder As Variant

If Not IsNull(Me.[P/O Number]) Then
' build path to save PDF file
varFolder = DLookup("Folderpath", "pdfFolder")



If IsNull(varFolder) Then
MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
Else
' create folder if does not exist
varFolder = varFolder & "\" & Me.Supplier
MkDir varFolder
'strFullPath = varFolder & "\" & ME.CustomerName & " " & Me.Invoicenumber & ".pdf"
strFullPath = varFolder & "\" & "Purchase order " & " " & Me.[P/O Number] & ".pdf"
' ensure current record is saved before creating PDF file
Me.Dirty = False
DoCmd.OutputTo acOutputReport, "order", acFormatPDF, strFullPath, True
End If
Else
MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
End If
Exit_Here:
Exit Sub

Err_Handler:
Select Case Err.Number
Case FOLDER_EXISTS
Resume Next
Case Else
MsgBox Err.Description
Resume Exit_Here
End Select
End Sub

thanks
steve

 

[pr2-eugin]

What identifies the ID? PO Number to me sounds like the Order number and not the ID that dictates which location the file has to be saved to. Explain in simple English what you want to do.

 

[steve111]

yes the purchase order number is the order number


that code will go into 3 forms but be modified slightly for each area

1. invoice details

2 order acknowledgement
3 purchase orders.



the code above will put the reports into each persons directory ( sales, purchase orders, etc using the customer name as the folder the reports go Into.
If they want to change the directory I don't want them to change code. just the table the directory is in

the code above code gets the supplier name creates the folder and
saves the report "invoice number 1" then the next one " invoice number 2" in that folder if it is the same supplier if not it will create another folder and put invoice 2 in that new folder

this will also happen with the invoice and the acknowledgement reports
therefore I want each code to direct the new folder /report into the 3 areas listed in the table pdffolder

so it it possible for the code to look at the id number in the pdffolder and then
use that directory to send the reports to
I could just put the directory straight into the code but is someone want to change the directory the code would need to be changed instead of the table

hope this is better

thanks
steve