Pull Downs

[Guest]

I have two pull downs, the second pull down feeds off from the first one.

First one has the critera to search by, company name, phone number, etc.

Second pull down displays the data.

When I have two records with the same company name, it only displays ONE,
not both. How do I get it to display both?

If you need more explanation, let me know.

Curtis

 

[Damon Heron]

Use a subform instead of the second combobox.

Damon

 

[Guest]

Maybe I'm not explaining it very well, these two pulls downs are used to pull
up a record. I select company name, then select one, which then displays the
info on that form, the form these two pull downs are on.

Pull Down 1: Search By
Pull Down 2: Search For

Curtis

 

[Damon Heron]

By "pull downs" I am assuming you mean comboboxes, right? My answer would
still work by using a sub form to display more than one record for a single
company. Your main form has a record source of the company table, right?
Then is there another table that has the info you are looking for? Like
Company table, then order table, as an example? The subform would show the
orders for that company ID if there is a foreign id in the order table to
companyid. the Master and Child link would be CompanyID, and the subform
would show all records for that company.

Damon

 

[Guest]

Yes combo boxes. I don't think you would want to use the second combo as a
subform, because when you select a record in the second combo, it then
displays that data for that record in the form, the same form the two combo
boxes are in.

Combo 1: Source is a query I made that has the categorys I want, company
name, phone, etc.

Combo 2: Source is table/query with this:

SELECT DISTINCT [Contact] FROM SearchByPotentialQuery ORDER BY [Contact];

Do you want me to upload a blank database on the Internet that has all my
forms but no data?

Thanks
Curtis

 

[Damon Heron]

