Dlookup function just pick the first record ?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mayskys1999
    New Member
    • Feb 2013
    • 12

    #16
    Seth,

    ModelID is PK data.

    Comment

    • Seth Schrock
      Recognized Expert Specialist
      • Dec 2010
      • 2965

      #17
      A PK could be either Text or Number (Integer, Long Integer or Auto Number). I have gotten in the habbit of always using Long Integer for my PKs, but I just need to know what yours is.

      Comment

      • mayskys1999
        New Member
        • Feb 2013
        • 12

        #18
        Seth,
        those are Number.

        Comment

        • mayskys1999
          New Member
          • Feb 2013
          • 12

          #19
          I'm sorry.

          that is Auto Number.

          Comment

          • Seth Schrock
            Recognized Expert Specialist
            • Dec 2010
            • 2965

            #20
            Okay. We now have the source of the problem. The combo box is passing a text string to the criteria and there is a mismatch between the number and the text string.

            What you need to do is go to your cmb_SelectModel ID combo box and edit it's row source. It needs to contain the PK of the table that is providing the values and be bound to that field so that it can pass the number to the criteria. What is the current Row Source and Row Source Type of your combo box?

            Comment

            • mayskys1999
              New Member
              • Feb 2013
              • 12

              #21
              Seth,
              Models table has Text data in Revision row and
              ProdReport table has Text date in Firmware row either.
              for my understanding is ModelID could be control to pick up right record when pick up model. Is that right ?
              Plese help me to understand more and help if I'm getting wrong.

              Thanks

              Comment

              • Seth Schrock
                Recognized Expert Specialist
                • Dec 2010
                • 2965

                #22
                I'm not totally sure what you just asked, but if you were asking if using a combo box to select the Revision to be entered into another form, then yes it is possible. You might want to look into Database Normalization sometime though.

                Back to this question, I need the information requested in my last post (post #20) in order to help you. If you need help in finding any of this information, just let us know.

                Comment

                • mayskys1999
                  New Member
                  • Feb 2013
                  • 12

                  #23
                  Seth,
                  I try using a combo box to select Revision to be entered in to the form of ProductsReport. That is my goal.

                  Thanks for your help.

                  Comment

                  • Seth Schrock
                    Recognized Expert Specialist
                    • Dec 2010
                    • 2965

                    #24
                    From post #20, What is the current Row Source and Row Source Type of your combo box?

                    Comment

                    • mayskys1999
                      New Member
                      • Feb 2013
                      • 12

                      #25
                      The Row Source is Model and Row Source Type is Text.

                      Comment

                      • Seth Schrock
                        Recognized Expert Specialist
                        • Dec 2010
                        • 2965

                        #26
                        You need to change your Row Source Type to Table/Query. I'm assuming that you have a field in the Models table that gives the model a name. In my code, I'll call it Model, but you will need to replace that name with whatever the actual name is. You need to make the Row Source be:
                        Code:
                        SELECT ModelID, Model FROM Models
                        The Bound Column property should be already set to 1, but if it isn't then make it be.
                        The Column Count property needs to be 2. The Column Widths property 0; 1.5 You can change the 1.5 to fit your data needs.

                        Something seems funny with what is going on as this has the DLookup finding a field from the same table as the combo box would be using, but this is the only way that I can figure it since the ModelID from the Lookup table needs to be the PK an the table being searched is searching for the PK as well. I'm having a hard time wrapping my mind around this problem.

                        Comment

                        • zmbd
                          Recognized Expert Moderator Expert
                          • Mar 2012
                          • 5501

                          #27
                          You need to learn the Control Object Basics.

                          mayskys1999
                          I used your code to my project.
                          I try to use cmb_SelectModel ID_AfterUpdate.
                          the immediate window showing ModelID = CCC when I picked the third model
                          Hey Seth, Mayskys1999
                          You two are going around in circles.

                          Mayskys1999: It is obvious from your posts that you do not have a basic understanding of the ComboBox list control. Thus, before you go any further, you need to take a look at the basic information for using the control on a form.

                          So a basic information is here at MS website.
                          ComboBox Object - Access 2010
                          You need to read thru EVERY link contained under the object model so that you will understand what we're trying to tell you.

                          Once you have completed that task, you should look thru the examples here to gain an understanding for practical usage:
                          From the Bytes > Sitemap > Microsoft Access / VBA Insights (you should take a look thru all the links here too - good stuff, save you hours!)
                          42.Cascaded Form Filtering
                          156.Cascading Combo/List Boxes
                          168.Example Filtering on a Form.

                          Personally, I'm not going to chase you two thru this maze any longer. Once Mayskys1999 has completed the tutorial session above, then maybe we can provide some actual help.

                          Comment

                          Working...