By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

User InputBoxes If DLOOKUP Fails to Return Values Problem

100+
P: 129
Hi i am trying to get User input if data does not exist within a DLOOKUP table. I have gotten it to work for one record but not multiple, i have tried to incorporate my code into a loop procedure but i have failed. Could anyone give me any advice?

Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim rst2 As Recordset
  4.     Dim strSQL As String
  5.     Dim Widthval As Integer
  6.     Dim Depthval As Integer
  7.     Dim Heightval As Integer
  8.     Dim IntWithGrain As Integer
  9.     Dim IntAcrossGrain As Integer
  10.     Dim StrBoth As String
  11.  
  12.     DoCmd.SetWarnings False
  13.  
  14.     Set db = CurrentDb()
  15.     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")
  16.  
  17.     'Lookup tables for values and place in form
  18.  
  19.     Do
  20.         rst.Edit
  21.         'If IsNull(rst![WithGrain]) Then
  22.             'IntWithGrain = InputBox("Please Enter the WithGrain Value", "Criteria Required")
  23.             'rst![WithGrain] = IntWithGrain
  24.             'Else
  25.         rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)
  26.                 'If IsNull(rst![AcrossGrain]) Then
  27.                     'IntAcrossGrain = InputBox("Please Enter the AcrossGrain Value", "Criteria Required")
  28.                     'rst![AcrossGrain] = IntAcrossGrain
  29.                     'Else
  30.         rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)
  31.                         'If IsNull(rst![Edged]) Then
  32.                             'strEdged = InputBox("Please Enter the Edged Value", "Criteria Required")
  33.                             'rst![Edged] = strEdged
  34.                             'Else
  35.         rst!Edged = DLookup("Edged", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)
  36.                         'End If
  37.                 'End If
  38.         'End If
  39.         rst.update
  40.         rst.MoveNext
  41.     Loop Until rst.EOF
  42.     rst.Close
  43.     Me.Requery
  44.     Set rst = Nothing
  45.  
  46.     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE WithGrain <> Null and AcrossGrain <> Null")
  47.  
  48.     'Update the Paramters Width, Depth and Height with the New Inputted Values
  49.  
  50.     Do
  51.         rst.Edit
  52.  
  53.         Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")
  54.         [Widthval] = rst!Width
  55.         db.Execute ("UPDATE parmas SET [Value] = " & [Widthval] & " WHERE ParameterShortDesc = 'Width';")
  56.  
  57.         Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")
  58.         [Depthval] = rst!Depth
  59.         db.Execute ("UPDATE parmas SET [Value] = " & [Depthval] & " WHERE ParameterShortDesc = 'Depth';")
  60.  
  61.         Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")
  62.         [Heightval] = rst!Height
  63.         db.Execute ("UPDATE parmas SET [Value] = " & [Heightval] & " WHERE ParameterShortDesc = 'Height';")
  64.  
  65.         rst!WithGrain = fCalcEquation(rst!WithGrain)
  66.         rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)
  67.         rst.update
  68.         rst.MoveNext
  69.  
  70.     Loop Until rst.EOF
  71.     rst.Close
  72.  
  73.     'Set Paramter Values back to Default after the Equation Event
  74.  
  75.         Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")
  76.         db.Execute ("UPDATE parmas SET [Value] = 500 WHERE ParameterShortDesc = 'Width';")
  77.  
  78.         Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")
  79.         db.Execute ("UPDATE parmas SET [Value] = 300 WHERE ParameterShortDesc = 'Depth';")
  80.  
  81.         Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")
  82.         db.Execute ("UPDATE parmas SET [Value] = 130 WHERE ParameterShortDesc = 'Height';")
  83.  
  84.     Me.Requery
  85.     Set rst = Nothing
  86.  
  87.     DoCmd.SetWarnings True
  88.  
  89.     'Enable the Specific Buttons to Progress, Disabling the Others
  90.  
  91.     cmdCheckStock.Enabled = False
  92.     cmdAddStock.Enabled = False
  93.     cmdAssignDetails.Enabled = False
  94.     cmdUpdateInformation.Enabled = False
  95.     cmdcalcgrainmeasures.Enabled = False
  96.     cmdImportintoOrderline.Enabled = True
  97.  
  98. Exit_cmdCalcGrainMeasures_Click:
  99.     Exit Sub
  100.  
  101. Err_cmdCalcGrainMeasures_Click:
  102.  
  103.     Set db = CurrentDb()
  104.     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")
  105.  
  106.     'If the DLOOKUP Fails to input values then ask the User to Input the values themselves
  107.  
  108.     Do
  109.         rst.Edit
  110.         If IsNull(WithGrain) Then
  111.             IntWithGrain = InputBox("Please Enter the WithGrain Value", "Criteria Required")
  112.             [WithGrain] = IntWithGrain
  113.             If IsNull(AcrossGrain) Then
  114.                 IntAcrossGrain = InputBox("Please Enter the AcrossGrain Value", "Criteria Required")
  115.                 [AcrossGrain] = IntAcrossGrain
  116.                 If IsNull(Edged) Then
  117.                     strEdged = InputBox("Please Enter the Edged Value", "Criteria Required")
  118.                     [Edged] = strEdged
  119.                 End If
  120.             End If
  121.         End If
  122.         rst.update
  123.         rst.MoveNext
  124.     Loop Until rst.EOF
  125.     rst.Close
  126.     Me.Requery
  127.  
  128.     cmdCheckStock.Enabled = False
  129.     cmdAddStock.Enabled = False
  130.     cmdAssignDetails.Enabled = False
  131.     cmdUpdateInformation.Enabled = False
  132.     cmdcalcgrainmeasures.Enabled = False
  133.     cmdImportintoOrderline.Enabled = True
  134.  
  135.     Resume Exit_cmdCalcGrainMeasures_Click
This code here within the Err_cmdCalcGrainMeasures works fine without a loop however after trying to incorporate a loop within, i failed!

This is the simple code alone that works for one record only;

Expand|Select|Wrap|Line Numbers
  1.         If IsNull(WithGrain) Then
  2.             IntWithGrain = InputBox("Please Enter the WithGrain Value", "Criteria Required")
  3.             [WithGrain] = IntWithGrain
  4.             If IsNull(AcrossGrain) Then
  5.                 IntAcrossGrain = InputBox("Please Enter the AcrossGrain Value", "Criteria Required")
  6.                 [AcrossGrain] = IntAcrossGrain
  7.                 If IsNull(Edged) Then
  8.                     strEdged = InputBox("Please Enter the Edged Value", "Criteria Required")
  9.                     [Edged] = strEdged
  10.                 End If
  11.             End If
  12.         End If
Any help would be greatly appreciated! Thanks in advance.


Chris
Sep 20 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,314
Chris,

You really need to look at our Posting Guidelines. This sort of dumping 150 lines of code is not how it works (It's not considerate). Nor does it help you get a solution to your problem.

As for your fundamental question, I suspect you need to read the help section for DLookup() (and most of the Domain Aggregate functions really), where it explains that calls which find no results return the Null value.

Simple use of the Nz() function allows you to handle this logically.
Sep 22 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.