No, you misunderstand my last answer. If both combo boxes act as filters to
limit the data you want displayed, then leave them as is. But add a subform
to display the data you want - your original question said that only one
record was displayed when there may be more than one for each company.
(incidently, a combobox can't be a subform, they are two different objects).
I am not totally clear on what you are trying to accomplish. How many
tables are involved? Are the tables relational? Access is a relational
database application. The second combo in your example filters the results
of the first combo by??? or vice versa?

Here is an example: 1st combobox selects Company "A", 2nd combobox uses
result of 1st ("A") and shows all company A contacts.
In second combobox, select one of the contacts. In the after update event
display the results in the subform. It may be one or many records for that
contact.... Are we on the same page?

Damon

Yes combo boxes. I don't think you would want to use the second combo as
a
subform, because when you select a record in the second combo, it then
displays that data for that record in the form, the same form the two
combo
boxes are in.

Combo 1: Source is a query I made that has the categorys I want, company
name, phone, etc.

Combo 2: Source is table/query with this:

SELECT DISTINCT [Contact] FROM SearchByPotentialQuery ORDER BY [Contact];

Do you want me to upload a blank database on the Internet that has all my
forms but no data?

Thanks
Curtis

By "pull downs" I am assuming you mean comboboxes, right? My answer
would
still work by using a sub form to display more than one record for a
single
company. Your main form has a record source of the company table, right?
Then is there another table that has the info you are looking for? Like
Company table, then order table, as an example? The subform would show
the
orders for that company ID if there is a foreign id in the order table to
companyid. the Master and Child link would be CompanyID, and the subform
would show all records for that company.

Damon

message

 

[Guest]

If you want a combo box based on another combo by see

http://office.microsoft.com/en-ca/access/HA011730581033.aspx?pid=CH063650361033

or

http://www.mvps.org/access/forms/frm0028.htm

or

www.rogersaccesslibrary.com - CascadingComboBoxes.mdb

See Combobox database in C:\All_My_Stuff\Access_examples\combobox

 

[Guest]

Here is an example: 1st combobox selects Company "A", 2nd combobox uses

result of 1st ("A") and shows all company A contacts.
In second combobox, select one of the contacts. In the after update event
display the results in the subform. It may be one or many records for that
contact.... Are we on the same page?

This is how it works. However, the results in combo box #2, if there are
two records with the same name, lets say a customer has two accounts with us,
same company name, different dba names. So when I choose company name, there
are two records with ABC Flowers, but the combo box will only display one to
select, not both (abc flowers & abc flowers below it)

Curtis

 

[Damon Heron]

Okay, so CBox1 has two companies listed. If they are, in fact, different,
then it should display two records. If they are the same, then your table
is not set up correctly. There must be a reason to have two companies with
the same name, otherwise, why do it? Sounds like you need to re-think your
table structure and the relationships between your tables.

A combobox ONLY allows selection of one item. There is a multiselect
listbox object that may be what you are thinking of, but that will require
some code to make it work properly.

Damon

 

[Guest]

I have one master table that holds all records with relationalship tables
pointing to it, etc. They are two individual records. This merchant has two
accounts or records as he has two businesses, two web sites he runs, dba
names are the domain names, but company names are the same for both. But he
has an account for each and wont go into details as to why that must be.

However you want to think about it, they are SEPERATE records, just as if it
was customer a and customer b with same business names by chance, as we deal
with customers all over the US.

That's the problem, it isn't displaying both records, if they have the same
name or same text info you are searching for, even though they are SEPERATE
records in your table, but simply same company name.

Curtis

 

[Damon Heron]

Okay, I think I understand now. What is your event code after a selection
is made in combo1? Are you setting the rowsource for combo2 and doing a
requery? Secondly, is the data in Combo2 from the same table as combo1?

Damon

 

[Guest]

Combo 1 = Search By
Combo 2 = Search For


Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"

End Sub

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

 

[Damon Heron]

What is your source for Combo1? I get an error because you are using the
rowsource in the From statement
put a debug.print at the end of this code and see if it says what you think
it should say...

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"

Here is what i get when I do the debug.print : SELECT DISTINCT [mytable]
FROM [1] ORDER BY [1]
I would do it differently:

var1=me.SearchFor.Text
Me.SearchFor.RowSource = "SELECT mytable.ID,mytable.Name, mytable.Title,
mytable.Extension, from mytable where ((mytable.Name)= " & "'" & Var1 & "'"
& ")"
me.searchfor.requery

The results in the second combobox are then all of the names that are the
same.
Since I don't know what your tables look like or what the row source of
Searchby is, this is just guesswork on my part.

Damon

Combo 1 = Search By
Combo 2 = Search For


Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"

End Sub

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] &
Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

 

[Guest]

Combo 1:

Row Source Type: Field List
Row Soure: Query123

Query 123 has 6 columns, all the options I want to be able to pick from,
dba, company, contact, etc. These are all fields in my master table,
customers.

Does this help?

Curtis

What is your source for Combo1? I get an error because you are using the
rowsource in the From statement
put a debug.print at the end of this code and see if it says what you think
it should say...

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"

Here is what i get when I do the debug.print : SELECT DISTINCT [mytable]
FROM [1] ORDER BY [1]
I would do it differently:

var1=me.SearchFor.Text
Me.SearchFor.RowSource = "SELECT mytable.ID,mytable.Name, mytable.Title,
mytable.Extension, from mytable where ((mytable.Name)= " & "'" & Var1 & "'"
& ")"
me.searchfor.requery

The results in the second combobox are then all of the names that are the
same.
Since I don't know what your tables look like or what the row source of
Searchby is, this is just guesswork on my part.

Damon

Combo 1 = Search By
Combo 2 = Search For


Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"

End Sub

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] &
Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

 

[Damon Heron]

Combo1 should have a rowsource as Table/query since you are using a query to
fill it.
The form itself should have a record source of your table, with text boxes
for all the fields you want displayed.

My SearchBy event was:
dim var1 as variant
var1=me.SearchFor.Text 'the textname, not the ID
Me.SearchFor.RowSource = "SELECT mytable.ID,mytable.Name, mytable.Title,
'all the fields you want in the combobox
mytable.Extension, from mytable where ((mytable.Name)= " & "'" & Var1 & "'"
& ")"
me.searchfor.

Finally, the SearchFor event:

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[tableID] = " & Str(Nz(Me![searchfor], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

The above works fine for me. I select a name (that has more than one
instance in my table) in the first combo box, then the second shows all
instances of the name I selected, I then select one of them, and the record
info is displayed on the form.

Is this what you want?

Damon

 

[Guest]

Combo1 can't be table/query as it will then show all the data in that query,
must e field list for it to show just the name of each column or row in the
table.

I'm using a query for combo1 so I can specify which records I want to show
up, such as ALL customers in database, only prospects, dead leads, etc. The
whole reason behind a query & why I'm using that.

Curtis

 

[Damon Heron]

Well, that's it, I give up. I went back and re-read the posts and its like
you are purposely withholding info. At the very first post you could have
explained all of this succinctly if you had wanted an answer.

Have a nice day,

Damon

 

[Guest]

I'm no expert on access, don't know what all info you need, tried to give you
everything I know to give, etc. From what I can see & based on my knowledge,
it has to do with the after update codes I posted before, but do not know the
code well enough to know what the cause is.

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"

End Sub

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

 

[Ron2006]

Take off the "DISTINCT" in the second combo row source. That is what
is causing only one to appear.

The distinct should be in the first combo but NOT the second.

Ron

 

[Guest]

I try that, did a search, it showed both, but the same record came up when I
clicked on both. Then I reselect company from combo one and it isn't showing
both anymore, access put distinct back in there.

????