How do I use DAO to compare text box on form to query result?

[KyleB]

I have created a query with one row of data, and six columns in this layout:

Month/Year in Question, Location, Criteria1, Criteria2, Criteria3, Criteria4

This layout could change to two rows at some point in the future, though I could create a second query where the Location is unique so that I maintain a single row if that is more efficient. My problem is that I cannot decipher how the DAO code references data in a query. I cannot find a detailed comment set that describes what the various parts do. I want to be able within VBA to compare a result in a text box in the AfterUpdate option to one of the criteria in the query. For instance, my continuous form has a text box called "Incremental_Grade", after a user inputs a numeric value in this text box and hits enter, or tab, the AfterUpdate action is called and the VBA code should go to the query, and look at column 4 to see what the numeric value for Criteria2 is, if the result is below this value then do some action, if above this value, then do another action. The if statements seem fairly straight forward, but the syntax for referencing the query result has me lost. There are four text boxes on this continuous form, and each one will have an AfterUpdate trigger that will each individually check a different Criteria from the query.

I cannot understand what the various parts of the DAO code do, so I don't know how to tell it to reference that specific column and compare those numbers. Would I be better off to try using a Dlookup to compare these values?

To complicate this somewhat, the query in question could be blank if someone forgot, or didn't have data to enter criteria for that particular month and year. As a result I've got a second query in the same format that has a set of default values to use instead. So what I need to be able to do is check the primary query first, if blank, then go to the secondary query with the default values in it, and use the same criteria2 from it instead.

 

[dcx693]

You can load the results of a query into a DAO recordset and reference the desired column by name or position. You can use a saved query or have one run on the fly.

A dlookup would work if you don't need to calculate anything in your query, but it would be slow.

If you want to use the 2nd query with default values, then just use a couple of IF statements to go through the queries until you find valid data.

 

[KyleB]

Thank you, but I was looking for something a little more specific here. I've spent half of yesterday, and all day today searching the archives, and I cannot find a clear-cut description of how to open one query with one row, and six columns, and pull out the value stored in row1, column4 for instance. I've been copying, pasting, and fiddling with many examples on the boards, but all I get are incomprehensible error messages. Most likely due to mis-use of the syntax on my part I'm sure. I know this is completely butchered, but this is my current non-working code version with comments of what I think things are doing, I'd appreciate any assistance in understanding the syntax, and functionality of the DAO code set:

Private Sub Incremental_Grade_AfterUpdate()
On Error GoTo Err_Incremental_Grade_AfterUpdate
Dim db As DAO.Database, rs As DAO.Recordset, qdf As QueryDef

Set db = CurrentDb '<--So this sets the current working database I assume
Set qdf = db.QueryDefs("qry Current Months Cutoff Grades") '<-- This totally baffles me. I'm guessing it's opening the query in question, and then what?
Set rs = qdf.OpenRecordset '<--Setting the current query definitions from above as the active recordset?

'This portion below is just a couple of if statements that compare the user 
'entered value to the field I'm trying to pull out of the query, then showing the 
'appropriate messagebox.
If ([Incremental_Grade] < rs!Fields![Incremental Cutoff]) Then
  MsgBox ("Please check the Incremental Grade, as the value you have entered is actually waste material based on grade.")
 Else
 If ((Me.[Cut___Fill_Development_] = True) And (Me.[Incremental_Grade] > [qry Cutoff Grades].[C&F Cutoff])) Then
  MsgBox ("Please check the Incremental Grade, as the value you have entered is actually normal ore material based on grade.")
 End If
End If

rs.Close '<--closing the open recordset to free up memory resources

Set rs = Nothing '<--Clearing up memory
Set db = Nothing '<--Clearing up memory

Exit_Incremental_Grade_AfterUpdate:
    Exit Sub

Err_Incremental_Grade_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Incremental_Grade_AfterUpdate

End Sub

