Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access VBA MsgBox showing multiple messages

I am using the code below that displays a message box. The problem is that what is held in the variable TheMessage is repeated multiple times. When I add a watch to the variable it only has the sentence once

Sub VM(ByVal oNumber As Integer)

    If oLanguage = "" Then
        MsgBox "Language error. Please exit the system and log in again!"
        Exit Sub
    End If

    Dim oCriteria As String
    Dim TheMessage As String
    oCriteria = "[Validation Number] = " & oNumber
    Debug.Print oCriteria

    TheMessage = Nz(DLookup("[" & oLanguage & "]", "[Validations]", oCriteria), "")
    'Debug.Print oCriteria
    'Debug.Print TheMessage
    MsgBox TheMessage, , "CAVR - ATD"
End Sub

Open in new window

Microsoft AccessVBA

Avatar of undefined
Last Comment
John Tsioumpris

8/22/2022 - Mon
Ryan Chong

check the source object of "Validations" and see what's the data being stored in field.

you could also verify that with a select sql statement, such as:

select * from Validations where [validation number] = <some value>

Open in new window

John Tsioumpris

If i remember correctly Dlookup returns only 1 entry so 2 possibilities
1.You have stored multiple lines in the field (just make a form out of the table with the Form Wizard)
2. Somewhere there's a loop and another msgbox/message
Daniel Pineault

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

Maybe property AppendOnly is set to Yes for that field:

Introduction to data types and field properties

If not, your code appends text to the field.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Murray Brown

Thanks for the advice. Here is the table, where I have not repeated any wording. When I look up the EN I get one sentence but when I look up the FR I get multiple. Very strange
John Tsioumpris

Murray Brown

The table looked normal because I think that pasting from the Google translator had a number of blank spaces or perhaps a carriage return before the sentence, which made it seem blank, causing me to paste a few more times because it just looked blank

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

The solution i proposed to make a quick form would have showed you that right away.