Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dlookup function (2000)

    You may smile, but laughing will hurt my feelings.
    I have been selecting certain posts to duplicate in an attempt to learn how to get the same results.
    Post number 266165 from Hans shows how to use Dlookup to fill in a form from another table. His code apparently worked for the original poster.
    I'm getting the following error message. Compile error. Expected variable or procedure, not project.

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> The table I am referencing is a linked table. Does that make a difference?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dlookup function (2000)

    Hi Lady Gnome,

    I won't laugh at you. The error you get usually means that there is a name conflict in your database. You can't give a function or procedure the same name as a module, for instance. Please check your database for such conflicts.

    If you can't find the culprit, please post the DLookup expression you used, and give some details about the table and field names used in the expression.

    (I don't think that it matters that your table is a linked table)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup function (2000)

    please post the DLookup expression you used, and give some details about the table and field
    names used in the expression.

    Probably should have done this in the first place.
    This is a trial database, so the only tables in it are the linked table Employee Info, and Work Record. The form Work Record is intended to fill the table. What I want to happen is that when the field Person number is typed into the form, the fields Name and SSN will be filled in from the information in Employee Info.
    Here is what I have typed in the code module.

    Private Sub Person_number_AfterUpdate()
    [Name] = Dlookup("[Name]", "[Employee Info]", "[Name]=" & Chr(34) & Me.[Name] & Chr(34))
    [SSN] = Dlookup("[SSN]", "[Employee Info]", "[SSN]=" & Chr(34) & Me.[SSN] & Chr(34))

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup function (2000)

    I just noticed that your DLookup has a capital L and mine doesn't. So I went to correct it, and my database changes it back to lower case as soon as I move off the word. Now -- why would it do that? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup function (2000)

    Okay okay, My database was named Dlookup. Changed that. Changed my code to

    Private Sub Person_number_AfterUpdate()
    [Name] = DLookup("[Name]", "[Source for Work Record]", "[Person number]=" & Me.[Person number])
    [SSN] = DLookup("[SSN]", "[Source for Work Record]", "[Person number]=" & Me.[Person number])
    End Sub

    Now I'm getting Run error 2135 This property is read only and can not be set.
    Yes, the linked table is read only. So I did a query - Source for Work Record. Same message.

    Many of the tables I link to for querys etc, are read only. How do I get around this?
    Oh -- and is my code better this time?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dlookup function (2000)

    1. Name is a dangerous name for a field or control, because it is also a VBA property of many objects. It is possible that the assignment [Name] = ... is interpreted as an attempt to change the name of the form.

    2. Is the Work Record table read-only? In that case, you can't modify the Name and SSN fields, of course. The controls should be unbound (and preferably, have different names.)

    3. If Name and SSN are already in the Employee Info table, there is no need to store them in the Work Record table - it is redundant information, you can retrieve it in a query whenever needed.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup function (2000)

    . Name is a dangerous name for a field or control, because it is also a VBA property of many objects. It is possible that the
    assignment [Name] = ... is interpreted as an attempt to change the name of the form.

    Good point. I will change it to something else.


    2. Is the Work Record table read-only? In that case, you can't modify the Name and SSN fields, of course. The controls should
    be unbound (and preferably, have different names.)

    I just built the Work Record Table. It shouldn't be read only, I didn't tell it to be.

    3. If Name and SSN are already in the Employee Info table, there is no need to store them in the Work Record table - it is
    redundant information, you can retrieve it in a query whenever needed.

    Well, your right, of course. However, this is just an excercise to learn how to get fields on a form to default to values that are stored in another table. I was using Employee Info because it was available. I can enter info into the table Work Record, so it is not read only. I duplicated the form without the code, and can manually enter information. I also tried building a small Employee Info from scratch.(only four records) I still get the run error.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dlookup function (2000)

    If this is just an exercise, I would start with non-linked tables. Delete the link to the Employee Info table, then import it. See if you can get DLokup to work then.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup function (2000)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Yea Hans!!! I changed the NAME column to Person name and --- it rocks! I'm not sure how I am going to use this little piece of knowledge, but I'm sure it will come in handy.

    Thanks so much.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup function (2000)

    Tried that - didn't work.
    But changing the name of the field did. Go figure

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •