Retrival of records from a table

[shreeks]

Hi I am new using Access database. I have created a table with several fields like (letter categrory, letter type, letter name etc) in access, using forms and inserted few records in my table. now I wanted to know like if i type in an existing letter name(already placed in a record) in the text field provided for letter name in my form, I need to retrive all other fields of that particular record.

say for example, my record has
Letter categrory : intr
Letter type : haaad
Letter Name : abcdef

Now if i type in abcdef for letter name in my forms window, the forms window should display values of letter type and letter name.
how do i do this

[orangeCat]

@shreeks
Here is a link that could be useful to you.

http://www.allenbrowne.com/casu-22.html

[ChipR]

Well, here are some suggestions if you don't want to read a whole book.

If you are ok with your user having to input the exact letter name to search, you can make a subform to display the records and set its Link Master Fields to the input textbox and the Link Child Fields to LetterName. This is a bit restrictive.

Another way would be to have the user type in a value and click a search button. The code in the search button is like:

subformControlName.Form.Filter = "LetterName LIKE '" _
& [inputTxtBox] & "*'"
subformControlName.Form.FilterOn = True

So, when they click search, your subform now only displays those records that have a Letter Name that starts with what was entered in the text box.
Later on, you can get fancy and update the subform whenever a letter is typed in the input, like an autocomplete, depending on how large your recordset is.

[shreeks]

I Created another form say 2nd form with Letter name, and made my original form with letter type, letter category and letter name as the subform. Acutually my Letter name field is set as primary key. Now i entered few values in the table and I am able to retrive the values of the table using my 2nd form by entering the letter name already existing in my table and I am able to see all the values in the subform.
let us say my table has
Letter name letter type letter category
andy aaa 123
sam bbb 333
rob ccc 324

when i enter 'andy' into the letter name i am able to see 'aaa' and '123' in my subform.
but now i want to edit this data in my subform. I want to change 'aaa' and '123' values in my subform directly and this change should reflect in my table.
can I do that, or is there any other method that i can work on that performs this job??

[ChipR]

The data in the subform is the table data, so if you change it in the subform, it will be updated in the table. What is preventing you from changing the data in the subform now?

[shreeks]

Acutually I have setup "Primary Key" on Letter Name field. So it is popping out a message like... " the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again"

but I dont want duplicates in my table....

[ChipR]

You get this message when you try to change the data in the subform? Change one field at a time until you can tell what field is violating the restrictions.

[shreeks]

Acutully here I am not entering the new data ... i am trying to edit the data which is popped up from the table in the subform.

I am not being allowed to edit any data there... I am getting the message as soon as I am trying to click my mouse anywhere on the subform.

[ChipR]

What code are you using to show the values in the subform?

[shreeks]

I didnt write any code there... I just dragged one form into another in design view .... one of my forms has just letter name in it and other form has letter name, letter type and letter description in it.. i dragged the second form and placed it in the first form... so when i type in a letter name in my first form.. the corrosponding record values are being displayed in the subform.....
so I haven't written any code there...
Could you please help me out writing out the code(please be very elobrative beccause this is my first time working on access)......

[ChipR]

Is your first form bound to a recordset? The field where you type in a letter name on your form should not be updating your table.

[shreeks]

yes my first form is also bound to a record set. yes it is not updating my table..

[ChipR]

Try removing the recordset source from the main form. It doesn't need one. Make sure that the master field of the subform is set to your input box.