Results 1 to 15 of 15
  1. #1
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13

    Query showing wrong information

    Morning all.


    I have a slight problem with my queries.

    Say for instance i have a few queries set to go through a table of contents with about 8 Columns of information i need to filter, Make, Model, EngineType, Transmission, Drive, Year, ServiceType.
    After these 8 columns there is information that represents a particular ID once filtered, As per belows table, EF3, AF3 and OF3 only belong to the Holden Colorado.

    ID Make Model EngineType Transmission Drive Year ServiceType EngineFilter AirFilter OilFilter
    1 Nissan Patrol TD42 Manual 4wd 2004 180000 EF1 AF1 OF1
    2 Ford Ranger 4JJ1 Auto 4wd 2010 40000 EF2 AF2 OF2
    3 Holden Colorado 4JJ1 Manual 4wd 2014 60000 EF3 AF3 OF3

    The problem i am having is once it gets to the EngineType, The next drop down menu will show both the auto and manual, As i have already started the filter with queries and drop down menus, And i have selected everything for a holden colorado. Is it possible to lock in the previous drop downs so that it does not go wandering when if finds another variable in the query. For example it is finding 2X 4JJ1, Ultimately, the above table will be hundreds of lines long, with many different variables, so being able to lock in the first selections will be 100% required.

    I have created everything using wizards as i am not at all Access and Code knowledged. So when replying please treat me like i know absolutely nothing at all. As this is definitely the case for me

    Below is the codes i have used through the wizards to get to where i am now.

    Vehicle Make Query from has been produced from VehicleMakeTable
    Code:
    SELECT VehMakeTab.[VehMake]FROM VehMakeTab;
    The Remaining has been created refering to the same table as ive recreated above. "Data Spread"
    Vehicle Model Query
    Code:
    SELECT [Data Spread].Make, [Data Spread].ModelFROM [Data Spread]
    WHERE ((([Data Spread].Make)=[Forms]![DataSelectForm]![VehMakeCombo]));
    EngineType Query
    Code:
    SELECT [Data Spread].Model, [Data Spread].EngineTypeFROM [Data Spread]
    WHERE ((([Data Spread].Model)=[Forms]![DataSelectForm]![VehModelCombo]));
    I hope my question is clear to understand, I just hope that locking it all in is at least possible.

    Cheers

    Dan

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Am I correct to believe you have a form with more than one combobox control and you want some of the comboboxes to be dependent on the selection/value of the previous combo?

  3. #3
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13
    Yeah thats right, I have already set up the combo boxes to be dependent on the previous box, But when i get to the point where there are duplicates in other non related lines, it instantly takes them into consideration.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You are going to need to adjust the Rowsource of your dependent combos. Take a look at this and let us know what questions you have regarding dynamically adjusting the RowSource property. Maybe none of it makes sense. We just need a place to start.
    http://www.baldyweb.com/CascadingCombo.htm

  5. #5
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13
    Thank you very much, As it is 2am, i am going to sleep for a bit, will have a read in the morning and see where i can go from there. you will hear from me soon,

    Thanks again

  6. #6
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13
    Haha, Had a quick look before sleep, That is completely confusing and i got no idea. Perhaps it is grounds for me to definitely start researching with

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    No worries. I suggest you set aside some time and review some tutorials. I started to compose a few tutorials. If you are interested in learning many of the nuances, I would recommend downloading and viewing all of the combo tutorials I provided. Starting at the beginning may be review, but there will likely be tidbits along the way. Tidbits that will mater when you get deeper into the series.
    https://www.accessforums.net/showthr...671#post277671
    You will need the login info provided in post #1. Maybe paste that info into an open text file for ease of logging in.

  8. #8
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13
    MMMAAATTTEEEE!!!!
    You are an absolute legend I have taken some time and followed your tutorials. I Highly recommend anyone to do the same, Even if you are brushing up on your basics.

    Thank you soo much "ItsMe" Your videos and tutorials have helped more then i expected.
    Problem Solved.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Glad to hear you have some new tools to work with now.

  10. #10
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13
    Service Sheets.zip

    Sorry to bother you again. Ive followed your tutorials and it has got me ALONG way,
    I am unable to work one last thing out with regards to the dynamic drop downs.

    I have set up about 8 dropdowns that are dynamically attached to each other and filterd out all the unnecessary info.
    For the final step I have included the "requery codes" as follows
    Code:
    Private Sub cboServType_AfterUpdate()Me.cboEngOilFilter.Requery
    Me.cboFuelFilter1.Requery
    Me.cboFuelFilter2.Requery
    Me.cboAirFilter.Requery
    Me.cboCabFilter.Requery
    Me.cboTransFilter.Requery
    obviously this is just a very small sample of the scripting page.


    Say the following script was the last of the set of drop downs that are filtering out the table. And i would like to set the only available result to be viewed in this drop down. Is there a way i can have it instantly shown instead of having to select the drop down arrow and manually selecting.

    Code:
    SELECT DISTINCT [Data Spread].Make, [Data Spread].Model, [Data Spread].Year, [Data Spread].EngineType, [Data Spread].Transmission, [Data Spread].Drive, [Data Spread].ServiceType, [Data Spread].EngineOilFilter FROM [Data Spread] WHERE ((([Data Spread].Make)=Forms!DataSelectForm!cboMake) And (([Data Spread].Model)=Forms!DataSelectForm!cboModel) And (([Data Spread].Year)=Forms!DataSelectForm!cboYear) And (([Data Spread].EngineType)=Forms!DataSelectForm!cboEngType) And (([Data Spread].Transmission)=Forms!DataSelectForm!cboTransmission) And (([Data Spread].Drive)=Forms!DataSelectForm!cboDrive) And (([Data Spread].ServiceType)=Forms!DataSelectForm!cboServType)) ORDER BY [Data Spread].Make, [Data Spread].Model, [Data Spread].Year, [Data Spread].EngineType, [Data Spread].Transmission, [Data Spread].Drive, [Data Spread].ServiceType, [Data Spread].EngineOilFilter;
    For this particular section, i have removed the (Value = "") of the scripting.
    Can any one assist please.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As long as it's guaranteed that the last combo will only have one row, try
    Me.cboServType.Value = Me.cboServType.ItemData(0) or even Me.cboServType = Me.cboServType.ItemData(0)
    This assumes the combo only has one column.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    As far as I know you would have to run a separate query to determine what data is on the combo. Once you understand how many records are in a given dataset, you could retrieve the value and assign said value to the Value property of the combo. I would probably use a DAO recordset and you might want to use a Dcount() combined with a Dlookup().

    Having said that, I need to mention that your database has only one table and I view this to be problematic. In a relational database, you need multiple tables. If you have a few combooxes on a form, it is because each combo represents an entity. Sometimes it makes sense to combine entities into a single combo. However each entity should be in its own table.

    In your case, you should have a table dedicated to each of the following entities...

    • Make
    • Model
    • EngineType
    • Transmission
    • Drive
    • Year (might be able to do without)
    • ServiceType


    Maybe take a few minutes and review this youtube video ...
    https://www.youtube.com/watch?v=-fQ-bRllhXc

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I admit I didn't open any of the attachments, but I thought this was about dependent combo boxes (where the 1st filters the 2nd, which filters the 3rd and so on).
    In that case the last combo row source is known based on its parent selection, which means my suggestion should work. Sorry if I've misunderstood the process. Here's picture proof it works though (I did not drop down the combo to set its value to the first in its list).
    Click image for larger version. 

