Find record in a table using combo box.

[alvarjo9 via AccessMonster.com]

Hi ,
Please help.
I am trying to create a form containing a Combo Box. This combo box has a
list of Names. When user try to click one name and click the OK buttion, it
should be able to generate the report.

I can't successfuly link the value of the combo box with the query that I
have created. When I ran the form with the combo box that has already the
list of names, it can't display or generate the report.

Please kindly help/assist if any one knows about the approach.

thanks

 

[Allen Browne]

Perhaps there are 2 stages to this:

a) Use the unbound combo to find the right record:
http://allenbrowne.com/ser-03.html

b) Print the form's current record:
http://allenbrowne.com/casu-15.html

If you prefer to use an unbound form, you can combine those 2 steps into
one, i.e. pass the value of the unbound combo as part of the WhereCondition
of OpenReport.

 

[alvarjo9 via AccessMonster.com]

Hi Allen,
Thanks for the help. I have put the code in the combo box. Please help on how
to show distinct Names in the combo box.
I have an error in the Dim rs As DAO.Recordset saying that user defined type
not defined.


The structure of the table is
Table Name: Scores
Field: Name, Date, Score, Calibre, Match and Comment


Basicaly the form will contain
a Combo Box to list the names
b. A text box for user to input the Date
c. An OK and cancel button.

Thanks for your help in advance.
thanks & regards,
Jonathan

Perhaps there are 2 stages to this:

a) Use the unbound combo to find the right record:
http://allenbrowne.com/ser-03.html

b) Print the form's current record:
http://allenbrowne.com/casu-15.html

If you prefer to use an unbound form, you can combine those 2 steps into
one, i.e. pass the value of the unbound combo as part of the WhereCondition
of OpenReport.

Hi ,
Please help.

[quoted text clipped - 10 lines]

thanks

 

[Allen Browne]

To fix the error with the DAO Recordset, open the code window and choose
References on the Tools menu. Check the box beside:
Microsoft DAO 3.6 Library.
More info on solving problems with references:
http://allenbrowne.com/ser-38.html

To show only distinct names, try setting the RowSource for your combo to
something like this:
SELECT DISTINCT Scores.[Name]
FROM Scores
WHERE Scores.[Name] Is Not Null
ORDER BY Scores.[Name];

If you are working in query design view, you can get Access to add the
DISTINCT to the query statement by setting its Unique Values property to
Yes.

Note that almost everything in Access has a Name property, so Access is
likely to confuse the Name of the form with the field called Name on the
form. Similarly, Date is a reserved word, so Access is likely to confuse the
value of the date field with today's date, or get confused about how to
execute the query ("Too complex" error.) If these really are the field
names, I suggest you rename them in the table, and in your form. Before you
do, be sure to uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

For a list of problem names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html
It's extensive, so you may want to refer to the page when you create your
tables.

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

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

Hi Allen,
Thanks for the help. I have put the code in the combo box. Please help on
how
to show distinct Names in the combo box.
I have an error in the Dim rs As DAO.Recordset saying that user defined
type
not defined.


The structure of the table is
Table Name: Scores
Field: Name, Date, Score, Calibre, Match and Comment


Basicaly the form will contain
a Combo Box to list the names
b. A text box for user to input the Date
c. An OK and cancel button.

Thanks for your help in advance.
thanks & regards,
Jonathan

Perhaps there are 2 stages to this:

a) Use the unbound combo to find the right record:
http://allenbrowne.com/ser-03.html

b) Print the form's current record:
http://allenbrowne.com/casu-15.html

If you prefer to use an unbound form, you can combine those 2 steps into
one, i.e. pass the value of the unbound combo as part of the
WhereCondition
of OpenReport.

Hi ,
Please help.

[quoted text clipped - 10 lines]

thanks