At present I'm getting an error saying that it's expecting two arguements, but it won't tell me where. Unfortunately, I suspect the biggest problem I'm having is in understanding exactly what the process of steps is doing, one by one. The way I understand it, is that you are setting aside a portion of memory to open the query, then you read the query, and designate a memory block to contain the value you wish to use/manipulate. This value is then used in your if statements, and when you're done you close the recordset and free up the memory again. Is that basically correct?

Kyle

 

[dcx693]

I cleaned up the syntax a bit. I highlighted the areas that confused me.

Private Sub Incremental_Grade_AfterUpdate()
On Error GoTo Err_Incremental_Grade_AfterUpdate
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qry Current Months Cutoff Grades", dbOpenSnapshot)

'This portion below is just a couple of if statements that compare the user
'entered value to the field I'm trying to pull out of the query, then showing the
'appropriate messagebox.
If [Incremental_Grade] < rs.Fields("Incremental Cutoff") Then
  MsgBox "Please check the Incremental Grade, as the value you have entered is actually waste material based on grade."
[b]'you need the MsgBox statement, so omit the parentheses[/b]
 Else
 If (Me.[b][Cut___Fill_Development_][/b] = True) And (Me.[Incremental_Grade] > [b][qry Cutoff Grades].[C&F Cutoff][/b]) Then
[b]'you really have a control on your form called "Cut___Fill_Development_"?[/b]
[b]'what is [qry Cutoff Grades].[C&F Cutoff] ?[/b]
  MsgBox "Please check the Incremental Grade, as the value you have entered is actually normal ore material based on grade."
 End If
End If

rs.Close '<--closing the open recordset to free up memory resources

Set rs = Nothing '<--Clearing up memory
Set db = Nothing '<--Clearing up memory

Exit_Incremental_Grade_AfterUpdate:
    Exit Sub

Err_Incremental_Grade_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Incremental_Grade_AfterUpdate

End Sub

 

[KyleB]

I thank you for the help, though unfortunately I still have the same problems, I'm getting an error saying too few parameters, it's expecting two, but as far as I can tell there are two here:

Set rs = db.OpenRecordset("qry Current Months Cutoff Grades", dbOpenSnapshot)

I'm assuming that we're using "rs" here as shorthand for record set, and we're telling it to open the records contained in the query called "qry Current Months Cutoff Grades", and to view it as a snapshot? I'm not sure why I would need to do this, I just need to open the query, and get the number from one of the cells, in order to compare it to what was entered on the form. The presence of this: [qry Cutoff Grades].[C&F Cutoff] was my attempt to reference the data in the query that contains the information I am trying to get out of column4, row1. I commented out my error capturing so that I could more accurately pinpoint where the error was occuring, but here's what I've got now:

Private Sub Incremental_Grade_AfterUpdate()
'On Error GoTo Err_Incremental_Grade_AfterUpdate
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qry Current Months Cutoff Grades", dbOpenSnapshot)

If [Incremental_Grade] < rs.Fields("Incremental Cutoff") Then
  MsgBox "Please check the Incremental Grade, as the value you have entered is actually waste material based on grade."
 Else
 If ((Me.[Cut___Fill_Development_] = True) And (Me.[Incremental_Grade] > [qry Cutoff Grades].[C&F Cutoff])) Then
  MsgBox "Please check the Incremental Grade, as the value you have entered is actually normal ore material based on grade."
 End If
End If

rs.Close

Set rs = Nothing
Set db = Nothing

'Exit_Incremental_Grade_AfterUpdate:
'    Exit Sub

'Err_Incremental_Grade_AfterUpdate:
'    MsgBox Err.Description
'    Resume Exit_Incremental_Grade_AfterUpdate

End Sub

I also am not sure what you're referring to Pat, my query is a stand alone query, separate from this function. The controls which limit the query are all contained on the form which triggers this function. All of the controls have default values, so the query should be able to run without passing any additional parameters to it. Essentially what I'm searching for here is not a rote, "do this" I'm looking for a break-down/description of what the DAO code is doing? How is it opening the query, how does it know what field to look in, so that it can get the value and assign it to a variable that I can then manipulate in an IF statement in order to compare user typed data to query contained data? Because I don't understand the syntax structure I'm unable to even make an educated guess about what goes where, or what it does. The Access help files are singularly un-informative with this, and I have been unable to find anything to date on the forums that describes the process. Should I be using a QueryDef? (yet another part I can't find a description/definition of anywhere) Or is that used for something else? If needed I can attach a piece with the form and the query in question if that would clarify it any as to what I'm trying to do.

As for the name of the control being [Cut___Fill_Development_], it's [Cut & Fill Development?] in actuality, but the use of the special characters causes all kinds of fun things. I agree it's quite ugly, however I'm attempting to make changes to an ex-employee's existing Db without taking it out of service long term to fix the poor naming setup as it exists, that's for the future, I'm attempting to get the limiting factors in first to eliminate poor data entry by the users as a primary goal.

I appreciate any enlightenment anyone could shed on this, I'm quite stumped.

Kyle

 

[dcx693]

Can you post the SQL of the query? Do that by going to query design view for the "qry Current Months Cutoff Grades" query, then choosing SQL from the View menu. Copy and paste the text.

 

[KyleB]

Ok, looking over the post you've given me Pat, I'm getting a little confused. The way I'm understanding it, is a parametered query defines a set of parameters within the query itself, that are then requested from the user when the query runs, in order to limit the query with the entered parameters acting as the restrictors on the data output. What I'm wondering is do I actually need to have the user put this in all the time? The query I'm using is this:

SELECT [tbl Cutoff Grades].[Cutoff Month & Year], [tbl Cutoff Grades].Mine,
[tbl Cutoff Grades].[C&F Cutoff], [tbl Cutoff Grades].[Dev Cutoff],
[tbl Cutoff Grades].[Stope Cutoff], [tbl Cutoff Grades].[Incremental Cutoff]
FROM [tbl Cutoff Grades]
WHERE ((([Forms].[frm Switchboard].[Month of Forecast])=Month([Cutoff 
Month & Year])) AND (([Forms].[frm Switchboard].[Year of Forecast])=
Year([Cutoff Month & Year])))
GROUP BY [tbl Cutoff Grades].[Cutoff Month & Year], [tbl Cutoff Grades].Mine,
[tbl Cutoff Grades].[C&F Cutoff], [tbl Cutoff Grades].[Dev Cutoff],
[tbl Cutoff Grades].[Stope Cutoff], [tbl Cutoff Grades].[Incremental Cutoff];

I've attempted to make changes based on your post, but I'm not understanding the syntax at all, so I'm really not sure I'm doing it anything close to correct. This is what I have, but the errors don't even make sense to me. I'm getting errors stating: "Item not found in this collection"

Private Sub Incremental_Grade_AfterUpdate()
'On Error GoTo Err_Incremental_Grade_AfterUpdate
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim QD1 As DAO.QueryDef
Dim strSQL As String
Dim qdf As Object

Set db = CurrentDb

Set QD1 = db.QueryDefs![qry Current Months Cutoff Grades]
QD1.Parameters![Forms]![Enter Forecast Development Form]![Incremental Grade] = [Forms]![Enter Forecast Development Form]![Incremental Grade]
'[B]Error message on line above.[/B]
QD1.Parameters![Forms]![Enter Forecast Development Form]![Cut___Fill_Development_] = [Forms]![Enter Forecast Development Form]![Cut___Fill_Development_]

Set qdf = db.QueryDefs("qry Current Months Cutoff Grades")

