Lookup form

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to catch
names like smith, small etc based on txtsurname a field in my table tbltcp
2.On clicking a command button the results are shown in a datasheet display
on the form
3.On clicking on one of the results it opens another form which shows the
whole record.

I've seen examples of this I am sure but I wondered if someone could get me
started on the basics so that I could have a go myself at building such a
form.
I'm a novice at coding so please simple instructions would be appreciated
Thanks
Tony

Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and a
list box to display the resault.

2. The Row source of the list box will include a filter to text box created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list box, so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that open
another form with the full details using the WhereCondition of the open form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to catch
names like smith, small etc based on txtsurname a field in my table tbltcp
2.On clicking a command button the results are shown in a datasheet display
on the form
3.On clicking on one of the results it opens another form which shows the
whole record.

I've seen examples of this I am sure but I wondered if someone could get me
started on the basics so that I could have a go myself at building such a
form.
I'm a novice at coding so please simple instructions would be appreciated
Thanks
Tony

Thanks that looks quite complicated but I'll have a go and come back to you
in the next couple of days.
Thanks again
Tony

"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that open
another form with the full details using the WhereCondition of the open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which shows the
whole record.

I've seen examples of this I am sure but I wondered if someone could get
me
started on the basics so that I could have a go myself at building such a
form.
I'm a novice at coding so please simple instructions would be appreciated
Thanks
Tony

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to search. EG
at the moment the text box searches for Surname, what if the user didn't
know the surname and wanted to search on say the company name or a reference
number so they had three possible search criteria. Is this possible or would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that open
another form with the full details using the WhereCondition of the open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which shows the
whole record.

I've seen examples of this I am sure but I wondered if someone could get
me
started on the basics so that I could have a go myself at building such a
form.
I'm a novice at coding so please simple instructions would be appreciated
Thanks
Tony

You can use more then one field for the search

Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "* And CompanyName Like
Forms![FormName]![companyTextBoxName] & "*"

--
Good Luck
BS"D


"Tony Williams" wrote:

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to search. EG
at the moment the text box searches for Surname, what if the user didn't
know the surname and wanted to search on say the company name or a reference
number so they had three possible search criteria. Is this possible or would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that open
another form with the full details using the WhereCondition of the open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which shows the
whole record.

I've seen examples of this I am sure but I wondered if someone could get
me
started on the basics so that I could have a go myself at building such a
form.
I'm a novice at coding so please simple instructions would be appreciated
Thanks
Tony

Thanks that sounds great!
Tony
"Ofer Cohen" wrote in message
...
You can use more then one field for the search

Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "* And CompanyName Like
Forms![FormName]![companyTextBoxName] & "*"

--
Good Luck
BS"D


"Tony Williams" wrote:

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to search.
EG
at the moment the text box searches for Surname, what if the user didn't
know the surname and wanted to search on say the company name or a
reference
number so they had three possible search criteria. Is this possible or
would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and
a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list
box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that
open
another form with the full details using the WhereCondition of the open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need
to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to
catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which shows
the
whole record.

I've seen examples of this I am sure but I wondered if someone could
get
me
started on the basics so that I could have a go myself at building
such a
form.
I'm a novice at coding so please simple instructions would be
appreciated
Thanks
Tony

Sorry just checking, does this mean my search form needs a separate text box
for each field I'm searching on as you have indicated two textboxnames?
Thanks
"Ofer Cohen" wrote in message
...
You can use more then one field for the search

Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "* And CompanyName Like
Forms![FormName]![companyTextBoxName] & "*"

--
Good Luck
BS"D


"Tony Williams" wrote:

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to search.
EG
at the moment the text box searches for Surname, what if the user didn't
know the surname and wanted to search on say the company name or a
reference
number so they had three possible search criteria. Is this possible or
would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and
a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list
box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that
open
another form with the full details using the WhereCondition of the open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need
to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to
catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which shows
the
whole record.

I've seen examples of this I am sure but I wondered if someone could
get
me
started on the basics so that I could have a go myself at building
such a
form.
I'm a novice at coding so please simple instructions would be
appreciated
Thanks
Tony

You don't have to, you can use only one text box, but it can be confusing for
the user to use, they might think that you get the wrong resault if they want
to search by the name only

--
Good Luck
BS"D


"Tony Williams" wrote:

Sorry just checking, does this mean my search form needs a separate text box
for each field I'm searching on as you have indicated two textboxnames?
Thanks
"Ofer Cohen" wrote in message
...
You can use more then one field for the search

Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "* And CompanyName Like
Forms![FormName]![companyTextBoxName] & "*"

--
Good Luck
BS"D


