Solved DMAX with miltiple criteria (1 Viewer)

[bftsg]

Hi again,

I must be missing something stupid as this command appeared to work before. I am trying to find the next number in sequence based upon three variables but it fails to compile with a compile error of "Method or data member not found".

I have checked the exact names / spelling of all variables against the table and the actual form but there doesn't appear to be anything wrong. The code is
"
Private Sub Combo55_AfterUpdate()

Dim nNextReq As Long
Dim SQL As String
Dim strUser As String
NextReq = 0

NextReq = DMax("Req_No", "tbl_Requirements", ("Req_Area = " & Me.Req_Area & " AND Req_Type = " & Me.Req_Type & " AND Req_Dept = " & Me.Req_Dept))
If IsNull(NextReq) Then NextReq = 0

NextReq = NextReq + 1
Me!Req_No = NextReq

strSQL = "Insert into tbl_Requirements (Req_Area, Req_Dept, Req_Type, Req_No) values (Forms!frm_Requirements!Req_Area, Forms!frm_Requirements!Req_Dept, Forms!frm_Requirements!Req_Type, '& NextReq &')"


End Sub"

Any help would be appreciated. Thanks

 

[Gasman]

I believe you would need to concatenate the form values, as you have tried with nextreq, but that syntax is incorrect?
Put all the criteria for the DMax into a string variable, and debug.print it to see what you have, not what you think you have.
Text needs single quote surrounds,dates use # and numbers do not need anything.

 

[theDBguy]

Hi. Which line is highlighted with the error message?

 

[Pat Hartman]

Text values MUST be enclosed in quotes.

Always build your strings in variables so that you can print them in the debug window to see your concatenation errors.

 

[plog]

This isn't going to fix your issue, but your first 4 lines of code are killing me. You've declared 3 variables that you never use. Then you set an undeclared variable to a value and immediately set it to another value.

Also, once you get this thing to compile, the only action its going to take is setting a form element to a number--it's not going to add any data to tbl_Requirements. You do nothing with strSQL other than build it. You need to execute the SQL via DoCmd.RunSQL

For your issue, I don't think its a syntax error--Access will say everything is syntaticaly correct although its not what you intended (more on that in a minute), my guess is that Me.Req_Area, Me.Req_Type, Me.Req_Dept or Me!Req_No are not names of controls on your form.

While Access won't see it, you did make syntax errors when you tried to compile your strings with variables. You need to learn about 'escaping'. I actualy escaped the word 'escaping' in this and the prior sentence. In language to literally talk about a word, we put it in quote marks so people know we aren't actually using it in the sentence, but talking about the word itself. Same thing needs to happen when compiling strings inside of code.

Var1="escaping"

Var1 is a variable made up of a literal (the word "escaping"). We can now use Var1 in making another string and put "escaping" into it by referencing Var1:

Var2="You need to learn about " & Var1 & "."

I put a literal string between double quotes, added an ampersand then the variable, another ampersand then I used more double quotes around a period. So Var2 holds the value "You need to learn about escaping."

But, the word escaping is not escaped inside that. We do that with single quotes in the literal part before and after the variable:

Var2="You need to learn about '" & Var1 & "'."

You need to apply that to both your DMax criteria and your strSQL variable so that they work like you want. As it is now the DMAX criteria doesn't have single quotes around the variable values you insert into it and strSQL doesn't use any variable values at all--because your references to them don't happen outside of double quotes.

 

[bftsg]

I believe you would need to concatenate the form values, as you have tried with nextreq, but that syntax is incorrect?
Put all the criteria for the DMax into a string variable, and debug.print it to see what you have, not what you think you have.
Text needs single quote surrounds,dates use # and numbers do not need anything.

All the variables are numeric. Thanks for your help

 

[bftsg]

Hi. Which line is highlighted with the error message?

The rejected field was Req_Dept and it was rejected wherever I put it in the command - I tried this in case the error was in another field but was hidden. The table included that field, the form used that field name and it is used in many places throughout the application. It is only in the DMAX command that I have a problem. It hjas worked for months like this until I changed the name of the field slightly. Is there anything happening behind the scenes that still refers to the old name? Thanks for your help

 

[bftsg]

Text values MUST be enclosed in quotes.

Always build your strings in variables so that you can print them in the debug window to see your concatenation errors.

Thanks for your input. All the fields are numeric.

 

[bftsg]

This isn't going to fix your issue, but your first 4 lines of code are killing me. You've declared 3 variables that you never use. Then you set an undeclared variable to a value and immediately set it to another value.

Also, once you get this thing to compile, the only action its going to take is setting a form element to a number--it's not going to add any data to tbl_Requirements. You do nothing with strSQL other than build it. You need to execute the SQL via DoCmd.RunSQL

