combo box filtering

[andyukcy]

Hi !

I have a subform showing in datasheet. There, i have a combobox where i select customers. In the next line, i do not want the user to be able to select the same customer as thsi would create duplicate data. How can the drop down menu of the second lise remove what was selected in the first row?

Andy

 

[PHV]

What are the SQL code of the subform's RecordSource and the combo's RowSource ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[andyukcy]

SQL code of the subform's RecordSource


SELECT payments.DatabaseCustomerId, Customer.CustomerName, Customer.CustomerSurname, payments.PaymentId, payments.Amount, payments.Information,
FROM invoice INNER JOIN (Customer INNER JOIN payments ON Customer.DatabaseCustomerId = payments.DatabaseCustomerId) ON invoice.PaymentId = payments.PaymentId
WHERE (((payments.PaymentId)=[Forms]![invoice]![PaymentId]));


combo's RowSource

SELECT Customer.DatabaseCustomerId, Customer.CustomerName, Customer.CustomerSurname, Customer.CustomerHomePhoneNumber
FROM Customer
WHERE (((Customer.CustomerHomePhoneNumber)=[Forms]![Customer]![CustomerHomePhoneNumber]));


What i want is whenever i select a customer in subform , i will not be able to select it again i.e. be removed from the list for that payment session.

 

[PHV]

You may try something like this in the BeforeUpdate event procedure of the combo:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "DatabaseCustomerId='" & Me![name of combo].Column(0) & "'"
If Not rs.NoMatch Then
MsgBox "Customer already treated"
Cancel = True
End If
Set rs = Nothing

If DatabaseCustomerId is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[andyukcy]

I get a "User defined type not specified" error message at Dim rs As DAO.Recordset

 

[andyukcy]

Private Sub Student_Id_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "DatabaseCustomerId=" & Me![Student Id].Column(0) & ""
If Not rs.NoMatch Then
MsgBox "Customer already treated"
Cancel = True
End If
Set rs = Nothing

End Sub

 

[PHV]

You have to add a reference to the Microsoft DAO 3.# Object Library:
menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886