Sleepless Combo Select not working on form

[MadeCurler]

I have a form with a combo box that has six values being pulled from a table "Catches 1 - 22 Salmon", "Catches 1 22 Sea Trout" plus another 4 values.


I have six corresponding reports called "Cathces122Salmon", "Catches122SeaTrout" plus another 4 reports.


What I want (obviously) is to open the report "Catches122Salmon" when I select "Catches 1 - 22 Salmon" from the combo


When I use the code below on a command button, a report opens but it is not the one referred to in the If..Else statement. For example, If I select "Catches 1-22 Grisle" from the drop down it opens "Catches122SeaTrout"


Private Sub Command5_Click()


Dim strForm As String


If Me.Combo6 = 1 Then
strForm = "Catches122Grisle"

ElseIf Me.Combo6 = 2 Then
strForm = "Catches122Salmon"




ElseIf Me.Combo6 = 3 Then
strForm = "Catches122SeaTrout"

ElseIf Me.Combo6 = 4 Then
strForm = "Catches2332Grisle"

ElseIf Me.Combo6 = 5 Then
strForm = "Catches2332Salmon"

Else
strForm = "Catches2332SeaTrout"
End If




'Debug.Print strForm


DoCmd.OpenReport strForm, acViewPreview


End Sub

[pbaldy]

Add this right before the OpenReport

Debug.Print Me.Combo6

And see what it produces. My guess is that the combo doesn't return what you expect.

[June7]

Also, can step debug. Set breakpoint, follow the code as it executes. Review link at bottom of my post.

Show the combobox RowSource sql statement.

[MadeCurler]

Thank you for that but I still have problems. When I add Debug.Print Combo6 at the beginning of the code and Debug.Print strForm at the end of the code I get weird results. For example if I select the first combo item I get Combo6 = 3 and strForm = CathchesSeaTrout in the immediate window. I would expect combo6 to =1 and strForm =Catches122Grisle




Another example if I select the second item I get combo6 = 1 and strForm=Catches122Grisle in the immediate window. I would expect combo6= 2 and strForm = Catches122Salmon.


I'm new to all this and it's now driving me mad!! I'd appreciate any help you can give.

[pbaldy]

Can you post the db here?

[MadeCurler]

Thank you very much for your kind offer but I'd be too embarrassed to do that. I'm an enthusiastic pensioner and I know my limitations. I'm trying hard to learn VBA from books and the WEB but it isn't easy (at 66). I quite enjoy it but this little project is driving me mad. Thanks again.

[pbaldy]

No need to be embarrassed; we've seen it all. It's just hard to debug sometimes without seeing it. What are the row source and bound column properties of the combo?

[orange]

There is a tutorial here that may be helpful.
http://www.fontstuff.com/access/acctut08.htm

[MadeCurler]

I set the data source of the combo as a table called "TblPoolSelect" in which I set the Primary Key to PoolSelectId (Autonumber) and the only other field I listed in that table was "PoolSelectRange" in which I entered 6 values...ranging from "Catches 1-22 Salmon" to "Catches 23 - 32 Grisle".....I then created 6 queries using other properly "related" tables in the Db where one of the criteria was "PoolSelectRange" and subsequently reports based on each of the 6 queries. You suggested in your earlier reply that three fields should have been created (including PK)......namely PoolsSelectID, reportName, categoryName...and I now understand why (I think).

My main problem with this was that I was too ambitious. I tried to present the data in a report driven , button "on click" interface without the need to use the Access menu system (like File>Print Query).....I found a tutorial on the web at.....

http://www.datapigtechnologies.com/f...earchform.html

....that suits me fine and works (although it's not as fancy as I'd like as far as producing customised reports is concerned)...

I really appreciate your help and understanding on this, but please don't put yourself to any more bother.

Kind Regards

MadCurler