By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

Find Record in Table and Return Values

P: 20
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.
Dec 13 '07 #1
Share this Question
Share on Google+
4 Replies


Minion
Expert 100+
P: 108
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:

Expand|Select|Wrap|Line Numbers
  1. sub txtBox_LostFocus()
  2.  
  3.    DoCmd.ApplyFilter, "ClaimNumber = " & txtBox.Value
  4.    DoCmd.Requery "Form Name"
  5.  
  6. end sub
  7.  
This should set the filter for the recordset to only find the record corresponding the claim number.

Hope this helps.

- Minion -
Dec 13 '07 #2

P: 20
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.
Dec 13 '07 #3

Minion
Expert 100+
P: 108
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton_Click()
  2.  
  3.     Me.Filter = "ClaimNumber = '" & Me.Text1.Value & "'"
  4.     Me.FilterOn = True
  5.  
  6. End Sub
  7.  
Just make sure to set the field name "ClaimNumber" to whatever you called the field in your connected table.

Hope this helps.

- Minion -
Dec 17 '07 #4

P: n/a
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!
Oct 26 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.