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? - Dim db As Database
-
Dim rst As Recordset
-
Dim rst2 As Recordset
-
Dim strSQL As String
-
Dim Widthval As Integer
-
Dim Depthval As Integer
-
Dim Heightval As Integer
-
Dim IntWithGrain As Integer
-
Dim IntAcrossGrain As Integer
-
Dim StrBoth As String
-
-
DoCmd.SetWarnings False
-
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")
-
-
'Lookup tables for values and place in form
-
-
Do
-
rst.Edit
-
'If IsNull(rst![WithGrain]) Then
-
'IntWithGrain = InputBox("Please Enter the WithGrain Value", "Criteria Required")
-
'rst![WithGrain] = IntWithGrain
-
'Else
-
rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)
-
'If IsNull(rst![AcrossGrain]) Then
-
'IntAcrossGrain = InputBox("Please Enter the AcrossGrain Value", "Criteria Required")
-
'rst![AcrossGrain] = IntAcrossGrain
-
'Else
-
rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)
-
'If IsNull(rst![Edged]) Then
-
'strEdged = InputBox("Please Enter the Edged Value", "Criteria Required")
-
'rst![Edged] = strEdged
-
'Else
-
rst!Edged = DLookup("Edged", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)
-
'End If
-
'End If
-
'End If
-
rst.update
-
rst.MoveNext
-
Loop Until rst.EOF
-
rst.Close
-
Me.Requery
-
Set rst = Nothing
-
-
Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE WithGrain <> Null and AcrossGrain <> Null")
-
-
'Update the Paramters Width, Depth and Height with the New Inputted Values
-
-
Do
-
rst.Edit
-
-
Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")
-
[Widthval] = rst!Width
-
db.Execute ("UPDATE parmas SET [Value] = " & [Widthval] & " WHERE ParameterShortDesc = 'Width';")
-
-
Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")
-
[Depthval] = rst!Depth
-
db.Execute ("UPDATE parmas SET [Value] = " & [Depthval] & " WHERE ParameterShortDesc = 'Depth';")
-
-
Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")
-
[Heightval] = rst!Height
-
db.Execute ("UPDATE parmas SET [Value] = " & [Heightval] & " WHERE ParameterShortDesc = 'Height';")
-
-
rst!WithGrain = fCalcEquation(rst!WithGrain)
-
rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)
-
rst.update
-
rst.MoveNext
-
-
Loop Until rst.EOF
-
rst.Close
-
-
'Set Paramter Values back to Default after the Equation Event
-
-
Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")
-
db.Execute ("UPDATE parmas SET [Value] = 500 WHERE ParameterShortDesc = 'Width';")
-
-
Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")
-
db.Execute ("UPDATE parmas SET [Value] = 300 WHERE ParameterShortDesc = 'Depth';")
-
-
Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")
-
db.Execute ("UPDATE parmas SET [Value] = 130 WHERE ParameterShortDesc = 'Height';")
-
-
Me.Requery
-
Set rst = Nothing
-
-
DoCmd.SetWarnings True
-
-
'Enable the Specific Buttons to Progress, Disabling the Others
-
-
cmdCheckStock.Enabled = False
-
cmdAddStock.Enabled = False
-
cmdAssignDetails.Enabled = False
-
cmdUpdateInformation.Enabled = False
-
cmdcalcgrainmeasures.Enabled = False
-
cmdImportintoOrderline.Enabled = True
-
-
Exit_cmdCalcGrainMeasures_Click:
-
Exit Sub
-
-
Err_cmdCalcGrainMeasures_Click:
-
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")
-
-
'If the DLOOKUP Fails to input values then ask the User to Input the values themselves
-
-
Do
-
rst.Edit
-
If IsNull(WithGrain) Then
-
IntWithGrain = InputBox("Please Enter the WithGrain Value", "Criteria Required")
-
[WithGrain] = IntWithGrain
-
If IsNull(AcrossGrain) Then
-
IntAcrossGrain = InputBox("Please Enter the AcrossGrain Value", "Criteria Required")
-
[AcrossGrain] = IntAcrossGrain
-
If IsNull(Edged) Then
-
strEdged = InputBox("Please Enter the Edged Value", "Criteria Required")
-
[Edged] = strEdged
-
End If
-
End If
-
End If
-
rst.update
-
rst.MoveNext
-
Loop Until rst.EOF
-
rst.Close
-
Me.Requery
-
-
cmdCheckStock.Enabled = False
-
cmdAddStock.Enabled = False
-
cmdAssignDetails.Enabled = False
-
cmdUpdateInformation.Enabled = False
-
cmdcalcgrainmeasures.Enabled = False
-
cmdImportintoOrderline.Enabled = True
-
-
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; - If IsNull(WithGrain) Then
-
IntWithGrain = InputBox("Please Enter the WithGrain Value", "Criteria Required")
-
[WithGrain] = IntWithGrain
-
If IsNull(AcrossGrain) Then
-
IntAcrossGrain = InputBox("Please Enter the AcrossGrain Value", "Criteria Required")
-
[AcrossGrain] = IntAcrossGrain
-
If IsNull(Edged) Then
-
strEdged = InputBox("Please Enter the Edged Value", "Criteria Required")
-
[Edged] = strEdged
-
End If
-
End If
-
End If
Any help would be greatly appreciated! Thanks in advance.
Chris
1 3612 NeoPa 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: KLAU |
last post by:
I have a field that retrieves information from an expression in a
query. I have used a DLookup function to get the calculated field
from the query. However, the relationship is 1-to-many so one...
|
by: Tripp Knightly |
last post by:
I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to...
|
by: g.ormesher |
last post by:
Hi, my primary key is a field called "ind"
'Code
Dim varUP as Varient
Dim varIND as Interger
varIND = Me.ind ' this sets varIND to the index value
|
by: MLH |
last post by:
DLookup("", "tblPreliminaryVINs",
"=Forms!frmVINODO!SerialNum")
is giving me a Type Mismatch error. That's confusing to me
and I don't know how to circumvent it. The field in...
|
by: chris.thompson13 |
last post by:
I am having a problem setting the criteria part of the DLookup method
correctly and am consequently getting an error message.
I have a database of staff duties, part of which is a query (qryDaily)...
|
by: boyleyc |
last post by:
Hi all
the following code works perfectly well. Basically it populates a
series of check boxes on my form, depending on whether dlookup finds an
associated record.
The problem i have is that...
|
by: ApexData |
last post by:
When I use Dlookup. I am only able to return a single value and
therefore cannot seem to assign a single records (3-field values) to
(3-Variables). I noticed that I can get the 3-field values, but...
|
by: supertsik |
last post by:
Hi
After a lot of research I didn't manage to solve the following problem:
I have a table called 'Autos' with fields , ,
1 ¦ Mike ¦ BMW
2 ¦ Mike ¦ Ford
3 ¦ Mike ¦ Toyota
4 ¦ Nick ¦ Audi
|
by: rleepac |
last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue
This is a table used to...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |