Query by type 2

[ailyn]

I have a table products that has a typeId joined to table types. Now I want to make a query that in the form displays the types and by chosing one it displays all the items available of that type.
I'm very newbie to Access queries and all my tries didn't work (by the way the official site doesn't really help);

 

[juliane26]

Ailyn,

it is easier to understand if you post sample data.
it appears all you need is a simple join similar to this:

select types.typeid, types.xyz, products.xyz
from types, products
where types.typeid = products.typeid

Well, I am talking Access-SQL here.
xyz are sample columns, since I don't know which cols you need. of course you can select more than just one column.

Is that what you need ?

Juliane

 

[ailyn]

Well, sort of but not. I know that it should be a simple thing but I don't get it:

table products
productId - autonum, key
reference - txt
serial - txt
typeId - num (from table types: typeId, type, show type)

table types
typeId - autonumkey
type - txt

I want in a form a choicelist with the types, when I pick one it shows ONLY all the references (products) with that type.
In the official site they just tell you how to filter by one product.

 

[dhookom]

Create a combo box (cboTypeID) on a form with the types table as the Row Source and the TypeID field is the bound field. Use the wizard to create a command button to open your report. Modify the code to look something like:

  Dim strWhere as String
  If Not IsNull(Me.cboTypeID) Then
    strWhere = "[TypeID] = " & Me.cboTypeID
  End If
  DoCmd.OpenReport "rptReportName", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ailyn]

Sorry for the delay in asnwering. I just tried it and it works perfect, but it gives me error when I select a type that is not available (that has no products available). What could I include to avoid that? I'd like a message box to popup

 

[dhookom]

What error? I don't see any error. I don't know what is causing the error.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ailyn]

I mean that when I choose a type that has no products available (there are no products classified by that type) It shows an error message Although the VBA code programmer shows the error is in the report where it is supposed to show an image.
The only thing I need in the answer you gave me is an else statement that cancels the action and shows a msgbox (I tried but it doesn't work) to avoid that problem. Is that possible?

 

[lespaul]

The If statement above checks to see if something has been selected in cboTypeID, if something has been selected it is copied into a variable strWhere. The query is tied to your report, the OpenReport command opens your report and strWhere is passed in as a parameter telling the query which records to select.

If you want to be able to check the query first, then you can't used the OpenReport command.

You will need to run the query (in code), if the query returns results, then run the OpenReport command or an equivalent, if there are no results show your error message.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dhookom]

You can also use the On No Data event of the report to close the report. You would need to trap and handle error 2501 in the code that opens the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

That's why you're a forum MVP!!

I don't work with Access enough to really know all the ins and outs!

Have a star and a GREAT day!

les

 

[ailyn]

Sorry there but whatever I tried it didn't work T.T Could you show me how to make the code either for trapping the error or to run the query in code.

 

[dhookom]

If you want to automatically close a report that has no data, find the On No Data event property of the report and add the following to the event procedure:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no records to report.", _
        vbOKOnly + vbInformation, "No Data"
End Sub

Trap for error 2501 in the code that opens the report

Private Sub cmdOpenRpt_Click()
On Error GoTo Err_cmdOpenRpt_Click

    Dim stDocName As String

    stDocName = "Customer Labels"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenRpt_Click:
    Exit Sub

Err_cmdOpenRpt_Click:
    Select Case Err
        Case 2501
            'ignore
        Case Else
            MsgBox Err.Description
    End Select
     Resume Exit_cmdOpenRpt_Click
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ailyn]

Sorry for the delay in answering I got way too much busy with an expo at work!

i tried this but it doesn't work, it says that the name has not been specified in the first line 'Private Sub cmdOpenRpt_Click()' But i tried mixing up the prev and this one, maybe I shouldn't?

Option Compare Database

Private Sub cboTypeId_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.cboTypeId) Then
strWhere = "[TypeId] = " & Me.cboTypeId
End If
'DoCmd.OpenReport "Products", acPreview, , strWhere

End Sub

Private Sub cmdOpenRpt_Click()
On Error GoTo Err_cmdOpenRpt_Click

Dim stDocName As String

stDocName = "Products"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenRpt_Click:
End Sub

Err_cmdOpenRpt_Click:
Select Case Err
Case 2501
'ignore
Case Else
MsgBox Err.Description
End Select
Resum Exit_cmdOpenRpt_Click
End Sub

 

[dhookom]

The code you entered into the cboTypeID before update should be in the cmdOpenRpt_Click

Private Sub cmdOpenRpt_Click()
    On Error GoTo Err_cmdOpenRpt_Click
    Dim strWhere As String
    If Not IsNull(Me.cboTypeId) Then
        strWhere = "[TypeId] = " & Me.cboTypeId
    End If
    Dim stDocName As String
    stDocName = "Products"
    DoCmd.OpenReport stDocName, acPreview, , strWhere
    
Exit_cmdOpenRpt_Click:
    End Sub

Err_cmdOpenRpt_Click:
   Select Case Err
       Case 2501
            'ignore
       Case Else
            MsgBox Err.Description
   End Select
    Resum Exit_cmdOpenRpt_Click
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ailyn]

Thanks for your fast answer Dhookom! But it still tells me that the name has not been defined?!? and this seems to be the problem because it gets in yellow:
Private Sub cmdOpenRpt_Click()

 

[dhookom]

What do you mean by "it gets in yellow"? What name has not been defined?

Does your report contain the field TypeID in its record source?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ailyn]

Er...The VBA Code Error gets an error when I click on the button 'cmdOpenRpt'. I don't understand why, but it says that the name has not been especified. and the head of the code I pointed out gets marked in yellow.

It is not a report, it's a form with a combobox 'cboTypeId' where the TypeId field (that indeed is included in the form) shows.

 

[dhookom]

Is there a combo box "cboTypeID" on the same form?
Also, I just noticed you missed the "e" in

Resum Exit_cmdOpenRpt_Click

and then I copied your error.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ailyn]

Yes, there is indeed a combobox called cboTypeId
& for the code I used I simply copied and pasted the one you made to avoid mistakes. I give me the same result no matter what.

Do you know what should I do?

 

[dhookom]

If you copied my code then I am not sure how the error got introduced in your post on 18 Oct 05 10:47. Did you fix the line of code that I recommended?

Have you attempted to compile the code?

Are you still getting an error message as you stated 19 Oct 05 9:47?

Have you selected a TypeID in the combo box?

What happens if you try this code for you command button?

Private Sub cmdOpenRpt_Click()
    On Error GoTo Err_cmdOpenRpt_Click
    Dim strWhere As String
    If Not IsNull(Me.cboTypeId) Then
        strWhere = "[TypeId] = " & Me.cboTypeId
    End If
    Dim stDocName As String
    stDocName = "Products"
    msgbox "strWhere: " & strWhere
    DoCmd.OpenReport stDocName, acPreview, , strWhere
    
Exit_cmdOpenRpt_Click:
    End Sub

Err_cmdOpenRpt_Click:
   Select Case Err
       Case 2501
            'ignore
       Case Else
            MsgBox Err.Description
   End Select
    Resume Exit_cmdOpenRpt_Click
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]