Form Advice Relating to Queries

[Guest]

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name fields as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox control, so
that when I click on a name in the dropdown list, it returns the selected
individuals contact Details in either a Labels or Text boxs on the form, or
any other recommended way of returning the contact details from the described
combobox?

 

[Allen Browne]

You can display the surname + first name in the combo by concatenating the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

 

[Guest]

Hi Allen,
Thanks for both the explanation and link
Dermot

You can display the surname + first name in the combo by concatenating the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox control,
so
that when I click on a name in the dropdown list, it returns the selected
individuals contact Details in either a Labels or Text boxs on the form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Guest]

Hi Allen,
I have tried the SQL Statement in the combo box Row Source Property as you
suggested....but still don't get the drop down list.
Please advise further.
My table is called tblStaffNumbers
The primary key field is Employee ID

I place the following sql statement in the combo box row source propety, but
this does not produce a dropdownlist.
(Copy and Pasted from cbo Row source))
SELECT Employee ID, LastName &" " & FirstName As FullName From
[tblContacts]ORDER BY LastName,FirstName

I will get the better of Access yet!! but it's a struggle.
I am not sure if I am entering the statement correctly (Syntax) or mmm
puzzled!!

You can display the surname + first name in the combo by concatenating the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox control,
so
that when I click on a name in the dropdown list, it returns the selected
individuals contact Details in either a Labels or Text boxs on the form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Guest]

Try making the column width of the first column (Emplyee ID) "0" and the
second column maybe"1". In property "Column Widths" you would type something
like 0";1"
THen make sure the List Width is at least 1"

HTH
--
sam

Hi Allen,
I have tried the SQL Statement in the combo box Row Source Property as you
suggested....but still don't get the drop down list.
Please advise further.
My table is called tblStaffNumbers
The primary key field is Employee ID

I place the following sql statement in the combo box row source propety, but
this does not produce a dropdownlist.
(Copy and Pasted from cbo Row source))
SELECT Employee ID, LastName &" " & FirstName As FullName From
[tblContacts]ORDER BY LastName,FirstName

I will get the better of Access yet!! but it's a struggle.
I am not sure if I am entering the statement correctly (Syntax) or mmm
puzzled!!

You can display the surname + first name in the combo by concatenating the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox control,
so
that when I click on a name in the dropdown list, it returns the selected
individuals contact Details in either a Labels or Text boxs on the form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Allen Browne]

Your field name contains a space, so you must wrap it in square brackets:
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

If you prefer, you can create a query to do this, save it, and then use the
query name in the combo's RowSource property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I have tried the SQL Statement in the combo box Row Source Property as you
suggested....but still don't get the drop down list.
Please advise further.
My table is called tblStaffNumbers
The primary key field is Employee ID

I place the following sql statement in the combo box row source propety,
but
this does not produce a dropdownlist.
(Copy and Pasted from cbo Row source))
SELECT Employee ID, LastName &" " & FirstName As FullName From
[tblContacts]ORDER BY LastName,FirstName

I will get the better of Access yet!! but it's a struggle.
I am not sure if I am entering the statement correctly (Syntax) or mmm
puzzled!!

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Guest]

Allen Please be Patient With Me!!
I want to understand this........which does contain all the information in
my tblContacts, Employee ID, FirstName, LastName.....as typed here. Also the
Employee ID is an AutoNumber, the other being Text.

As you suggested to me I entered into the cbo row source property:-
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

But still it wouldn't work.

So tried your second option by creating a query to concatenate the names and
then using the cbo wizard to insert the cbo control on the form....this
worked and I got my list.

Row Source Property Entered By Query Wizard
SELECT qryLastNameFirst.LastNameFirst FROM qryLastNameFirst;

QryLastNameFirst SQL Statement
SELECT [LastName] & IIf([FirstName] Is Not Null,", " & [FirstName],"") AS
LastNameFirst
FROM tblEmployees;

I would like to understand where you think I might be going wrong with
respect to the first method you suggested to me. I manually place the cbo on
the form and then enter the Row source property as suggested. I will
understand if I have you confused by now!!! I know it's got to be something
silly I am missing....the annoying thing is I have done this before.....and
kept a note in a save place!!!

Your field name contains a space, so you must wrap it in square brackets:
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

