Page 1 of 4 123 ... LastLast
Results 1 to 15 of 58

Thread: Combo Box Code

  1. #1
    Join Date
    Aug 2012
    Posts
    49

    Unanswered: Combo Box Code

    Hi All,
    I'm having a little trouble with a form i'm working on. I've got a combo box from which you can make a selection from 10 choices, and beside the combo box is a logo which will change depending on the selection you make from the combo box. This works fine, but i've also got a text box on the form which i use as a search tool....so when i enter a record number in the search box and press enter, the record changes but the logo doesn't ?. My combo box is called Combo 309 and the image holder for the logo is called Image5. The code below is what i have in the OnLoad event of the Form and the AfterUpdate event of the combo box


    Code:
    Private Sub Form_Load()
    
        Me.Image5.Picture = Nz(Me.Combo309.Column(1), "S:\Documents\RA Database\Images\RiskDefault.jpg")
    
    End Sub


    Code:
    Private Sub Combo309_AfterUpdate()
    
        Me.Image5.Picture = Me.Combo309.Column(1)
        
    End Sub
    Any help would be greatly appreciated and save me a lot of work,
    Thanks guys

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the picture of Image5 can only be changed through the Combo309_AfterUpdate and Form_Load procedures, and if a textbox is used to programatically change the row selected in Combo309, then you need to call Combo309_AfterUpdate explicitly in your code after changing the current row of Combo309. Changing values through VBA code does not trigger the AfterUpdate event of a control the value of which is changed.

    If the textbox is used to programatically change the current record of the Form, then you need to create a Form_Current event and place the code needed to change the picture of Image5 into it.
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    49
    Ah Sinndho you're a genius....worked first time !. Thank you.

    Don't want to push my luck but there's one other thing ....if i wanted to print out that form as a report, would it be difficult to get the logo to display on the report ?....even though the images are linked rather than saved in the table.

    Thanks again for your help

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Jens46 View Post
    ....if i wanted to print out that form as a report, would it be difficult to get the logo to display on the report ?....even though the images are linked rather than saved in the table.
    Not at all: the process would remain the same. As an example, I created a report with an Image control Image3. The Report is based on a query, like this:
    Code:
    SELECT Customers.ID, Customers.Name, Customers.Recommended_by
    FROM Customers;
    In the module of the report, I have:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Report_Activate()
    '
    ' This is necessary because the Report_Page() procedure will not be called when the report opens
    ' while we need to retrieve the name of the picture for the first page.
    '
    ' We cannot use the Form_Open event because the value of Me.ID is unknown at that time.
    '
        Me.Image3.Picture = GetPictureName(Me.ID)
    
    End Sub
    
    Private Sub Report_Page()
    '
    ' Retrieve the name of the picture for each page.
    '
        Me.Image3.Picture = GetPictureName(Me.ID)
        
    End Sub
    GetPictureName is the name of a public function (i.e. a function that resides in a general module, not the module of a Form or of a Report, and not a Class module), like this:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function GetPictureName(ByVal RowID As Long) As String
    
        Const c_PicturePath As String = "C:\Documents and Settings\SinnDHo\My  documents\My Pictures\"
    
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("Tbl_Pictures", dbOpenSnapshot)
        With rst
            .FindFirst "RowID=" & RowID
            If .NoMatch = True Then
                GetPictureName = "Default.jpg"
            Else
                GetPictureName = !PictureName
            End If
            .Close
        End With
        Set rst = Nothing
        GetPictureName = c_PicturePath & GetPictureName
        
    End Function
    Quote Originally Posted by Jens46 View Post
    Thanks again for your help
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Aug 2012
    Posts
    49
    Hi Sinndho....thanks for the reply. I have to admit that i haven't a clue what it is i'm supposed to do....my knowledge of code is pretty poor and what you've posted is a mile over my thick head !

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Jens46 View Post
    Hi Sinndho....thanks for the reply. I have to admit that i haven't a clue what it is i'm supposed to do....my knowledge of code is pretty poor and what you've posted is a mile over my thick head !
    if you are struggling then now is a good time to start going through each line of code, press the F1 (help Key) for each verb/function/property/method you don't understand. short of going on a VBA course its the only way you are going to learn.

    yes it would help if contributors posting code commented their offerings but then those offerings are made voluntarily and without charge so we need to respect the decisions made by contributors. despite everyone saying commenting code is a good idea it doesn't always get done
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2012
    Posts
    49
    Would it work if i created a look-up table with the combo box selections in Field1 and the relative image paths in Field2. I could then add Field2 to the query on which the report is based and hopefully display the relevant image on the report.....or am i way off ?

  8. #8
    Join Date
    Aug 2012
    Posts
    49
    Quote Originally Posted by Jens46 View Post
    Would it work if i created a look-up table with the combo box selections in Field1 and the relative image paths in Field2. I could then add Field2 to the query on which the report is based and hopefully display the relevant image on the report.....or am i way off ?
    Ok that was a total disaster....error messages all over the place.


    healdem....I know that's the way to go but it's not going to help me here. I've bought a book to work on but am only on the second chapter at the moment.

    Sinndho.....What is Tbl_Pictures ?. Do i need to set up another table to make the code work ? Where does it go in the report ?
    At the moment i've got the path to the pictures in Field2 of a table, where Field1 is the source for my combo box

    Thanks

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    at a guess tbl_pictures is a table that holds the path(URL) to pictures

    the problem you are hitting is that you don't know, or haven't taken the time to work out what the code is doing. if you are going to use someone else's code then you need to understand it. a good way of understanding it is to go through it line by line commenting what it does.

    Ok that was a total disaster....error messages all over the place.
    but you don't supply the error messages so its tricky, read impossible, for anyone else to diagnose what is going wrong

    a book is fione, bbut therei s a perfectly good help system (OK its not brilliant) but it will answer most of your questiomns and it usually has code attached to show you what to do. place the cursor over a word you don't know that is part of VBA NOT your variable names or table/column names. and press F1 puit the cursor over a function and press f1 it will tell you what that function is, how its called and so on
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Aug 2012
    Posts
    49
    Quote Originally Posted by healdem View Post
    the problem you are hitting is that you don't know, or haven't taken the time to work out what the code is doing. if you are going to use someone else's code then you need to understand it.
    First of all i havetaken the time to work out what the code is doing but haven't been able to as i'm not trained in VBA. I've tried pressing the F1 key but it doesn't explain in a way a beginner can understand. Also, the reason i'm trying to use someone else's code is because i can't write code, and therefore must use it even though i don't fully understand it.


    Quote Originally Posted by healdem View Post
    bbut therei s a perfectly good help system (OK its not brilliant) but it will answer most of your questions
    You're joking right ?. It answers my questions using more VBA which i don't understand

    I'm thinking i might just add a field to the master table and just save the image for each record....probably save me a lot of grief even though the database might get quite big eventually.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    move the cursor over a function name
    if you are using A2007/2010 you may need to select all of the function text
    then press the F1 key
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Aug 2012
    Posts
    49
    Ok i've tried to work it out and here's what i've come up with (probably incorrect). GetPictureName is a module into which ive placed the following code

    Code:
    Option Compare Database
    Option Explicit
    
    Public Function GetPictureName(ByVal RowID As Long) As String
    
        Const c_PicturePath As String = "S:\RA Database\Images"
    
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("Tbl_AssessmentTypes", dbOpenSnapshot)
        With rst
            .FindFirst "RowID=" & RowID
            If .NoMatch = True Then
                GetPictureName = "RiskDefault.jpg"
            Else
                GetPictureName = !PictureName
            End If
            .Close
        End With
        Set rst = Nothing
        GetPictureName = c_PicturePath & GetPictureName
        
    End Function
    I've also placed the following code into the OnActivate and OnPage events of the report itself as per Sinndhos instructions

    Code:
    Private Sub Report_Activate()
    
        Me.Image3.Picture = GetPictureName(Me.AssessmentID)
    
    End Sub
    
    
    Private Sub Report_Page()
    
    
        Me.Image3.Picture = GetPictureName(Me.AssessmentID)
        
    
    End Sub
    When i run it i'm getting the following error with the word GetPictureName highlighted in yellow (in the OnActivate event)

    Compile Error:
    Expected variable or procedure, not module

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Jens46 View Post
    Sinndho.....What is Tbl_Pictures ?. Do i need to set up another table to make the code work ? Where does it go in the report ?
    At the moment i've got the path to the pictures in Field2 of a table, where Field1 is the source for my combo box
    Thanks
    In my example, Tbl_Pictures is a table in which the names of the picture files are stored. The function GetPictureName uses this table to return the name of a picture file according to the numeric RowID it receives as argument. Its structure is very simple:
    - RowID, Number (Long Integer)
    - PictureName, Text (255)

    In my example, the function provides the path to the file (it is defined in the constant c_PicturePath:
    Code:
        Const c_PicturePath As String = "C:\Documents and Settings\rf\Mes documents\My Pictures\"
    But you could store the full path into the table and modify the function accordingly.
    Have a nice day!

  14. #14
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Three things come to mind:
    • Change the name of the module to something other than the name of the function
    • Add a trailing "\" to your constant path name
    • Do you have an image file called "RiskDefault.jpg" in your image directory?

    The first point should deal with the error message. The second will prevent an error when the function is successfully called. The third is a question to eliminate an error of the type that I've made in the past!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I agree with weejas: rename the module in (for instance) Mod_GetPictureName.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •