Results 1 to 11 of 11
  1. #1
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63

    Post DLOOKUP on multi record form

    I am attempting to create a mulit record form named "Inventory IN" which will be used to check inventory in and maintain a log for all the items scanned in.


    For example:
    Use Enters part#...Dlookup finds Description....... user enters quantity received
    b13-211 ....................Wheel 10x15...................... 2
    c12-245.................... Seat Tractor .......................15
    N45-225....................Pogo Stick Red .....................3

    The idea is to enter an inventory part number in the "Part Number In" field, then have code to look up and display the "Part Description" automatically in an uncontrolled "Textbox13"

    The table to store the data is in the "Inventory In" Table which contains:

    ID an auto number field
    Time Stamp an auto date time field
    In Part Number a short text field since the part numbers are alpha numeric
    In Quantity a number field

    Note: I am not storing the Part Description field i just need it to display on the form


    The part descriptions are found in a Table Called "Part Description"
    this table has the fields:

    Id an auto id field
    Part Number a short text field since they are alpha numeric
    Descriptions a short text field


    I've tried all sorts of dlookup options but I cant see to get it right
    I know it should be something like

    What do I want to find?
    Description

    What Table has the info i need?
    Part Descriptions

    how do we find the match
    IN Part Number on current form = Part number on Descriptions Table


    So i may be way off but i think it should be something like
    DLookup("Description", "[Part Descriptions]="& "IN Part Number","Part Number")

    Code would be ok too but i'm not very good at writing code

    Thanks

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    The dlookup should look more like
    Code:
    dlookup("[Descriptions]", "[Part Descriptions]", "[Part Number]=" & [In Part Number])
    This is based on the field and table names as you've written them in your question. I suspect [Descriptions] is actually [Description]. Description is a reserved word and shouldn't be used as a field name. Here is a list of reserved words http://www.allenbrowne.com/AppIssueBadWord.html
    Also, you should try not to use spaces in your object names, for example [In Part Number] should be InPartNumber or in_part_number or something like that.

    [EDIT]
    Can you post a screenshot of the relationship view of your DB? I'm assuming that you DO NOT have separate Parts and Parts Descriptions tables, your Parts Descriptions table is probably better described as just a Parts table. If thats the case then you shouldn't be storing the Part Number in the Inventory In table but rather the part's auto id primary key. If you want to post your db I can take a look at it.

  3. #3
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    You are right about the table field name it should have been description, I changed that and put it in the "on focus" and its showing a compiler error. Any thoughts?
    Click image for larger version. 