"Tony Williams" wrote:

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to search.
EG
at the moment the text box searches for Surname, what if the user didn't
know the surname and wanted to search on say the company name or a
reference
number so they had three possible search criteria. Is this possible or
would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname, and
a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list
box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that
open
another form with the full details using the WhereCondition of the open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you need
to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to
catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which shows
the
whole record.

I've seen examples of this I am sure but I wondered if someone could
get
me
started on the basics so that I could have a go myself at building
such a
form.
I'm a novice at coding so please simple instructions would be
appreciated
Thanks
Tony

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
Thanks
Tony
"Ofer Cohen" wrote in message
...
You don't have to, you can use only one text box, but it can be confusing
for
the user to use, they might think that you get the wrong resault if they
want
to search by the name only

--
Good Luck
BS"D


"Tony Williams" wrote:

Sorry just checking, does this mean my search form needs a separate text
box
for each field I'm searching on as you have indicated two textboxnames?
Thanks
"Ofer Cohen" wrote in message
...
You can use more then one field for the search

Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "* And CompanyName Like
Forms![FormName]![companyTextBoxName] & "*"

--
Good Luck
BS"D


"Tony Williams" wrote:

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to
search.
EG
at the moment the text box searches for Surname, what if the user
didn't
know the surname and wanted to search on say the company name or a
reference
number so they had three possible search criteria. Is this possible or
would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname,
and
a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list
box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that
open
another form with the full details using the WhereCondition of the
open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you
need
to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need
me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to
catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which
shows
the
whole record.

I've seen examples of this I am sure but I wondered if someone
could
get
me
started on the basics so that I could have a go myself at building
such a
form.
I'm a novice at coding so please simple instructions would be
appreciated
Thanks
Tony

Mybe there is a confusion here,
The Where condition used to open the second form.
The Select se to filter the list box, I thought you want to filter the list
box with more then one option, and this is why I gave the select example.

Do you want to add more fields to the filter when you open the second form?

--
Good Luck
BS"D


"Tony Williams" wrote:

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
Thanks
Tony
"Ofer Cohen" wrote in message
...
You don't have to, you can use only one text box, but it can be confusing
for
the user to use, they might think that you get the wrong resault if they
want
to search by the name only

--
Good Luck
BS"D


"Tony Williams" wrote:

Sorry just checking, does this mean my search form needs a separate text
box
for each field I'm searching on as you have indicated two textboxnames?
Thanks
"Ofer Cohen" wrote in message
...
You can use more then one field for the search

Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "* And CompanyName Like
Forms![FormName]![companyTextBoxName] & "*"

--
Good Luck
BS"D


"Tony Williams" wrote:

That worked absolutely great!!!
Now can I get even cleverer? Can I have more than one text box to
search.
EG
at the moment the text box searches for Surname, what if the user
didn't
know the surname and wanted to search on say the company name or a
reference
number so they had three possible search criteria. Is this possible or
would
it work with an Option Group?
Really pleased with the result so far thanks a lot
Tony
"Ofer Cohen" wrote in message
...
Mybe instead of a sub form you can use a list box

1. Create a new form, in it create a Text box to enter the surname,
and
a
list box to display the resault.

2. The Row source of the list box will include a filter to text box
created.
Select Field1, Field2, Filed3 From TableName Where SurName Like
Forms![FormName]![TextBoxName] & "*"

3. On the after update event of text box you need to reresh the list
box,
so
enter the code in that event
Me.[ListBoxName].Requery

4. On the DblClick event of the ListBox you can write the code that
open
another form with the full details using the WhereCondition of the
open
form
command line

Dim MyWhereCondition as String
MyWhereCondition = "[KeyField] = " & Me.[ListBoxName]
Docmd.OpenForm "FormName" , , , MyWhereCondition
=========================
If the key field is string use
MyWhereCondition = "[KeyField] = '" & Me.[ListBoxName] & "'"
=========================
If the key field is not the first column in the list box then you
need
to
specify hat column number that start with 0

MyWhereCondition = "[KeyField] = " & Me.[ListBoxName].Column(1)
=========================
I hope it's clear enough, I'll be happy to help you more if you need
me
to.
--
Good Luck
BS"D


"Tony Williams" wrote:

I want to create a lookup form which works like this
1.There is a textbox which will accept a wildcard input eg sm* to
catch
names like smith, small etc based on txtsurname a field in my table
tbltcp
2.On clicking a command button the results are shown in a datasheet
display
on the form
3.On clicking on one of the results it opens another form which
shows
the
whole record.

I've seen examples of this I am sure but I wondered if someone
could
get
me
started on the basics so that I could have a go myself at building
such a
form.
I'm a novice at coding so please simple instructions would be
appreciated
Thanks
Tony