Re: [MS_AccessPros] Deriving a file name from a list box item

 

Its a unique identifier for a document record. I get what you are saying. But I am trying to phase the field out anyway. Its an old legacy field from the original table. 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 7, 2018 12:35 PM
Subject: Re: [MS_AccessPros] Deriving a file name from a list box item

 

Art,

No comment about the single or double digits?

12923 could be:

gintCabinetID 12

gintFolderID  9

gintSubFolderID  23

or

gintCabinetID 1

gintFolderID  29

gintSubFolderID  23

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 7, 2018 11:48 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Deriving a file name from a list box item

 

The :&" worked out great. Thank you. 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 7, 2018 11:32 AM
Subject: Re: [MS_AccessPros] Deriving a file name from a list box item

 

Art,

I expect you want to concatenate the values together which uses the "&" rather than "+". The only time I use + with strings is if I want to propagate a Null value for names or addresses or similar.

I can't imagine this value would make sense if you have both single and double digit numbers. Are any of the integers greater than 9 or are they all in a range of 100 to 999?

Regards,

Duane Hookom

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 7, 2018 11:23 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Deriving a file name from a list box item

 

That worked great thank you. Can you tell me why I am getting a Type Mismatch on this line:

rs!DocumentKey = gintCabinetID + gintFolderID + gintSubFolderID + Format(Now, "yyyy-mm-dd hh-mm") + strCurrentUserName

DocumentKey is a string

gintCabinetID is integer

gintFolderID  is integer

gintSubFolderID  is integer

strCurrentUserName is string

I think it has to do with it trying to add my three gint fields together.

Any ideas?

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, March 6, 2018 3:28 PM
Subject: Re: [MS_AccessPros] Deriving a file name from a list box item

 

Art, 

You should be able to change your code as noted:

 If IsNull(DLookup("DocumentLocation", "tblDocuments", "DocumentLocation = '" & strDocLoc & "'")) Then

    rs.AddNew

    rs!DocumentNAme = Mid(strDocLoc, InstrRev(strDocLoc, "\")+1)

    rs!DocumentLocation = strDocLoc

Notice I have used the strDocLoc rather than continually grabbing from the FileList control.

Duane Hookom

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, March 6, 2018 2:44 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Deriving a file name from a list box item

 

I have a listbox control that is set up as a Value List and is populated by the following code in a command button called cmdGetFiles:

Private Sub cmdGetFiles_Click()

Dim fDialog1 As Office.FileDialog

   Dim varFile1 As Variant

 

   ' Clear listbox contents. '

   Me.FileList.RowSource = ""

 

   ' Set up the File Dialog. '

   Set fDialog1 = Application.FileDialog(msoFileDialogFilePicker)

 

   With fDialog1

 

      ' Allow user to make multiple selections in dialog box '

      .AllowMultiSelect = True

 

      ' Set the title of the dialog box. '

      .Title = "Please select one or more files"

 

      ' Clear out the current filters, and add our own.'

      .Filters.Clear

      .Filters.Add "PDF Files", "*.pdf"

      .Filters.Add "Word Documents", "*.Doc*"

      .Filters.Add "All Files", "*.*"

 

      ' Show the dialog box. If the .Show method returns True, the '

      ' user picked at least one file. If the .Show method returns '

      ' False, the user clicked Cancel. '

      If .Show = True Then

 

         'Loop through each file selected and add it to our list box. '

         For Each varFile1 In .SelectedItems

            Me.FileList.AddItem varFile1

         Next

 

      Else

         MsgBox "You clicked Cancel in the file dialog box."

      End If

   End With

End Sub

This populated the FileList list box. I now have a command button called cmdImport:

Private Sub cmdImport_Click()

Dim db As Database

 Dim rs As Recordset

 Dim iRecCount As Integer

 Dim strDocLoc As String

 Dim intI As Integer

 

 Set db = CurrentDb()

 Set rs = db.OpenRecordset("tblDocuments")

 

 

 iRecCount = Me.FileList.ListCount

 For intI = 0 To Me.FileList.ListCount - 1

  strDocLoc = Me.FileList.ItemData(intI)

  Debug.Print strDocLoc

 If IsNull(DLookup("DocumentLocation", "tblDocuments", "DocumentLocation = '" & FileList.ItemData(intI) & "'")) Then

 rs.AddNew

 rs!DocumentNAme =

  rs!DocumentLocation = FileList.ItemData(intI)

 rs!DocumentKey = gUserID + gintCabinetName + gintFolderID + gintSubFolderID + Format(Now, "yyyy-mm-dd hh-mm")

 rs!CreatedDate = Now()

 rs!CreatedByUserID = glngThisEmpID

 'rs!DOCUSER_ID = glngThisEmpID

 rs!DocumentFileType = FileExtensionFromPath(FileList.ItemData(intI))

 'rs!ACTIVE = -1

 rs.Update

 End If

 Next intI

MsgBox "You have imported " & CStr(iRecCount) & " records.", vbOKCancel

  rs.Close

  Set rs = Nothing

  Set db = Nothing

 Me!sfrmDocumentList.Form.Requery

Me.FileList.RowSource = ""

End Sub

I need to grab just the file name from the listbox and pass it into my addnew.

rs!DocumentName = 

The items in the list box contain the path as well as the file name and all i need to is the filename and extension to pass into the database,

This is a sample of what is in the listbox.

 C:\Work\MSP-in-10-weeks.pdf

C:\Work\Helpdesk Database Structure 270.pdf

C:\Work\HMIS-Data-Dictionary.pdf

 

Thank you 

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: Art Lorenzini <dbalorenzini@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___