If you prefer, you can create a query to do this, save it, and then use the
query name in the combo's RowSource property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I have tried the SQL Statement in the combo box Row Source Property as you
suggested....but still don't get the drop down list.
Please advise further.
My table is called tblStaffNumbers
The primary key field is Employee ID

I place the following sql statement in the combo box row source propety,
but
this does not produce a dropdownlist.
(Copy and Pasted from cbo Row source))
SELECT Employee ID, LastName &" " & FirstName As FullName From
[tblContacts]ORDER BY LastName,FirstName

I will get the better of Access yet!! but it's a struggle.
I am not sure if I am entering the statement correctly (Syntax) or mmm
puzzled!!

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Allen Browne]

1. Create a new query that uses the tblContacts table.

2. Drag the EmployeeID from the upper pane into the grid.

3. In the next column of the grid, in the Field row, enter:
FullName: [LastName] & " " & [FirstName]

4. Test that the query works.

5. To specify the sorting, drag LastName into the grid.
In the Sorting row under this field, choose Ascending.
Uncheck the box in the Show row.

6. Repeat setp 5 for the FirstName field.

7. Test again: you should see 2 columns (the id and the full name), sorted
by surname and then firstname.

8. Save the query. Close.

9. Open your form in deisgn view.

10. Set the RowSource property of your combo to the name of the query saved
at step 8.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Please be Patient With Me!!
I want to understand this........which does contain all the information in
my tblContacts, Employee ID, FirstName, LastName.....as typed here. Also
the
Employee ID is an AutoNumber, the other being Text.

As you suggested to me I entered into the cbo row source property:-
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

But still it wouldn't work.

So tried your second option by creating a query to concatenate the names
and
then using the cbo wizard to insert the cbo control on the form....this
worked and I got my list.

Row Source Property Entered By Query Wizard
SELECT qryLastNameFirst.LastNameFirst FROM qryLastNameFirst;

QryLastNameFirst SQL Statement
SELECT [LastName] & IIf([FirstName] Is Not Null,", " & [FirstName],"") AS
LastNameFirst
FROM tblEmployees;

I would like to understand where you think I might be going wrong with
respect to the first method you suggested to me. I manually place the cbo
on
the form and then enter the Row source property as suggested. I will
understand if I have you confused by now!!! I know it's got to be
something
silly I am missing....the annoying thing is I have done this
before.....and
kept a note in a save place!!!

Your field name contains a space, so you must wrap it in square brackets:
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

If you prefer, you can create a query to do this, save it, and then use
the
query name in the combo's RowSource property.

Hi Allen,
I have tried the SQL Statement in the combo box Row Source Property as
you
suggested....but still don't get the drop down list.
Please advise further.
My table is called tblStaffNumbers
The primary key field is Employee ID

I place the following sql statement in the combo box row source
propety,
but
this does not produce a dropdownlist.
(Copy and Pasted from cbo Row source))
SELECT Employee ID, LastName &" " & FirstName As FullName From
[tblContacts]ORDER BY LastName,FirstName

I will get the better of Access yet!! but it's a struggle.
I am not sure if I am entering the statement correctly (Syntax) or mmm
puzzled!!



:

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for
the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described combobox?

 

[Guest]

Thanks Allen,
I can see now where I went wrong.
I have save you explanation...in a safer place!!
I will have a look at the cbo as a record selector on your site next, but I
need to get some kip now
Best wishes
Dermot

1. Create a new query that uses the tblContacts table.

2. Drag the EmployeeID from the upper pane into the grid.

3. In the next column of the grid, in the Field row, enter:
FullName: [LastName] & " " & [FirstName]

4. Test that the query works.

5. To specify the sorting, drag LastName into the grid.
In the Sorting row under this field, choose Ascending.
Uncheck the box in the Show row.

6. Repeat setp 5 for the FirstName field.

7. Test again: you should see 2 columns (the id and the full name), sorted
by surname and then firstname.

8. Save the query. Close.

9. Open your form in deisgn view.

10. Set the RowSource property of your combo to the name of the query saved
at step 8.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Please be Patient With Me!!
I want to understand this........which does contain all the information in
my tblContacts, Employee ID, FirstName, LastName.....as typed here. Also
the
Employee ID is an AutoNumber, the other being Text.

As you suggested to me I entered into the cbo row source property:-
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

