Find Record in Table and Return Values

[bruce24444]

I'm working on a database created in Access 97 which has a form which tracks claims made for damages. When the person reports the claim, a form is used to enter Date Reported, Claim Number, Loss Type, Reported To, and Caller Name. This information is recorded into tbl:Claims_Assignment.

I've been asked to create a form, to correct these initial details if entered incorrectly, so the end user doesn't have to go into tbl:Claims_Assignment, find the record and make appropriate corrections. (There’s over 5000 entries in this table thus far)

Example:
Date Reported: Dec. 12/07 (textbox)
Claim Number: 121212 (textbox)
Loss Type: Damaged in transit (Combobox) (Should have been “Water”)
Reported To: John (Combobox) (Should have been "James")
Caller Name: Mary (textbox)

On the form I was attempting to create, I want to have a textbox where the user would enter in the claim number and afterupdate, it would search the tbl:Claims_Assignment, find the record and return the initial information for editing.

On the new form I have a textbox to input claim number, then the same fields as on the original frm:Main

I’m fairly new at this and not sure if I have to create a macro, a code, or a query to get this information and from what I’ve read and attempted, I just can’t seem to make this work.

[Minion]

I'm working on a database created in Access 97 which has a form which tracks claims made for damages. When the person reports the claim, a form is used to enter Date Reported, Claim Number, Loss Type, Reported To, and Caller Name. This information is recorded into tbl:Claims_Assignment.

I've been asked to create a form, to correct these initial details if entered incorrectly, so the end user doesn't have to go into tbl:Claims_Assignment, find the record and make appropriate corrections. (There’s over 5000 entries in this table thus far)

Example:
Date Reported: Dec. 12/07 (textbox)
Claim Number: 121212 (textbox)
Loss Type: Damaged in transit (Combobox) (Should have been “Water”)
Reported To: John (Combobox) (Should have been "James")
Caller Name: Mary (textbox)

On the form I was attempting to create, I want to have a textbox where the user would enter in the claim number and afterupdate, it would search the tbl:Claims_Assignment, find the record and return the initial information for editing.

On the new form I have a textbox to input claim number, then the same fields as on the original frm:Main

I’m fairly new at this and not sure if I have to create a macro, a code, or a query to get this information and from what I’ve read and attempted, I just can’t seem to make this work.

This should be relatively easy to do. What you will need to do is apply a filter to the records using the value inputed into the text box. The code would look something like:

sub txtBox_LostFocus()
 
   DoCmd.ApplyFilter, "ClaimNumber = " & txtBox.Value
   DoCmd.Requery "Form Name"
 
end sub
 

This should set the filter for the recordset to only find the record corresponding the claim number.

Hope this helps.

- Minion -

[bruce24444]

This additional form must have fried some brain cells cause some reason I just can't get this.

Form Name: F:Claims_Assignment_Mtce
At the top is a textbox named: Text1
Then a command button to apply a filter
On click, of this command button, I want it to find the Claim Number entered in the text box and fill in the remainder of the form which contains fields linked to tbl:Claims_Assignment with textboxes for Claim Number, Reported To, Loss Type, etc

Again I apologize, I'm just completely frustrated and can't get it to do what I need.

[Minion]

This additional form must have fried some brain cells cause some reason I just can't get this.

Form Name: F:Claims_Assignment_Mtce
At the top is a textbox named: Text1
Then a command button to apply a filter
On click, of this command button, I want it to find the Claim Number entered in the text box and fill in the remainder of the form which contains fields linked to tbl:Claims_Assignment with textboxes for Claim Number, Reported To, Loss Type, etc

Again I apologize, I'm just completely frustrated and can't get it to do what I need.

My apologies. I had posted the code I used when opening forms to a particular record.I've gone back and tested the following with one of my projects. Hopefully these two lines will do it for you.

Just add this code to your command button and make sure the values are correct for your project and I think you'll have it.

Private Sub CommandButton_Click()
 
    Me.Filter = "ClaimNumber = '" & Me.Text1.Value & "'"
    Me.FilterOn = True
 
End Sub
 

Just make sure to set the field name "ClaimNumber" to whatever you called the field in your connected table.

Hope this helps.

- Minion -

[emily rogers]

THANK YOU SO much
i have been working on the same problem for 3weeks and this thread found me the answer! You have made me soooo happy!