For your issue, I don't think its a syntax error--Access will say everything is syntaticaly correct although its not what you intended (more on that in a minute), my guess is that Me.Req_Area, Me.Req_Type, Me.Req_Dept or Me!Req_No are not names of controls on your form.

While Access won't see it, you did make syntax errors when you tried to compile your strings with variables. You need to learn about 'escaping'. I actualy escaped the word 'escaping' in this and the prior sentence. In language to literally talk about a word, we put it in quote marks so people know we aren't actually using it in the sentence, but talking about the word itself. Same thing needs to happen when compiling strings inside of code.

Var1="escaping"

Var1 is a variable made up of a literal (the word "escaping"). We can now use Var1 in making another string and put "escaping" into it by referencing Var1:

Var2="You need to learn about " & Var1 & "."

I put a literal string between double quotes, added an ampersand then the variable, another ampersand then I used more double quotes around a period. So Var2 holds the value "You need to learn about escaping."

But, the word escaping is not escaped inside that. We do that with single quotes in the literal part before and after the variable:

Var2="You need to learn about '" & Var1 & "'."

You need to apply that to both your DMax criteria and your strSQL variable so that they work like you want. As it is now the DMAX criteria doesn't have single quotes around the variable values you insert into it and strSQL doesn't use any variable values at all--because your references to them don't happen outside of double quotes.

Well spotted about the variables. I will fix that. The setting of the field NextReq to 0 prevents problems adding 1 to the field. The data is actually written to the table but the Req_No is always set to one. This must be numerically higher than the previous record matching the three other criteria. The code is executed as an AfterUpdate command so the fieldnames are in memory. The only change I have made from the working version is to change one field name. Nothing else. Hmmm...

 

[Gasman]

So Debug.Print the criteria string and post it back here? (within code tags as well please, using the </> icon)

 

[bftsg]

I changed the me.fieldname to forms!formname!fieldname and everything works! No idea why as all names matched.

Some days, you waste more time using shortcuts rather than a few more keystrokes. Thanks everybody for their help.

 

[Gasman]

Well a Debug.Print would have told you in the first place?

Plus I cannot believe that Me does not work, as the code is in a combo event on a form.? I'd hardly call Me. a shortcut?

However, the main thing, is it works, but remember all this for the next time.

 

[bftsg]

Well a Debug.Print would have told you in the first place?

Plus I cannot believe that Me does not work, as the code is in a combo event on a form.? I'd hardly call Me. a shortcut?

However, the main thing, is it works, but remember all this for the next time.

I was getting to desperation stakes with it as, like you, I cannot see why me.fieldname would not have worked. It worked in other parts of the function so... Anyway, all is good in the world again - until the next time The application has been running for over ten years without this hickup occurring before so it makes it doubly difficult to understand. My only idea is that there may be something running that we do not see that got upset by changing the fieldname in the function. Everything else in the rest of the app uses the new name without error. Time to tuck this one away and carry on but not regardless.

 

[Pat Hartman]

I have recently noticed some changes in forms that make them act more like reports and that isn't desirable.

Reports rewrite your query and save the MS version even though you can still see your version in the RecordSource. The result of this is that the orderby in your query is ignored. you must use the sort property of the report. As part of this rewrite, all fields that are not bound to controls are removed from the replacement RecordSource. That means that even if you includ a column in the recordsource, you cannot use it in VBA unless it is bound to some control. The control may be tiny and/or hidden but it must exist. However, this removel doesn't happen immediately so immediately after you add the RecordSource and while you are testing (before closing the report), everything seems fine. It is only after the report is saved and reopened that things stop working.

Forms used to immediately pick up changes to querydefs. In my current version they don't seem to. So, if you change something in the querydef, delete it from the RecordSource and past it back to jog Access into reregistering it.

 

[bftsg]

I have recently noticed some changes in forms that make them act more like reports and that isn't desirable.

Reports rewrite your query and save the MS version even though you can still see your version in the RecordSource. The result of this is that the orderby in your query is ignored. you must use the sort property of the report. As part of this rewrite, all fields that are not bound to controls are removed from the replacement RecordSource. That means that even if you includ a column in the recordsource, you cannot use it in VBA unless it is bound to some control. The control may be tiny and/or hidden but it must exist. However, this removel doesn't happen immediately so immediately after you add the RecordSource and while you are testing (before closing the report), everything seems fine. It is only after the report is saved and reopened that things stop working.

Forms used to immediately pick up changes to querydefs. In my current version they don't seem to. So, if you change something in the querydef, delete it from the RecordSource and past it back to jog Access into reregistering it.

Does explain a few problems I have had in the past. Frustrating having to go through the refresh cycles every time a change is made.

 

[Pat Hartman]

I thought I was going crazy. They didn't seem to go all the way. They just didn't refresh the RecordSource if the QueryDef was independently changed.