DLOOKUP criteria using variable

[Thumbs]

So I have been researching this one for a couple of days now, and have been experimenting with various solutions, but I havent come up with anything that works.

I am trying to use the DLOOKUP function to search a query for a value (with the criteria coming from a string variable), then compare that value to the selected record on the current form, and perform a task based on the discovery of a match between the two. I'll try to lay it out as detailed as possible.

I have a form (for asset data) with search fields that returns the search data in a subform in datasheet view. I have a button on that form called "cmdPMIDo". When the user presses that button, I want two fields on the selected record to update. One updates with the current date, and the second with a date that is 'x' number of days in the future.

I have a query based on a table that contains various maintenance tasks, the model number of the equipment it must be performed on, and the interval (in days) that each task must be performed. I am trying to use the model number as the connection between the specific asset and the maintenance task that needs to be performed on it.

I have included the code below that I am using to try to make this happen. I can push the button all day long and not get any errors, but nothing happens. My fields dont change value at all.



I am new to Access and VB, so I dont quite understand everything here. I am using the variable "passModel" to store the value of the model number field on the currently selected record on the form after the search has been performed. I am using the variable "lookupPMI" to search PMIQuery for a value matching that of passModel. I want to compare the two, to make sure they match, then insert today's date in one column of the currently selected record on the form, then add x number of days (listed in another column in PMIQuery) and put that value in the next column over.

 Private Sub cmdPMIDo_Click()

'Declare variables
Dim passModel As Variant
Dim lookupPMI As Variant

