Search a Table with a Form using a Query

[DonMcCoy]

Hello

I have a "pocket money new" table (Shown in attachments) contains the money that we gave every week to the people.

I want a form to show me the duplicated CardID (all takings of one person) using queries.

I tried to make criteria in queries to check the text box in the form and search the table for desired CardID and show it in sub form but failed.

what is the right criteria to write.

any help would be greatly appreciated.

thanks

[Seth Schrock]

I think that what you are saying is that you want to be able to enter a CardID on the form and then have the query look for all the duplicates for that CardID. If that is the case, then your WHERE clause would be

WHERE CardID = Forms!YourFormName!YourControlName And DCount("*", "Pocket Money New", "CardID = " & CardID) > 1

[hvsummer]

@DonMcCoy
you want "a form to show me the duplicated CardID"

I suggest you create a combobox (1st) having row source querytype

SELECT DISTINCT CARDID from [pocket money new]

then another combox/listbox (2nd) having these code in row source

SELECT CARDID, Date/time, amount from [pocket money new] WHERE ((CARDID = Forms!formname.CboxcardID))

then set column count of 2nd combobox/listbox to 3, bound column to 1.
design that 2nd comboxbox/listbox width long enough to let you see 3 column.

now, create onclick event for 1st cbox, give it code me.2ndcbox/listboxname.requery

ok, now whenever you choose the IDCARD in 1st cbox, it'll show more detail on 2nd cbox/lbox,
you'll know that IDCARD is dupplicate when detail having more than 2 rows.

[NeoPa]

First I would suggest that you need two linked tables (This is part of the very basics of database design Don. Have a trawl through Database Normalisation and Table Structures for some more on that).

One table would contain data related to whatever element is represented by the [CardID] field. The other by pocket money payments that reference a cardID.

Next you would define a relationship between the two tables on the [CardID] field that is common to both. From there set up a form to manage the main (CardID) table. Onto that form add a subform for the other table and let the wizard do the rest for you. You may well be interested in examining what the wizard produces for you so that you can learn and move forward, but it should handle all you need without further interference from you.

It's perfectly possible to use Cascaded Form Filtering, but why make life complicated when a better solution is provided with no effort using an Access wizard.