But still it wouldn't work.

So tried your second option by creating a query to concatenate the names
and
then using the cbo wizard to insert the cbo control on the form....this
worked and I got my list.

Row Source Property Entered By Query Wizard
SELECT qryLastNameFirst.LastNameFirst FROM qryLastNameFirst;

QryLastNameFirst SQL Statement
SELECT [LastName] & IIf([FirstName] Is Not Null,", " & [FirstName],"") AS
LastNameFirst
FROM tblEmployees;

I would like to understand where you think I might be going wrong with
respect to the first method you suggested to me. I manually place the cbo
on
the form and then enter the Row source property as suggested. I will
understand if I have you confused by now!!! I know it's got to be
something
silly I am missing....the annoying thing is I have done this
before.....and
kept a note in a save place!!!

Your field name contains a space, so you must wrap it in square brackets:
SELECT [Employee ID], LastName & " " & FirstName As FullName
FROM tblContacts ORDER BY LastName, FirstName;

If you prefer, you can create a query to do this, save it, and then use
the
query name in the combo's RowSource property.

Hi Allen,
I have tried the SQL Statement in the combo box Row Source Property as
you
suggested....but still don't get the drop down list.
Please advise further.
My table is called tblStaffNumbers
The primary key field is Employee ID

I place the following sql statement in the combo box row source
propety,
but
this does not produce a dropdownlist.
(Copy and Pasted from cbo Row source))
SELECT Employee ID, LastName &" " & FirstName As FullName From
[tblContacts]ORDER BY LastName,FirstName

I will get the better of Access yet!! but it's a struggle.
I am not sure if I am entering the statement correctly (Syntax) or mmm
puzzled!!



:

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for
the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described combobox?

 

[Guest]

Hi Allen
Thanks for the link to the cboSelector....I am using it already to select
internal and external phone numbers using lastnamefirst.
One question I wondered about....what is the difference between using vba in
the record source property and entering it in the vba editor....it woiuld
seem to achieve the same goal but there must be a difference...would it be
that the record source property is limited to a small code size?

You can display the surname + first name in the combo by concatenating the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox control,
so
that when I click on a name in the dropdown list, it returns the selected
individuals contact Details in either a Labels or Text boxs on the form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Allen Browne]

Not sure I understand this question.

The form's RecordSource property is a string. The string needs to be a valid
SQL statement (or the name of a table or query.)

If it is a simple string, just type it straight into the Properties box in
the RecordSource property.

If you would like the query builder to help you, click the Build button
(...) beside the property, and design the query graphically.

If the query statement needs to change based on other circumstances (such as
which criteria boxes the user entered something in, or selectively using a
subquery to limit the form to matching records in another table), then you
will want to use VBA code to create the string and assign it at runtime.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
Thanks for the link to the cboSelector....I am using it already to select
internal and external phone numbers using lastnamefirst.
One question I wondered about....what is the difference between using vba
in
the record source property and entering it in the vba editor....it woiuld
seem to achieve the same goal but there must be a difference...would it be
that the record source property is limited to a small code size?

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Guest]

Thanks Allen,
Quote
Not sure I understand this question.

It was a bit of a silly question.....I asked it before thinking!!
For simple queries use a simple sql statement in the record source property.
For more complex queries, use vba programming.....

I haven't quite worked out how to use the Query Builder via ... , any time I
have tried using it I am unsucess, up to now I have really taken advise on
what to enter....would you have any tutorial links explaining how to use it
properly?
If this should be a nother posting let me know and I will repost fresh.
Thanks again
Dermot

Not sure I understand this question.

The form's RecordSource property is a string. The string needs to be a valid
SQL statement (or the name of a table or query.)

If it is a simple string, just type it straight into the Properties box in
the RecordSource property.

If you would like the query builder to help you, click the Build button
(...) beside the property, and design the query graphically.

If the query statement needs to change based on other circumstances (such as
which criteria boxes the user entered something in, or selectively using a
subquery to limit the form to matching records in another table), then you
will want to use VBA code to create the string and assign it at runtime.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
Thanks for the link to the cboSelector....I am using it already to select
internal and external phone numbers using lastnamefirst.
One question I wondered about....what is the difference between using vba
in
the record source property and entering it in the vba editor....it woiuld
seem to achieve the same goal but there must be a difference...would it be
that the record source property is limited to a small code size?

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Allen Browne]