'Declare values
passModel = Me.AssetSearchSub.Form.Model
lookupPMI = DLookup("[ModelNumber]", "PMIQuery", "[ModelNumber]= "' & passModel &"' ")

'Compare ModelNumber of currently selected record with PMIQuery, then append date info as necessary
    If passModel = lookupPMI Then
        Me.AssetSearchSub.Form.LastPMIDate = DateValue(Now())
        Me.AssetSearchSub.Form.NextPMIDate = LastPMIDate + DLookup("PMIFrequency", "PMITasks", "[ModelNumber]='" & passModel & "'")
    
    End If
End Sub

I know I have something wrong with my code, and I think it is with the 'if' statement, I just can't seem to figure it out. If I can clarify anything, please let me know. Thanks in advance for your assistance.

[pbaldy]

Do you know how to set a breakpoint and step through the code, so you can see what the variables contain and what the code is doing?

http://www.baldyweb.com/Debugging.htm

[ssanfu]

 Private Sub cmdPMIDo_Click()

'Declare variables
Dim passModel As Variant
Dim lookupPMI As Variant

'Declare values
passModel = Me.AssetSearchSub.Form.Model
lookupPMI = DLookup("[ModelNumber]", "PMIQuery", "[ModelNumber]= " ' & passModel &"' ") <= Error

'Compare ModelNumber of currently selected record with PMIQuery, then append date info as necessary
    If passModel = lookupPMI Then
        Me.AssetSearchSub.Form.LastPMIDate = DateValue(Now())
        Me.AssetSearchSub.Form.NextPMIDate = LastPMIDate + DLookup("PMIFrequency", "PMITasks", "[ModelNumber]='" & passModel & "'")
    
    End If
End Sub

You have a single quote in the wrong place in the first Dlookup(). See the underline in the above code.

"Now()" is the date and time. If you want only the date, use "Date()".

I modified your code a little. See if this works:

Private Sub cmdPMIDo_Click()

'Declare variables
Dim passModel As String
Dim lookupPMI As String
Dim Freq as Integer

'Declare values
passModel = Me!AssetSearchSub.Form!Model
lookupPMI = DLookup("[ModelNumber]", "PMIQuery", "[ModelNumber]= '" & passModel &"'")

'Compare ModelNumber of currently selected record with PMIQuery, then append date info as necessary
    If passModel = lookupPMI Then
        Freq = DLookup("PMIFrequency", "PMITasks", "[ModelNumber]='" & passModel & "'")
        Me!AssetSearchSub.Form!LastPMIDate = Date()
        Me!AssetSearchSub.Form!NextPMIDate = DateAdd("d", Freq, Date())
    
    End If
End Sub

Single step through the code to see what values are returned

[John_G]

There is something wrong with your LookupPMI = DLookup.... statement. What do you want the DLookup to return - the model number or the PMI interval? (The variable name suggests the latter). The way you have it, the DLookup is always going to return the same model number as passModel, so the IF statement will always be true. That assumes the [ModelNumber] field in your query is correct - if it isn't, then your DLookup may always be returning a Null, in which case your IF statement will NEVER be true.

You also stated that the number of days to be added also comes from PMIQuery, but you never look that data up in PMIQuery - you use PMITasks instead.

Can you clear some of this up for us, please?

John

[Thumbs]

Ok, so I went through it line by line (thanks for the link PBaldy, I havent ever had to do that before). I had already caught the misplaced single quote. When I run line by line, the variable lookupPMI doesnt get a value after I assign it via the DLOOKUP.

Steve: After adding your modified code, it doesnt appear that the relation between the records is happening. The value of lookupPMI is listed as "" and freq has a value of 0.

Is there a problem using the same field as both the criteria and returned value in a DLOOKUP (as I do for the lookupPMI variable)? I really questioned that when i wrote the code, but I couldnt find any information to substantiate it either way. My inexperienced best guess is that the problem lies in the value declaration of that variable, or in the cpoarison of the two strings in the if statement.

[Thumbs]

There is something wrong with your LookupPMI = DLookup.... statement. What do you want the DLookup to return - the model number or the PMI interval? (The variable name suggests the latter). The way you have it, the DLookup is always going to return the same model number as passModel, so the IF statement will always be true. That assumes the [ModelNumber] field in your query is correct - if it isn't, then your DLookup may always be returning a Null, in which case your IF statement will NEVER be true.

You also stated that the number of days to be added also comes from PMIQuery, but you never look that data up in PMIQuery - you use PMITasks instead.

Can you clear some of this up for us, please?

John

Doh, you're right, I forgot to change PMITasks to the query, i was originally using the table itself, but decided to go with a query instead based on several recommendations I had read about similar issues in various forums.

Basically, I want passModel to return the model number from the currently selected record (which it does). I then need to match that returned string with the same value in PMIQuery. That specific model number is going to be associated with a certain PMI interval, so i need to find a way to associate those lines so that the right PMI interval is added to the current date.

If there is another way I need to be going about this, I am all ears. I am VERY green here and have been learning my way through Access, VB, and SQL by reading and doing...over and over again...until I get it right.

[Thumbs]

So the PMI table consists of Columns and Rows like this (there are more columns, but these are the only ones that matter at this point, the rest are just informational):

ModelNumber_|_TaskToPerform_|_Interval__
X3550 M3 .... | (insert task) .... | 180
xxxxxx...........| xxxxxx............ | xxx
xxxxxx...........| xxxxxx............ | xxx
xxxxxx...........| xxxxxx............ | xxx

The periods are only there as placeholders since this site keeps removing what it thinks are erroneous spaces.

I came up with a better way to explain what I need done. I need to search PMIQuery using the output of passModel as the only search criteria. That will return one record (because the model number will be a unique field in PMIQuery). I then need to use the interval "PMIFrequency" from that record as the number of days to add to the current date in the AssetData table (which is the source of the data for the query in the subform on the search page).

[ssanfu]

I'm getting confused with the different names for the fields and tables.

I don't like to use DLookup(), so I use queries. In the following code, check the field and table names.

Private Sub cmdPMIDo_Click()

'Declare variables
   Dim rst As DAO.Recordset

   Dim passModel As String
   Dim lookupPMI As String
   Dim sSQL As String

   Dim Freq As Integer


   passModel = Me!AssetSearchSub.Form!Model
   '    lookupPMI = DLookup("[ModelNumber]", "PMIQuery", "[ModelNumber]= '" & passModel &"'")

   'open recordset on PMI table to get the model and interval
   sSQL = "SELECT ModeNumber, Interval"
   sSQL = sSQL & " FROM PMITable"
   sSQL = sSQL & " WHERE [ModelNumber] = '" & passModel & "';"

   Set rst = CurrentDb.OpenRecordset(sSQL)

   ' are there records returned?
   If Not rst.BOF And Not rst.EOF Then
      lookupPMI = rst!ModeNumber
      Freq = rst!Interval

      'Compare ModelNumber of currently selected record with PMIQuery, then append date info as necessary
      If passModel = lookupPMI Then
         Me!AssetSearchSub.Form!LastPMIDate = Date
         Me!AssetSearchSub.Form!NextPMIDate = DateAdd("d", Freq, Date)
      End If
   Else
      MsgBox "No Records found"
   End If

   On Error Resume Next
   rst.Close
   Set rst = Nothing
End Sub

[Thumbs]

Steve, thanks for the reply. i am sort of following your logic here with replacing the DLOOKUP. I corrected the field names and stuff and went over the code. It runs until the Set rst = CurrentDB.Recordset (sSQL) line where it returns an error saying "too few parameters: Expected 1".

[John_G]

Hi -

All you need to do is change line to look up the PMI Interval to :

lookupPMI = DLookup("[PMIFrequency]", "PMIQuery", "[ModelNumber]= '" & passModel &"'")
This says: "From the query PMIQuery, give me the PMIFrequency for the model passModel. "

No match check is needed because as long as lookupPMI is not Null, you know you have the right one, because ModelNumber is unique in PMIquery.


In fact, you don't need lookupPMI at all, neither do you need the IF statement. The three shown by Steve, with one change (in bold) , are all you need:

Freq = DLookup("PMIFrequency", "PMIQuery", "[ModelNumber]='" & passModel & "'")
Me!AssetSearchSub.Form!LastPMIDate = Date()
Me!AssetSearchSub.Form!NextPMIDate = DateAdd("d", Freq, Date())


John

[Thumbs]

Hi -

All you need to do is change line to look up the PMI Interval to :

lookupPMI = DLookup("[PMIFrequency]", "PMIQuery", "[ModelNumber]= '" & passModel &"'")
This says: "From the query PMIQuery, give me the PMIFrequency for the model passModel. "

No match check is needed because as long as lookupPMI is not Null, you know you have the right one, because ModelNumber is unique in PMIquery.


In fact, you don't need lookupPMI at all, neither do you need the IF statement. The three shown by Steve, with one change (in bold) , are all you need:

Freq = DLookup("PMIFrequency", "PMIQuery", "[ModelNumber]='" & passModel & "'")
Me!AssetSearchSub.Form!LastPMIDate = Date()
Me!AssetSearchSub.Form!NextPMIDate = DateAdd("d", Freq, Date())


John

Hmm, ok, this is what I wound up with after reading this post:

Private Sub cmdPMIDo_Click()'Declare variables
Dim passModel As String
Dim Freq As Integer
'Declare values
passModel = Me!AssetSearchSub.Form!Model
'Compare ModelNumber of currently selected record with PMIQuery, then append date info as necessary
Freq = DLookup("PMIFrequency", "PMIQuery", "[ModelNumber]='" & passModel & "'")
Me!AssetSearchSub.Form!LastPMIDate = Date
Me!AssetSearchSub.Form!NextPMIDate = DateAdd("d", Freq, Date)
    
End Sub

It runs without errors (as I expected since I am starting learn some of the code here) but it doesnt do anything to the tables and I can't get any output to the locals window. Could you clarify for me please?

[ssanfu]

Add the line in blue

   'open recordset on PMI table to get the model and interval    
   sSQL = "SELECT ModeNumber, Interval"    
   sSQL = sSQL & " FROM PMITable"    
   sSQL = sSQL & " WHERE [ModelNumber] = '" & passModel & "';" 
Debug.Print sSQL
   Set rst = CurrentDb.OpenRecordset(sSQL)

Then set a breakpoint and step through the code. After executing the Debug line, press "<control>-G" (the control key and the letter g). This will open the immediate window. You can see if the SQL is composed correctly.

Post the result of the debug statement (and maybe the current code using the recordset)

[John_G]

Is the query PMIquery updatable? (If it is based on only one table, it should be, but if it is a totals query, then no).
To check this, open the query in Query Design, run it, and then see if you can make any updates manually, particularly on LastPMIDate and NextPMIDate. Does that work?

You could also try adding Me!AssetSearchSub.Form.Refresh right before the End Sub. This forces an update of the underlying table/query of the subform.

John

[Thumbs]

John: The query is updateable.

Steve: I ran through the code line by line. As of the "Set rst" line, the value of sSQL is SELECT [ModelNumber], [PMIFrequency] FROM PMIQuery WHERE [ModelNumber] = 'X3550 M3'; which does appear to be correct.

As I step through to the next line of code, the value of rst is set to "Nothing". As I continue to step through the code line by line, that value stays the same. The value of lookupPMI also stays at "" and Freq stays at 0. It appears that rst is not returning a value from the CurrentDb.OpenRecordset operation.

The data in the main table is being displayed on a subform, and this button is on the parent form. Does that change the way this needs to be written?

Here is the code I am currently using, based on Steve's suggestions to get rid of DLOOKUP:

Private Sub cmdPMIDo_Click()
'Declare variables
   Dim rst As DAO.Recordset
   Dim passModel As String
   Dim lookupPMI As String
   Dim sSQL As String
   Dim Freq As Integer

   passModel = Me!AssetSearchSub.Form!Model
   '    lookupPMI = DLookup("[ModelNumber]", "PMIQuery", "[ModelNumber]= '" & passModel &"'")
   'open recordset on PMI table to get the model and interval
   sSQL = "SELECT [ModelNumber], [PMIFrequency]"
   sSQL = sSQL & " FROM PMIQuery"
   sSQL = sSQL & " WHERE [ModelNumber] = '" & passModel & "';"
Debug.Print sSQL
   Set rst = CurrentDb.OpenRecordset(sSQL)
   ' are there records returned?
   If Not rst.BOF And Not rst.EOF Then
      lookupPMI = rst![ModelNumber]
      Freq = rst![PMIFrequency]
      'Compare ModelNumber of currently selected record with PMIQuery, then append date info as necessary
      If passModel = lookupPMI Then
         Me!AssetSearchSub.Form!LastPMIDate = Date
         Me!AssetSearchSub.Form!NextPMIDate = DateAdd("d", Freq, Date)
      End If
   Else
      MsgBox "No Records found"
   End If
   On Error Resume Next
   rst.Close
   Set rst = Nothing
End Sub

[Thumbs]

Hold the phone! I got it to work, and it was all because of a stupid typo on my part in the model number in the PMITasks table (that PMIQuery pulls from). Thank you all for your assistance, you have been a great help. Going through all of this code line by line (and learning how to do that) has also really helped me gain a greater understanding of some more VBA and SQL elements.