UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup With Criteria That References Control Values On Current Form Not Working, Access 2013    
 
   
ScottyBee
post Jul 3 2019, 06:24 PM
Post#1



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Attached File  Database.zip ( 122.86K )Number of downloads: 8
In my form named EER Form, I am trying to create a control that looks up the value of the Rating field in a table named Ratings based on the values of two existing controls. The controls are named SubSystemID and txtCatID.

The code I used for DLookup is:
CODE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]='" & Forms![EER Form].Form.[SubSystemID] & "' AND [fk_CategoryID]='" & Forms![EER Form].Form.[txtCatID] & "'")



When I run the form, I see this field continually flashing with no error message until I click on the control. The above syntax has worked for me before but I am obviously missing something.

I have attached my database Attached File  Database.zip ( 122.86K )Number of downloads: 8
in case anyone would like to open it. The form is named EER Form. I have placed the control at the bottom left of the form. Once I get this to work, then I will be able to move forward. I have researched when to use double quotes and single quotes but still have not wrapped my mind around it.

Any ideas or info on web that might clarify the placement of the single, double quotes and & character? It is easy for me to build a WHERE clause that would do all of this in a query but I am having a hard time transferring this logic to the DLookup function. Thanks
Go to the top of the page
 
R. Hicks
post Jul 3 2019, 06:33 PM
Post#2


UA Forum Administrator
Posts: 40,505
Joined: 8-February 00
From: Birmingham, Alabama USA


I haven't looked at your database but it appears that you are referencing both controls as if they contain text datatypes …
I am assuming that both of these are numeric datatypes as they are designated by their naming convention as ID's.
Also if both of these controls are located on the current form .. you need to reference the control using the "Me" identifier.

If I am correct with my above assumptions … try the following code:
CODE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=" & Me.[txtCatID])


R.

--------------------
Ricky Hicks
Birmingham, Alabama USA
Go to the top of the page
 
ITguaranteed
post Jul 3 2019, 07:20 PM
Post#3



Posts: 32
Joined: 19-June 19
From: Tasmania, Australia


Hi Scotty,

I had a look at your database.
why are you trying to lookup rating when it is in the underlying query?
why not just drag it on the form from the Field List?

Am I missing something? iconfused.gif


Go to the top of the page
 
ScottyBee
post Jul 3 2019, 11:34 PM
Post#4



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello Ricky, I tried your code and it gave me a Name? error. Any ideas? I appreciate your help.
Go to the top of the page
 
ScottyBee
post Jul 4 2019, 01:04 AM
Post#5



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello IT, yes, there is a reason for me not to simply drag the Ratings field onto the form. I think it would be helpful if I upload an image to show you what I am trying to accomplish:
Attached File  Database_Overview.jpg ( 389.08K )Number of downloads: 8


In the main form, you will notice there are 12 categories that have different titles with a rating of 1 to 5. The form shown is just one SubSystem. Each SubSystem uses the same 12 categories and there are hundreds if not thousands of SubSystems that will be in the database eventually. This means that each Subsystem has MANY Categories and each Category is used in MANY SubSystems.

Look at the group that is circled. The title "Mechanical Condition P-5" is listed in the Categories table and has a CategoryID of 10. The current SubSystemID is 1 and is seen at the top of the form. When the user clicks a rating from 1 to 5 in this group, this value is stored in the Ratings table for CategoryID = 10 and fk_SubSystemID = 1

The code you helped me with yesterday worked great but only works for the circled group since it has a CategoryID of 10 and the code references this value. To make the code work for the other 11 groups, I simply can create an event procedure for each, copy you code into them and change the CategoryID value to match. I have included the code again below in case other viewers want to see it.

Which brings me to finally answering your question:
I was experimenting with what tables to include in the query that the form is based on. I have since deleted the Ratings table from this query so the main form will navigate from one SubSystem to the next. This is where the problem lies. Since the Ratings on the form are not bound controls, the user has no idea what the current ratings are for each of the 12 groups. I need a way for Access to select the appropriate rating for each group as stored in the Ratings table. My goal is to use DLookup to get the current rating for each group and use code to synchronize the radio buttons with these values.

First, I need to get DLookup to work for the circled group. Here is Ricky's suggested code. Note that Me.[txtCatID] is a textbox control i placed on form containing a default value of 10. Again, I got a name? error when I ran this control.
CODE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=" & Me.[txtCatID])


I should be able to simplify this code by not even having the txtCatID control on the form and just hardcode the 10 as follows:
CODE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=10)

I get an"Invalid String" error

