Help with API/VBA Coding

[Dannyc989]

Hey Folks,

I am a novice when it comes to VBA/API, and need some help and am now up against my deadline for showing this database working and I cant get this last part working.

I need to return a folders directory to a text box on my forms record called Files_Directory when i click the Browse command button... The folder will have more folders within it along with documents all relivant to the folder selected, hense the need for just the folder directory rather than a file.

Could anyone please supply me with some working code or module code and just tell me where to put what i have been hunting the net and will carry on looking but if anyone can help me out in a big way i would be very greatful.

Many Thanks

Daniel

 

[Rx_]

Try to break the run-on sentence into several short steps.
Want to make sure we clearly understand the objective.

Don't worry about being asked to be more clear with details on the question.
Anyone else here will tell you that I Resemble This Too!

 

[Dannyc989]

Ok here goes...

I have a form which has some sales data in it...

For each record (quote number unique ref) there is a folder on a hdd partition, which contains other folders and sales paperwork (costsheet.xls and quote.doc) related to this particular quote.

On the form there is a text box linked to the table called Files Directory and a command button called browse next to that.

When a sales person creates a quote they need to put the folder location(folder directory) into the text field, I wanted them to do this by clicking the browse command, finding the correct folder and then saving that path, so as when anyone from projects visits it on a different search form they can click the open command and it will take them directly to the relevant folder.

Any other questions please ask

 

[BigHappyDaddy]

This is a function I use for similar requirements.

It requires Microsoft Office XX.X Object Library reference.

Public Function GetFolder(Optional ByVal strPath As String = "") As String
   Dim fldr As FileDialog
   Dim sItem As String
 
   Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
   With fldr
      .Title = "Select a Folder"
      .AllowMultiSelect = False
      .InitialFileName = strPath
      If .Show <> -1 Then GoTo NextCode
      sItem = .SelectedItems(1)
   End With
 
NextCode:
   GetFolder = sItem
   Set fldr = Nothing
 
End Function

 

[Dannyc989]

Does that go as a module or vba code against a command click

 

[BigHappyDaddy]

As it stands, it would probably be better to add to module and call the function in a click event of a button, then assign the value to your textbox.

 

[Dannyc989]

Never done anything like that can you possibly give me a step by step, sorry to push my luck but this has fried my brain...

 

[BigHappyDaddy]

So copy the above code into a module. Then in the "Browse" button click event add this:

me.yourTextBoxName = GetFolder()

 

[Dannyc989]

Thank you it worked a treat once I got time to sit down and work on the db....
I do now however have another request from management and wondered if it is even possible....

In broken down terms...

The quote has been placed on the db and that's all fine and working, an order has been received and the details of the p/o input and this record has now been appended to a different table called projects...

Projects has its own individual project number which is an auto number...

The quote files are still in the quote folder which is the path that was selected and saved at the time of input (the previous issue in this thread)

What needs to happen if at all possible:
The folder path as it stands currently (I.e. Q:\ .... ) needs to be copied and pasted in to the projects folder on drive (I.e. P:\ ), but I need to rename this folder with details from the projects form so it states unique references (I.e. The unique projects number followed by the clients name) and also the directory path needs to be changed on the form ...

Is this possible??

Many thanks in advance...

 

[BigHappyDaddy]

Yes, research "FileCopy" on this site or on Google. That should enough to get you going.

 

[jdraw]

The folder path as it stands currently (I.e. Q:\ .... ) needs to be copied and pasted in to the projects folder on drive (I.e. P:\ ), but I need to rename this folder with details from the projects form so it states unique references (I.e. The unique projects number followed by the clients name) and also the directory path needs to be changed on the form ...

Tell us more about your table(s) structure -- fields, datatype and even some sample records.

This info may be useful, but I'd like to know more about your database structure.

 

[Dannyc989]

I'm sorry guys, hold fire on my requests I may have to change how it's all works, I forgot about the cancelled orders and the yearly sales reports etc... I will have a rejigger and see how I'm left and come back to you guys in a couple of days

 

[jdraw]

Good luck Danny,

If you need some help with modifying the database structure, here is a tutorial to lead your through database and table design.