Results 1 to 12 of 12
  1. #1
    cheyanne is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Valencia, Spain
    Posts
    91

    Help required for DLookup function

    I am having a problem with this line of code




    txtCost = DLookup("[Cost]", "tblTreatments", "[TreatmentRef]=forms!frmVisits!frmSubTreatmentsByLine![txtTreatmentRef]")

    I have a table tblTreatments which includes fields Cost and TreatmentRef.

    I have a subform on frmVisits named frmSubTreatmentsByLine


    On form frmSubTreatmentsByLine I have a field Cost named txtCost

    Can anybody advise me why this DLookup code will not work.
    I have several other DLookups in the database and they all work as intended.

    I have spent the best part of an afternoon trying to identify a fault but cannot see one.


    I would really appreciate any assistance someone can offer me.


    Regards

    Cheyanne

  2. #2
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,442
    This may help to diagnose the issue. It may be the data type in your field that is causing this. Look at this link for the different syntax based upon the data type. Second look and I think you are missing an ampersand "&" from in front of the forms!.....

    http://www.techonthenet.com/access/f...in/dlookup.php

    Alan

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,667
    Variables must be concatenated into the expression. Do not enclose within quotes. Reference to control is a variable:

    txtCost = DLookup("[Cost]", "tblTreatments", "[TreatmentRef]='" & forms!frmVisits!frmSubTreatmentsByLine![txtTreatmentRef] & "'")

    If TreatmentRef is not text datatype, remove the apostrophe delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    cheyanne is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Valencia, Spain
    Posts
    91
    Hi

    I cut and pasted your line of code but I still have a problem.

    The field TreatmentRef is a numeric field so, I removed the apostrophe delimiters but it still will not work and I got error messages.

    The field datatypes are as follows
    txtCost is currency - and is the name of the field Cost, which resides on the frmSubTreatmentsByLine subform
    TreatmentRef is autonumber and resides on in tblTreatments
    txtTreatmentRef is the name of the TreatmentRef field that resides on the frmSubTreatmentsByLine subform.

    To make sure that I only removed what I should have done, would you be kind enough to write the line of code using
    TreatmentRef as a numeric field.

    Thanks

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,667
    If you just removed both apostrophes, syntax should be okay.

    What are error messages?

    Where is the code - behind what form, in what event? Post the entire procedure code or attach database. If you provide db, tell us what objects are involved - table, query, form, report.

    Referencing subform can be tricky. Why do you even need this?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    cheyanne is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Valencia, Spain
    Posts
    91
    Hi

    I only removed the apostrophes as you suggested.

    The code on the attachment is on the AfterUpdate event of the txtCost (name of the Cost field)
    which resides on the frmSubTreatmentsByLine subform. I have attached a screenshot of the
    code on the event. What other choices do I have?
    Click image for larger version. 

Name:	AfterUpdate.JPG 
Views:	17 
Size:	33.9 KB 
ID:	7139

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,667
    Why would you need this code if you enter data into txtCost? If you just entered a value in that field, this code, if successful, will overwrite the entry.

    Textbox txtCost is in the subform and txtTreatmentRef is also in the subform?

    Me.txtCost = DLookup("[Cost]", "tblTreatments", "[TreatmentRef]=" & Me.txtTreatmentRef)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    cheyanne is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Valencia, Spain
    Posts
    91
    Click image for larger version. 

