Hi, I have this (sub)form with a

[djeeten]

Hi,

I have this (sub)form with a listbox containing the different products a user can choose from to order. Now, once a user has choosen a product, it needs to be out of the list, or the user should not be able to choose it(giving a message when choosen for example).

A user can of course also delete a product on the subform, after which the product has to be available again in the listbox.

Has anyone had a problem like this before? Any suggestions?

Thanks in advance,

dj, Belgium.

 

[djeeten]

Oops, I just found it myself

This is my code:

Private Sub cboID_BeforeUpdate(Cancel As Integer)
Dim con As Object
Dim rs As Object
Dim stSql As String
Dim found As Integer


Set con = Application.CurrentProject.Connection
stSql = "SELECT tblOrderlines.ProductId FROM tblOrderlines, tblOrders "
stSql = stSql & "WHERE tblOrderlines.OrderId =" & Me!OrderId
Set rs = CreateObject("ADODB.Recordset&quot
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
found = 0
While (Not (rs.EOF)) And found = 0
If rs![ProductId] = Me!cboID Then
MsgBox "This product has already been choosen!"
cboID.Undo
Cancel = 1
found = 1
End If
rs.MoveNext
Wend
End Sub

Thanks anyway ;-)

dj, Belgium.

 

[djeeten]

As I kind of helped myself out of my first problem, someone could perhaps help me with this problem:

On a subform(the same one as the above) a user can choose a product from a list, and also has to fill in the amount of products he wishes to order.

Product Amount
-------- ------
***** ***

My problem now is that a user can fill in an amount, without having chosen a product from the list. This of course should not be possible.

How should I solve this problem? I have tried initializing the list at a certain value, but then every line on the subform has the same product initially, and this is not the way it should be(as a product may be choosen only once on the same Order). Is it possible to make the listbox 'required'?

Thanks in advance!

dj, Belgium.

 

[freyni]

dj

As an alternative solution to your first problem, you could have created a 'not in' query which showed the list of products that do not have a corresponding value in your order list table.

Re: your second problem, you could disable your 'amount' box and enable it in the lostfocus or afterupdate section of your product dropdown:

eg if not isnull(me![ProductList]) then me!amount.enabled = true

Hope this helps