So IT, this is long explanation but I hope this made sense. If I can get the last DLookup statement to work and use this value to synchronize the rating on the form when it is viewed, I will be very happy. Thanks for all of your help.


Here is the code from an earlier post that is referenced above. This code updates the Ratings table just for group 10 and the current SubSystem ID as displayed at the top of the form.
CODE
Private Sub fraMechCondP5_AfterUpdate()
    
    Dim dbs As Database
    Dim SubSystemIDValue As String
    Dim frmCategory As String
    SubSystemIDValue = [Forms]![EER Form]![SubSystemID]
    frmCategory = "10"
    Set dbs = CurrentDb
    
    Select Case fraMechCondP5.Value
        
        Case 5
             dbs.Execute "Update Ratings " & _
             "Set Ratings.Rating = 5 " & _
             "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & " And Ratings.fk_CategoryID = " & frmCategory & "));"
             dbs.Close
        
        ' This SQL Statement works
        Case 4
            dbs.Execute "Update Ratings " & _
             "Set Ratings.Rating = 4 " & _
             "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & " And Ratings.fk_CategoryID = " & frmCategory & "));"
             dbs.Close
        
        ' This SQL Statement works
        Case 3
            dbs.Execute "Update Ratings " & _
             "Set Ratings.Rating = 3 " & _
             "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & " And Ratings.fk_CategoryID = " & frmCategory & "));"
             dbs.Close
        
        ' This SQL Statement works
        Case 2
            dbs.Execute "Update Ratings " & _
             "Set Ratings.Rating = 2 " & _
             "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & " And Ratings.fk_CategoryID = " & frmCategory & "));"
             dbs.Close
        
        ' This SQL Statement works
        Case 1
            dbs.Execute "Update Ratings " & _
             "Set Ratings.Rating = 1 " & _
             "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & " And Ratings.fk_CategoryID = " & frmCategory & "));"
             dbs.Close
    End Select
End Sub
Go to the top of the page
 
ITguaranteed
post Jul 5 2019, 12:53 AM
Post#6



Posts: 32
Joined: 19-June 19
From: Tasmania, Australia


Hope this helps you along the way.

CODE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=10")



Count your double quotes. there should always be an even number.

I did drag ratings on the form and it worked for me. Sorry it wasn't what you wanted.
Go to the top of the page
 
ScottyBee
post Jul 5 2019, 11:07 AM
Post#7



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello IT,

I am still getting a #Name? error. Below is a diagram of where the SubSystemID control is and where the control is that contains the error. I have also re-attached the latest version of my database in case you need it and have time to help me identify why I am getting this error. Thanks for your efforts.

Attached File  Diagram_of_Name_Error.png ( 324.27K )Number of downloads: 5


Attached File  Database.zip ( 145.63K )Number of downloads: 1
Go to the top of the page
 
tina t
post Jul 5 2019, 01:49 PM
Post#8



Posts: 6,023
Joined: 11-November 10
From: SoCal, USA


QUOTE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=" & Me.[txtCatID])

well, a couple things. first the fieldname above in red is misspelled. need to fix that first. next, you say you have a mainform with a subform. sorry if i'm slow, but it wasn't clear to me which form holds the control with the DLookup() function, and which form holds the fields that you're referring to in DLookup() criteria argument. and it's not clear if you're running the DLookup() function in VBA, or using it directly as the control's ControlSource. it makes a huge difference in how you write the reference.

so there are several possible scenarios here, but rather than me writing out the syntax for all the possible setups, it would be much simpler if you will answer the following questions:

1. which form is the DLookup() control on - mainform or subform?
2. which form includes the fields that are referenced in the DLookup() function's criteria argument - mainform or subform?
3. is the DLookup() being called in VBA?
4. or, is the DLookup() being used directly as the control's ControlSource?
5. what is the name of the subform "container" control? not the name of the subform itself, but the name of the specialized control that "holds" or "contains" the subform within the mainform - the two names may be the same, or they may be different.

hth
tina
This post has been edited by tina t: Jul 5 2019, 01:51 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ScottyBee
post Jul 8 2019, 04:00 PM
Post#9



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello Tina,

To answer your questions more clearly, I have attached my table relationships here:
Attached File  Table_Relationships.JPG ( 39.11K )Number of downloads: 1



The main form draws from these 3 tables: Buildings, Systems and SubSystems. I did not have any subforms created and had VBA code that would insert the rating into the Ratings table when an radio button is clicked. There were to be 12 categories on main form each with a click event and separte VBA code that inserted the rating and CategoryID into the Ratings table.

This logic works fine for getting the data entered but then I realized the ratings chosen on main form are gone when you close then re-open form as the RatingID field is not bound to the form. I then went down a "rabbit hole" further by looking for a way to synchronize all 12 categories (sections) on the main form my using DLookup to grab the RatingID value from the Ratings table and then use this value to synchronize the 12 different ratings shown on the main form.

After much frustration and effort, I realized my logic was flawed. I have since created a subform for each of the 12 categories and they are synchronized. No Dlookup or code needed!

However, I would still like to know what I am doing incorrectly with my DLookup syntax as I can see me finding this a very useful function in the future. I have answered your five questions below

Note in code below, I was just testing category 10 of 12 so am hard coding the value instead of using a textbox on form and setting it's default value to 10
CODE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=10")


1. which form is the DLookup() control on - mainform or subform? mainform
2. which form includes the fields that are referenced in the DLookup() function's criteria argument - mainform has the SubSystemID, The fk_SubSystemID is in the Ratings table, and fk_CategoryID is also in the Ratings table. The 10 is hard coded in the DLookup so no we can ignore the reference to Me.[txtCatID])
3. is the DLookup() being called in VBA? No, it is being called from an unbound textbox control on main form
4. or, is the DLookup() being used directly as the control's ControlSource? See answer 3.
5. what is the name of the subform "container" control? not the name of the subform itself, but the name of the specialized control that "holds" or "contains" the subform within the mainform - the two names may be the same, or they may be different. No subform was used but I definitely have them in my new version.

Note: The code you had in Red in your reply is correct as I was trying to use the SubSystemID shown on main form in top left in the criteria statement for the Ratings table. Since fk_SubSystemID is in Ratings table, this expression should be correct.

In summary, I have used subforms to provide all of the functionality that I need but still want to know why my last DLookup statement that I posted is giving an error message. Thanks Tina for your reply.
Go to the top of the page
 
tina t
post Jul 8 2019, 04:40 PM
Post#10



Posts: 6,023
Joined: 11-November 10
From: SoCal, USA


QUOTE
still want to know why my last DLookup statement that I posted is giving an error message.

i already told you, hon.

QUOTE
DLookup("[Rating]", "Ratings", "[fk_SubsytemID]=" & Me.[SubSystemID] & " AND [fk_CategoryID]=" & Me.[txtCatID])

QUOTE
the fieldname above in red is misspelled. need to fix that first.

the screenshot of the Relationships window that you posted, shows that table Ratings has a field called fk_SubSystemID, not the fk_SubsytemID that you have in the posted DLookup() function's criteria argument.

hth
tina
This post has been edited by tina t: Jul 8 2019, 04:41 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ScottyBee
post Jul 8 2019, 06:10 PM
Post#11



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello Tina,

I did change the spelling and am still receiving the ?Name error. I used the code:

CODE
DLookUp("[Rating]","Ratings","[fk_SubsystemID]=" & [Me].[SubSystemID] & " AND [fk_CategoryID]=" & [Me].[txtCatID])



I have attached my most recent database with the txtCatID control and the SubSystemID control on the EER Form if you have time to assist? Thank you very much for your efforts so far.

Attached File  Database.zip ( 289.04K )Number of downloads: 4
Go to the top of the page
 
tina t
post Jul 8 2019, 07:19 PM
Post#12



Posts: 6,023
Joined: 11-November 10
From: SoCal, USA


i'm sorry, hon, i can't go further with you. i'm neck-deep in a project at work that is going very poorly for me, and i need every spare minute to concentrate on that. good luck with your project. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ScottyBee
post Jul 8 2019, 07:25 PM
Post#13



Posts: 38
Joined: 6-June 05
From: Portland Oregon


I appreciate your help, you got me going in the right direction. Hope you get caught up in your project smile.gif
Go to the top of the page
 
cheekybuddha
post Jul 9 2019, 05:35 AM
Post#14


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


[Me] is not valid within an expression. It is used as a reference to the containing class in VBA - most often in Access referring to the form class in whose module the code is placed.

Change the ControlSource of Text380 to:
CODE
=DLookUp("[Rating]","Ratings","[fk_SubsystemID]=" & [SubSystemID] & " AND [fk_CategoryID]=" & [txtCatID])


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ScottyBee
post Jul 15 2019, 05:52 PM
Post#15



Posts: 38
Joined: 6-June 05
From: Portland Oregon


You really are a buddha! That worked out great. Thanks for your help.
Go to the top of the page
 
cheekybuddha
post Jul 15 2019, 07:30 PM
Post#16


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 12:19 PM