Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Unanswered: MS Access 03 Combo Box issue

    Hi All

    I'm having some problems programming a combo box, which I'm using to find and display records.

    Quick bit of background. I've got a form set up in a tabbed format. The main screen displays the set information (the more generic bits) and the second tab displays the information for each weight within the set. I'm working on setting up navigation controls for the user and want the user to be able to be able to search the records to select a weight record. The simplest method seemed to be to use a combo box (particularly since users may not know which weights are in the set).

    Using the wizard I can get the combo box to work, where it displays all available records (in this case a list of all weights in the system) and navigates to the selected record correctly. However I would like to add a criteria to the select query behind the combo box to limit the results to showing weights within the set being viewed by the user. I've opened up the query designer (via the properties window for the combo box - on row source) and added the criteria Forms!frmSet.subfrmWeightA.SetID, which in theory should pick up what Set ID the user is viewing. However, when using the combo box the first time this treats my criteria code as a parameter and opens a prompt box. I can't work out why.

    The second issue I'm finding is that once you have triggered the select query, unless you close and re-open the form it doesn't re-query when viewing a different set. I've tried using VBA to trigger a requery but haven't been able to work it out.

    Any ideas on how to resolve this (presuming what I want to do is possible) would be greatly appreciated.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    1. make sure the combo control IS called SetID.
    2. if Forms!frmSet.subfrmWeightA.SetID is a sub form, the path is
    Forms!frmSet.Form!subfrmWeightA.SetID.
    You have to add the FORM to show the sub form.

    Insead of triggering a query, try a filter. They can be turned on and off while viewing.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by SamJP View Post
    However, when using the combo box the first time this treats my criteria code as a parameter and opens a prompt box. I can't work out why.
    1. It's very likely that Forms!frmSet.subfrmWeightA.SetID is not available when the query is run the first time, this because the Form is not totally ready at that time (i.e. the query is run before the Form_Current event occurs for the first time). This is a problem when a query is used as the recordsource property of a form while using a reference to the same form.

    A solution would be to let the RecordSource property of the Form blank (i.e. use a -temporarily- unbound form), then assign the query to the same recordsource property when the Form_Current event occurst for the first time:
    Code:
    Private Sub Form_Current()
    
        If Me.RecordSource = "" Then Me.RecordSource = "QueryName"
        
    End Sub
    2.
    Quote Originally Posted by SamJP View Post
    The second issue I'm finding is that once you have triggered the select query, unless you close and re-open the form it doesn't re-query when viewing a different set. I've tried using VBA to trigger a requery but haven't been able to work it out.
    You can use the same technique as above:
    Code:
    Private Sub ComboName_AfterUpdate()
    
        Me.RecordSource = "QueryName"
    
    End Sub
    @ranman256: Where does the subform come from?
    Have a nice day!

  4. #4
    Join Date
    Jun 2013
    Posts
    81

    Talking Success

    Thank you both for your input I really appreicate it. You've been a great help.

    I am very pleased to say it works now YAY. Its took a while to get there, but what I've ended up with, is deleting the SQL statement from the row source and instead creating with VBA using the Forms Current event. Here's the code I've ended up with:

    Code:
    Private Sub Form_Current()
    
    Dim strSetID As String
    Dim strSQL As String
    
    strSetID = Me.SetID
    
    strSQL = "SELECT tblWeight.WeightID, tblWeight.WeightSerialNumber, tblWeight.WeightQuantity, tblWeight.SetID  " & _
             "FROM tblWeight " & _
             "WHERE tblWeight.SetID = '" & strSetID & "' "
    Me.CmbSelectWeightRecord.RowSource = strSQL
    
    End Sub
    The combo box wizard had already added code to the combo boxes after update event to find the chosen record.

    Thus far it seems to be working perfectly.

    Somehow or other I've managed to hide the Weight ID from view in the combo box, happy with this though - makes a nice change to accidentally do something I want, rather than stuff it up completely lol. All I want to do now is play around with column widths for the combo box so its a bit tidier.

    Thank you both for your help.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •