Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with split form

Posted on 2013-06-08
4
Medium Priority
?
509 Views
Last Modified: 2013-06-09
I have a split form with the datasheet on the right.  In the left column I have an unbound field.  When the user enters a string of characters in the unbound field and then clicks a command button at the bottom of the left column I want any records that have that typed string of characters in any field to display in the rows in the datasheet.

What would the onclick code be for the command button?

Also, when the form opens no records display which is fine but I can't figure out why they aren't displaying.  The form has a table as its recordsource. I have Data Entry set to No.
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 39232530
You can try to use filters:
http://allenbrowne.com/ser-28.html

May be you can upload sample DB?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39232563
Here is one way of doing it:

assuming you want to search in the following Table fields:
fName
address
phone1
phone2

assuming your Form is Form1, your search field is Search1,

make a query, add your table, add all the fields you want, then
add a field like this:
my_Fields: [fName] & " " & [address] & " " & [phone1] & " " & [phone2]
in the criteria write this:
Like "*" & Forms!Form1!Search1 & "*"

now make the datasheet form's Record Source, the above query.
after you enter the search criteria, your command button should contain the following command:
me.requery
this will bring all the Records that contain your search :)

as for No data show, make sure the Filter of the Form is empty too, and you might want to make FilterOn=NO

jaffer
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 39232845
1.  You simply need to set the Forms Filter property and turn it on.

Private sub cmd_Filter_Click

     me.Filter = "[field1] & [Field2] & [Field3] Like '*" & me.txtSearch & "*'"
     me.FilterOn = True

End Sub

2.  Not entirely sure why the form comes up with no records displayed.  Check the FilterOnOpen property and set it to No.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39233112
Hi  SteveL13

if you use fyed's solution, make sure you leave a space between the fields, like I did, like this:
so instead of
me.Filter = "[field1] & [Field2] & [Field3] Like '*" & me.txtSearch & "*'"

do it like this:
me.Filter = "[field1] & chr(32) & [Field2] &  chr(32) & [Field3] Like '*" & me.txtSearch & "*'"

the reason is:
assuming:
filed1= 1234
field2= 5678
field3= 90ab

and if you search for "3456":
based on  fyed's solution, your search will be in:
1234567890ab
here your search WILL find a match, and it will show you the Record/s, which is wrong,

but when you add a space between the fields, then your search will be in:
1234 5678 90ab
and obviously, you will NOT find a match :)

something to keep in mind :)

jaffer

ps: chr(32) means sapce
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question