Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 11th, 2005, 06:36 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Linked Subforms and Search Function

Hi,

There are two sections to the form that I am creating. In the top section, I have the fields that I want the users to be able to update. In the bottom section (subform), I have all the records listed in the datasheet view. These records are locked so that users can't manipulate them at all, only view them.

1.) Since the record source in both the main form and subform are the same, I know I can link them using any of the fields. If a user accesses one of the records in the main form, is there a way to show that record and all the records before and after in the subform Datasheet view in the bottom half of my form?

2.) I want to create a search function for my users because there will be approximately 40,000 records in this database. I have figured out how to create a query that takes input from the user and produces the results. How can I take that query and apply it to the record source for my form so that when I search for *bank*, my form will populate with all records that contain the word bank in them?

If anyone has insight on these two problems, I would greatly appreciate it. Please let me know if anything is unclear and I can clarify. Thanks for all the help.

 
Old December 12th, 2005, 08:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It sounds as if you are saying that you have a parent form and a child form, and both forms are displaying the same data. Why would you do this?

In the second issue, are you trying to do an English search on all fields in the table? Or just some fields?


mmcdonal
 
Old December 12th, 2005, 05:54 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You should be able to do it by setting the "On Current" event for both to display the same record in the other form. As for your search example, set whatever you saved your query as in VB to be the RecordSource of the form. That query should also be used for any record set you want to display on that form, be it all records or just the ones searched for.

 
Old December 12th, 2005, 11:17 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the tips.

mmcdonal:
I do have the same data displayed twice. In the top form, I want the users to be able to edit the data using the look of a form. In the bottom subform, I want the users to be able to view all records that come before and after the current record that is displayed in the top form. The bottom form is a datasheet view and the top form is a form view. There is going to be something like 40,000 records in the DB, and users have expressed that they would like to see other records around the current record. It may be best to just do one form in the datasheet view so that they can see all records that way, and then just lock down the fields I don't want users to edit.


kindler:
I figured out how to set the recordsource equal to the search. Everything works fine when I search for something that is in the database. If I search for something that can't be found, my page disappears and I have to refresh it to get the form back. My code is below. I'm thinking I could create a temporary search and run a statement that said If tempsearch is null then cancel action, but I am not sure how to code that. I have never worked with VB before so I am trying to pick it up as I go. Any thoughts if this would work and how something like this would be structured?

Private Sub cmdSearch_Click()
Dim varWhere As Variant

If IsNull([txtSearchBox]) Then
    Ok = General.Err_msg("The search feild is mandatory." & Chr(10) & Chr(10) & "Please enter a search item.")

Else

    Me.RecordSource = _
    "SELECT * FROM [tbl11I_BANK_BRANCH_ALL] " & "WHERE [11I_BANK_NAME] Like Forms![frm11I_BANK_BRANCH_ALL].[txtSearchBox]"

End If

End Sub


 
Old December 13th, 2005, 04:23 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I figured it out. Here is the code in case anyone is interested.

Private Sub cmdSearch_Click()
Dim varBankSearch

If IsNull([txtSearchBox]) Then
    Ok = General.Err_msg("The search feild is mandatory." & Chr(10) & Chr(10) & "Please enter a search item.")

Else

varBankSearch = DLookup("[11I_BANK_NAME]", "[tbl11I_BANK_BRANCH_ALL]", "[11I_BANK_NAME] Like Forms![frm11I_BANK_BRANCH_ALL].[txtSearchBox]")

    If IsNull(varBankSearch) Then
    Ok = General.Err_msg("The search returned no results. Please try another search.")

    Else
    Me.RecordSource = _
    "SELECT * FROM [tbl11I_BANK_BRANCH_ALL] " & "WHERE [11I_BANK_NAME] Like Forms![frm11I_BANK_BRANCH_ALL].[txtSearchBox]"
    End If

End If

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Subforms and Search Function Jae8201 Access VBA 4 June 22nd, 2006 10:46 AM
Search function talktome Excel VBA 1 September 6th, 2005 12:47 AM
search function elania MySQL 1 February 6th, 2005 07:00 PM
Search function Urban_Roughneck Beginning PHP 9 January 21st, 2005 08:31 AM
function in linked js file 'is not defined' John K. King Javascript 4 May 7th, 2004 09:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.