Name:	unchecked.jpg 
Views:	11 
Size:	3.3 KB 
ID:	25650
    Private Sub Check25_Click()
    Me.Combo14.Value = Me.Combo14.ItemData(0)
    End Sub

    Click image for larger version. 

Name:	checked.jpg 
Views:	11 
Size:	4.0 KB 
ID:	25651
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    @Micron
    Yeah, I got side tracked before hitting the submit button on my previous post and after you submitted yours. After I posted, I saw #11. At that time I was thinking a Dcount could check the recordcount and then that could determine whether or not to implement the ItemData thing.

  15. #15
    Dan T is offline Novice
    Windows 8 Access 2016
    Join Date
    Aug 2016
    Posts
    13
    Good afternoon gents.
    Ive just had a little play with your suggestions, Ive just come up with an easier solution. Instead of a combo box, ive turned it into a listbox, I am still able to format the filters as i have been doing and this is then showing the only list item that is required, with no additional coding.

    I will post a final attachment to this reply once ive completed the form i am creating.

    Thank you all very much for your time

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-09-2015, 10:44 AM
  2. Replies: 2
    Last Post: 02-05-2015, 03:51 PM
  3. Form Combo Box Saving Wrong Information
    By Steve62 in forum Reports
    Replies: 5
    Last Post: 11-12-2012, 02:29 PM
  4. Replies: 1
    Last Post: 09-30-2011, 06:54 PM
  5. Number showing in wrong format
    By Douglasrac in forum Database Design
    Replies: 10
    Last Post: 05-26-2011, 05:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums