How do I "hide" a value on a Combo-box after it is used in a record?

[fettleman]

On my form, I have a combo-box with the record source linked to a table. The items in the table are serial numbers. Each serial number should correspond to one record. The serial number is indexed with No Duplicates.

The problem I am having is that when a serial number is used on a record, and I move to a new record, the previous (used) serial number still displays on the drop-down list as being available. (If the "used" serial number is selected, an error message will appear AFTER attempting to save the record advising that it cannot be saved because it would create duplicate records.)

How do I prevent "used" serial numbers from appearing on the drop-down list on a new record.

Thanks in advance for your time/help.

 

[fneily]

For your Rowsouce of the combobox, use:
SELECT Distinct [serial number] FROM [YourTablename];

Then on the AfterUpdate event of the combobox put:
Private Sub Combob_AfterUpdate()
Dim R As DAO.Recordset
Set R = CurrentDb.OpenRecordset("Select [serial number] From [YourtableName] Where [Serial Number] = " & Me![Combob].Value)
R.Delete
R.Close
Me![Combob].Requery
End Sub

Change YourTableName to the table name.
Change Combob to your combobox name.

 

[TheAceMan1]

How are ya fettleman . . .

[blue] . . . when a serial number is used on a record, and I move to a new record, the previous (used) serial number still displays on the drop-down list as being available.[/blue]

Confusing this. If the serial numbers in the table are all used and you could accomplish your goal, wouldn't you have a [purple]blank combobox?[/purple]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[JerryKlmns]

For your Rowsouce of the combobox, use:

SELECT SN.SerialNumber
FROM tblSerialNumbers As SN LEFT JOIN tblRecordsUsingSerialNumbers As RUSN On
SN.SerialNumber = RUSN.SerialNumber
WHERE RUSN.SerialNumber Is Null;


Then on the AfterUpdate event of the combobox put:

Private Sub Combob_AfterUpdate()
Me![Combob].Requery
End Sub

No deletion