asked on

Problem with split form

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.

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjafferr

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

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjafferr

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