Name:	Visits page.jpg 
Views:	6 
Size:	162.3 KB 
ID:	7141


    Hi
    I appreciate it is difficult to advise "blind" but the subform is required.
    I have attached a screenshot of the Visits entry page.


    On the subform I have a Combo Box which contains the types of treatments on offer with the
    appropriate cost for each treatment. This information comes from the Treatments table.
    What I was doing before was using the combo box to select the treatment, noting the cost,
    and then entering the cost into the Cost field. It would be much better if the cost could be taken
    from the tblTreatments directly and placed in the Cost field on the subform. Then each required
    treatment is selected in turn and the appropriate cost entered into the cost field automatically.
    The costs are then total on the subform footer.

    That total figure is then placed on the main form i.e. frmVisits and it, along with other costs
    produces an invoice for that client's visit to the surgery.

    I shall have several more of these DLookup exercises to do and have already managed to get
    text fields to work. Maybe the problem is because this is a number (Cost is a currency datatype)

    Thanks

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,667
    Datatype doesn't matter as long as the correct delimiters are used, if needed. Numbers don't, dates use #.

    If you want to eliminate manual entry of Cost and also want to save the Cost in effect at time record is created, I would use the AfterUpdate event of the Treatment combobox with the code in my last post.

    Is the Treatment combobox multi-column? Is the Bound column a number ID field? The DLookup references txtTreatmentRef as the criteria. Is that the combobox name?

    Showing the revised total cost on the main form might require a Requery or Refresh of the main form after the Cost value is saved:
    Forms!frmVisits.Requery

    Or don't bother showing total cost on the main form. The subform can show the total of the Cost field.

    Is the invoice a report object?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    cheyanne is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Valencia, Spain
    Posts
    91
    Click image for larger version. 

Name:	ComboBox - Form View.JPG 
Views:	3 
Size:	22.0 KB 
ID:	7143Click image for larger version. 

Name:	ComboBox.JPG 
Views:	3 
Size:	41.8 KB 
ID:	7144Click image for larger version. 

Name:	frmSubTreatmentsByLine.JPG 
Views:	3 
Size:	34.1 KB 
ID:	7145

    Hi

    I have attached some more images to try to assist you.

    The combo box is named cboTreatmentByLine and has three columns

    Column 1 is the treatmentRef (hidden)
    Column 2 is the description of the treatment (See attachment Combo box Form View)
    Column 3 is the cost of the treatment which I used to remember the cost, and then manually enter it into the Cost field which is of course outside of the combo box

    I apologise for the delay in replying but my time is now 22.35 (I live in Spain) which is why the labels on the visits from are in Spanish ' albeit I am from the U.K.
    Where are you based? And how many hours are you, presumably, infront of me.

    I have to say I am more than happy with the time you have spent on this matter and offer you my sincere thanks.

    If you reply again this evening IŽll reply tomorrow.

    Thanks again

    Cheyanne (Peter)

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,667
    Okay, since you have the cost in a column of combobox, don't need the DLookup. Try this in the AfterUpdate event of the combobox:

    Me!Cost = Me.cboTreatmentByLine.Column(2)

    Column index starts with 0 so the Cost column is index 2.

    My profile has a clue as to my location, a long, long way from Spain.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    cheyanne is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Valencia, Spain
    Posts
    91

    Many thanks

    Hi June7

    Worked like a treat (just needed Me.txtCost rather than Me.Cost)

    Many thanks again.

    No doubt you'll see other help messages from me in the future.

    Thanks

    Cheyanne
    Peter



    Quote Originally Posted by June7 View Post
    Okay, since you have the cost in a column of combobox, don't need the DLookup. Try this in the AfterUpdate event of the combobox:

    Me!Cost = Me.cboTreatmentByLine.Column(2)

    Column index starts with 0 so the Cost column is index 2.

    My profile has a clue as to my location, a long, long way from Spain.

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

Similar Threads

  1. DlookUp Function.
    By cap.zadi in forum Forms
    Replies: 11
    Last Post: 09-22-2011, 12:56 PM
  2. using the and function in a dlookup statement
    By englisap in forum Programming
    Replies: 10
    Last Post: 01-10-2011, 09:53 PM
  3. Format in Dlookup function
    By tpcervelo in forum Forms
    Replies: 6
    Last Post: 10-22-2010, 10:23 AM
  4. dlookup function problem
    By bdaniel in forum Programming
    Replies: 3
    Last Post: 04-26-2010, 05:55 AM
  5. Need Help for Dlookup function
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 01-04-2006, 08:18 AM

Tags for this Thread

Posting Permissions

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