Name:	Capture1.JPG 
Views:	54 
Size:	80.7 KB 
ID:	41374

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Is it textbox13 per OP or 11 per the screenshot?

    You'll have to assign the dlookup value to the textbox like this
    Code:
    Me.Textbox11 = DLookUp(yadda yadda yadda)
    The screenshot shows the GotFocus event, I would probably choose the after update event of the part number text box.

    All that said there might be a better way to do it by just selecting the description from a combobox. Can you post your database file?

  5. #5
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    INVENTORY.zipyou betchya! Thanks for you help

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your screenshot shows that the In Part Number field is actually text as its left aligned.
    So you need single quotes in the criteria part of your DLookup expression
    Code:
    Dlookup("[Descriptions]", "[Part Descriptions]", "[Part Number]='" & [In Part Number] & "'")
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Thanks for posting your DB. I do see a few issues. In no particular order:

    * I suggest you rename all your table names and field names to something without spaces or special characters, and make sure they're not in the list of bad words I linked to in post #2.
    * You're using the part number to reference your part in the related tables when you should be using the ID or primary key field
    * You haven't set up your table relationships. I haven't watched this particular video but this guy has good beginner tutorials: https://www.youtube.com/watch?v=iCEMy7Khhss
    * Your orders table is set up such that an order will only ever be for one kind of part, usually I would assume one order could have many different kinds of parts?
    * You have separate tables for inventory coming in and going out, I would just make this one table with inventory coming in positive quantity and going out a negative quantity

    Here is an example data model design for Inventory and Sales, it might work for your situation. Check it out: http://www.databaseanswers.org/data_...ales/index.htm

    You can search for the keyword inventory on this page and find several more examples of data models for inventory databases. http://www.databaseanswers.org/data_models/index_all_models.htm

    I'm going to pull an @orange here and ask you to explain in a few short sentences, in plain english, what is the purpose of this database? What do you ultimately want this database to be able to do?

  8. #8
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    well first let me say that I'm only maybe 2 hours in to this database aside from the several hours i have spent on trying to figure out my main hurdle. The Dlookup issue. So this is my plan in its simplest starting way. The idea of the database is for inventory management. I want to be able to scan items in to my inventory and then later as they are used remove them. Think of a parts department. I want to be able to receive items by scanning them in and At the same time as I am receiving things my staff may be taking things out of my warehouse. The dlookup is a verification that im scanning in the right parts.

    So after this is built I will start by entering part numbers and descriptions in the part Description table. this table is just a reference table.

    From there as a parts are received i want to open a multi record form that will look like a spreadsheet where line 1 i scan a part, column 1 shows the part number, column 2 shows a description (taken from the dlookup of the descriptions table) them moves to column 3 where the user enters the amount received and column 4 is auto dated so that at a later time I can see all of the items received in a period of time.
    then the next record begins on line 2 and the whole process is repeated over and over again until the order is complete.
    so one order may show 15 to 20 lines of different parts.


    Now on the other hand I may be having a staff person using parts out of my inventory. The process is the same but it will be stored in the Inventory out table. basically the same process as receiving parts but this is for removing parts. I haven't done it yet but Ill probably add a job number so I can see where the parts went.

    Now I can run reports showing when parts came in or out and also show a total inventory number for all of my parts in my warehouse
    and also show where my parts went.

    The Dlookup is the part that is holding me up. Im going to take your suggestion though about renaming my fields, its been a long time since i created an Access Database so Im rusty and I can see how that will help later on.

    I recreated the database and named it Inventory 2.its attached.

    Hopefully it will give you a better sense of where Im going with this.
    I still have the same issue on the Parts In and Parts out forms. The issue is with the Lookup of the part number without storing it.
    Please keep in mind the database is very messy i built it in 3 hours, so please over look allot. : )


    I do have one question though. not really related, do I remember right that if there is a space in a field name the reference to the field has to be in quotes? IE [inventory in] needs to be "[inventory in]" or does it have to be [inventory_in]?

    Thanks again for your help
    Attached Files Attached Files
    Last edited by davedinger; 03-23-2020 at 09:41 PM. Reason: added attachment

  9. #9
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Well this is close,

    Private Sub Text23_GotFocus()
    Me.Text23 = DLookup("[Description]", "[TblPartDescription]", "[PartNumber] =" & "[PartInNumber]")


    End Sub

    but I guess I didn't mention that this is a continuous form. So what happens now is when i enter a part number and click on the description field (got focus event) it changes every record to show the same description (the answer for the record I'm on).

    any thoughts?
    thanks

  10. #10
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Got It!!
    I just needed to add the code to the Properties Control Source, I was trying to put it in the code builder

    =DLookUp("[Description]","[TblPartDescription]","[PartNumber] =" & "[PartInNumber]")

    Thanks for the help

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Sorry I didn't see your replies before and didn't get back to you.

    First of all I'm glad you get it working, but I am confused because it shouldn't!

    I downloaded your second database and plugged in your control source dlookup and it crashed my access. Refer back to isladogs reply #4, that dlookup should have single or double quotation marks for the part number since it's a string datatype (in red here)
    Code:
    =DLookUp("[Description]","[TblPartDescription]","[PartNumber] ='" & [PartInNumber] & "'")
    That said I have more suggestions.

    1. Instead of using the PartNumber field as a foreign key use the primary key.
    2. In your FrmPartsIn and FrmPartsOut use a combobox instead of a textbox to select the part, the part description can be displayed in the combobox with the part number OR the description text box can just grab the part description from the combo, all avoiding the DLookUp entirely. Note that with a combobox you can still type your part number but now the combobox control will "autocomplete" and find the part number as you type.
    3. Enable referential integrity in your relationships
    4. Why not combine your parts in and parts out tables, postive qty for parts in, negative qty for parts out.
    5. Change the name of the [Description] field to something else, "Description" is in the list of bad words for field/object names (ref. reply #2)


    I've attached a database that incorporates suggestions 1, 2, and 3. Check out how I modified the FrmPartsIn, I think you'll like the combobox functionality.
    Attached Files Attached Files

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

Similar Threads

  1. Dlookup in multi value
    By Carloj in forum Modules
    Replies: 9
    Last Post: 10-19-2018, 02:20 PM
  2. Replies: 7
    Last Post: 11-06-2017, 02:45 AM
  3. Replies: 3
    Last Post: 02-06-2013, 07:23 PM
  4. Multi-record form
    By dougie in forum Programming
    Replies: 2
    Last Post: 12-13-2011, 12:41 AM
  5. Totals in a multi-record sub form
    By bginhb in forum Forms
    Replies: 7
    Last Post: 09-03-2011, 07:33 PM

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