You do not know how to use the query design window in Access?

Any basic book on Access will help you with that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,
Quote
Not sure I understand this question.

It was a bit of a silly question.....I asked it before thinking!!
For simple queries use a simple sql statement in the record source
property.
For more complex queries, use vba programming.....

I haven't quite worked out how to use the Query Builder via ... , any time
I
have tried using it I am unsucess, up to now I have really taken advise on
what to enter....would you have any tutorial links explaining how to use
it
properly?
If this should be a nother posting let me know and I will repost fresh.
Thanks again
Dermot

Not sure I understand this question.

The form's RecordSource property is a string. The string needs to be a
valid
SQL statement (or the name of a table or query.)

If it is a simple string, just type it straight into the Properties box
in
the RecordSource property.

If you would like the query builder to help you, click the Build button
(...) beside the property, and design the query graphically.

If the query statement needs to change based on other circumstances (such
as
which criteria boxes the user entered something in, or selectively using
a
subquery to limit the form to matching records in another table), then
you
will want to use VBA code to create the string and assign it at runtime.

Hi Allen
Thanks for the link to the cboSelector....I am using it already to
select
internal and external phone numbers using lastnamefirst.
One question I wondered about....what is the difference between using
vba
in
the record source property and entering it in the vba editor....it
woiuld
seem to achieve the same goal but there must be a difference...would it
be
that the record source property is limited to a small code size?
:

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for
the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described
combobox?

 

[Guest]

Well, it's not entirely true that I don't know how to use it.
It's probably lazyness / slow reading on my part. I have several excellent
books, Mastering Access 2002 Premium Edition, Access InsideOut 2002 by Helen
Feddema and VBA 2000 by Suzanne Novalis, so I really need as slap on the
hand!!
Rather just looking for any tips that would help me reason it out that
constantly refer to the books.
Regards
Dermot

You do not know how to use the query design window in Access?

Any basic book on Access will help you with that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,
Quote
Not sure I understand this question.

It was a bit of a silly question.....I asked it before thinking!!
For simple queries use a simple sql statement in the record source
property.
For more complex queries, use vba programming.....

I haven't quite worked out how to use the Query Builder via ... , any time
I
have tried using it I am unsucess, up to now I have really taken advise on
what to enter....would you have any tutorial links explaining how to use
it
properly?
If this should be a nother posting let me know and I will repost fresh.
Thanks again
Dermot

Not sure I understand this question.

The form's RecordSource property is a string. The string needs to be a
valid
SQL statement (or the name of a table or query.)

If it is a simple string, just type it straight into the Properties box
in
the RecordSource property.

If you would like the query builder to help you, click the Build button
(...) beside the property, and design the query graphically.

If the query statement needs to change based on other circumstances (such
as
which criteria boxes the user entered something in, or selectively using
a
subquery to limit the form to matching records in another table), then
you
will want to use VBA code to create the string and assign it at runtime.

Hi Allen
Thanks for the link to the cboSelector....I am using it already to
select
internal and external phone numbers using lastnamefirst.
One question I wondered about....what is the difference between using
vba
in
the record source property and entering it in the vba editor....it
woiuld
seem to achieve the same goal but there must be a difference...would it
be
that the record source property is limited to a small code size?
:

You can display the surname + first name in the combo by concatenating
the 2
fields in the RowSource property of the combo.

Assuming your table has an AutoNumber primary key field named ID, you
might
set the combo's RowSource to something like this:
SELECT ID, LastName & " " & FirstName AS FullName
FROM [Contact Numbers] ORDER BY LastName, FirstName
Other propertiers for the combo:
Column Count 2
Column Widths 0
Control Source {leave this blank!}

It sounds like you want to use this combo to move to the record for
the
names selected. If so, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If I have a Flat table "Contact Numbers" (No Relationships)
Which has within it fields "FirstName" and "LastName"
I am using an SQL statement to concatenate both of the above name
fields
as
"LastName First" separated by a comma.....ie..Bloggs, Joe

How do I incorporate the "LastName First" field into a combobox
control,
so
that when I click on a name in the dropdown list, it returns the
selected
individuals contact Details in either a Labels or Text boxs on the
form,
or
any other recommended way of returning the contact details from the
described
combobox?