Combo Box Issues

[eurohomie]

Hi, This is a repost from my yahoo answers because I haven't really received any replies on there.

Alright so I have a Database and I'm attempting to get a report to open up after I select something from a combo box. So far I have the Combo box "Combo24" which lists organisms. Once an organism is clicked it should open a report "Report 1" which gives me all of the samples that are part of that organism from a Query "Query1."

So far the combo box opens up the correct Report but does not display any results in it. When I made the report prompt user for input it actually works...

Criteria for organism in the report is [Forms]![Navigation Form]![Combo24]

Event for the combo box is:
Private Sub Combo24_AfterUpdate()
DoCmd.OpenReport "Report1", acViewPreview
DoCmd.Close acForm, "Navigation Form"
End Sub

Any help on getting it to display results? thanks

 

[Alansidman]

Just to clarify, your report uses the query as its record source?

 

[eurohomie]

Correct it pulls from a query that has all the samples listed as well as which organism each of them are from

 

[CJ_London]

You're not using the dreaded lookup option in the table design by any chance?

This link explains why it is a bad desgin feature

http://www.access-programmers.co.uk/forums/showthread.php?t=242330&goto=newpost

 

[eurohomie]

Yeah its somewhat like that but the thing I'm not understanding is why it works when I simply copy whats in the combobox and paste it into the criteria field, it works but not when I try to pull it from the combobox

 

[CJ_London]

What you are seeing in the combo box is a looked up a value relating to something else e.g.

tblPerson
ID Name
1 Jones
2 Smith

tblSomething
person (Select Name from tblPerson)

So when you open tblSomething you will see the name (Jones) but it is storing 1 for the id.

So your combo may be showing the name but it's actual value is the ID. So you report is trying to match to ID rather than name - but when you copy and paste you are copying the name

My advice is drop the lookup in the table and when you need it use it in a form, change the text box to a combo box

If you don't want to change then modify the report criteria from OrganismName=[Forms]![Navigation Form]![Combo24] to
OrganismID=[Forms]![Navigation Form]![Combo24]

Obviously, change the bits in red to match whatever you have

 

[eurohomie]

Thank you very much, will try when I get home from work

 

[eurohomie]

My advice is drop the lookup in the table and when you need it use it in a form, change the text box to a combo box

If you don't want to change then modify the report criteria from OrganismName=[Forms]![Navigation Form]![Combo24] to
OrganismID=[Forms]![Navigation Form]![Combo24]

Obviously, change the bits in red to match whatever you have

I tried the second thing and it didn't work. I'm not 100% sure what you meant by the first part though.

I pretty much have a Query That has Sample Name, Organism Name, Strain Name, and Organism ID(Which I added in an attempt to get it to match that up. For the report, I'm only trying to list off all of the samples that correspond with a certain organism. So I have Sample A,C, and D that correspond with organism A and Sample B and E that corresponds with Organism B. When I select Organism A, the report should list off A, C, and D with their strain names.

 

[Alansidman]

Suggest you upload your db so that we can analyze and understand it completely. If you have confidential data, dummy it up. It is not necessary to post the entire db, but only enough data that we can determine what is happening. Run a compact and repair before you upload. Click on the Go Advanced button and follow the wizard.

Alan

 

[eurohomie]

Alright, Did a compact and repair and Since there's a Ton of information in the database I only included what's necessary for what I am trying to Accomplish. In the Navigation form, The first combo box is the one I'm trying to get to work and then I'll move on from there. "Combo16"

 

[CJ_London]

Hi,

I see you still have some table lookups which you should remove however the problem is actually with your code:

Private Sub Combo16_AfterUpdate()
DoCmd.OpenReport "report search for sample", acViewPreview
DoCmd.Close acForm, "Navigation Form"
End Sub

What you are doing is closing the navigation form which the source query for the report refers to - so it returns nothing

You have two choices
1. Don't close the navigation form i.e. remove the Docmd.Close line or

2. Filter the report on opening rather than in the search form sample query - i.e. DoCmd.OpenReport "report search for sample", acViewPreview, , "Organism_ID = " & Me!Combo16. You will also need to remove the reference to the form in the search for sample query.

I've modified on the basis of option 2 and returned your db.

Incidentally, you don't need the search for sample query - you could simply base the report on the sample_org_Strain query

Hope this solves your problem

 

[eurohomie]

Thank you so much!