Auto filling a box based on combobox selection 1

[Chummly66]

hey Guys,

I've run into a stumper and was hoping someone could smack me in the back of the ehad and get me going.

I have a table, called "tblStores" and in it, there are two fields; "strStoreID" and "strStoreLocation". strStoreId is set to primary key since there will never be a duplicate store ID. There is only one store ID and only one location per store ID.

On my form, I have a combobox, "cboStoreID" where I select what store ID I need. Next to it, I have a list box, titled "txtStoreLocation". This txt box never gets focus, nor is ever typed in or anything. Its sole purpose, is to automatically fill in the store location that is a match to the combobox's store ID, based on the table "tblStores" data.

No matter what I do, I cannot get the list box to display the store location.

The Row Source code for "cboStoreID":
SELECT tblStores.strStoreID
FROM tblStores
ORDER BY tblStores.strStoreID;

The After_Update event for this has:
Private Sub cboStoreID_AfterUpdate()
txtStoreLocation.Requery
End Sub


The Row Source code for "txtStoreLocation" listbox:
SELECT tblStores.strStoreID, tblStores.strStoreLocation
FROM tblStores
WHERE (((tblStores.strStoreLocation)=[Forms]![frmDataEntry]![cboStoreID]))
ORDER BY tblStores.strStoreID;

Is there something I am missing? Would I be better off using a text box instead of a list box?


Thanks guys!!

 

[PHV]

Private Sub cboStoreID_AfterUpdate()
txtStoreLocation.RowSource = "SELECT strStoreID,strStoreLocation FROM tblStores WHERE strStoreID=[tt]'"[/tt] & Me!cboStoreID & [tt]"'"[/tt]
End Sub

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

 

[Chummly66]

PVH,

You the man! Worked like a charm. By the way, I would like to thank you and everyone else here for the awesome help that I've received from this forum. You guys are the best and thank you very very much for everything!!!

Richard

 

[clickaccess]

Slightly different need.

I want to access files in a windows directory based on a the selection in the combo box.

Thanks in advance

Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[MajP]

Clickaccess,
Need a little more information. When you click on the combobox what does it return? What information about a file is stored.
My guess is that your combobox lists files names or descriptions, and in that same table there are paths? Are these relative paths or absolute?
Once I get the path what do you want to do? What kind of files are they. Do you want an application to open it? Do you want to input/output to a filestream? Do you want to show and image?

 

[clickaccess]

MajP, Thanks for responding.

I have a table with the account names. That table is linked to the combo box. In a particular directory there are folders named exactly as the account names and those folders contain excel files. These files will than be loaded into the current database for manupulation and report generation.

Directories will be absolute paths G:\reports\groups\walmart...

Thanks
Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[MajP]

Here is an example of a Class file that I built so that I could load pictures from a directory. The rule is that the user must have the picture folder in the same folder that the application .mdb resides or in the folder where a linked .mdb resides. The picture is of a piece of equipment, and the must be named the same as the auto number of the piece of equipment.
Take a look at this and simplify it. A lot of what I am doing is searching and building the path (for flexibility and portability of the application). Yours is easier since you are using an absolute path.
However, I can reuse this class. All I do is instantiate the class, and set the textbox that contains the ID, an image control, and the subfolder name. Then when a form moves through a record the picture shows without any other code.
Take a look at this example, but I am sure if you search you can find nearly exact code for what you are doing. Go to the advanced search on this site, and try "importing files". I think I even saw an example on this site. This is pretty common desire for importing into Access.

------ My pictureFromFile class-------------------
'This class works when the folder with pictures is located in the same folder as the linked tables
'or the current database
Option Compare Database
Option Explicit

Private mPictureNameControl As TextBox
Private mImagePath As String
Private mImageControl As Image
Private mPictureDirectoryName As String
Private mLinkedTableName As String
Private mBlnLinked As Boolean
Private WithEvents mForm As Access.Form
Private WithEvents mReport As Access.Report

Public Property Set PictureNameControl(thePictureNameControl As TextBox)
Set mPictureNameControl = thePictureNameControl
End Property

Public Property Set PictureControl(theControl As Image)
Set mImageControl = theControl
End Property

Public Property Set PictureForm(theForm As Access.Form)
On Error GoTo HandleError
Set mForm = theForm
mForm.OnCurrent = "[Event Procedure]"
Exit Property
HandleError:
MsgBox Err.Number & " " & Err.Description

End Property

Public Property Set PictureReport(theReport As Access.Report)
Set mReport = theReport
mReport.OnPage = "[Event Procedure]"
mReport.OnActivate = "[Event Procedure]"
End Property

