Lookup form

[Tony Williams]

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

 

[Guest]

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.

 

[Tony Williams]

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

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

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

 

[Tony Williams]

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

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

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

 

[Guest]

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

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

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

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

 

[Tony Williams]

Thanks that sounds great!
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

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

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


:

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

 

[Tony Williams]

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

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

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

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


:

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

 

[Guest]

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

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

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

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
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


:

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

 

[Tony Williams]

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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

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

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


:

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
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


:

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

 

[Guest]

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

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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

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
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


:

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
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


:

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

 

[Tony Williams]

I've spoken to the person who is going to use this database and what they
have now asked for is can the search form have one text box which they enter
text (with a wildcard) and that searches 3 fields one from each of three
tables. Eg the text input in the text box should look for the string in
table1.field1, table2.field2 and table3.field3 The three fields are all text
strings ie names. the content of the list box would display all the results
from the three tables. So presumably could the record source of the list box
be a query created from the three joined tables, they all have a field that
connects them called txtrefnbr.

The root of this database is one table which holds details of a contract,
the contract can have 1, 2 or 3 parties, the other three tables are details
of the three separate parties to the contract linked on the txtrefnbr.

I thought they would be happy with what you did the first time but show
someone something and they want more! Ah well that's life
Is this getting too complicated?

Thanks, really appreciate you sticking with me
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

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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


:

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
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


:

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
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


:

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

 

[Guest]

The RowSource of the list box is a kind of query, you can use few tables,
display few fields, and apply filter to more then one field, just as you do
with a normal query.
Try it, play with the idea and if you'll get a spacific problem , I'll be
more then happy to try and help you.

--
Good Luck
BS"D

I've spoken to the person who is going to use this database and what they
have now asked for is can the search form have one text box which they enter
text (with a wildcard) and that searches 3 fields one from each of three
tables. Eg the text input in the text box should look for the string in
table1.field1, table2.field2 and table3.field3 The three fields are all text
strings ie names. the content of the list box would display all the results
from the three tables. So presumably could the record source of the list box
be a query created from the three joined tables, they all have a field that
connects them called txtrefnbr.

The root of this database is one table which holds details of a contract,
the contract can have 1, 2 or 3 parties, the other three tables are details
of the three separate parties to the contract linked on the txtrefnbr.

I thought they would be happy with what you did the first time but show
someone something and they want more! Ah well that's life
Is this getting too complicated?

Thanks, really appreciate you sticking with me
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

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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


:

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
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


:

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
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


:

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

 

[Tony Williams]

Thanks for that. I have played about with more than one table and got the
results I expexted. Here is my SQL based on 2 tables but I assume it will
work on 3 with another OR

SELECT tblInstitution1.txtRefNbr, tblInstitution1.txtInstitution,
[txtfirstname] & " " & [txtlastname] AS Contact,
tblInstitution2.txtInstitution2, [txtFirstnameInstitution2] & " " &
[txtlastnameInstitution2] AS [Contact 2]
FROM (tblInstitution1 LEFT JOIN tblInstitution2 ON tblInstitution1.txtRefNbr
= tblInstitution2.txtRefNbr) LEFT JOIN tblInstitution3 ON
tblInstitution1.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch2]![txtname] &
"*")) OR (((tblInstitution2.txtlastnameInstitution2) Like
[Forms]![frmsearch2]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution, tblInstitution2.txtInstitution2;

The list box is populated correctly but when I double click on the list box
to open my form I get an error message Runtime error 13 and Type mismatch.
When I click on debug the MYWhereCondition ="[txtlastname] etc etc line of
my code is highlighted. Here is the current Doubleclick code

Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtlastname]='" & Me.[List3] & "'" Or MyWhereCondition
= "[txtlastnametblInstitution2]='" & Me.[List3] & "'"
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

I'm getting real close to solving this thanks to your help. Can you see what
I'm doing wrong here?
Incidently I don't know whether you've realised but I'm in the UK, the time
is 19.40 so it's getting late, my 61 year old head wont take much more
tonight but if you answer and I don't get back to you tonight I certainly
will tommorow.

Thanks again
Tony

The RowSource of the list box is a kind of query, you can use few tables,
display few fields, and apply filter to more then one field, just as you
do
with a normal query.
Try it, play with the idea and if you'll get a spacific problem , I'll be
more then happy to try and help you.

--
Good Luck
BS"D

I've spoken to the person who is going to use this database and what they
have now asked for is can the search form have one text box which they
enter
text (with a wildcard) and that searches 3 fields one from each of three
tables. Eg the text input in the text box should look for the string in
table1.field1, table2.field2 and table3.field3 The three fields are all
text
strings ie names. the content of the list box would display all the
results
from the three tables. So presumably could the record source of the list
box
be a query created from the three joined tables, they all have a field
that
connects them called txtrefnbr.

The root of this database is one table which holds details of a contract,
the contract can have 1, 2 or 3 parties, the other three tables are
details
of the three separate parties to the contract linked on the txtrefnbr.

I thought they would be happy with what you did the first time but show
someone something and they want more! Ah well that's life
Is this getting too complicated?

Thanks, really appreciate you sticking with me
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


:

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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


:

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
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


:

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
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


:

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

 

[Tony Williams]

Could the problem be the Bound column of the list box? How does the code
distinguish which column to use if I'm searching on two fields?
Just a thought
Tony

The RowSource of the list box is a kind of query, you can use few tables,
display few fields, and apply filter to more then one field, just as you
do
with a normal query.
Try it, play with the idea and if you'll get a spacific problem , I'll be
more then happy to try and help you.

--
Good Luck
BS"D

I've spoken to the person who is going to use this database and what they
have now asked for is can the search form have one text box which they
enter
text (with a wildcard) and that searches 3 fields one from each of three
tables. Eg the text input in the text box should look for the string in
table1.field1, table2.field2 and table3.field3 The three fields are all
text
strings ie names. the content of the list box would display all the
results
from the three tables. So presumably could the record source of the list
box
be a query created from the three joined tables, they all have a field
that
connects them called txtrefnbr.

The root of this database is one table which holds details of a contract,
the contract can have 1, 2 or 3 parties, the other three tables are
details
of the three separate parties to the contract linked on the txtrefnbr.

I thought they would be happy with what you did the first time but show
someone something and they want more! Ah well that's life
Is this getting too complicated?

Thanks, really appreciate you sticking with me
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


:

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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


:

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
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


:

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
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


:

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

 

[Tony Williams]

I realised that the possible reason for the type mismatch was that I changed
the field in the WHERE criteria from txtrefnbr, which is a number, to
txtlastname which is a text field. In fact I can leave it at txtrefnbr
because that field is common in all 3 tables and is the field that is used
to link them. The form frmMDi that I am trying to open is based on a query
that uses all three tables.

So I changed the filter back to txtrefnbr but I now get an error 2501 The
OpenForm action was cancelled and the docmd Openform line is highlighted.
Here is the new OnDoubleclick of my list box

Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[tblInstitution1].[txtRefNbr]='" & Me.[List3] & "'"
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

What is it that's cancelling the OpenForm instruction?
I'm sure I'm getting close to solving this!

Thanks for all your help
Tony

Could the problem be the Bound column of the list box? How does the code
distinguish which column to use if I'm searching on two fields?
Just a thought
Tony

The RowSource of the list box is a kind of query, you can use few tables,
display few fields, and apply filter to more then one field, just as you
do
with a normal query.
Try it, play with the idea and if you'll get a spacific problem , I'll be
more then happy to try and help you.

--
Good Luck
BS"D

I've spoken to the person who is going to use this database and what
they
have now asked for is can the search form have one text box which they
enter
text (with a wildcard) and that searches 3 fields one from each of three
tables. Eg the text input in the text box should look for the string in
table1.field1, table2.field2 and table3.field3 The three fields are all
text
strings ie names. the content of the list box would display all the
results
from the three tables. So presumably could the record source of the list
box
be a query created from the three joined tables, they all have a field
that
connects them called txtrefnbr.

The root of this database is one table which holds details of a
contract,
the contract can have 1, 2 or 3 parties, the other three tables are
details
of the three separate parties to the contract linked on the txtrefnbr.

I thought they would be happy with what you did the first time but show
someone something and they want more! Ah well that's life
Is this getting too complicated?

Thanks, really appreciate you sticking with me
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


:

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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


:

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
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


:

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
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


:

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

 

[Tony Williams]

Success!!!!!!! I realised that as my "keyfield" wasn't a string I didn't
need to use the ' an "'" in the Where and it worked!!!!!
Thanks so much for guiding me down the right path. I owe you one!!!
Cheers
Tony

The RowSource of the list box is a kind of query, you can use few tables,
display few fields, and apply filter to more then one field, just as you
do
with a normal query.
Try it, play with the idea and if you'll get a spacific problem , I'll be
more then happy to try and help you.

--
Good Luck
BS"D

I've spoken to the person who is going to use this database and what they
have now asked for is can the search form have one text box which they
enter
text (with a wildcard) and that searches 3 fields one from each of three
tables. Eg the text input in the text box should look for the string in
table1.field1, table2.field2 and table3.field3 The three fields are all
text
strings ie names. the content of the list box would display all the
results
from the three tables. So presumably could the record source of the list
box
be a query created from the three joined tables, they all have a field
that
connects them called txtrefnbr.

The root of this database is one table which holds details of a contract,
the contract can have 1, 2 or 3 parties, the other three tables are
details
of the three separate parties to the contract linked on the txtrefnbr.

I thought they would be happy with what you did the first time but show
someone something and they want more! Ah well that's life
Is this getting too complicated?

Thanks, really appreciate you sticking with me
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


:

Sorry to be so naive but does the SELECT statement replace the
MyWhereCondition in the OnDoubleClick?
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


:

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
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


:

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
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


:

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