Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

informative Messagebox!

Discussion in 'Information Technology' started by JOM, Nov 8, 2005.

  1. JOM

    JOM
    Expand Collapse
    Guest

    I have a field that a employee enters an item. and another field that the
    employee enters the user of that item....
    What I would to see is when a user enter enters an item, a popup will appear
    and inform the user that there are several users of that item and list the
    users e.g., if the employee enters printer xyz the following will appear!

    printer xyz has 4 users
    user1's name
    user2's name
    user3's name
    user4's name
     
  2. Loading...

    Similar Threads Forum Date
    A Spiritually Informative and Inspiring Blog Spiritual Articles Dec 27, 2005

  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Try this code

    Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    Set MyDb = currentdb
    Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    Me.Item & "'")
    If not MyRec.Eof then
    Myrec.MoveLast
    MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    MyRec.movefirst
    While Not MyRec.Eof
    MyStr = MyStr & vbCrLf
    MyStr = MyStr & MyRec.[User_Name]
    MyRec.MoveNext
    Wend
    End If

    I didn't try the code, so you might get an erorr, also change the fields and
    table name according to your data
    This code need to be on the after update event of the Item field

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > I have a field that a employee enters an item. and another field that the
    > employee enters the user of that item....
    > What I would to see is when a user enter enters an item, a popup will appear
    > and inform the user that there are several users of that item and list the
    > users e.g., if the employee enters printer xyz the following will appear!
    >
    > printer xyz has 4 users
    > user1's name
    > user2's name
    > user3's name
    > user4's name
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    I forgot to put the message box before the End if

    MsgBox MyStr


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Ofer" wrote:

    > Try this code
    >
    > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > Set MyDb = currentdb
    > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > Me.Item & "'")
    > If not MyRec.Eof then
    > Myrec.MoveLast
    > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > MyRec.movefirst
    > While Not MyRec.Eof
    > MyStr = MyStr & vbCrLf
    > MyStr = MyStr & MyRec.[User_Name]
    > MyRec.MoveNext
    > Wend
    > End If
    >
    > I didn't try the code, so you might get an erorr, also change the fields and
    > table name according to your data
    > This code need to be on the after update event of the Item field
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > I have a field that a employee enters an item. and another field that the
    > > employee enters the user of that item....
    > > What I would to see is when a user enter enters an item, a popup will appear
    > > and inform the user that there are several users of that item and list the
    > > users e.g., if the employee enters printer xyz the following will appear!
    > >
    > > printer xyz has 4 users
    > > user1's name
    > > user2's name
    > > user3's name
    > > user4's name
     
  5. JOM

    JOM
    Expand Collapse
    Guest

    I have tried it but am having an error in the following line
    Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    Me.Item & "'")

    its saying that I need a list separator or )





    "Ofer" wrote:

    > Try this code
    >
    > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > Set MyDb = currentdb
    > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > Me.Item & "'")
    > If not MyRec.Eof then
    > Myrec.MoveLast
    > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > MyRec.movefirst
    > While Not MyRec.Eof
    > MyStr = MyStr & vbCrLf
    > MyStr = MyStr & MyRec.[User_Name]
    > MyRec.MoveNext
    > Wend
    > End If
    >
    > I didn't try the code, so you might get an erorr, also change the fields and
    > table name according to your data
    > This code need to be on the after update event of the Item field
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > I have a field that a employee enters an item. and another field that the
    > > employee enters the user of that item....
    > > What I would to see is when a user enter enters an item, a popup will appear
    > > and inform the user that there are several users of that item and list the
    > > users e.g., if the employee enters printer xyz the following will appear!
    > >
    > > printer xyz has 4 users
    > > user1's name
    > > user2's name
    > > user3's name
    > > user4's name
     
  6. Ofer

    Ofer
    Expand Collapse
    Guest

    Did you change the names
    TableName = The table name where the data is stored
    Item = the name of the Item Field

    It also seem that I forgot a & before the Me.Item

    Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    Me.Item & "'")

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > I have tried it but am having an error in the following line
    > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > Me.Item & "'")
    >
    > its saying that I need a list separator or )
    >
    >
    >
    >
    >
    > "Ofer" wrote:
    >
    > > Try this code
    > >
    > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > Set MyDb = currentdb
    > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > Me.Item & "'")
    > > If not MyRec.Eof then
    > > Myrec.MoveLast
    > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > MyRec.movefirst
    > > While Not MyRec.Eof
    > > MyStr = MyStr & vbCrLf
    > > MyStr = MyStr & MyRec.[User_Name]
    > > MyRec.MoveNext
    > > Wend
    > > End If
    > >
    > > I didn't try the code, so you might get an erorr, also change the fields and
    > > table name according to your data
    > > This code need to be on the after update event of the Item field
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > I have a field that a employee enters an item. and another field that the
    > > > employee enters the user of that item....
    > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > and inform the user that there are several users of that item and list the
    > > > users e.g., if the employee enters printer xyz the following will appear!
    > > >
    > > > printer xyz has 4 users
    > > > user1's name
    > > > user2's name
    > > > user3's name
    > > > user4's name
     
  7. JOM

    JOM
    Expand Collapse
    Guest

    Ok that did the trick, but another error came up,
    method or data member not found on the following line

    MyStr = MyStr & MyRec.[User_Name]



    "Ofer" wrote:

    > Did you change the names
    > TableName = The table name where the data is stored
    > Item = the name of the Item Field
    >
    > It also seem that I forgot a & before the Me.Item
    >
    > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > Me.Item & "'")
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > I have tried it but am having an error in the following line
    > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > Me.Item & "'")
    > >
    > > its saying that I need a list separator or )
    > >
    > >
    > >
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > Try this code
    > > >
    > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > Set MyDb = currentdb
    > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > Me.Item & "'")
    > > > If not MyRec.Eof then
    > > > Myrec.MoveLast
    > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > MyRec.movefirst
    > > > While Not MyRec.Eof
    > > > MyStr = MyStr & vbCrLf
    > > > MyStr = MyStr & MyRec.[User_Name]
    > > > MyRec.MoveNext
    > > > Wend
    > > > End If
    > > >
    > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > table name according to your data
    > > > This code need to be on the after update event of the Item field
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > I have a field that a employee enters an item. and another field that the
    > > > > employee enters the user of that item....
    > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > and inform the user that there are several users of that item and list the
    > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > >
    > > > > printer xyz has 4 users
    > > > > user1's name
    > > > > user2's name
    > > > > user3's name
    > > > > user4's name
     
  8. Ofer

    Ofer
    Expand Collapse
    Guest

    Sorry, my mistake

    change it to
    MyRec![User_Name]

    instead of a dot

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > Ok that did the trick, but another error came up,
    > method or data member not found on the following line
    >
    > MyStr = MyStr & MyRec.[User_Name]
    >
    >
    >
    > "Ofer" wrote:
    >
    > > Did you change the names
    > > TableName = The table name where the data is stored
    > > Item = the name of the Item Field
    > >
    > > It also seem that I forgot a & before the Me.Item
    > >
    > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > Me.Item & "'")
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > I have tried it but am having an error in the following line
    > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > Me.Item & "'")
    > > >
    > > > its saying that I need a list separator or )
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > Try this code
    > > > >
    > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > Set MyDb = currentdb
    > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > Me.Item & "'")
    > > > > If not MyRec.Eof then
    > > > > Myrec.MoveLast
    > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > MyRec.movefirst
    > > > > While Not MyRec.Eof
    > > > > MyStr = MyStr & vbCrLf
    > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > MyRec.MoveNext
    > > > > Wend
    > > > > End If
    > > > >
    > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > table name according to your data
    > > > > This code need to be on the after update event of the Item field
    > > > >
    > > > > --
    > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > know that my answer was helpful
    > > > > HTH, good luck
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > employee enters the user of that item....
    > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > and inform the user that there are several users of that item and list the
    > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > >
    > > > > > printer xyz has 4 users
    > > > > > user1's name
    > > > > > user2's name
    > > > > > user3's name
    > > > > > user4's name
     
  9. JOM

    JOM
    Expand Collapse
    Guest

    ok, that did the trick but another error came up run time error 3131 Syntax
    error in from clause
    in the following line!
    Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    me.Item & "'")

    "Ofer" wrote:

    > Sorry, my mistake
    >
    > change it to
    > MyRec![User_Name]
    >
    > instead of a dot
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > Ok that did the trick, but another error came up,
    > > method or data member not found on the following line
    > >
    > > MyStr = MyStr & MyRec.[User_Name]
    > >
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > Did you change the names
    > > > TableName = The table name where the data is stored
    > > > Item = the name of the Item Field
    > > >
    > > > It also seem that I forgot a & before the Me.Item
    > > >
    > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > Me.Item & "'")
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > I have tried it but am having an error in the following line
    > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > Me.Item & "'")
    > > > >
    > > > > its saying that I need a list separator or )
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > Try this code
    > > > > >
    > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > Set MyDb = currentdb
    > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > Me.Item & "'")
    > > > > > If not MyRec.Eof then
    > > > > > Myrec.MoveLast
    > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > MyRec.movefirst
    > > > > > While Not MyRec.Eof
    > > > > > MyStr = MyStr & vbCrLf
    > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > MyRec.MoveNext
    > > > > > Wend
    > > > > > End If
    > > > > >
    > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > table name according to your data
    > > > > > This code need to be on the after update event of the Item field
    > > > > >
    > > > > > --
    > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > know that my answer was helpful
    > > > > > HTH, good luck
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > employee enters the user of that item....
    > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > >
    > > > > > > printer xyz has 4 users
    > > > > > > user1's name
    > > > > > > user2's name
    > > > > > > user3's name
    > > > > > > user4's name
     
  10. Ofer

    Ofer
    Expand Collapse
    Guest

    Can you post the full code that you are using, and not the one I posted?


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > ok, that did the trick but another error came up run time error 3131 Syntax
    > error in from clause
    > in the following line!
    > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > me.Item & "'")
    >
    > "Ofer" wrote:
    >
    > > Sorry, my mistake
    > >
    > > change it to
    > > MyRec![User_Name]
    > >
    > > instead of a dot
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > Ok that did the trick, but another error came up,
    > > > method or data member not found on the following line
    > > >
    > > > MyStr = MyStr & MyRec.[User_Name]
    > > >
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > Did you change the names
    > > > > TableName = The table name where the data is stored
    > > > > Item = the name of the Item Field
    > > > >
    > > > > It also seem that I forgot a & before the Me.Item
    > > > >
    > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > Me.Item & "'")
    > > > >
    > > > > --
    > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > know that my answer was helpful
    > > > > HTH, good luck
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > I have tried it but am having an error in the following line
    > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > Me.Item & "'")
    > > > > >
    > > > > > its saying that I need a list separator or )
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > Try this code
    > > > > > >
    > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > Set MyDb = currentdb
    > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > Me.Item & "'")
    > > > > > > If not MyRec.Eof then
    > > > > > > Myrec.MoveLast
    > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > MyRec.movefirst
    > > > > > > While Not MyRec.Eof
    > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > MyRec.MoveNext
    > > > > > > Wend
    > > > > > > End If
    > > > > > >
    > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > table name according to your data
    > > > > > > This code need to be on the after update event of the Item field
    > > > > > >
    > > > > > > --
    > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > know that my answer was helpful
    > > > > > > HTH, good luck
    > > > > > >
    > > > > > >
    > > > > > > "JOM" wrote:
    > > > > > >
    > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > employee enters the user of that item....
    > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > >
    > > > > > > > printer xyz has 4 users
    > > > > > > > user1's name
    > > > > > > > user2's name
    > > > > > > > user3's name
    > > > > > > > user4's name
     
  11. JOM

    JOM
    Expand Collapse
    Guest

    Private Sub ItemDescNo__AfterUpdate()

    Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    Set MyDb = CurrentDb

    Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    ItemDescNo_ = '" & Me.ItemDescNo_ & "'")

    If Not MyRec.EOF Then
    MyRec.MoveLast
    MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    MyRec.MoveFirst
    While Not MyRec.EOF
    MyStr = MyStr & vbCrLf
    MyStr = MyStr & MyRec![EmplName]
    MyRec.MoveNext
    Wend
    MsgBox MyStr
    End If
    End Sub


    "Ofer" wrote:

    > Can you post the full code that you are using, and not the one I posted?
    >
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > error in from clause
    > > in the following line!
    > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > me.Item & "'")
    > >
    > > "Ofer" wrote:
    > >
    > > > Sorry, my mistake
    > > >
    > > > change it to
    > > > MyRec![User_Name]
    > > >
    > > > instead of a dot
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > Ok that did the trick, but another error came up,
    > > > > method or data member not found on the following line
    > > > >
    > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > >
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > Did you change the names
    > > > > > TableName = The table name where the data is stored
    > > > > > Item = the name of the Item Field
    > > > > >
    > > > > > It also seem that I forgot a & before the Me.Item
    > > > > >
    > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > Me.Item & "'")
    > > > > >
    > > > > > --
    > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > know that my answer was helpful
    > > > > > HTH, good luck
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > I have tried it but am having an error in the following line
    > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > Me.Item & "'")
    > > > > > >
    > > > > > > its saying that I need a list separator or )
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > > "Ofer" wrote:
    > > > > > >
    > > > > > > > Try this code
    > > > > > > >
    > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > Set MyDb = currentdb
    > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > Me.Item & "'")
    > > > > > > > If not MyRec.Eof then
    > > > > > > > Myrec.MoveLast
    > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > MyRec.movefirst
    > > > > > > > While Not MyRec.Eof
    > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > MyRec.MoveNext
    > > > > > > > Wend
    > > > > > > > End If
    > > > > > > >
    > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > table name according to your data
    > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > >
    > > > > > > > --
    > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > know that my answer was helpful
    > > > > > > > HTH, good luck
    > > > > > > >
    > > > > > > >
    > > > > > > > "JOM" wrote:
    > > > > > > >
    > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > employee enters the user of that item....
    > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > >
    > > > > > > > > printer xyz has 4 users
    > > > > > > > > user1's name
    > > > > > > > > user2's name
    > > > > > > > > user3's name
    > > > > > > > > user4's name
     
  12. Ofer

    Ofer
    Expand Collapse
    Guest

    When the table name or the field name contain two words you need to put it in
    square brackets

    Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    ItemDescNo_ = '" & Me.ItemDescNo_ & "'")

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > Private Sub ItemDescNo__AfterUpdate()
    >
    > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > Set MyDb = CurrentDb
    >
    > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    >
    > If Not MyRec.EOF Then
    > MyRec.MoveLast
    > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > MyRec.MoveFirst
    > While Not MyRec.EOF
    > MyStr = MyStr & vbCrLf
    > MyStr = MyStr & MyRec![EmplName]
    > MyRec.MoveNext
    > Wend
    > MsgBox MyStr
    > End If
    > End Sub
    >
    >
    > "Ofer" wrote:
    >
    > > Can you post the full code that you are using, and not the one I posted?
    > >
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > error in from clause
    > > > in the following line!
    > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > me.Item & "'")
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > Sorry, my mistake
    > > > >
    > > > > change it to
    > > > > MyRec![User_Name]
    > > > >
    > > > > instead of a dot
    > > > >
    > > > > --
    > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > know that my answer was helpful
    > > > > HTH, good luck
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > Ok that did the trick, but another error came up,
    > > > > > method or data member not found on the following line
    > > > > >
    > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > Did you change the names
    > > > > > > TableName = The table name where the data is stored
    > > > > > > Item = the name of the Item Field
    > > > > > >
    > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > >
    > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > Me.Item & "'")
    > > > > > >
    > > > > > > --
    > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > know that my answer was helpful
    > > > > > > HTH, good luck
    > > > > > >
    > > > > > >
    > > > > > > "JOM" wrote:
    > > > > > >
    > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > Me.Item & "'")
    > > > > > > >
    > > > > > > > its saying that I need a list separator or )
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > > "Ofer" wrote:
    > > > > > > >
    > > > > > > > > Try this code
    > > > > > > > >
    > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > Set MyDb = currentdb
    > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > Me.Item & "'")
    > > > > > > > > If not MyRec.Eof then
    > > > > > > > > Myrec.MoveLast
    > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > MyRec.movefirst
    > > > > > > > > While Not MyRec.Eof
    > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > MyRec.MoveNext
    > > > > > > > > Wend
    > > > > > > > > End If
    > > > > > > > >
    > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > table name according to your data
    > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > know that my answer was helpful
    > > > > > > > > HTH, good luck
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "JOM" wrote:
    > > > > > > > >
    > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > >
    > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > user1's name
    > > > > > > > > > user2's name
    > > > > > > > > > user3's name
    > > > > > > > > > user4's name
     
  13. JOM

    JOM
    Expand Collapse
    Guest

    ok, another error came up on the same line Run-time error'3061': Too few
    parameters. expected 1.


    "Ofer" wrote:

    > When the table name or the field name contain two words you need to put it in
    > square brackets
    >
    > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > Private Sub ItemDescNo__AfterUpdate()
    > >
    > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > Set MyDb = CurrentDb
    > >
    > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > >
    > > If Not MyRec.EOF Then
    > > MyRec.MoveLast
    > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > MyRec.MoveFirst
    > > While Not MyRec.EOF
    > > MyStr = MyStr & vbCrLf
    > > MyStr = MyStr & MyRec![EmplName]
    > > MyRec.MoveNext
    > > Wend
    > > MsgBox MyStr
    > > End If
    > > End Sub
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > Can you post the full code that you are using, and not the one I posted?
    > > >
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > error in from clause
    > > > > in the following line!
    > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > me.Item & "'")
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > Sorry, my mistake
    > > > > >
    > > > > > change it to
    > > > > > MyRec![User_Name]
    > > > > >
    > > > > > instead of a dot
    > > > > >
    > > > > > --
    > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > know that my answer was helpful
    > > > > > HTH, good luck
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > Ok that did the trick, but another error came up,
    > > > > > > method or data member not found on the following line
    > > > > > >
    > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > > "Ofer" wrote:
    > > > > > >
    > > > > > > > Did you change the names
    > > > > > > > TableName = The table name where the data is stored
    > > > > > > > Item = the name of the Item Field
    > > > > > > >
    > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > >
    > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > Me.Item & "'")
    > > > > > > >
    > > > > > > > --
    > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > know that my answer was helpful
    > > > > > > > HTH, good luck
    > > > > > > >
    > > > > > > >
    > > > > > > > "JOM" wrote:
    > > > > > > >
    > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > Me.Item & "'")
    > > > > > > > >
    > > > > > > > > its saying that I need a list separator or )
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Ofer" wrote:
    > > > > > > > >
    > > > > > > > > > Try this code
    > > > > > > > > >
    > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > Me.Item & "'")
    > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > MyRec.movefirst
    > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > Wend
    > > > > > > > > > End If
    > > > > > > > > >
    > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > table name according to your data
    > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > know that my answer was helpful
    > > > > > > > > > HTH, good luck
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "JOM" wrote:
    > > > > > > > > >
    > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > >
    > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > user1's name
    > > > > > > > > > > user2's name
    > > > > > > > > > > user3's name
    > > > > > > > > > > user4's name
     
  14. Ofer

    Ofer
    Expand Collapse
    Guest

    You can get this error message if the field name,ItemDescNo_, in the table is
    wrong


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > ok, another error came up on the same line Run-time error'3061': Too few
    > parameters. expected 1.
    >
    >
    > "Ofer" wrote:
    >
    > > When the table name or the field name contain two words you need to put it in
    > > square brackets
    > >
    > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > Private Sub ItemDescNo__AfterUpdate()
    > > >
    > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > Set MyDb = CurrentDb
    > > >
    > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > >
    > > > If Not MyRec.EOF Then
    > > > MyRec.MoveLast
    > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > MyRec.MoveFirst
    > > > While Not MyRec.EOF
    > > > MyStr = MyStr & vbCrLf
    > > > MyStr = MyStr & MyRec![EmplName]
    > > > MyRec.MoveNext
    > > > Wend
    > > > MsgBox MyStr
    > > > End If
    > > > End Sub
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > Can you post the full code that you are using, and not the one I posted?
    > > > >
    > > > >
    > > > > --
    > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > know that my answer was helpful
    > > > > HTH, good luck
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > error in from clause
    > > > > > in the following line!
    > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > me.Item & "'")
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > Sorry, my mistake
    > > > > > >
    > > > > > > change it to
    > > > > > > MyRec![User_Name]
    > > > > > >
    > > > > > > instead of a dot
    > > > > > >
    > > > > > > --
    > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > know that my answer was helpful
    > > > > > > HTH, good luck
    > > > > > >
    > > > > > >
    > > > > > > "JOM" wrote:
    > > > > > >
    > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > method or data member not found on the following line
    > > > > > > >
    > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > > "Ofer" wrote:
    > > > > > > >
    > > > > > > > > Did you change the names
    > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > Item = the name of the Item Field
    > > > > > > > >
    > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > >
    > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > Me.Item & "'")
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > know that my answer was helpful
    > > > > > > > > HTH, good luck
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "JOM" wrote:
    > > > > > > > >
    > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > Me.Item & "'")
    > > > > > > > > >
    > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "Ofer" wrote:
    > > > > > > > > >
    > > > > > > > > > > Try this code
    > > > > > > > > > >
    > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > Wend
    > > > > > > > > > > End If
    > > > > > > > > > >
    > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > table name according to your data
    > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > >
    > > > > > > > > > > --
    > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > HTH, good luck
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > >
    > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > user1's name
    > > > > > > > > > > > user2's name
    > > > > > > > > > > > user3's name
    > > > > > > > > > > > user4's name
     
  15. JOM

    JOM
    Expand Collapse
    Guest

    this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    have to change it to _ if I change the first part of the sql to have # it
    gives me an error:
    "Ofer" wrote: Run-time error 3075:
    systax error in date in query expression 'ItemDescNo# =' 1212121212"

    .................................................

    > You can get this error message if the field name,ItemDescNo_, in the table is
    > wrong
    >
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > ok, another error came up on the same line Run-time error'3061': Too few
    > > parameters. expected 1.
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > When the table name or the field name contain two words you need to put it in
    > > > square brackets
    > > >
    > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > >
    > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > Set MyDb = CurrentDb
    > > > >
    > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > >
    > > > > If Not MyRec.EOF Then
    > > > > MyRec.MoveLast
    > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > MyRec.MoveFirst
    > > > > While Not MyRec.EOF
    > > > > MyStr = MyStr & vbCrLf
    > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > MyRec.MoveNext
    > > > > Wend
    > > > > MsgBox MyStr
    > > > > End If
    > > > > End Sub
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > >
    > > > > >
    > > > > > --
    > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > know that my answer was helpful
    > > > > > HTH, good luck
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > error in from clause
    > > > > > > in the following line!
    > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > me.Item & "'")
    > > > > > >
    > > > > > > "Ofer" wrote:
    > > > > > >
    > > > > > > > Sorry, my mistake
    > > > > > > >
    > > > > > > > change it to
    > > > > > > > MyRec![User_Name]
    > > > > > > >
    > > > > > > > instead of a dot
    > > > > > > >
    > > > > > > > --
    > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > know that my answer was helpful
    > > > > > > > HTH, good luck
    > > > > > > >
    > > > > > > >
    > > > > > > > "JOM" wrote:
    > > > > > > >
    > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > method or data member not found on the following line
    > > > > > > > >
    > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Ofer" wrote:
    > > > > > > > >
    > > > > > > > > > Did you change the names
    > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > >
    > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > >
    > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > Me.Item & "'")
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > know that my answer was helpful
    > > > > > > > > > HTH, good luck
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "JOM" wrote:
    > > > > > > > > >
    > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > >
    > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > Try this code
    > > > > > > > > > > >
    > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > Wend
    > > > > > > > > > > > End If
    > > > > > > > > > > >
    > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > >
    > > > > > > > > > > > --
    > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > >
    > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > user4's name
     
  16. Ofer

    Ofer
    Expand Collapse
    Guest

    This name also need to be in square brackets

    [ItemDescNo#] if that the name of the field in the table



    "JOM" wrote:

    > this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    > error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    > have to change it to _ if I change the first part of the sql to have # it
    > gives me an error:
    > "Ofer" wrote: Run-time error 3075:
    > systax error in date in query expression 'ItemDescNo# =' 1212121212"
    >
    > ................................................
    >
    > > You can get this error message if the field name,ItemDescNo_, in the table is
    > > wrong
    > >
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > ok, another error came up on the same line Run-time error'3061': Too few
    > > > parameters. expected 1.
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > When the table name or the field name contain two words you need to put it in
    > > > > square brackets
    > > > >
    > > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > >
    > > > > --
    > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > know that my answer was helpful
    > > > > HTH, good luck
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > > >
    > > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > > Set MyDb = CurrentDb
    > > > > >
    > > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > >
    > > > > > If Not MyRec.EOF Then
    > > > > > MyRec.MoveLast
    > > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > > MyRec.MoveFirst
    > > > > > While Not MyRec.EOF
    > > > > > MyStr = MyStr & vbCrLf
    > > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > > MyRec.MoveNext
    > > > > > Wend
    > > > > > MsgBox MyStr
    > > > > > End If
    > > > > > End Sub
    > > > > >
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > know that my answer was helpful
    > > > > > > HTH, good luck
    > > > > > >
    > > > > > >
    > > > > > > "JOM" wrote:
    > > > > > >
    > > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > > error in from clause
    > > > > > > > in the following line!
    > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > me.Item & "'")
    > > > > > > >
    > > > > > > > "Ofer" wrote:
    > > > > > > >
    > > > > > > > > Sorry, my mistake
    > > > > > > > >
    > > > > > > > > change it to
    > > > > > > > > MyRec![User_Name]
    > > > > > > > >
    > > > > > > > > instead of a dot
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > know that my answer was helpful
    > > > > > > > > HTH, good luck
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "JOM" wrote:
    > > > > > > > >
    > > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > > method or data member not found on the following line
    > > > > > > > > >
    > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "Ofer" wrote:
    > > > > > > > > >
    > > > > > > > > > > Did you change the names
    > > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > > >
    > > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > > >
    > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > >
    > > > > > > > > > > --
    > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > HTH, good luck
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > >
    > > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > Try this code
    > > > > > > > > > > > >
    > > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > > Wend
    > > > > > > > > > > > > End If
    > > > > > > > > > > > >
    > > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > > >
    > > > > > > > > > > > > --
    > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > >
    > > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > > user4's name
     
  17. JOM

    JOM
    Expand Collapse
    Guest

    I think that took care of the erros, but the popoup did not appear... did I
    happen to put the msgbox in the wrong place? I put it after Wend

    "Ofer" wrote:

    > This name also need to be in square brackets
    >
    > [ItemDescNo#] if that the name of the field in the table
    >
    >
    >
    > "JOM" wrote:
    >
    > > this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    > > error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    > > have to change it to _ if I change the first part of the sql to have # it
    > > gives me an error:
    > > "Ofer" wrote: Run-time error 3075:
    > > systax error in date in query expression 'ItemDescNo# =' 1212121212"
    > >
    > > ................................................
    > >
    > > > You can get this error message if the field name,ItemDescNo_, in the table is
    > > > wrong
    > > >
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > ok, another error came up on the same line Run-time error'3061': Too few
    > > > > parameters. expected 1.
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > When the table name or the field name contain two words you need to put it in
    > > > > > square brackets
    > > > > >
    > > > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > >
    > > > > > --
    > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > know that my answer was helpful
    > > > > > HTH, good luck
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > > > >
    > > > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > > > Set MyDb = CurrentDb
    > > > > > >
    > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > >
    > > > > > > If Not MyRec.EOF Then
    > > > > > > MyRec.MoveLast
    > > > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > > > MyRec.MoveFirst
    > > > > > > While Not MyRec.EOF
    > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > > > MyRec.MoveNext
    > > > > > > Wend
    > > > > > > MsgBox MyStr
    > > > > > > End If
    > > > > > > End Sub
    > > > > > >
    > > > > > >
    > > > > > > "Ofer" wrote:
    > > > > > >
    > > > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > > > >
    > > > > > > >
    > > > > > > > --
    > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > know that my answer was helpful
    > > > > > > > HTH, good luck
    > > > > > > >
    > > > > > > >
    > > > > > > > "JOM" wrote:
    > > > > > > >
    > > > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > > > error in from clause
    > > > > > > > > in the following line!
    > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > me.Item & "'")
    > > > > > > > >
    > > > > > > > > "Ofer" wrote:
    > > > > > > > >
    > > > > > > > > > Sorry, my mistake
    > > > > > > > > >
    > > > > > > > > > change it to
    > > > > > > > > > MyRec![User_Name]
    > > > > > > > > >
    > > > > > > > > > instead of a dot
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > know that my answer was helpful
    > > > > > > > > > HTH, good luck
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "JOM" wrote:
    > > > > > > > > >
    > > > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > > > method or data member not found on the following line
    > > > > > > > > > >
    > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > Did you change the names
    > > > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > > > >
    > > > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > > > >
    > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > >
    > > > > > > > > > > > --
    > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > >
    > > > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > >
    > > > > > > > > > > > > > Try this code
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > > > Wend
    > > > > > > > > > > > > > End If
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > --
    > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > > > user4's name
     
  18. Ofer

    Ofer
    Expand Collapse
    Guest

    Did you enter an Item that does exist in the table? other wise you wont get
    any message.
    Put a code break in the OpenRecordset line, press F9, and step the code by
    pressing F8 to see if the recordset return any records and it enter the loop


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > I think that took care of the erros, but the popoup did not appear... did I
    > happen to put the msgbox in the wrong place? I put it after Wend
    >
    > "Ofer" wrote:
    >
    > > This name also need to be in square brackets
    > >
    > > [ItemDescNo#] if that the name of the field in the table
    > >
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    > > > error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    > > > have to change it to _ if I change the first part of the sql to have # it
    > > > gives me an error:
    > > > "Ofer" wrote: Run-time error 3075:
    > > > systax error in date in query expression 'ItemDescNo# =' 1212121212"
    > > >
    > > > ................................................
    > > >
    > > > > You can get this error message if the field name,ItemDescNo_, in the table is
    > > > > wrong
    > > > >
    > > > >
    > > > > --
    > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > know that my answer was helpful
    > > > > HTH, good luck
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > ok, another error came up on the same line Run-time error'3061': Too few
    > > > > > parameters. expected 1.
    > > > > >
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > When the table name or the field name contain two words you need to put it in
    > > > > > > square brackets
    > > > > > >
    > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > >
    > > > > > > --
    > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > know that my answer was helpful
    > > > > > > HTH, good luck
    > > > > > >
    > > > > > >
    > > > > > > "JOM" wrote:
    > > > > > >
    > > > > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > > > > >
    > > > > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > > > > Set MyDb = CurrentDb
    > > > > > > >
    > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > >
    > > > > > > > If Not MyRec.EOF Then
    > > > > > > > MyRec.MoveLast
    > > > > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > MyRec.MoveFirst
    > > > > > > > While Not MyRec.EOF
    > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > > > > MyRec.MoveNext
    > > > > > > > Wend
    > > > > > > > MsgBox MyStr
    > > > > > > > End If
    > > > > > > > End Sub
    > > > > > > >
    > > > > > > >
    > > > > > > > "Ofer" wrote:
    > > > > > > >
    > > > > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > know that my answer was helpful
    > > > > > > > > HTH, good luck
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "JOM" wrote:
    > > > > > > > >
    > > > > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > > > > error in from clause
    > > > > > > > > > in the following line!
    > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > me.Item & "'")
    > > > > > > > > >
    > > > > > > > > > "Ofer" wrote:
    > > > > > > > > >
    > > > > > > > > > > Sorry, my mistake
    > > > > > > > > > >
    > > > > > > > > > > change it to
    > > > > > > > > > > MyRec![User_Name]
    > > > > > > > > > >
    > > > > > > > > > > instead of a dot
    > > > > > > > > > >
    > > > > > > > > > > --
    > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > HTH, good luck
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > > > > method or data member not found on the following line
    > > > > > > > > > > >
    > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > Did you change the names
    > > > > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > > > > >
    > > > > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > > > > >
    > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > >
    > > > > > > > > > > > > --
    > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > >
    > > > > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > > Try this code
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > > > > Wend
    > > > > > > > > > > > > > > End If
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > --
    > > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > > > > user4's name
     
  19. JOM

    JOM
    Expand Collapse
    Guest

    did that, it only goes upto the first line: If Not MyRec.EOF Then
    then end if. It takes time when in the Sql Statement before it goes to the
    first line of the loop....

    "Ofer" wrote:

    > Did you enter an Item that does exist in the table? other wise you wont get
    > any message.
    > Put a code break in the OpenRecordset line, press F9, and step the code by
    > pressing F8 to see if the recordset return any records and it enter the loop
    >
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > I think that took care of the erros, but the popoup did not appear... did I
    > > happen to put the msgbox in the wrong place? I put it after Wend
    > >
    > > "Ofer" wrote:
    > >
    > > > This name also need to be in square brackets
    > > >
    > > > [ItemDescNo#] if that the name of the field in the table
    > > >
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    > > > > error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    > > > > have to change it to _ if I change the first part of the sql to have # it
    > > > > gives me an error:
    > > > > "Ofer" wrote: Run-time error 3075:
    > > > > systax error in date in query expression 'ItemDescNo# =' 1212121212"
    > > > >
    > > > > ................................................
    > > > >
    > > > > > You can get this error message if the field name,ItemDescNo_, in the table is
    > > > > > wrong
    > > > > >
    > > > > >
    > > > > > --
    > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > know that my answer was helpful
    > > > > > HTH, good luck
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > ok, another error came up on the same line Run-time error'3061': Too few
    > > > > > > parameters. expected 1.
    > > > > > >
    > > > > > >
    > > > > > > "Ofer" wrote:
    > > > > > >
    > > > > > > > When the table name or the field name contain two words you need to put it in
    > > > > > > > square brackets
    > > > > > > >
    > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > >
    > > > > > > > --
    > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > know that my answer was helpful
    > > > > > > > HTH, good luck
    > > > > > > >
    > > > > > > >
    > > > > > > > "JOM" wrote:
    > > > > > > >
    > > > > > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > > > > > >
    > > > > > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > > > > > Set MyDb = CurrentDb
    > > > > > > > >
    > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > > >
    > > > > > > > > If Not MyRec.EOF Then
    > > > > > > > > MyRec.MoveLast
    > > > > > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > MyRec.MoveFirst
    > > > > > > > > While Not MyRec.EOF
    > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > > > > > MyRec.MoveNext
    > > > > > > > > Wend
    > > > > > > > > MsgBox MyStr
    > > > > > > > > End If
    > > > > > > > > End Sub
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Ofer" wrote:
    > > > > > > > >
    > > > > > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > know that my answer was helpful
    > > > > > > > > > HTH, good luck
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "JOM" wrote:
    > > > > > > > > >
    > > > > > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > > > > > error in from clause
    > > > > > > > > > > in the following line!
    > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > me.Item & "'")
    > > > > > > > > > >
    > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > Sorry, my mistake
    > > > > > > > > > > >
    > > > > > > > > > > > change it to
    > > > > > > > > > > > MyRec![User_Name]
    > > > > > > > > > > >
    > > > > > > > > > > > instead of a dot
    > > > > > > > > > > >
    > > > > > > > > > > > --
    > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > > > > > method or data member not found on the following line
    > > > > > > > > > > > >
    > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > >
    > > > > > > > > > > > > > Did you change the names
    > > > > > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > --
    > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > Try this code
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > > > > > Wend
    > > > > > > > > > > > > > > > End If
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > --
    > > > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > > > > > user4's name
     
  20. Ofer

    Ofer
    Expand Collapse
    Guest

    Plese post the full code you have now, and while in code break, copy the
    select line
    "Select * From ..... & "'"

    Open The immidiate window, (ctrl + g) paste this line with a question mark
    ?"Select ,..... "

    Press enter, the out come, copy and create a new query, and paste this SQL,
    to see if it returns any record, if it doesn't check if you have the right
    item name.
    Check that you comparing the right fields.


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "JOM" wrote:

    > did that, it only goes upto the first line: If Not MyRec.EOF Then
    > then end if. It takes time when in the Sql Statement before it goes to the
    > first line of the loop....
    >
    > "Ofer" wrote:
    >
    > > Did you enter an Item that does exist in the table? other wise you wont get
    > > any message.
    > > Put a code break in the OpenRecordset line, press F9, and step the code by
    > > pressing F8 to see if the recordset return any records and it enter the loop
    > >
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "JOM" wrote:
    > >
    > > > I think that took care of the erros, but the popoup did not appear... did I
    > > > happen to put the msgbox in the wrong place? I put it after Wend
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > This name also need to be in square brackets
    > > > >
    > > > > [ItemDescNo#] if that the name of the field in the table
    > > > >
    > > > >
    > > > >
    > > > > "JOM" wrote:
    > > > >
    > > > > > this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    > > > > > error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    > > > > > have to change it to _ if I change the first part of the sql to have # it
    > > > > > gives me an error:
    > > > > > "Ofer" wrote: Run-time error 3075:
    > > > > > systax error in date in query expression 'ItemDescNo# =' 1212121212"
    > > > > >
    > > > > > ................................................
    > > > > >
    > > > > > > You can get this error message if the field name,ItemDescNo_, in the table is
    > > > > > > wrong
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > know that my answer was helpful
    > > > > > > HTH, good luck
    > > > > > >
    > > > > > >
    > > > > > > "JOM" wrote:
    > > > > > >
    > > > > > > > ok, another error came up on the same line Run-time error'3061': Too few
    > > > > > > > parameters. expected 1.
    > > > > > > >
    > > > > > > >
    > > > > > > > "Ofer" wrote:
    > > > > > > >
    > > > > > > > > When the table name or the field name contain two words you need to put it in
    > > > > > > > > square brackets
    > > > > > > > >
    > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > know that my answer was helpful
    > > > > > > > > HTH, good luck
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "JOM" wrote:
    > > > > > > > >
    > > > > > > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > > > > > > >
    > > > > > > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > > > > > > Set MyDb = CurrentDb
    > > > > > > > > >
    > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > > > >
    > > > > > > > > > If Not MyRec.EOF Then
    > > > > > > > > > MyRec.MoveLast
    > > > > > > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > MyRec.MoveFirst
    > > > > > > > > > While Not MyRec.EOF
    > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > Wend
    > > > > > > > > > MsgBox MyStr
    > > > > > > > > > End If
    > > > > > > > > > End Sub
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "Ofer" wrote:
    > > > > > > > > >
    > > > > > > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > --
    > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > HTH, good luck
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > > > > > > error in from clause
    > > > > > > > > > > > in the following line!
    > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > me.Item & "'")
    > > > > > > > > > > >
    > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > Sorry, my mistake
    > > > > > > > > > > > >
    > > > > > > > > > > > > change it to
    > > > > > > > > > > > > MyRec![User_Name]
    > > > > > > > > > > > >
    > > > > > > > > > > > > instead of a dot
    > > > > > > > > > > > >
    > > > > > > > > > > > > --
    > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > >
    > > > > > > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > > > > > > method or data member not found on the following line
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > > Did you change the names
    > > > > > > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > --
    > > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > Try this code
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > > > > > > Wend
    > > > > > > > > > > > > > > > > End If
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > --
    > > > > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > > > > > > user4's name
     
  21. JOM

    JOM
    Expand Collapse
    Guest

    Thank you so much for your help, it actually worked, I have 2 tables that I
    was using the wrong one...
    thanks alot, the only thing its doing though it takes a long time when in
    the SQL statement, my code is still the same... as I had posted earlier....

    "Ofer" wrote:

    > Plese post the full code you have now, and while in code break, copy the
    > select line
    > "Select * From ..... & "'"
    >
    > Open The immidiate window, (ctrl + g) paste this line with a question mark
    > ?"Select ,..... "
    >
    > Press enter, the out come, copy and create a new query, and paste this SQL,
    > to see if it returns any record, if it doesn't check if you have the right
    > item name.
    > Check that you comparing the right fields.
    >
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "JOM" wrote:
    >
    > > did that, it only goes upto the first line: If Not MyRec.EOF Then
    > > then end if. It takes time when in the Sql Statement before it goes to the
    > > first line of the loop....
    > >
    > > "Ofer" wrote:
    > >
    > > > Did you enter an Item that does exist in the table? other wise you wont get
    > > > any message.
    > > > Put a code break in the OpenRecordset line, press F9, and step the code by
    > > > pressing F8 to see if the recordset return any records and it enter the loop
    > > >
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "JOM" wrote:
    > > >
    > > > > I think that took care of the erros, but the popoup did not appear... did I
    > > > > happen to put the msgbox in the wrong place? I put it after Wend
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > This name also need to be in square brackets
    > > > > >
    > > > > > [ItemDescNo#] if that the name of the field in the table
    > > > > >
    > > > > >
    > > > > >
    > > > > > "JOM" wrote:
    > > > > >
    > > > > > > this is how its in my table ItemDescNo#, so when I change the _ to # I get an
    > > > > > > error in the 2 part of the SQl saying that ItemDescNo# is not defined so I
    > > > > > > have to change it to _ if I change the first part of the sql to have # it
    > > > > > > gives me an error:
    > > > > > > "Ofer" wrote: Run-time error 3075:
    > > > > > > systax error in date in query expression 'ItemDescNo# =' 1212121212"
    > > > > > >
    > > > > > > ................................................
    > > > > > >
    > > > > > > > You can get this error message if the field name,ItemDescNo_, in the table is
    > > > > > > > wrong
    > > > > > > >
    > > > > > > >
    > > > > > > > --
    > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > know that my answer was helpful
    > > > > > > > HTH, good luck
    > > > > > > >
    > > > > > > >
    > > > > > > > "JOM" wrote:
    > > > > > > >
    > > > > > > > > ok, another error came up on the same line Run-time error'3061': Too few
    > > > > > > > > parameters. expected 1.
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Ofer" wrote:
    > > > > > > > >
    > > > > > > > > > When the table name or the field name contain two words you need to put it in
    > > > > > > > > > square brackets
    > > > > > > > > >
    > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From [Information Tracking] Where
    > > > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > know that my answer was helpful
    > > > > > > > > > HTH, good luck
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "JOM" wrote:
    > > > > > > > > >
    > > > > > > > > > > Private Sub ItemDescNo__AfterUpdate()
    > > > > > > > > > >
    > > > > > > > > > > Dim MyDb As Dao.Database, MyRec As Dao.Recordset, MyStr As String
    > > > > > > > > > > Set MyDb = CurrentDb
    > > > > > > > > > >
    > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From Information Tracking Where
    > > > > > > > > > > ItemDescNo_ = '" & Me.ItemDescNo_ & "'")
    > > > > > > > > > >
    > > > > > > > > > > If Not MyRec.EOF Then
    > > > > > > > > > > MyRec.MoveLast
    > > > > > > > > > > MyStr = Me.ItemDescNo_ & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > MyRec.MoveFirst
    > > > > > > > > > > While Not MyRec.EOF
    > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > MyStr = MyStr & MyRec![EmplName]
    > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > Wend
    > > > > > > > > > > MsgBox MyStr
    > > > > > > > > > > End If
    > > > > > > > > > > End Sub
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > Can you post the full code that you are using, and not the one I posted?
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > --
    > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > >
    > > > > > > > > > > > > ok, that did the trick but another error came up run time error 3131 Syntax
    > > > > > > > > > > > > error in from clause
    > > > > > > > > > > > > in the following line!
    > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > > me.Item & "'")
    > > > > > > > > > > > >
    > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > >
    > > > > > > > > > > > > > Sorry, my mistake
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > change it to
    > > > > > > > > > > > > > MyRec![User_Name]
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > instead of a dot
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > --
    > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > >
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > >
    > > > > > > > > > > > > > > Ok that did the trick, but another error came up,
    > > > > > > > > > > > > > > method or data member not found on the following line
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > Did you change the names
    > > > > > > > > > > > > > > > TableName = The table name where the data is stored
    > > > > > > > > > > > > > > > Item = the name of the Item Field
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > It also seem that I forgot a & before the Me.Item
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '" &
    > > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > --
    > > > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > I have tried it but am having an error in the following line
    > > > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > its saying that I need a list separator or )
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > "Ofer" wrote:
    > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > Try this code
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > Dim MyDb as Dao.Database, MyRec as Dao.Recordset, MyStr as String
    > > > > > > > > > > > > > > > > > Set MyDb = currentdb
    > > > > > > > > > > > > > > > > > Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Item = '"
    > > > > > > > > > > > > > > > > > Me.Item & "'")
    > > > > > > > > > > > > > > > > > If not MyRec.Eof then
    > > > > > > > > > > > > > > > > > Myrec.MoveLast
    > > > > > > > > > > > > > > > > > MyStr = Me.Item & " Has " & MyRec.RecordCount & " Users"
    > > > > > > > > > > > > > > > > > MyRec.movefirst
    > > > > > > > > > > > > > > > > > While Not MyRec.Eof
    > > > > > > > > > > > > > > > > > MyStr = MyStr & vbCrLf
    > > > > > > > > > > > > > > > > > MyStr = MyStr & MyRec.[User_Name]
    > > > > > > > > > > > > > > > > > MyRec.MoveNext
    > > > > > > > > > > > > > > > > > Wend
    > > > > > > > > > > > > > > > > > End If
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > I didn't try the code, so you might get an erorr, also change the fields and
    > > > > > > > > > > > > > > > > > table name according to your data
    > > > > > > > > > > > > > > > > > This code need to be on the after update event of the Item field
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > --
    > > > > > > > > > > > > > > > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > > > > > > > > > > > > > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > > > > > > > > > > > > > > > know that my answer was helpful
    > > > > > > > > > > > > > > > > > HTH, good luck
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > "JOM" wrote:
    > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > > I have a field that a employee enters an item. and another field that the
    > > > > > > > > > > > > > > > > > > employee enters the user of that item....
    > > > > > > > > > > > > > > > > > > What I would to see is when a user enter enters an item, a popup will appear
    > > > > > > > > > > > > > > > > > > and inform the user that there are several users of that item and list the
    > > > > > > > > > > > > > > > > > > users e.g., if the employee enters printer xyz the following will appear!
    > > > > > > > > > > > > > > > > > >
    > > > > > > > > > > > > > > > > > > printer xyz has 4 users
    > > > > > > > > > > > > > > > > > > user1's name
    > > > > > > > > > > > > > > > > > > user2's name
    > > > > > > > > > > > > > > > > > > user3's name
    > > > > > > > > > > > > > > > > > > user4's name
     

Share This Page