Private Sub subLoadImage()
Dim strDBPathAndFile As String
Dim strImagePath As String
Dim strImagePathAndFile As String
Dim intSlashLoc As Integer
Dim intLastSlashLoc As Integer
Dim objFileSystem As Object
Dim connection As String
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
' On Error GoTo PictureNotAvailable
'Obtain the full path of the current database or the linked database
If mBlnLinked Then
connection = CurrentDb.TableDefs(mLinkedTableName).Connect
strDBPathAndFile = Mid(connection, 11)
Else
strDBPathAndFile = Application.DBEngine(0).Databases(0).Properties("Name")
End If
intSlashLoc = 1
Do
intLastSlashLoc = intSlashLoc
intSlashLoc = InStr(intSlashLoc + 1, strDBPathAndFile, "\")
Loop Until intSlashLoc = 0
'Trim off the database name, leaving the path
'and append the name of the image file
strImagePath = Left(strDBPathAndFile, intLastSlashLoc) & mPictureDirectoryName & "\"
strImagePathAndFile = strImagePath & mPictureNameControl & ".BMP"
If objFileSystem.FileExists(strImagePathAndFile) Then
mImageControl.Picture = strImagePathAndFile
mImagePath = strImagePathAndFile
Else
'If .gif exists Set ImageFrame to the path of the image file
strImagePathAndFile = strImagePath & mPictureNameControl & ".GIF"
If objFileSystem.FileExists(strImagePathAndFile) Then
mImageControl.Picture = strImagePathAndFile
mImagePath = strImagePathAndFile
Else
'If BMP exists Set ImageFrame to the path of the image file
strImagePathAndFile = strImagePath & mPictureNameControl & ".JPG"
If objFileSystem.FileExists(strImagePathAndFile) Then
mImageControl.Picture = strImagePathAndFile
mImagePath = strImagePathAndFile
Else
mImageControl.Picture = ""
End If
End If
End If
Exit Sub
PictureNotAvailable:
MsgBox Err.Number & " " & Err.Description & Chr(13) & "In PictureFromFile Class"
End Sub

Private Sub mForm_Current()
Call subLoadImage
End Sub

Private Sub mReport_Activate()
Call subLoadImage
End Sub

Private Sub mReport_Page()
Call subLoadImage
End Sub
Public Property Get ImagePath() As String
ImagePath = mImagePath
End Property
Public Property Get PictureDirectoryName() As String
PictureDirectoryName = mPictureDirectoryName
End Property

Public Property Let PictureDirectoryName(ByVal theDirectoryName As String)
mPictureDirectoryName = theDirectoryName
End Property

Public Property Get LinkedTableName() As String
LinkedTableName = mLinkedTableName
End Property

Public Property Let LinkedTableName(ByVal theLinkedTableName As String)
mLinkedTableName = theLinkedTableName
End Property

Public Property Get IsLinked() As Boolean
IsLinked = mBlnLinked
End Property
Public Property Let IsLinked(ByVal blnIsLinked As Boolean)
mBlnLinked = blnIsLinked
End Property

----------------------end code -------------------

This is how you would use it from a form

Private Sub Form_Open(Cancel As Integer)
'instantiate
Set objPictureFromFileEdit = New PictureFromFile
'set the Form
Set objPictureFromFileEdit.PictureForm = Me.Form
'Set the image control
Set objPictureFromFileEdit.PictureControl =
Me.imgCntlEquipment
'Set the textbox that contains the system ID which is 'the same as the picture name in the picture folder
Set objPictureFromFileEdit.PictureNameControl =
Me.autoIDSystem
'pass the folder name
objPictureFromFileEdit.PictureDirectoryName =
"MERSPictures"
End Sub

All the form and report events are in the class. See how I sink the events in the class. In your case you would set the equivalent "PictureNameControl" to your combo box, but add the "with events". Then you could sink the "change" event of the combobox:

Private WithEvents mComboBox As control

'Sink the event in the class
Private Sub mComboBox_Change()
Call subLoadImage (in your case call subLoadExcel)
End Sub

Now just add code to my "subLoadImage" (your subLoadExcel) to import Excel instead of load a file.

 

[clickaccess]

Thanks, I will study this one and get back to you. It may be a while.

Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[MajP]

If I get time, I will simplify it and do the excel part for you. I just do not have an example on me.

 

[MajP]

Simplified for excel using a combobox with the file name in it.

---------------Class module------------------------

Option Compare Database
Option Explicit

Private WithEvents mFileNameControl As Access.ComboBox
Private mFileName
Private mFilePath As String
Private mForm As Access.Form

Public Property Set FileNameControl(theFileNameControl As Control)
  Set mFileNameControl = theFileNameControl
  mFileNameControl.OnChange = "[Event Procedure]"
 End Property
Public Property Let FilePath(ByVal theFilePath As String)
  mFilePath = theFilePath
End Property
Public Property Get FilePath() As String
  FilePath = mFilePath
End Property
Private Sub subLoadFile()
   Dim objFileSystem As Object
   Dim strPathAndFile As String
   Dim xlObject As Object
   Set objFileSystem = CreateObject("Scripting.FileSystemObject")
   strPathAndFile = mFilePath & mFileName
   If objFileSystem.FileExists(strPathAndFile) Then
    MsgBox "loading " & strPathAndFile
     'Put your import code here.  This is only an example
     DoCmd.TransferSpreadsheet acImport, , "tblImport", strPathAndFile, True, "A1:B10"
   Else
      MsgBox strPathAndFile & " Could not be Found."
   End If
   Exit Sub
FileNotAvailable:
   MsgBox Err.Number & "  " & Err.Description & Chr(13) & "In XcelImporter Class"
End Sub

Private Sub mFileNameControl_Change()
    mFileName = mFileNameControl.Text
    MsgBox mFileName
    Call subLoadFile
End Sub

----------------------- end class --------------------

How to call it from your form:

Private Sub Form_Load()
   Set xliFile = New XcelImporter
   xliFile.FilePath = "C:\Documents and Settings\"
   Set xliFile.FileNameControl = Me.Combo0
End Sub

It will automatically react (sink) to a change on your combo box.