Remove duplicate entry in combobox

[Guest]

I'm BaaAAaaack!

Well, thanks to all the folks who have helped me by posting their questions
and getting answers etc, I can now set up a basic search using a combobox to
pick the criteria.
The problem I am running into now is that if I try to set up a combo box
based on entries in a sub-table, I get duplicate entries in the combo box.
When I pick one of the entries, I only get the report based on that one
entry. Example: I have a database that tracks deliveries to my area. The
main form is for entering the driver's information and is the "parent" table.
The "child" table is one that tracks the destination of the delivery. I
have multiple drivers/vendors that deliver to the same places. I want to be
able to generate a report based on the number of deliveries to the facility.
When I open the report and get the unbound form/combo box if I have multiple
deliveries to a facility by one or more drivers, I get multiple entries of
the same facility in the combo box.
My question is is there a way to set this up with a combo box or other means
without prompting the user to type in the location? The field name is
Destination so if you have something that will work, please use that as the
field name. Thanks again for everyone's help to this point.

Chaz

 

[Guest]

Make your rowsource for your combo something like this(with the correct
names, of course):

"SELECT DISTINCT Destination FROM MyChildTable;"

It will then return only one occurance of each destination in the table.

 

[Guest]

Klatuu,

I tried what you suggested and cannot seem to get it to work. If I only use
the "Destination" field I get the desired results - one entry in the combo
box. However, when the report opens, it is blank, even for those entries
that occur only once in the entire subtable. If I include the primary key
field (oddly enough it's called [PrimaryKey]) without the Select Distinct I
get a populated report but there are multiple entries in the combo box for
the destinations that have been visited more than once. Each one only pulls
up the information associated with its PrimaryKey.
I also tried using Select on the PrimaryKey and Select Distinct on the
Destination fields but could not get it set up properly. I'm fairly familiar
with a lot of different coding commands but this is one I've never used
before (just like combo boxes). If there is an easier way to input this in
the SQL/Query modules, I'd like to know it as I was actually typing the
statements into the rowsource box of my combo box.
By the way, I don't know if this will make a difference but the Combo box
and the form it is on are both unbound.
If I take the coding out of the report that opens the combo box form I am
prompted for the information. When I manually enter it I get all the parent
records associated with the entry with is on a subtable. For instance, I've
input sample data into the database using forms I created. Lets say I have
Dorm 1 used one time in the Destination field and Dorm 2 is used at least
twice. If I enter Dorm 1 manually, I get the single (parent table record)
associated with it. Dorm 2 yields all the records on the parent table
associated with it. I want the same results using a combo box or something
similar so that typos won't get in the way of record searching.

Thanks for the assist,
Chaz

 

[Guest]

I think I'm having a little trouble understanding the issue. If you want to
present your report based on a specific location, then the method I propsed
will provide that capability. I don't understand how the primary key figures
into this. The usual method is to filter the report using the value in the
combo using the Where argument of the OpenReport method.

Klatuu,

I tried what you suggested and cannot seem to get it to work. If I only use
the "Destination" field I get the desired results - one entry in the combo
box. However, when the report opens, it is blank, even for those entries
that occur only once in the entire subtable. If I include the primary key
field (oddly enough it's called [PrimaryKey]) without the Select Distinct I
get a populated report but there are multiple entries in the combo box for
the destinations that have been visited more than once. Each one only pulls
up the information associated with its PrimaryKey.
I also tried using Select on the PrimaryKey and Select Distinct on the
Destination fields but could not get it set up properly. I'm fairly familiar
with a lot of different coding commands but this is one I've never used
before (just like combo boxes). If there is an easier way to input this in
the SQL/Query modules, I'd like to know it as I was actually typing the
statements into the rowsource box of my combo box.
By the way, I don't know if this will make a difference but the Combo box
and the form it is on are both unbound.
If I take the coding out of the report that opens the combo box form I am
prompted for the information. When I manually enter it I get all the parent
records associated with the entry with is on a subtable. For instance, I've
input sample data into the database using forms I created. Lets say I have
Dorm 1 used one time in the Destination field and Dorm 2 is used at least
twice. If I enter Dorm 1 manually, I get the single (parent table record)
associated with it. Dorm 2 yields all the records on the parent table
associated with it. I want the same results using a combo box or something
similar so that typos won't get in the way of record searching.

Thanks for the assist,
Chaz

Make your rowsource for your combo something like this(with the correct
names, of course):

"SELECT DISTINCT Destination FROM MyChildTable;"

It will then return only one occurance of each destination in the table.

 

[Guest]

Ahhhhhh (he said as the light went on),

This is the first time I've ever worked with combo boxes and unbound forms.
I'm doing things with this database I've never done before. I'm used to
having the report based on a query where the user (me for the most part) had
to enter the criteria. I'll look up openreport and Where arguement. For the
most part, I see no reason why the help you provided hasn't at the very least
pointed me in the right direction. Thanks as always.

Chaz

I think I'm having a little trouble understanding the issue. If you want to
present your report based on a specific location, then the method I propsed
will provide that capability. I don't understand how the primary key figures
into this. The usual method is to filter the report using the value in the
combo using the Where argument of the OpenReport method.

Klatuu,

I tried what you suggested and cannot seem to get it to work. If I only use
the "Destination" field I get the desired results - one entry in the combo
box. However, when the report opens, it is blank, even for those entries
that occur only once in the entire subtable. If I include the primary key
field (oddly enough it's called [PrimaryKey]) without the Select Distinct I
get a populated report but there are multiple entries in the combo box for
the destinations that have been visited more than once. Each one only pulls
up the information associated with its PrimaryKey.
I also tried using Select on the PrimaryKey and Select Distinct on the
Destination fields but could not get it set up properly. I'm fairly familiar
with a lot of different coding commands but this is one I've never used
before (just like combo boxes). If there is an easier way to input this in
the SQL/Query modules, I'd like to know it as I was actually typing the
statements into the rowsource box of my combo box.
By the way, I don't know if this will make a difference but the Combo box
and the form it is on are both unbound.
If I take the coding out of the report that opens the combo box form I am
prompted for the information. When I manually enter it I get all the parent
records associated with the entry with is on a subtable. For instance, I've
input sample data into the database using forms I created. Lets say I have
Dorm 1 used one time in the Destination field and Dorm 2 is used at least
twice. If I enter Dorm 1 manually, I get the single (parent table record)
associated with it. Dorm 2 yields all the records on the parent table
associated with it. I want the same results using a combo box or something
similar so that typos won't get in the way of record searching.

Thanks for the assist,
Chaz

Make your rowsource for your combo something like this(with the correct
names, of course):

"SELECT DISTINCT Destination FROM MyChildTable;"

It will then return only one occurance of each destination in the table.

:

I'm BaaAAaaack!

Well, thanks to all the folks who have helped me by posting their questions
and getting answers etc, I can now set up a basic search using a combobox to
pick the criteria.
The problem I am running into now is that if I try to set up a combo box
based on entries in a sub-table, I get duplicate entries in the combo box.
When I pick one of the entries, I only get the report based on that one
entry. Example: I have a database that tracks deliveries to my area. The
main form is for entering the driver's information and is the "parent" table.
The "child" table is one that tracks the destination of the delivery. I
have multiple drivers/vendors that deliver to the same places. I want to be
able to generate a report based on the number of deliveries to the facility.
When I open the report and get the unbound form/combo box if I have multiple
deliveries to a facility by one or more drivers, I get multiple entries of
the same facility in the combo box.
My question is is there a way to set this up with a combo box or other means
without prompting the user to type in the location? The field name is
Destination so if you have something that will work, please use that as the
field name. Thanks again for everyone's help to this point.

Chaz

 

[Guest]

Well,

I thought I would be able to get this to work. Here is the situation I am
running into now.
When I open the unbound form, I get the single entries as opposed to the
duplicate entries I was getting. However, when I select one which activates
the OpenReport with Where command in the AfterUpdate actions, I get a type
mismatch error. The exact error message is

Run-time error '3464':
Data type mismatch in criteria expression

When I try to debug I hover over the where statement and the value assigned
is numerical based on the order of the selections in the combo box. I select
the first the value is 0, second is 1, etc. How do I code the OpenReport
command to utilize the Text/Number "value" that is actually in the combo box
that the user selects (i.e. "Dorm 12" when the user selects Dorm 12 from the
combo box)? I've tried using some of the other variations I see in the
discussion group but I'm not getting anywhere. The code I have in the
AfterUpdate of the Combo Box is

DoCmd.OpenReport "DestinationSingleRPT", acViewPreview, , "[Destination] = "
& Me!DestinationSearchCMB

I've tried using
DoCmd.OpenReport "DestinationSingleRPT", acViewPreview, , "[Destination] =
'" & Me!DestinationSearchCMB & "'" but I don't know what I'm doing wrong.

Help!

Thanks again.

Ahhhhhh (he said as the light went on),

This is the first time I've ever worked with combo boxes and unbound forms.
I'm doing things with this database I've never done before. I'm used to
having the report based on a query where the user (me for the most part) had
to enter the criteria. I'll look up openreport and Where arguement. For the
most part, I see no reason why the help you provided hasn't at the very least
pointed me in the right direction. Thanks as always.

Chaz

I think I'm having a little trouble understanding the issue. If you want to
present your report based on a specific location, then the method I propsed
will provide that capability. I don't understand how the primary key figures
into this. The usual method is to filter the report using the value in the
combo using the Where argument of the OpenReport method.

Klatuu,

I tried what you suggested and cannot seem to get it to work. If I only use
the "Destination" field I get the desired results - one entry in the combo
box. However, when the report opens, it is blank, even for those entries
that occur only once in the entire subtable. If I include the primary key
field (oddly enough it's called [PrimaryKey]) without the Select Distinct I
get a populated report but there are multiple entries in the combo box for
the destinations that have been visited more than once. Each one only pulls
up the information associated with its PrimaryKey.
I also tried using Select on the PrimaryKey and Select Distinct on the
Destination fields but could not get it set up properly. I'm fairly familiar
with a lot of different coding commands but this is one I've never used
before (just like combo boxes). If there is an easier way to input this in
the SQL/Query modules, I'd like to know it as I was actually typing the
statements into the rowsource box of my combo box.
By the way, I don't know if this will make a difference but the Combo box
and the form it is on are both unbound.
If I take the coding out of the report that opens the combo box form I am
prompted for the information. When I manually enter it I get all the parent
records associated with the entry with is on a subtable. For instance, I've
input sample data into the database using forms I created. Lets say I have
Dorm 1 used one time in the Destination field and Dorm 2 is used at least
twice. If I enter Dorm 1 manually, I get the single (parent table record)
associated with it. Dorm 2 yields all the records on the parent table
associated with it. I want the same results using a combo box or something
similar so that typos won't get in the way of record searching.

Thanks for the assist,
Chaz




:

Make your rowsource for your combo something like this(with the correct
names, of course):

"SELECT DISTINCT Destination FROM MyChildTable;"

It will then return only one occurance of each destination in the table.

:

I'm BaaAAaaack!

Well, thanks to all the folks who have helped me by posting their questions
and getting answers etc, I can now set up a basic search using a combobox to
pick the criteria.
The problem I am running into now is that if I try to set up a combo box
based on entries in a sub-table, I get duplicate entries in the combo box.
When I pick one of the entries, I only get the report based on that one
entry. Example: I have a database that tracks deliveries to my area. The
main form is for entering the driver's information and is the "parent" table.
The "child" table is one that tracks the destination of the delivery. I
have multiple drivers/vendors that deliver to the same places. I want to be
able to generate a report based on the number of deliveries to the facility.
When I open the report and get the unbound form/combo box if I have multiple
deliveries to a facility by one or more drivers, I get multiple entries of
the same facility in the combo box.
My question is is there a way to set this up with a combo box or other means
without prompting the user to type in the location? The field name is
Destination so if you have something that will work, please use that as the
field name. Thanks again for everyone's help to this point.

Chaz

 

[Guest]

If anyone else is having the same problem I had regarding data type mismatch
here is an example of how I got the text value vs. numerical value based on
position in combo box.

DoCmd.OpenReport "MyReport", acViewPreview, , "[MyField] = '" &
Me!MyCombo.Text & "'"

Put this in the After Update area of the Combo Box and you should get the
actual "text" from the combo box.

Regards,
Chaz

Ahhhhhh (he said as the light went on),

This is the first time I've ever worked with combo boxes and unbound forms.
I'm doing things with this database I've never done before. I'm used to
having the report based on a query where the user (me for the most part) had
to enter the criteria. I'll look up openreport and Where arguement. For the
most part, I see no reason why the help you provided hasn't at the very least
pointed me in the right direction. Thanks as always.

Chaz

I think I'm having a little trouble understanding the issue. If you want to
present your report based on a specific location, then the method I propsed
will provide that capability. I don't understand how the primary key figures
into this. The usual method is to filter the report using the value in the
combo using the Where argument of the OpenReport method.

Klatuu,

I tried what you suggested and cannot seem to get it to work. If I only use
the "Destination" field I get the desired results - one entry in the combo
box. However, when the report opens, it is blank, even for those entries
that occur only once in the entire subtable. If I include the primary key
field (oddly enough it's called [PrimaryKey]) without the Select Distinct I
get a populated report but there are multiple entries in the combo box for
the destinations that have been visited more than once. Each one only pulls
up the information associated with its PrimaryKey.
I also tried using Select on the PrimaryKey and Select Distinct on the
Destination fields but could not get it set up properly. I'm fairly familiar
with a lot of different coding commands but this is one I've never used
before (just like combo boxes). If there is an easier way to input this in
the SQL/Query modules, I'd like to know it as I was actually typing the
statements into the rowsource box of my combo box.
By the way, I don't know if this will make a difference but the Combo box
and the form it is on are both unbound.
If I take the coding out of the report that opens the combo box form I am
prompted for the information. When I manually enter it I get all the parent
records associated with the entry with is on a subtable. For instance, I've
input sample data into the database using forms I created. Lets say I have
Dorm 1 used one time in the Destination field and Dorm 2 is used at least
twice. If I enter Dorm 1 manually, I get the single (parent table record)
associated with it. Dorm 2 yields all the records on the parent table
associated with it. I want the same results using a combo box or something
similar so that typos won't get in the way of record searching.

Thanks for the assist,
Chaz




:

Make your rowsource for your combo something like this(with the correct
names, of course):

"SELECT DISTINCT Destination FROM MyChildTable;"

It will then return only one occurance of each destination in the table.

:

I'm BaaAAaaack!

Well, thanks to all the folks who have helped me by posting their questions
and getting answers etc, I can now set up a basic search using a combobox to
pick the criteria.
The problem I am running into now is that if I try to set up a combo box
based on entries in a sub-table, I get duplicate entries in the combo box.
When I pick one of the entries, I only get the report based on that one
entry. Example: I have a database that tracks deliveries to my area. The
main form is for entering the driver's information and is the "parent" table.
The "child" table is one that tracks the destination of the delivery. I
have multiple drivers/vendors that deliver to the same places. I want to be
able to generate a report based on the number of deliveries to the facility.
When I open the report and get the unbound form/combo box if I have multiple
deliveries to a facility by one or more drivers, I get multiple entries of
the same facility in the combo box.
My question is is there a way to set this up with a combo box or other means
without prompting the user to type in the location? The field name is
Destination so if you have something that will work, please use that as the
field name. Thanks again for everyone's help to this point.

Chaz