Compile Error: Sub/Function not defined

[TheKillerMonkey]

So, I'm getting a compiling error when I try to run this. For context its a form for setting a new password if its their first login.

Private Sub ConfOk_Click()
On Error GoTo ConfOk_Click_Err


If (NewP = ConfP) Then
    Update Users
        Set Password = Forms!NewPass!NewP.Value
            Where UserName = Forms!UserLogin!tbUsername.Value
        Set First = 0
            Where UserName = Forms!UserLogin!tbUsername.Value
        DoCmd.Close acForm, "NewPass"
    Else
    MsgBox ("Passwords Do Not Match")
End If


ConfOk_Click_Exit:
   Exit Sub


ConfOk_Click_Err:
   MsgBox Error$
   Resume ConfOk_Click_Exit


End Sub

Any help would be awesome!
Thanks,
-Matt

[Micron]

Your information is very sketchy.
Which line causes the error?
Is the name of the sub/function defined in the message?
What is First (besides maybe being a reserved name that you should not use)? What is NewP, ConfP? Functions/subs?

[TheKillerMonkey]

It's highlighting the first line (private sub ConfOk_Click).

I don't know how to answer the name of the sub/function question...

First is a yes/no box in the user table. It sets the flag for whether the enter a new password form needs to be opened(Which is the form where this button is located). NewP and ConfP are textboxes containing the new password that the user wants to set up.

I wasn't giving that much information because I was hoping it was just an obvious issue with the set up of the sub.

[Micron]

I'm getting a compiling error when I try to run this.

A compile error is one that occurs when you try to compile the project. When you get an error during code execution, it is a run-time error - the distinction is important. I'm going with the assumption it's a run-time error since you say it occurs when you try to run it.

It's highlighting the first line (private sub ConfOk_Click).

Often, the first line is yellow and there is also a different highlight on the offending line further down, although not always.

I don't know how to answer the name of the sub/function question

I'm asking if the name of the sub or function that is 'undefined' is part of the message. I was hoping your answer would be "Update Users". It looks like you're not only trying to use an incorrect construct for a sql (where Access thinks Update Users is a call to an unrecognized procedure) but you are expecting it to run when execution gets there (it will not). This would explain the error message.

If that is not a procedure call, but it's a sql statement that works when you dump it into a new test query, then you either have to use line continuation characters or concatenate the sql statement while assigning it to a variable, THEN code to run it (note I did not say Execute it - that is for Action Queries). I very much dislike line continuation characters, so I will show you my way and leave you to research the other and decide which to use.

Dim sql as String 
'some would preface this name with the variable type (svSql, strSql, etc) I think it's universally accepted that sql is a string
'however, it is a clue for you to research naming convention (as well as reserved names, per my comment on the name FIRST)
sql = Update Users Set Password = Forms!NewPass.NewP.Value Where " 
sql = sql & "UserName = Forms!UserLogin.tbUsername.Value Set First = 0 Where "
sql = sql & "UserName = Forms!UserLogin.tbUsername.Value"

Value is not necessary. It's the default property of controls that contain values.
Forms!UserLogin! - I recommend you use the ! to denote objects which have collections (such as the Forms! collection) and the period where the ! is not required. Both should work, but a mis-spelled control name (!NewwP) won't be caught until run time; .NewwP will be caught during a compile.
If you adopt this concatenation method, be consistent. Either end with a required space OR start with one. I prefer the former.
Once the sql concatenation is complete, you must tell Jet to run it:

Docmd.RunSql sql

There are other ways to accomplish this task (such as Docmd.RunSql ("sql string here") if short or with a whole bunch continuation characters if it's not.

If Update Users is in fact a procedure call, Access can't find it. I hope it is not, otherwise I've written a lot for little gain.

[TheKillerMonkey]

It was highlighting Update in blue, and the first line of code in yellow. Wasn't sure if the blue was me highlighting it since it was like the last thing i changed or the program was highlighting it because of the error. Since it was a sub/function was undefined I was thinking that I started the call wrong or something since it was a copy and paste from what you gave me earlier.

I don't remember what specifically the message box yelled at me for, but I don't think I would've used the word compile unless it told me about it. I can update tomorrow although I'm pretty sure its because I got the wrong syntax from the site I was looking at telling me about Update.

It seems more important when asking questions about it but it feels redundant calling a table a table.

I'll try this tomorrow when I have my database in front of me.

If that is not a procedure call, but it's a sql statement that works when you dump it into a new test query, then you either have to use line continuation characters or concatenate the sql statement while assigning it to a variable, THEN code to run it (note I did not say Execute it - that is for Action Queries). I very much dislike line continuation characters, so I will show you my way and leave you to research the other and decide which to use.

This scares me when all I wanted to do was make it update a value in a table >.>

Thanks for the constant support!
-Matt

[Micron]

It was highlighting Update in blue

Then I am 99% sure the problem is as I outlined in my 3rd paragraph of my last post. Access can't find the sub or function "Update". Whether you intend to or not, you are trying to call one by that name (and with a reserved word at that! ). Re-write this

Update Users
        Set Password = Forms!NewPass!NewP.Value
            Where UserName = Forms!UserLogin!tbUsername.Value
        Set First = 0
            Where UserName = Forms!UserLogin!tbUsername.Value

using one of the suggested methods as instructed and that should take care of this particular issue. However, after more careful review, I notice your dual SET operator, which is incorrect. The shortened version should look something like this, after noticing my own glaring errors (you cannot pass literal strings that are form control references):

sql = "Update Users Set [Password] = ' " & Forms!NewPass.NewP & " ', [First] = 0 " 
sql = sql & "Where UserName = ' " & Forms!UserLogin.tbUsername & " ' "

notwithstanding your use of reserved words. You can remove the extra spaces I put between the single and double quotes. They are there only because I thought you might overlook them otherwise. If you put a break on this line, Docmd.RunSql sql then click to run the code, execution will stop there. At this point, the entire sql string will have been constructed and you can, in the immediate window of the vb editor, type ?sql and hit enter. You will get an answer to your question. You can accept the result, or go back to the db window, open a new query, dump in the output you got and go do datasheet view and see what the query will do. Then you can go back to the vb editor and either stop or run the rest of your code.

I looked at all of your threads and could not see where I supplied you with any code before this one. Also, if you wish to elaborate, I don't get the comment about a table.

[TheKillerMonkey]

My apologies, I thought you were also the user at the end of my macro question, but apparently that was someone else, and they commented on my lack of meaningful names to my objects >.>

(Updated Username to have a lowercase n since thats how it is on my table)

sql = "Update Users Set [Password] = ' " & Forms!NewPass.NewP & " ', [First] = 0 "
sql = sql & "Where Username = ' " & Forms!UserLogin.tbUsername & " ' "

So, adding in the DoCmd.RunSQL SQL, I get a pop-up saying 0 records will be changed. And checking the table, nothing was updated.

If you put a break on this line, Docmd.RunSql sql then click to run the code, execution will stop there. At this point, the entire sql string will have been constructed and you can, in the immediate window of the vb editor, type ?sql and hit enter. You will get an answer to your question. You can accept the result, or go back to the db window, open a new query, dump in the output you got and go do datasheetview and see what the query will do. Then you can go back to the vb editor and either stop or run the rest of your code.

Not sure what you mean by breaking the line, so the rest of this is kind of a ? to me

Private Sub ConfOk_Click()On Error GoTo ConfOk_Click_Err


If (NewP = ConfP) Then


        SQL = "Update Users Set [Password] = '" & Forms!NewPass.NewP & "', [First] = 0 "
        SQL = SQL & "Where Username = ' " & Forms!UserLogin.tbUsername & "'"
        
        DoCmd.RunSQL SQL
    
        DoCmd.Close acForm, "NewPass"
        DoCmd.Close acForm, "UserLogin"
    Else
    MsgBox ("Passwords Do Not Match")
End If


ConfOk_Click_Exit:
   Exit Sub


ConfOk_Click_Err:
   MsgBox Error$
   Resume ConfOk_Click_Exit


End Sub

[Micron]

So either my sql construct is not correct or yours wasn't right to begin with. Two things to try.
Google Access vba editor and get some understanding of the tool. In my version, a break is the brown dot which is placed by clicking there. Execution will stop at that line, which will highlight yellow. Everything in the procedure above that break has been processed, so I can mouse over variables and their value will show up in bubble help. Or in the immediate window at the bottom (you may not have elected to make this window visible yet) I can question what the value of a variable is by typing ?strCurName (for example), hit return, and the answer will be there. In your case, you can type ?SQL and post the sql for analysis. The vbe is too big of a thing to go into here completely, so I suggest you read up on it.


HOWEVER, I think it would be better if you create a new update query (if you don't already have one) that does what you want. Use an actual value for the password and if that works, you can use Forms!NewPass.Newp (for example) as the criteria for the update but the form has to be open with a password in the control when you run this. Once you have that working, you can use the sql from it and rewrite the sql in your code, or look for the differences to ascertain why the current one is not working and fix that one.

[TheKillerMonkey]

I did a bad job of removing deadspaces with the single quotes. There was still the space after the single quote for the username... so it was looking for ' Matt' instead of 'Matt'

I can add a break by clicking off to the side.

[Micron]

Glad you solved it. Did you spot that by using the immediate window? It can be a very valuable tool.

[TheKillerMonkey]

I used msgbox(sql). When I hovered over the code for sql it cuts off the tooltip midway through username, and ?sql only adds the line 'Print SQL', it wasn't showing what it evaluated as. But it is pretty cool to see the evaluations, essentially my equivalent has been using msgboxes for the values i'm checking.

[Micron]

?sql only adds the line 'Print SQL', .

You must not be putting in the immediate window as instructed.

[TheKillerMonkey]

Well, ... sure .. I guess commands will work if you put them in the right window . . . >.>