Deleting table entries from combo box

[smardonl]

Hi. I am developing a database using Access 2000 and have a problem I can't attempt to explain. I have a form designed with one combo box that the user is supposed to select from and retrieves a series of reports based on their selection. The combo box refers to a table called PI Information which give first and last name and department for employees. My problem is that with each test, the top employee on the list has disappeared. For example, the first used to be Ambulos, but it is now listed as Ames. Other entries are also duplicating in this process such as one employee who is now listed four times. Any suggestions as to why this is doing this would be helpful. Thanks,

L

 

[Norie]

I'd take a look at this table and see if it has the repeats.

If it does you could change the source of the combo to a query based on the table but only containing unique records/values.

 

[SydneyGeek]

Extending Norie's post:

1. Go to the SQL statement behind the combo box
[Row Source -- click the Builder (...) button].
2. Select SQL from the drop-down in Design view (top left of screen)
3. You will see a statement that says SELECT [Something] FROM [Somewhere] ... [Possible other clauses here];
Change SELECT to SELECT DISTINCT. This will give you unique records.
Now go back to Design view. If you haven't already done it, set the Name to Sort Ascending.
Close the SQL statement, save the changes and check it out.

Denis

 

[mdmilner]

I'm hoping the above fixes work because I haven't a clue what is wrong without more information. On my first read of the problem description, I'm wondering what the source table looks like and is it actually being modified.

Mike

 

[smardonl]

Hi all. Thanks for the quick response. First of all, I'll try to explain a little better so maybe it will be clear. Basically, once the user makes a selection from the combo box, it runs a macro which opens a report tied to a query. After the report is opened, the macro then closes the form and closes the query (it was opened on an AfterUpdate event for testing purposes). I have tried requerying the selections and made sure that the row source is defined as a SELECT DISTINCT. I know it is a strange problem and is likely related to something small that I am overlooking, but it is still occuring. Also, if anyone could recommend any good web sites for some Access help or sample stuff, it would be appreciated. THANKS IN ADVANCE!

L

 

[smardonl]

Also, the source table is actually being changed. Sorry, I forgot to mention it...

L

 

[mdmilner]

Ok - off the top of my head, the only way for the source table to receive new data is for it to be edited. Usually either by SQL Append queries (INSERT INTO) or via Recordset objects (rs.AddNew/rs.Update)

From your description, the source table populates the combo box only to provide a list for the users to select. Nothing should be updated or changed - unless maybe you're throwing in a date/time stamp to show when you last ran that particular series of reports.

Just a thought though. How did you arrange to add data to the source table? Does the combo box allow you type a value and add to the table perhaps through a VBA function? And the follow-up, is that code being executed in error?

Mike