Combobox item to be removed after entry is made

[Krs13]

Hi,

I have a table with Auto number as Id. This id is being linked to all tables as well as forms. Now i have a form with combobox which shows Id(linking two tables), when i select the id the details like name,age automatically appear in the fields. I could do that, but the problem is if i select the same ID again it becomes a duplicate entry or wrong entry. How to overcome this situation.

Can we remove the item from Combobox after an entry is made for that or make a form with subform and filter the Id's is subform.

Can somebody help pls. Thanks

[maximus]

This is what I have done:

I have two Tables:
1) tblEmp: {EmpID(PK Autonumber), EmpName, EmpDOB, EmpAddress, Selected (Yes/No)}
2) tblCourseDetails {IdCourseID(PK Autonumber), EmpName, EmpDOB, EmpAddress, CourseDetails}

I have a form RecordSource bound to tblCourseDetails. I have a ComboBoxin which if you select an employee name it auto fills EmpName, EmpDOB, EmpAddress on the form. Moreover once a name is selected the name is removed from the Combobox List.


How this is done:

The RowSource of the Combobox is table tblEmp is:

SELECT [tblEmp].[EmpID], [tblEmp].[EmpName], [tblEmp].[EmpDOB], [tblEmp].[EmpAddress], [tblEmp].[Selected] FROM tblEmp WHERE ((([tblEmp].[Selected])=False));

Here the condition of Selected is =False

Now I have put this following code in the AfterUpdate Event of the Combox:

Private Sub Combo12_AfterUpdate()
Dim intEmpID As Integer
Dim strSQL As String

intEmpID = Me.Combo12

Me.EmpName = Me.Combo12.Column(1)
Me.EmpDOB = Me.Combo12.Column(2)
Me.EmpAddress = Me.Combo12.Column(3)



strSQL = "Update tblEmp Set Selected=True Where EmpID=" & intEmpID
CurrentDb.Execute strSQL, dbFailOnError

Me.Combo12.Requery
End Sub


This code auto fills the Textboxes on the form and then Runs an Sql and updates the tblEmp making selecetd true for the ID of the employee thats is selected, thus removing it from the list.

The name once selected in the combobox will not appear until and unless Selected Field is updated to False again. So if u want all the names to be available when u open the form again after Closing u have to make sure all selected fields are updated to false.


I am attaching a sample mdb file

Please mark the thread solved if this solves you problem.

[Krs13]

Hi Maximum,

Sorry for very late reply. Thank you so much for your help, it solved my problem.