Dlookup Criteria error 1

[dandl]

I am having a bit of a problem with Dlookup criteria and have looked through quite a few threads but none seem to answer my problem,

I am updating a month calendar changing the backcolor of the box if the criteria match.

This actually works fine.

The problem is that it needs to be specific to a field.
What is actually happening is! It goes through all the records and populates the calendar with all the data instead of just the record I am on.


Here is the code.
Sub RefDates()
Dim D1 As Variant, D2 As Integer, D3 As Integer, MaintTp
If IsNull(Me![AssetID]) Then
MsgBox ("There is no record for this asset number " _
& "Try another record.&quot
Exit Sub
End If
Me![scrMonth] = Format(Me![scrCDate], "mmmm&quot
Me![scrYear] = Format(Me![scrCDate], "yyyy&quot
D1 = DateSerial(Year(Me![scrCDate]), Month(Me![scrCDate]), 1)
D2 = DatePart("w", D1, vbMonday)
Do Until DatePart("w", D1, vbMonday) = 1
D1 = DateAdd("d", -1, D1)
Loop
Me![scr1Date] = D1
D3 = 1
Do Until D3 > 42
Me("C" & Format(D3, "00&quot) = Day(D1)
If Month(D1) <> Month(Me![scrCDate]) Then
Me(&quot;C&quot; & Format(D3, &quot;00&quot).ForeColor = 8421504
Else
Me(&quot;C&quot; & Format(D3, &quot;00&quot).ForeColor = 0
'If Me(strt).ForeColor = -2147483634 Then
End If

'#######################################################

MaintTp = DLookup(&quot;AssetID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot

'#######################################################
If IsNull(MaintTp) Then
MaintTp = 0
End If
Select Case MaintTp
Case 0
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 12632256
Case 1
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 65280
Case 2
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 255
Case Else
Me(&quot;C&quot; & Format(D3, &quot;00&quot).BackColor = 3355443
Me(&quot;C&quot; & Format(D3, &quot;00&quot).ForeColor = 16777215
End Select
D3 = D3 + 1
D1 = DateAdd(&quot;d&quot;, 1, D1)
Loop
Me.Repaint
End Sub

if I change the criteria to
MaintTp = DLookup(&quot;AssetID&quot;, &quot;Maintenance&quot;, &quot;SELECT DISTINCT [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot

it gives me a syntax error.

Can anyone help please


Dandl

 

[scriverb]

Current:
MaintTp = DLookup(&quot;AssetID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot

Proposal #1:
MaintTp = DLookup(&quot;AssetID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = &quot; & D1)

Since D1 is a variant variable and should according to your code have a date value stored in it then a straight comparison to [MaintenanceDate] should work. If not then try the next proposal:

Proposal #2:
MaintTp = DLookup(&quot;AssetID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = &quot; & DateValue(D1))




Bob Scriver

 

[dandl]

Thanks for your suggestion, when I tried both of the lines of code it did not populate any data in the calendar at all.

To clarify my problem, I am on record 1 with a assetid of 1 and I need the dlookup to go through the data in the maintenance table and look for records that match the criteria (The dates populate ok) but it populates the data from all of the assets regardless of the assetid.

I am not 100% with vba and SQL statements so am not quite sure that my criteria is correct.

Any more ideas would be greatly appreciated.

Dandl

 

[scriverb]

Your DLookUp is going to return the value of field AssetID in table Maintenance. Is this the field value that you want returned?

Please explain the difference between AssetID and MaintAssetID fields in the table?

Bob Scriver

 

[dandl]

The DLookUp should be returning MaintTypeID from the Maintenance Table so my first part of the code is wrong??

Can you confirm it should be
MaintTp = DLookup(&quot;MaintTypeID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot

Although now I have have changed it to this it does not populate any of the boxes.

My theory was,
I have a months calendar displayed.
Then I do my query on a loop through each date of the calendar to say..
If this date has a record in the Maintenance table for the AssetID that the recordset is on

return the MaintTypeID and change the backcolor of that box to say there is a record (They color denotes the record type(MaintTypeID))

Then move onto the next date till It reaches the end of the month.

Thanks for your help so far, things are becoming a little clearer.

Dandl

 

[scriverb]

At this point the code looks okay to me. What I would do is put a STOP command behind the DLookUp and walk through your code a step at a time and verify the data that is expected in your variables and being returned from your DLookUp. Use the F8 key and the Debug window to analyze your data.

MaintTp = DLookup(&quot;MaintTypeID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot
STOP


Check the value of MainTp at each stop. Just put your cursor over the word MaintTp to see the value being returned.

Let me know how this data looks.

Bob Scriver

 

[dandl]

Since my last post I have change the code as follows.

'MaintTp = DLookup(&quot;MaintTypeID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot


I still have a problem with the calendar being populated with the MaintTypeID Data from every AssetID record... Not just the AssetID I am on.

I have tried adding a SELECT DISTINCT and breaking the code down as follows

'=-=-=-=-=-= Code Bit =-=-=-=-=-=-=-
Dim strSQL As String
Dim dteDate As Date


strSQL = &quot; AND Me.[MaintenanceDate] = #&quot; & _
Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot;

MaintTp = DLookup(&quot;AssetID&quot;, &quot;Maintenance&quot;, &quot;SELECT DISTINCT [MaintAssetID] = &quot; & [AssetID] & strSQL)

'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
But this still gives me a syntax error...

If anyone can offer any advise it would be greatly appreciated.

Dandl

....To clarify the AssetID and MaintAssetID

These are the same just in different tables its the key I use to link the records.

 

[scriverb]

Like I said use the following and the stop and walk through and see what is being returned:
MaintTp = DLookup(&quot;MaintTypeID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = #&quot; & Format(D1, &quot;mm/dd/yy&quot & &quot;#&quot
STOP

Do this and you will have an idea of whether you are returning data with the DLookUp.


Bob Scriver

 

[dandl]

Hi Bob Scriver

Did this and this is how I got to It is returning the MaintTypeID for all the AssetID's

It goes through the current month and when I get to my test data although the AssetID and the MaintAssetID are the same &quot;1&quot; the D1 is the date which the calendar is on &quot;02/05/03&quot; the Maintenace Table does not hold any date for this AssetID on this Date but it has a record for AssetID &quot;2&quot; and it returns the MainTypeID for the AssetID = &quot;2&quot;

Any ideas??

Thanks
Dandl

 

[scriverb]

Try this:
MaintTp = DLookup(&quot;MaintTypeID&quot;, &quot;Maintenance&quot;, &quot; [MaintAssetID] = &quot; & [AssetID] & &quot; AND [MaintenanceDate] = &quot; & D1)

Since D1 is a variant and should be holding the Date being checked there is no need to format and then convert back to a Date Value with the # signs. Keep the stop in and check the value of D1 at the time of the dLookup.

Bob Scriver

 

[dandl]

Tried your suggestion

D1 changes as you go through the calendar but it returns no MaintTypeID unless you have the # either side of the format

Dandl

 

[scriverb]

If you are returning a MaintTp value for every date in the calendar means one of two things. Either there is a valid record for each date or your data in Maintenance has a record that meets the requirements of this DLookUp and is being selected each time.

On each call to this it is using the me.[ASSETID} value to do the lookups. Just what is that controls value?

Bob Scriver

 

[dandl]

Found it! Yes you were right.. I was using the wrong control it should have been AssetID on the Maintenance table.

Thanks for all you help

Dandl

 

[scriverb]

Is it all working now?

Bob Scriver

 

[dandl]

Hi scriverb

It is all working now, thanks!
I was not very confident with the DLookUp but thanks to your ideas I found where I was going wrong.

Thanks
Dandl