strSQL = "SELECT [tbl Cutoff Grades].[Cutoff Month & Year], [tbl Cutoff Grades].Mine, " _
& "[tbl Cutoff Grades].[C&F Cutoff], [tbl Cutoff Grades].[Dev Cutoff], " _
& "[tbl Cutoff Grades].[Stope Cutoff], [tbl Cutoff Grades].[Incremental Cutoff] " _
& "FROM [tbl Cutoff Grades] " _
& "WHERE ((([Forms].[frm Switchboard].[Month of Forecast])=Month([Cutoff Month & Year])) AND " _
& "(([Forms].[frm Switchboard].[Year of Forecast])=Year([Cutoff Month & Year]))) " _
& "GROUP BY [tbl Cutoff Grades].[Cutoff Month & Year], [tbl Cutoff Grades].Mine, " _
& "[tbl Cutoff Grades].[C&F Cutoff], [tbl Cutoff Grades].[Dev Cutoff], " _
& "[tbl Cutoff Grades].[Stope Cutoff], [tbl Cutoff Grades].[Incremental Cutoff]"

Set rs = db.OpenRecordset("qry Current Months Cutoff Grades")

If [Incremental_Grade] < rs.Fields("Incremental Cutoff") Then
  MsgBox "Please check the Incremental Grade, as the value you have entered is actually waste " _
  & "material based on grade."
 Else
 If ((Me.[Cut___Fill_Development_] = True) And (Me.[Incremental_Grade] > rs.Fields("C&F Cutoff"))) Then
  MsgBox "Please check the Incremental Grade, as the value you have entered is actually normal " _
   & "ore material based on grade."
 End If
End If

rs.Close

Set rs = Nothing
Set db = Nothing

'Exit_Incremental_Grade_AfterUpdate:
'    Exit Sub

'Err_Incremental_Grade_AfterUpdate:
'    MsgBox Err.Description
'    Resume Exit_Incremental_Grade_AfterUpdate

End Sub

if I comment out the three lines containing the Parameters settings, then I get an error on the Set rs = db.OpenRecordset("qry Current Months Cutoff Grades") line saying that I have too few parameters, it expects 2.

Now something I maybe should have mentioned before that could have an impact on this: the date criteria which this query is based upon exists on the switchboard form, which is never closed, this is the date that is used to run the query, the control form in no way controls the output of the query, all the function does is compare the input value on the form, to the data contained in the query, which is based on the switchboard control sources. Is there a restriction on data control that won't let the query run due to the switchboard no longer having the active focus? What I'm working with, is a month, and a year on two seperate unbound text boxes on the switchboard, these act as our default month and year for almost all queries, and controls within the database. The query showing the current data is run based on this information, and the results contain a set of data, of which one cell is the one that is compared to a text box on my form. I don't know if that clarifies anything, but thought I should mention it, in case it has an impact.

Going back to the parameter query post you pointed me to, I don't understand this particular piece of code at all:

Replace:
Dim DB As Database
Dim Rsdb As Recordset
Set Rsdb = DB.OpenRecordset("Qry_bscactivity", dbOpenDynaset)

With:
Dim DB As DAO.Database
Dim Rsdb As DAO.Recordset
Dim DB1 As DAO.QueryDef
Set QD1 = DB.QueryDefs![qry_bsactivity]
QD1.Parameters![forms]![frm_reports]![frmnetworkelement]=
[forms]![frm_reports]![frmnetworkelement]
QD1.Parameters![forms]![frm_reports]![frmnetworkelement]=
[forms]![frm_reports]![frmnetworkelement]
...
QD1.Parameters![Plant]=gPlant
Set Rsdb = QD1.OpenRecordset

What doesn't make sense to me is the duplicate reference to the parameters after the QueryDefs line. Why would you set the parameter twice, and why is it the same definition on both sides of the equal sign? Then, where does [Plant], and [gplant] come from? I was unable to find any reference to such a command in help, or in his example code, so I don't know what control that's referencing, or why it's there. I also followed the link by TimK further down, but I'm not sure that I understand the connection to my particular function problem. Those examples use a set of parameters that change each time the query is run. While I could see that possibly happening here, I'm wondering if I need to be soliciting the date information from the user each time, or if it's possible to just query the switchboard control's and define those two boxes as my parameters to pass to the query? Generally speaking the month, and year of all work being performed at any given time will be set upon opening the database, and won't need to be changed, until a future session.

KyleB