Query by type: CHANGES TYPE! 1

[ailyn]

As a continuation of my previous thread:

http://www.tek-tips.com/viewthread.cfm?qid=1123497&page=1

The code that works has a failure. Everytime there is a type that has no records related to it, the first row of products changes to that type. How could I avoid that?
The code I use is:
Option Compare Database

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:
Exit Sub

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

It works on frmByType that works with tblTypes and rptProducts that shows tblProducts. But for more reference you could also check my previous thread.

 

[BoxHead]

Can you tie the combobox rowsource to the table/query on which the report is based? SOmething like:

SELECT DISTINCT typeID from myTable;

That should eliminate the possibility of anyone selecting a type that doesn't exist in the recordset.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[ailyn]

I did. I changed my whole query to the one you gave me and left the VB code as it was. Now it only selects one type. The combobox displays when I click on the arrow, but it doesn't let me select any other type, despite the fact that I have like 12 types.
?.?

 

[BoxHead]

What is the purpose of this combo box? Is it tied to a table or is it to select criteria for a query and subsequent report?

John

Use what you have,
Learn what you can,
Create what you need.

 

[ailyn]

It's to select criteria to open a report that will show only the chosen type.
tblProducts has
productId
name
reference
serial
type (from tblTypes)
...
tblTypes has
typeId
type

The frmByType, is supposed to get the type from tblProducts and open the rprtProducts with that value, so that it will only show products with type x(selected).

The form works, the report works, the VB given works. BUT when you select in the form a type that has no products to match it catches the error and doesn't open the rprtProducts (VB for that included in the report itself) AND it changes the first record of tblProducts into the Type you selected.
I'd like to avoid that. If it is of any help, here you have the SQL query for frmByType:

SELECT Products.ProductId, Products.TypeId AS Products_TypeId, Products.Avail, Products.[uit], Types.TypeId AS Types_TypeId, Types.Type
FROM Types INNER JOIN Products ON Types.TypeId = Products.TypeId
WHERE (((Products.[uit])=False));

I hope I've been clear enough.

 

[BoxHead]

ailyn,

The fact that it's changing the data in tblProducts suggests that you have the combobox bound to tblProducts. I would suggest using an unbound combobox, and base the RowSource of the comboBox on tblProducts instead of on tblTypes. That way, the only choices in the combobox would be the ones that are in tblProducts.

In the form's design view, the Data tab of the Properties should have the following values when the comboBox is selected. I believe that right now you may have tblProducts.Type in the Control Source.

Control Source .......... >leave empty<
Input Mask .............. >empty<
Row Source Type ......... Table/Query
Row Source .............. SELECT DISTINCT Type FROM Products;
Bound Column ............  1
Limit To List ...........  Yes

If this doesn't resolve it, let me know. Also, let me know whether frmByType is the name of your combo box or the name of your form.
HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[ailyn]

Er...It doesn't show anything now. By frm By type I meant the form 'ByType'.

The combobox is called 'cboTypeId'

I also forgot to mention the combo had a query (actually I forgot it had one X):

Control Source...Products_TypeId
Input Mask.......>Empty<
Row Source type..Table/Query
Row Source.......SELECT Types.TypeId, Types.Type FROM Types
ORDER BY Types.Type;
Bound Column.....1
Limir to List....Yes

 

[BoxHead]

Er...?

First problem: The control source should be blank. Binding it to your Products table is why changing your selection is changing the field value in the table. That is what Bound Controls do.

Second Problem: People can select a Type that does exist in the Products table because you have them choosing from a list that is based on the Types table.

To eliminate the possibiity of someone selecting a TypeId that does not exist in the Products table, change your Row Source to display the list of types that are already in the Products table. Does that make sense?

SELECT DISTINCT TypeId, Type FROM Products ORDER BY Type;

I think the reason it doesn't show anything is because the combobox has two fields in the query and I only knew of the one. If you go to the Format tab of the properties window you'll probably see the Column Widths is set to 0";1".

If your Products table does not have a field called TypeId and a field called Type, then Selecting them will have no meaning.

I'm getting confused on what fields you have in which tables because you listed the fields in Products in a previous post but you didn't list TypeId as one of them. Now it is the control source for the combo box.

To clarify, I use prefixes like tbl, frm, and qry in the names of my objects so I can keep track of what is what when I'm coding. If you use tblProducts when the table's name is Products, Access won't know what you mean.

HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[ailyn]

Maybe to clarify a bit you should check my previous thread (marked above). Here is the info you need of what I'm trying to do:

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.

Afterwards dhookom gave me the instructions to build the combobox ("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.") and the code whose initial errors were later solved by him in that thread, and that you can see above.

I'm afraid that I can't be any clearer than this without the possibility of attachements.

 

[BoxHead]

I've read that thread so many times that Amazon.com is sending me popups asking if I want to buy books about thread701-1123497.

I understand what Duane suggested and I am suggesting something different.

I have to assume that when Duane said to

Create a combo box (cboTypeID) on a form with the types table as the Row Source and the TypeID field is the bound field.

he was referring to the bound column of the combobox. If this is the only purpose for the form then there is no reason to bind the form to the Products table and the Control Source for the combobox should be left empty. A combo box based on the TypeID and Type fields in the types table will show you all of the types that are in the types table.

What I am suggesting is that you base the combo box on the TypeID and Type fields in the Products table.

I would suggest that you:
Select the Queries pane of the database window.
Click 'New'
Click 'OK' to select 'Design View'
In the Add Table list, double click Products.
In the Add Table list, double click Types.
In the Add Table list, 'Close'.
In the top half of the Query Grid window you should see a window for each table with the fields list for each.
There should be a line connecting them on the TypeId field.
In the Products table, double click 'TypeId'
In the Types table, double-click 'Type'
Double-click in the grey area surrounding the two table windows to display the Properties Window.
On the Properties Window line item for 'Unique Values' change the entry to 'YES'
Save the query and name it qry4cbo.
In your form's design view, select the combobox.
Open the properties window and select the Data tab.
Clear the control source entry.
On the rowsource line item, click the dropdown button and select the query you just saved.
On the Bound Column line, type 1.
Click on the Format tab.
On the Column Count line, type 2.
On the Column Widths line, type 1";1"
Switch to form view and click the combo box dropdown.

The list you see should be all of the types that are in the Products table.

HTH







John

Use what you have,
Learn what you can,
Create what you need.

 

[ailyn]

Thanks a lot! It works perfectly. There you get a star and my apologies for being such a headache, I really could not get your prev. instructions to work.
Thanks again!