Results 1 to 10 of 10
  1. #1
    pthomp52 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7

    Using Dlookup on a form

    Hi,


    I have am making a database for my son in law's building company. I have an employees table where each employee has a day rate for each employee.
    I have a costs table which was originally designed to log billable costs, which are input form a form using some combo boxes to select the cost type and building site and the "amount" field for each cost. This feeds an enquiry and report whereby a combo box selects the site to run the report which works fine. I wanted to add a cost category of labour which I have done, and have created a form to input the labour into the costs table. I have created a combo box which used the employees table to give the drop down choice to select the employee and this data goes into the "company" field in the costs table and works fine. I have also a combo box for the site which they worked on for that day. What I want to do now is have access look up in the employees table what their day rate is, and enter it in the amount field when the employee is selected from the combo box.

    I have tried to do this using Dlookup with the following string as an after update on the combo box to select the employee.

    Amount = DLookup("Day Rate", "Employees", "Employee Name='" & Company & "'")

    I want the "amount" to retrieved from the employees table from the Day Rate field when the Employee Name is the same as the Company field (which is where the employee name goes once selected from the combo box on the form), both the Amount field and the Day Rate field are formatted as currency.
    When I run this though I get an error state which says Run time error 3075 Syntax error (missing operator) in query expression 'Day Rate'

    Any ideas what I am doing wrong?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    Amount = DLookup("[Day Rate]", "Employees", "Employee Name='" & Company & "'")

    One of the "pit falls" with having spaces in the names of objects is the need to use square brackets.
    IMHO it's better NOT to have spaces eg: DayRate
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    If you have spaces in field names (bad idea) surround them with [ and ]
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    pthomp52 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7
    Brilliant thank you guys that worked a treat! noted about the spaces will not use them in future

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by pthomp52 View Post
    Brilliant thank you guys that worked a treat! noted about the spaces will not use them in future
    You're welcome. Always glad to help if I can
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    pthomp52 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7
    Now I have fixed that, I want to have the cost type auto fill the word Labour on update of the Employee Name in the Form, I am in the add labour form so I know that every time I add and employee name that the cost type is labour and don't want to have to choose it from a combo box each time. What is the VB code I need for that? Tried sereval things but no joy must just be using the wrong syntax.

    Private Sub Combo101_AfterUpdate()
    Type = (Labour)
    End Sub

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    Me.Type = "Labour"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Type is a reserved word and a Control property, so is a very poor choice of field name.
    CostType or BillType would be much better and more descriptive when you revisit things in 6 months.

    Also refer to the control names on the current form by using Me. in your code - it will give you intellisense.

    So assuming your control was called txtCostType your code should be and your field is a text field then something like

    Me.txtCostType = "Labour"

    Should work. If the Cost type is a Combo and you have a hidden Id field then you would need to set it to the ID number.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    pthomp52 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7
    OK now I have the costs and labour all working how I want them, I am trying to set up payment schedules. I have created a new table Payment_Sched_Tab in which I have a filed called Job_Site and Customer. I have created a form with all of the fileds from the Payment_Sched_Tab table and made Job_Site a combo box called Job_Site_Combo. I want the customer field to auto fill with D lookup but cannot make it work. I am using after update event in the code builder of teh property sheet for the Job_Site_Combo property sheet. I have a teble called JobSitesT where the customer information is kept, and I want the Dlookup to fill in the Customer field in the form from the JobsitesT table CustomerName.
    I was therefore using the following string Customer =Dlookup("CustomerName","JobSitesT","SiteName='"&J ob_Site_Combo&"'") But this doesn't work and I Keep getting syntax error any advice? Is there a better way to do it? by the way it displays here with a space between J and ob but when I edit the post it doesn't show that.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I would expect the combo would be an autonumber, so lose the quotes around the variable.

    Use the code tags (# on the toolbar) if you want to keep text as it is really.

    Code:
    Test for code tags
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Form Dlookup
    By jbtyler in forum Forms
    Replies: 1
    Last Post: 08-02-2019, 03:33 PM
  2. DLookup not working for form
    By ndehhh in forum Programming
    Replies: 6
    Last Post: 10-15-2015, 02:38 PM
  3. DLOOKUP in textbox on form
    By laavista in forum Access
    Replies: 6
    Last Post: 10-20-2014, 06:24 AM
  4. Dlookup in form
    By GregTheSquarePeg in forum Forms
    Replies: 1
    Last Post: 09-05-2013, 04:47 AM
  5. Form Dlookup macro
    By lukekelly in forum Forms
    Replies: 2
    Last Post: 01-20-2010, 06:24 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