Results 1 to 14 of 14
  1. #1
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7

    Dlookup error

    Hello guys!



    I had to turn to a forum where people are active. First of all I have to state that I have never learnt about ACCESS, and I make my database via trying.
    But now I get stuck with DlookUp. I searched forums, helps but my dlookup still comes back with #error.
    I have a table with data of colleagues' jobs done. I have another table with data which I feed my form from. Here I have full names (look nice in the report) and initials (easy to type in as parameter). I would like to automatize my form (or my table) the way that the initials get in field automatically by the full names.
    I have a "Data" table with the names and initials.

    Click image for larger version. 

Name:	Data.jpg 
Views:	23 
Size:	15.5 KB 
ID:	24738

    And the Form:

    Click image for larger version. 

Name:	Vágólap01.jpg 
Views:	22 
Size:	37.7 KB 
ID:	24739

    I wrote the following to the Field or argument line of the field "Initial" and I would like to store the INIT from the table "Data".

    =DLookUp("[INIT]";"[Data]";"[Name]=" & [FA])

    If I understand Dlookup well: The "Initial" field in the "Tasks" form will be fed with the "INIT" from the table "Data" which is in the same record as the "Name" and stored in the same form as "FA".
    So If I choose Wilbur Smith from the combo box in the "FA" field of my "Tasks" form I expect "WS" there. Unfortunately I see #error :-(
    I tried this too, but as I read somewhere if the field is in the same form the rest is not necessary to be written.
    =DLookUp("[INIT]";"[Data]";"[Name]=" & [Forms]![Tasks]![FA])

    My main goal is to feed the "Tasks" table with the initials. I tried to put this equation to the "Initial" filed of my "Tasks" table too, but did not work at all. If anybody know a better solution...

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your answer is here:

    DLookup Usage Samples

    but this may be easier and more efficient:

    BaldyWeb - Autofill
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What you see in the combo box - the name - might not be what it actually contains as a value. What is the row source for the combo box, how many columns are in the source, are any of them hidden, and what is the bound column?

    Your problem is easily solved I think, bit the solution will depend on how the combo box is set up.

  4. #4
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7
    pbaldy's answer solved a part of my problem. I expected a string as the initial, and with the apostrophes
    ´For strings: (note the apostrophe before and after the value)
    DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

    I see the initials now. But... at the beginning I thought if I see something in the "initial" field in my form, I will see it in my Tasks table too. Of course I was stupid, I replaced the connection between the form and the table in the Field or expression line with the dlookup :-(

    In my Tasks table I have three columns for names and three for the right initials. When I make my report there is a pop-up window (sorry, I do not know the proper English word for it in access) for the name. It is not that easy to write the entire name then the initial only. So my real problem is how to fill the initials automatically in my Tasks table, where I could use it as the parameter of my report from.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see the second link?

    By the way, your "pop-up window" sounds like a parameter prompt. You don't need to store the initials in the Tasks table, just base the report on a query that joins that table to the data table. That enables you to return the initials as one of the field for the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7
    Thanks Paul for the parameter prompt...
    I saw the second link, I just don't understand it. Either because of my English knowledge or my ACCESS one.
    For me the saving option would be better.
    First of all I don't know the right syntax of the Combo box: SELECT CustID, CustName, CustPhone FROM Customers Right now mine looks like: SELECT DATA.[Name], DATA.[Name] FROM [Data] ORDER BY [Azonosító]; Where should I put the INIT column?
    Then what to do with this: Me.TextboxName = Me.ComboName.Column(2)


  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Why are you selecting the Name field twice? Leaving it like it is:

    SELECT DATA.[Name], DATA.[Name], DATA.INIT FROM [Data] ORDER BY [Azonosító]

    Then make sure the column count and column widths properties of the combo have been adjusted. The code would go in the after update event of the combo:

    http://www.baldyweb.com/FirstVBA.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7
    Double selection: I did it with the wizard. I did not know that I select the field twice. Now I know I created every single combo box like this
    So I corrected those mistakes, and I replaced one of them for the "NAME" with the "INIT". So now I have this:
    SELECT [DATA].Name, [DATA].INIT FROM [Data] ORDER BY [Azonosító]
    The combo box works. I also created a text box with the Field or Formula line with this: =[Name].[Column](1) and now I see the initial. Great... so I don't even need the DlookUp (days of struggling ).
    I created another text box bound to the INIT column in the Tasks table but I couldn't force it to be filled with the INIT. I tried to use the VBA After Update Event procedure in the "Name" combo box Me.INIT = Me.Name.Column(1) but nothing happens. I don't see the very desired Initial in this text box and I don't see it in the Tasks table. If I write something to that text box it is copied into the table, so the bound is real.
    What is wrong with it?

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, Name is a reserved word thus not a good choice for an object name. Access would normally interpret Me.Name as the name of the form. Try changing the name of the combo at least.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7
    Actually the column name is not "Name", it is "IC", the combo is "FA" and the text box is FAINIT. I was careful not to use too common expressions.
    So it looks like: Me.FAINIT = Me.FA.Column(1)
    Still doesn't work. I was thinking whether VBA scripts are allowed or not, but when I open the database I don't see any messages about any restriction.
    I put the VBA under the FA combo. So when I call up for the name in the FA combo, the FAINIT should automatically show the initial of the FA (as the simple text box with the formula: =[FA].[Column](1) does it) and in the meanwhile copy the data to the Table.
    Is that right? For some reason ACCESS use this brackets [] everywhere auto. So I tried in the VBA... still no luck.
    The other strange thing is that ACCESS is translated to my language (that is why I don't know the proper English words) but VBA is not. So sometimes it is not easy to find out the right syntax.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here? If the "=[FA].[Column](1) " worked, the VBA should as well.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7
    It is quite a big file and I'm not in a place where I can upload that right now.
    I will upload a reduced version tomorrow.
    For testing reason I started creating a brand new database and I found something strange. I had only 2 columns in the INPUT table which provide the data for the combo box. I created the combo box with the help of the wizard... When I did it, it doubled the field for the IC (which stores the name) again. So I deleted the first part and then I saw the ID in the combo box instead of the name. So that is why I had every combo box selection doubled in my form. Another one: as I call for the name and the initial too, in this new database I see both in the same combo box, but my other database I see only the name!???
    By the way, the VBA script doesn't work in the new database...

  13. #13
    illes72zoli is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2016
    Posts
    7
    Paul, the problem is solved. Everything you wrote about the string was perfect. VBA scripts were disabled as well as the notification message that scripts are disabled.
    As I allowed the scripts running, the initials were nicely copied to the table.
    I really appreciate your help. If you come to Hungary I owe you a beer. Or more

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah, sorry, I forgot to mention making sure code was enabled or the db was in a trusted location. Glad you got it sorted. I'll remember that if I get to Hungary!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-04-2016, 09:40 AM
  2. Dlookup error
    By Risto85 in forum Programming
    Replies: 7
    Last Post: 02-25-2015, 11:06 AM
  3. DLOOKUP #Error
    By neo651 in forum Forms
    Replies: 6
    Last Post: 12-12-2012, 12:54 PM
  4. Error in Dlookup?
    By cindytan in forum Forms
    Replies: 1
    Last Post: 02-07-2012, 02:40 AM
  5. DLookup error
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 08-25-2010, 07:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums