Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 2003: DLookup Errors

    Hi all its that pest again, this time its DLookups. (I've got to ask does anything ever work on the first or even 31st attempt?)

    I'm attempting to build a DLookup into my form. I've got a form (and table) with generic information about a set of equipment and a form (and table) with specific information about each weight within that set. I've got 3 fields I would like to auto update in the Weight information, the accuracy class, the date of aquisition and the prescribed limit of error. I realise this is not the most elegent method, but I haven't been able to see how to make a join work for what I want to do (I want to keep it as simple as possible - 1 form for viewing, updating and adding data).

    The dilema, I keep getting run time error 13: type mismatch on the Prescribed limit of error and date aquired fields. The only thing I can think of, is its down to the fact that I have a mixture of variables. The code I'm using is as follows:

    Private Sub WeightPrescribedLimitOfErrorInMG_AfterUpdate()

    Dim WeightPrescribedLimitOfErrrorInMG As Double
    Dim PLEWeightPrescribedLimitOfErrorInMG As Double
    Dim WeightAccuracyClass As String
    Dim WeightQuantity As String

    WeightPrescribedLimitOfErrorInMG = DLookup("PLEWeightPrescribedLimitOfErrorInMG", _
    "tblPLEsWeights", "[WeightAccuracyClass] = ' " & Forms!frmSet!subfrmWeightA!WeightAccuracyClass & " ' " _
    And "[WeightQuantity] = ' " & Forms!frmSet!subfrmWeightA!WeightQuantity & "' ")


    End Sub

    Private Sub WeightAquiredOn_AfterUpdate()

    Dim WeightAquiredOn As Double
    Dim SetAquireOn As Double
    Dim SetID As String

    WeightAquiredOn = DLookup("SetAquireOn", _
    "tblSet", "[SetID] = ' " & Forms!frmSet!subfrmWeightA!SetID& " ' ")


    End Sub

    If anyone could shed any light on this I would really appreciate it.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what does your code actually look like?
    set a breakpoint on the code and examine it
    this sort of error usually occurs when you don't quote text literals or there is some fault that means the code which is valid VBA fails as SQL. effectively the 3rd parameter is a 'WHERE' clause without using the word WHERE
    looking at your code
    Code:
    "[WeightAccuracyClass] = ' " & Forms!frmSet!subfrmWeightA!WeightAccuracyClass & " ' " _
    And "[WeightQuantity] = ' " & Forms!frmSet!subfrmWeightA!WeightQuantity & "' "
    im suspicious of a variable called Forms!frmSet!subfrmWeightA!WeightQuantity but quoted sop its type string / text / character, I'd expect that to be numeric, in which case it shouldn't be quoted. For information date literals should be supplied in ISO (yyyy/mm/dd) or US (mm/dd/yyyy) delimited by a hash symbol. eg:-
    Code:
    avariable = "mydatecolumn = #2013/07/20#"
    but check that you have values in Forms!frmSet!subfrmWeightA!WeightAccuracyClass and Forms!frmSet!subfrmWeightA!WeightQuantity
    check they are sane (ie values of the type and range that you'd expect.
    check that the datatype of the variable you are assigning the lookup is of the same or suitable type.

    form your code you are saying the values of Forms!frmSet!subfrmWeightA!WeightAccuracyClass And Forms!frmSet!subfrmWeightA!WeightQuantity are type string
    if that isn't the case then thats your problem
    say Forms!frmSet!subfrmWeightA!WeightAccuracyClass = "A"
    and Forms!frmSet!subfrmWeightA!WeightQuantity = "10lb"
    then the code should work OK. that should reslove to
    Code:
    "[WeightAccuracyClass] = 'A' And "[WeightQuantity] = '10lb'
    one thing to bear in mind is that the quote mark DELIMITS the literal value so you MUST not have spaces between it and the thing its supposed to delimt
    Code:
    "[WeightAccuracyClass] = ' " & Forms!frmSet!subfrmWeightA!WeightAccuracyClass & " ' " _
    And "[WeightQuantity] = ' " & Forms!frmSet!subfrmWeightA!WeightQuantity & "' "
    converts to
    Code:
    "[WeightAccuracyClass] = ' A ' And "[WeightQuantity] = ' 10lb '
    NOT
    Code:
    "[WeightAccuracyClass] = 'A' And "[WeightQuantity] = '10lb'
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013
    Posts
    81
    Hi Healdem thanks for the quick response I really appreciate it.
    *
    I'm not familiar with SQL, I've seen bits when I'm searching the net but I've never used it.* From the little I do know I can't see why my code wouldn't translate to SQL.* Basically what I'm trying to say is I want the programme to tell me the prescribed limit of error (field name PLEWeightPrescribedLimitOfErrorInMG), which is found in the table tblPLEsWeights where it matches the accuracy class (field name WeightAccuracyClass) and quantity (field name WeightQuantity) of the weight record open.**
    *
    I've checked to make sure there are records in the relevant fields (there are) and also double checked the field names in tblWeight and tblPLEsWeights for the criteria field are the same (I've copied and pasted the field names between tables and my code to be sure).
    *
    I've chosen to use a string type variable for the Weight quantity as I'm recording the value and weight denomination in the same field (e.g. 200g).
    *
    With regard to the quote marks, I've just played around deleting the spaces and ended up with (I did the same for both of the criteria) "[WeightQuantity] = '" & Forms!frmSet!subfrmWeightA!WeightQuantity & "'" Access wouldn't accept me removing any more spaces.* I've ran the programme again, but got the same error.
    *
    While I'm thinking about it, does specifying the form location for the criteria fields make any real difference.* Some websites say to specify the form location (as I did) whereas others simply put the field names.* I've tried both methods and had the same errors, but I have wondered if I've specified the wrong form location for the criteria.* The form location I've specified is the form I want the prescribed limit of error returned to (if that makes sense).
    *
    If it works, I would expect to be able to say in the weight record that I have a 200g weight of class M2 and have the programme tell me the prescribed limit of error for that weight is 30mg (although its recorded in the system as 30.00).
    *
    Thinking about it, another possible downfall is with the prescribed limit of error variable.* In the tables I've asked it to treat the value as a decimal (many of my values are to 2 decimal places), but VBA doesn't seem to like a decimal variable, so I went with Double (seemed the most logical at the time).
    *
    Nearly forgot, I'm not sure what you mean when you ask what does my code actually say.* I've posted it exactly as it appears in the VBE.* Although I did forget to highlight where the error appears, so I will go back and do that in a moment.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok if both things you are looking for are string then you must delimit them so the SQL engine knows where the text to match against starts and stops. which you have done (sort of)
    except you have used a space between the delimiter AND the text to be matched
    "[WeightAccuracyClass] = ' A ' And "[WeightQuantity] = ' 200g '
    NOT
    "[WeightAccuracyClass] = 'A' And "[WeightQuantity] = '200g'.
    those extra spaces ARE important to a computer

    there is a world of difference between the VBA that creates the SQL AND the actual code sent to the SQL engine. bear in mind SQL is a separate language, a lnguage that isn't validated by the compiler in Access, but the compiler (read parser) in the SQL engine

    Code:
    "[WeightAccuracyClass] = ' " & Forms!frmSet!subfrmWeightA!WeightAccuracyClass & " ' " _
    And "[WeightQuantity] = ' " & Forms!frmSet!subfrmWeightA!WeightQuantity & "' "
    compiles, ie is valid VB but that is not what is sent to the SQL engine.
    problems could arise if
    there is no value in Forms!frmSet!subfrmWeightA!WeightQuantity OR Forms!frmSet!subfrmWeightA!WeightAccuracyClass
    the values could be incompatable
    the values may be valid in VB but incomprehensible in SQL eg a date or alphanumeric in a numeric column.
    putting a breakpooint on the code and actaully examining the values can often reveal quickly where the fault is

    its easier to spot
    [WeightAccuracyClass] = ' A ' And "[WeightQuantity] = ' 200g '
    as the value sent to the sql engine than looking at the VB that creates the SQL, as unless you hard code values during testing you have no proof of what is being sent just an assumption.

    a smarter way of developing anything that uses SQL (and although purists would say dlookup isn't SQL, and they'd be right) is to assign the value of what you are building in VB to a variable. then either examine the variable in a breakpoint or output to a msgbox or even dispaly on a form / report

    incidentally what do you propsoe to do when the dlookup doesn't find a value

    before using values in a dlookup or SQL then I'd strongly recommned that those values are sane.
    ie how do you know that there is a valid value in either of the parameters
    good system design should take into account expected problems and handle them gracefully. it means testing for all errors you can think of. and always have a backup strategy for those errors you didn't think of. that means error trapping.

    as regards your tolerances issue
    VBA can handle decimals fine but be aware that using type double or single can cause rounding errors. I doubt you will hit that problem
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2013
    Posts
    81
    Hi Healdem, thank you for your quick response I really appreciate it. Sorry I haven't replied sooner. I can only work on the database at work (I don't have Access at home) and I've not been in the office much the last few days. Thankfully (and in some strange way annoyingly) this database is a very very small part of my job.

    I've attempted altering the code temporarily to search for one specific value and also removing the rights spaces (I hope) as follows:

    "Private Sub WeightPrescribedLimitOfErrorInMG_AfterUpdate()

    Dim WeightPrescribedLimitOfErrrorInMG As Double
    Dim PLEWeightPrescribedLimitOfErrorInMG As Double
    Dim WeightAccuracyClass As String
    Dim WeightQuantity As String


    WeightPrescribedLimitOfErrorInMG = DLookup("PLEWeightPrescribedLimitOfErrorInMG", _
    "tblPLEsWeights", "[WeightAccuracyClass] ='M2'" And "[WeightQuantity] ='500g'")



    End Sub"

    The weight record I'm playing with is for a 500g weight of class M2, so it made sense to pick those. The figure I expect Access to find is 80.00. This figure is entered in the tblPLEsWeights table. (Just in case I didn't mention it before, the PLEsWeights table has a joint primary key, it uses the weight quantity and its accuracy class as the primary key)

    Once again however, the code results in a type mismatch error (the code I've highlighted in red, is the section the VBE highlights. The little arrow points to the last line of code)

    After the error has activated I've looked at the result in the locals box it tells me that the two fields I've specified as doubles have a value of 0 and the two fields I've specified as strings have a value of ""

    Just out of curiosity I've tried removing the And section of the criteria and reducing it to a single value. Oddly enough that works. With just searching for the weight quantity I've used:

    Private Sub WeightPrescribedLimitOfErrorInMG_AfterUpdate()

    Dim WeightPrescribedLimitOfErrrorInMG As Double
    Dim PLEWeightPrescribedLimitOfErrorInMG As Double
    Dim WeightAccuracyClass As String
    Dim WeightQuantity As String


    WeightPrescribedLimitOfErrorInMG = DLookup("PLEWeightPrescribedLimitOfErrorInMG", _
    "tblPLEsWeights", "[WeightQuantity] ='" & Forms!frmSet!subfrmWeightA!WeightQuantity & "'")


    End Sub

    This gives me an answer of 25 (which is correct for a 500g weight of class M1 - but I want class M2). As soon as I attempt to add a second criteria, the programme throws up a type mismatch error. Considering both criteria work singly (although give me some interesting results), are of the same type and reside in the same table, I cannot understand why Access can't cope with them as joint criteria. By the way I tried this code with both the Forms!... option and the specific criteria (e.g. M2 and 500g) and both ways gave me the same answers.

    I'm very new to VBA and debugging, I'm not sure how to go about setting a variable and then examining the variable in a breakpoint.

    Error handling, is something I definately plan to add, just hadn't quite worked out how. There will eventually be a weight that wont have a corresponding record in the PLEs table (although this shouldn't be a regular occurance). I'd initially hoped to basically tell Access to find the nearest value (so for example with a 4g weight I would expect Access to return the PLE for a 5g weight rather than a 2g, but I'm not sure I can do this. In any case as this is unlikley to be a regular occurance I think I will set up (or at least try to) error handling which brings up a message box instructing the user to enter the PLE manually.

    I will need to move my code to another text box on the form (I didn't really think it through initially) but for the moment I'm concentrating on why it wont work. I will also be looking at adding an IF statement, which basically says that if box X and box Y aren't complete, don't do anything. My code will then be linked to the after update event of box X and box Y. (I've just moved the code that works to box X and it still works fine).

    As for rounding errors, this is something I need to avoid. Eventually I need the programme to be able to determine whether a weight has passed or failed testing based on errors. The smallest permitted error possible is 0.003mg. As I'm working with very small tolerances and the English legal system I need to avoid rounding errors. How do I go about programming it to a Decimal. I'd initially attempted Dim WeightPrescribedLimitOfErrrorInMG As Decimal, but VBE threw up "Compile Error: Expected New or Type Name". I had a read through the help section, but am none the wiser.

    I really appreciate your help with this. I'm finding all this very interesting but extremely frustrating. It may just be my tired brain getting paranoid but it seems Access is fighting me every step of the way. So far I've got about 10 A4 pages of notes on the codes I've tried for 2 procedures (and I've not written everything down).

  6. #6
    Join Date
    Jun 2013
    Posts
    81

    It works!!!!!

    I've fixed it ! Such a simple little thing as well. The code I now have is:

    Private Sub WeightPrescribedLimitOfErrorInMG_AfterUpdate()

    Dim WeightPrescribedLimitOfErrrorInMG As Double
    Dim PLEWeightPrescribedLimitOfErrorInMG As Double
    Dim WeightAccuracyClass As String
    Dim WeightQuantity As String


    WeightPrescribedLimitOfErrorInMG = DLookup("PLEWeightPrescribedLimitOfErrorInMG", _
    "tblPLEsWeights", "[WeightQuantity] ='" & Forms!frmSet!subfrmWeightA!WeightQuantity & "'" _
    & " AND [WeightAccuracyClass] = '" & Forms!frmSet!subfrmWeightA!WeightAccuracyClass & "'")

    End Sub

    I really don't get VBA, most of the sites I've found with dlookup information for multiple criteria puts the AND outside of the quotes. I've found exactly one reference to AND inside of the quotes (and believe me I've seen a lot of different codes) and that works!

    Healdem many many thanks for your input with this, I really appreciate the help.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one trick you can use to avoid confusion is to use the the chr function
    so instead of
    Code:
    DLookup("PLEWeightPrescribedLimitOfErrorInMG", _
    "tblPLEsWeights", "[WeightQuantity] ='" & Forms!frmSet!subfrmWeightA!WeightQuantity & "'"
    use
    Code:
    DLookup("PLEWeightPrescribedLimitOfErrorInMG", _
    "tblPLEsWeights", "[WeightQuantity] =" & chr(39) & Forms!frmSet!subfrmWeightA!WeightQuantity  & chr(39)
    does it make nay functional difference... No, but it can make it easier to read, and easier to spot where problems lie
    if you need to use the " instead of ' then use chr(34)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2013
    Posts
    81
    Thanks Healdem, I'll keep that in mind for future attempts.

  9. #9
    Join Date
    Jun 2013
    Posts
    81

    Question More DLookup Woes

    I'm back again, DLookups are rapidly becomming the bane of my existence.

    I'm attempting to reduce user input in the Weight Record, by asking Acess to automatically populate the accuracy class and date aquired field based on the information recorded in the Set table. I'd hoped to set it up so that when you create a new record this will happen, but couldn't quite see how. So I've settled for setting up an after update DLookup (at the moment on the WeightQuantity field but I will extend it to a couple of other fields). So far I've only worked on the WeightAccuracyClass automation. The code I'm using is:

    Private Sub WeightQuantity_AfterUpdate()
    On Error GoTo ErrorHandler

    Dim WeightPrescribedLimitOfErrrorInMG As Double
    Dim PLEWeightPrescribedLimitOfErrorInMG As Double
    Dim WeightAccuracyClass As String
    Dim WeightQuantity As String
    Dim SetAccuracyClass As String
    Dim SetID As String

    WeightAccuracyClass = DLookup("SetAccuracyClass", "tblSet", _
    "[SetID] = '" & Forms!frmSet!subfrmWeightA!SetID & "'")


    ' Test if the weight accuracy class has been completed - if not blank, find PLE
    #other DLookup code mixed with ID statement that works.

    Exit Sub

    ErrorHandler:
    MsgBox Err.Description
    Exit Sub

    End Sub

    This code isn't giving me any errors, it just doesn't give me any values. I've also attempted to switch to a hard coded criteria (Metric 22) with the same result. I got lucky and found some great debugging help using the immediate window and have now broken down the code into sections and evaluated each section (and the whole thing) in the immediate window. So long as I don't include the WeightAccuracyClass = bit it evaluates correctly to "M1". If I include WeightAccuracyClass = at the start of the DLookup code, it evaluates to True.

    I can't understand whats going on here. Has anyone got any ideas?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is setid numeric or alphanumeric?
    what happens if you put a messagebox after the dlookup

    Code:
    WeightAccuracyClass = DLookup("SetAccuracyClass", "tblSet", _
    "[SetID] = '" & Forms!frmSet!subfrmWeightA!SetID & "'")
    if isnull(WeightAccuracyClass ) then
       msgbox ("bollux, dlookup returned null")
    else
      msgbox ("my Weight Accuracy Class is:" & WeightAccuracyClass
    endif
    or a marginly debug friendlier
    Code:
    dim strWhereClause as string
    strWhereClause = "[SetID] = '" & Forms!frmSet!subfrmWeightA!SetID & "'"
    WeightAccuracyClass = DLookup("SetAccuracyClass", "tblSet", strWhereClause)
    if isnull(WeightAccuracyClass ) then
       msgbox ("bollux, dlookup returned null." & vbcrlf & strwhereclause)
    else
      msgbox ("my Weight Accuracy Class is:" & WeightAccuracyClass
    endif
    code rarely does 'nothing'. I'd strongly recommend that you develop some debugging skills. The Access IDE has a very very good debugger.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2013
    Posts
    81
    Thanks for the link Healdem, I'll have a dig. I've got an e-book which touches on debugging and have found a couple of other bits of information, but they haven't really helped. Hopefully I can find a good debugging guide from your link.

    I've just tried your first suggested code, it opens up a message box saying "my Weight Accuracy Class is: M1", which is the correct accuracy class. However, the field I need the information to be included in remains blank.

    Just for completeness I've also tried your second code, which also results in a message box saying "my Weight Accuracy Class is: M1". I've stepped through the code and cannot see anything which would explain why the code isn't entering the information in the WeightAccuracyClass field (and to be sure I've copied the field name from the properities box for that field). But if I'm honest I probably wouldn't spot anything anyway.

    I really appreciate your help with this.

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You're Dimming a Variable as SetID as a String, then using it here

    WeightAccuracyClass = DLookup("SetAccuracyClass", "tblSet", _
    "[SetID] = '" & Forms!frmSet!subfrmWeightA!SetID & "'")

    In your code the first instance of SetID has to be an actual Field Name in the tblSet Table, and in the second instance it has to be the actual name of a Control on the subfrmWeightA Form.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one of the gotcha's that often cause problems is that plain vanilla access can be too accomodating. I'd strongly recommend that you force explicit variable declaration. there is an option (IIRC) in A2007 on
    or for preference start every code (whether thats a form, report or module) with OPTION EXPLICIT.
    if weightaccuracyclass is a control then it should be
    weightaccuracyclass.text or possiblye weightaccuracyclass.value
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jun 2013
    Posts
    81
    Hi Missinglinq, thank you for your comments. SetID is the name of the field in tblSet and is also the name of the control in subfrmWeightA. Just to double check I've not made any errors (I don't rename fields once I've created the tables, nor to I alter any of the control names (other than labels) all of which are bound to the original fields in the tables), I've copied the field name from frmSet and subfrmWeightA into the appropriate spaces and had the same result as previously.

    Hi Healdem many thanks for your help. I've already set Access to force explicit variable declaration, its already picked up a couple my typo's in the last few weeks (handy tool).

    If I've understood form controls correctly, WeightAccuracyClass is a bound control within subfrmWeightA, it is also a field name tblWeight. I don't understand why you would refer to the control as WeightAccuracyClass.Text (or value). This is the first time I've come across this. Regardless I've tried adding .text and .value (seperately of course) after WeightAccuracyClass in the formula (so it reads WeightAccuracyClass.Value =" Both versions bring up a "compile error: invalid qualifier" and highlights WeightAccuracyClass. Have I entered the .text / .value incorrectly?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it all depends
    you mention that you are using a sub form. depending on where this code is running you may have to provide the name of the form (you don't if the code is running in the same form, but you would if say the code runs in the main (parent) form and the control is in the sub form. its just the way access does things.

    lots of developers deride Access but it does a very good job of hiding what its doing and making things 'too' easy. Access has various default properties for objects and depdnign on context it can work out hat property you (probably) want. but to avoid confusion I prefer to explictly refer to the property or methid iI want to use. those from a more anal language such as C, C++, Java and the likefind that an anathema. I find its quite usefull when coding to define variable using camel case such as WeightAccuracyClass but when using those variables /controls etc use lower case as intellisense will change capitalisation to match the eralier definition. so you have clear confirmation as you develop your code that the variable you have just typed exists.

    so lets assume
    you have a parent form called MyParentForm, a child form called MyChildForm, then in code running in the child form, where you want to pull values from the parent form you must explicitly tell Access to look for the value from the parent form.

    eg
    Code:
    MyLocalVariable = forms!myparentform!thatcontrol.value
    and vice versa, so in the parent form
    Code:
    thatcontrol.value = forms!mychildform!MyLocalVariable
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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