MsgBox

[Soegstad]

Msg Box

Hi.
I have a subform (frmOrderSub) which is linked with the form (frmOrderMain).
In the subform I have a PoductID combobox where one line is added for every product chosen.
What I need to do is set up a VBA statement that pops up a message when the subform has duplicate lines.
I.e. if I choose product #0123 in the first line I want a message to pop up if this product is chosen again (within the same order)
All help is greatly apprechiated.
Mads

 

[WayneRyan]

Mads,

You can put something like this in the AfterUpdate
event of your combo.

Dim IsThere As Variant

IsThere = DLookUp("[ProductID]", _
                  "YourSubTable", _
                  "[OrderID] = " & Me.OrderID & " And " & _
                  "[ProductID] = " & Me.ProductID)
If Not IsNull(IsThere) Then
   MsgBox("That product is already on this order.")
End If

Wayne

 

[Soegstad]

Thanks for you reply wayne.
I think I'm very close now, but I keep getting RunTime Error 3075 (Operator missing)
Do you know what's causing this?
Could it be because I have multiple columns in my combo?
Thanks again.
Mads

 

[WayneRyan]

Mads,

Can you cut and paste your code here? Please say which
line the error occurs on.

Wayne

 

[Soegstad]

Wayne,
Here's my code

Private Sub Typebetegnelse_AfterUpdate()
Me![Pris] = Me![Typebetegnelse].Column(4)
Me![PID] = Me![Typebetegnelse].Column(1)

Dim IsThere As Variant

IsThere = DLookup("[Typebetegnelse]", _
"tblKalkSub", _
"[KalkyleID] = " & Me.KalkyleID & " And " & _
"[Typebetegnelse] = " & Me.Typebetegnelse)
If Not IsNull(IsThere) Then
MsgBox ("Produktet er allerede i ordren.")
End If
End Sub

I am working with a Norwegian version but I'll try to translate the error message:

Run-Time error '3075'
Syntax error (operator is missing) in query [KalkyleID] = 114
And [Typebetegnelse] = Bremselys 2 stk

The '114' and 'Bremselys 2 stk' are the values for [kalkyleID] and [Typebetegnelse]

Mads

 

[WayneRyan]

Mads,

Darn punctuation ...

Private Sub Typebetegnelse_AfterUpdate()
Me![Pris] = Me![Typebetegnelse].Column(4)
Me![PID] = Me![Typebetegnelse].Column(1)

Dim IsThere As Variant

IsThere = DLookup("[Typebetegnelse]", _
                  "tblKalkSub", _
                  "[KalkyleID] = " & Me.KalkyleID & " And " & _
                  "[Typebetegnelse] = '" & Me.Typebetegnelse & "'")
If Not IsNull(IsThere) Then
   MsgBox ("Produktet er allerede i ordren.")
End If
End Sub

Merry Xmas,
Wayne

 

[Soegstad]

Thanks Wayne,
Worked like a charm
Merry Xmas
Mads

 

[WayneRyan]

Mads,

Glad to help. Have a Merry Xmas too.

Wayne

 

[Soegstad]

Take a look at this thread as well