Going to the specific record

[Guest]

Dear all,

I have a four table viz, Product, Category, Dealers(Suppliers) and State. I
have created another table Called Proposal Details. In Proposal Details i
have created relationships to all four other tables. Proposal Details tables
has a Running Internal No.(Autonumber starts form 1169), month, Proposal No,
Applicant Name, Received Time, Dealer, Product, Category, State, Report(yes
or No), Billed, Replied Time and Result. I have created primary key to
Running Internal No. (Duplicates= No) and Proposal Number(Duplicates = Yes).

Now i have made a Form using Proposal Details Table. I have about 10,000
records now. So far have been entring records smoothly. Now we started to
receive Reports to each record. I need to scroll down and search for each
record to tick the check box. For Example we receive some where in between
the 10,000 records i need to scroll to that particular record.

I want you'll to help me, how to create a form or sub form ( i don't know
much about how to create a sub form) where if i enter a particular Proposal
No. i should get that record(s). Any help or suggestions will be highly
appriciated.

Thanks

Karan.

 

[Carl Jansson via AccessMonster.com]

I have solved the same problem in my database by putting in a search field
in the form header that allows a user to jump to the correct ID of that
record.

The field in my database which is searched is called Case_ID which i an
autonumber

*************
In the form header create a unbound textbox, in my case it is called
search_fix_txt. Then create a button, in my example Cmd_search_fix) that
will execute the following code on click:

Private Sub Cmd_search_fix_Click()
Dim str_fix_id As String
Dim strSearch As String

'Check search_fix_txt for Null value or Nill Entry first.

If IsNull(Me![search_fix_txt]) Or (Me![search_fix_txt]) = "" Then
MsgBox "Enter a value!!", vbOKOnly, "Invalid choice!"
Me![search_fix_txt].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into search_fix_txt
'and evaluates this against values in case_ID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("case_ID")
DoCmd.FindRecord Me!search_fix_txt

case_ID.SetFocus
str_fix_id = case_ID.Text
search_fix_txt.SetFocus
strSearch = search_fix_txt.Text

'If matching record found sets focus in case_ID
'and clears search control

If str_fix_id = strSearch Then
case_ID.SetFocus
search_fix_txt = ""

'If value not found sets focus back to search_fix_txt and shows msgbox
Else
MsgBox "Could not find a fix with number " & strSearch, _
, "Could not find a fix"
search_fix_txt.SetFocus
search_fix_txt = ""
End If
End Sub

*************

Best regards
Carl

 

[Guest]

Thanks it works great!!!

One more small doubt, how about multiple records with the same Case_ID, in
your example it is not possible as it is a auto number. But, in my database
i do have duplicates. Any help will be greatly accepted.

Once again thanks for your valuable tips.

Karan.