How do u do this in MS Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How do u do this in MS Access

Author

Topic

KDANKWAH
Starting Member

1 Post

Posted - 2003-06-07 : 14:54:25

I have a table that looks like this:

Name of table: CategoryTab

Fields in tabe: 2

Fieldnames: Costctr and Category

The data in the table looks like this:

200500 Office

200500 Meetings

200500 Agency

200500 Revenue Offset

200502 Meetings

200502 Office

200502 Agency

200502 Travel

My problem is I want a query on a combo box that will list the costctr NUMBER once IN THE COSTCTR COLUMN list all categories associated with that cost center IN THE CATEGORIES COLUMN.

Can someone help!

Thank You!

K.

GreySky
Starting Member

20 Posts

Posted - 2003-06-09 : 11:00:39

I don't agree with your naming conventions, and I'm not exactly sure how you intend to use this, but I solved it for you. Unfortunately, it's all left-aligned, as apparently this forum doesn't render tabs.

You will need to add an AutoNumber as the first field of your table. Call it CategoryTabID.

The solution consists of two queries and a function that acts as your RowSourceType.

--qryCategoryTabFirst
SELECT CategoryTab.Costctr, First(CategoryTab.CategoryTabID) AS FirstCategoryTabID
FROM CategoryTab
GROUP BY CategoryTab.Costctr;

--qryCategoryTab
SELECT CategoryTab.CategoryTabID, CategoryTab.Costctr, CategoryTab.Category, Not IsNull([qryCategoryTabFirst].[FirstCategoryTabID]) AS IsFirstInSet
FROM CategoryTab LEFT JOIN qryCategoryTabFirst ON CategoryTab.CategoryTabID = qryCategoryTabFirst.FirstCategoryTabID;

On the form I added a combo-box called cboCategory. Here are a few of its properties.

RowSourceType: CategoryRowSource (yes, it's not in the list, type it in)
ColumnWidths: 0";1";2";0"
ColumnsCount: 4
ListWidth: 3"

And finally, place this function behind your form class object to feed the data to the combo.

Private Function CategoryRowSource(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant

Static rs As DAO.Recordset

Select Case code
Case acLBInitialize
' open the recordset
Set rs = CurrentDb.OpenRecordset("qryCategoryTab")
CategoryRowSource = True

Case acLBOpen
' return a unique ID
CategoryRowSource = Timer

Case acLBGetRowCount
' drag all records across the network and return a valid count
rs.MoveLast: rs.MoveFirst
CategoryRowSource = rs.RecordCount

Case acLBGetColumnCount
CategoryRowSource = 4

Case acLBGetColumnWidth ' Get column width
Select Case col
' define the width of the columns (ID field is bound)
Case 0
CategoryRowSource = 0
Case 1
CategoryRowSource = 1440
Case 2
CategoryRowSource = 2880
Case 3
CategoryRowSource = 0
End Select

Case acLBGetValue
' align to this position and return its value
rs.AbsolutePosition = row

' only show the first 2nd col value if not the first
If col = 1 Then
' ensure is the first of the set
If CBool(rs(3)) Then
CategoryRowSource = rs(col)
Else
CategoryRowSource = ""
End If
Else
' if not the first column, always show data
CategoryRowSource = rs(col)
End If

Case acLBEnd
' close out
Set rs = Nothing
End Select

End Function

-------------
David Atkins, MCP

Edited by - GreySky on 06/09/2003 11:07:47

GreySky
Starting Member

20 Posts

Posted - 2003-06-09 : 11:12:39

And now you've seen the function method, I'll show you the 'nr special' method.

Just use a query. You can combine this into one query, but I usually like breaking these into two.

--qryCategoryTabFirst (same as previous)
SELECT CategoryTab.Costctr, First(CategoryTab.CategoryTabID) AS FirstCategoryTabID
FROM CategoryTab
GROUP BY CategoryTab.Costctr

--final query showing results
SELECT CategoryTab.CategoryTabID, IIf(IsNull([FirstCategoryTabID]),Null,[qryCategoryTabFirst].[Costctr]) AS TheCostCtr, CategoryTab.Category, Not IsNull([qryCategoryTabFirst].[FirstCategoryTabID]) AS IsFirstInSet
FROM CategoryTab LEFT JOIN qryCategoryTabFirst ON CategoryTab.CategoryTabID = qryCategoryTabFirst.FirstCategoryTabID

----- Heh
David Atkins, MCP

Andy Verity
Starting Member

12 Posts

Posted - 2003-06-24 : 03:28:11

Not quite sure what you are trying to achieve here.

If you have a very long list of categories you could end up without seeing the Costctr. How about putting the costctr into a separate combo box.

What is the purpose for the combo box, out of curiosity?

Andy

quote:

I have a table that looks like this:

Name of table: CategoryTab

Fields in tabe: 2

Fieldnames: Costctr and Category

The data in the table looks like this:

200500 Office

200500 Meetings

200500 Agency

200500 Revenue Offset

200502 Meetings

200502 Office

200502 Agency

200502 Travel

My problem is I want a query on a combo box that will list the costctr NUMBER once IN THE COSTCTR COLUMN list all categories associated with that cost center IN THE CATEGORIES COLUMN.

Can someone help!

Thank You!

K.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources