473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Login Form Compile Error

33
I'm working with a preexisting database here, so forgive me if this seems a bit clumsy.

I'm trying to populate an entry in the table TBL_CURRENTUSER with the value of the record in the table TBLUSER that corresponds to the username/pass entered into the login form that this code applies to.

However, I am receiving the compile error "Sub or Function not defined. What am I doing wrong? The code in question is:

Expand|Select|Wrap|Line Numbers
  1. Update TBL_CURRENTUSER
  2.   Set TBL_CURRENTUSER.UserName = DLookup("[USER NAME]", "TBLUSER", "USERLOGIN ='" & Me.txtPASSWORD.Value & "'")
  3.   Set TBL_CURRENTUSER.USERLOGIN = Me.txtLOGINID
  4.   Set TBL_CURRENTUSER.Password = Me.txtPASSWORD
  5.   WHERE USERID = 1
  6.  
Attached Images
File Type: jpg error message.jpg (57.5 KB, 238 views)
Dec 16 '15 #1

✓ answered by jforbes

This should get you closer:
Expand|Select|Wrap|Line Numbers
  1.  Dim RN As String
  2.  RN = Me.RealName.Value
  3.  Dim LID As String
  4.  LID = Me.txtLOGINID.Value
  5.  Dim PW As String
  6.  PW = Me.txtPASSWORD.Value
  7.  Dim SQLText As String
  8.  SQLText = "UPDATE TBL_CURRENTUSER " & _
  9.  "Set USERNAME = '" & RN  & "' " & _
  10.  ", USERLOGIN = '" & LID & "' " & _
  11.  ", Password = '" & PW& "' " & _
  12.  "WHERE USERID = 1" 
  13.  
String/Text values need to be enclosed in quotes.

12 1019
Rabbit
12,516 Expert Mod 8TB
That's SQL code, not VBA code. They are not the same and not interchangeable. You need to use the VBA DoCmd.RunSQL function to execute a string that contains SQL code.
Dec 16 '15 #2
abcrf
33
Ah. Stupid error, of course. Is there a equivalent VBA command that I can replace it with?
Dec 16 '15 #3
Rabbit
12,516 Expert Mod 8TB
You need to use the VBA DoCmd.RunSQL function to execute a string that contains SQL code.
Dec 16 '15 #4
hvsummer
215 128KB
As Rabbit said, you need to do something like this
Expand|Select|Wrap|Line Numbers
  1. public function/sub Name()
  2. dim StrSQL as string
  3. strSQL = "Update ... Where .."
  4.  
  5. Docmd.RunSQL strSQL
  6. end function/sub
  7.  
but I don't suggest you to use docmd.runSQL since that method not really stable.
instead, you could use CurrentDb.Execute
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "SQL string", dbFailOnError and dbSeeChanges
Dec 17 '15 #5
mbizup
80 64KB
I also generally use CurrentDB.Execute, with dbFailOnError. One of the huge advantages of this over the OpenQuery method is that WHEN the SQL fails, you will see a meaningful error message that lets you know that the UPDATE, INSERT etc did not happen.

Faulty SQL with the OpenQuery method on the other hand, may fail 'quietly' with no indication of any problem.
Dec 17 '15 #6
zmbd
5,501 Expert Mod 4TB
HVS:but I don't suggest you to use docmd.runSQL since that method not really stable.
You'll have to explain what you mean by "not stable." This is a reliable and supported method of executing SQL from within VBA.

mbizipI also generally use CurrentDB.Execute, with dbFailOnError. One of the huge advantages of this over the OpenQuery method is that WHEN the SQL fails, you will see a meaningful error message that lets you know that the UPDATE, INSERT etc did not happen
Not only the ability to trap for the failure is an advantage for this method but also the fact that there is no user prompt, YEA, and you can return the number of effected records!

One thing that was pointed out to me awhile back is the use of the "CurrentDB" object several times in a procedure can have some unusual results. It appears that it is best practice to assign a pointer to the CurrentDB object, (set objDB=CurrentDB), instead of repeatedly calling the currentdb - besides, it's a lot easier to type objDB a dozen times instead of CurrentDB. ;-)
Dec 17 '15 #7
abcrf
33
Thanks, everyone.

I'm much closer, I think, to getting this up and running, but I'm getting "Run-time error 3061: Too few parameters. Expected 3". I tried, per a suggestion on another forum, substituting variables inside the SQL code, but that hasn't helped. Any suggestions?

The code in question is:

Expand|Select|Wrap|Line Numbers
  1. Dim RN As String
  2. RN = Me.RealName.Value
  3. Dim LID As String
  4. LID = Me.txtLOGINID.Value
  5. Dim PW As String
  6. PW = Me.txtPASSWORD.Value
  7. Dim SQLText As String
  8. SQLText = "UPDATE TBL_CURRENTUSER " & _
  9. "Set USERNAME = RN " & _
  10. ", USERLOGIN = LID " & _
  11. ", Password = PW " & _
  12. "WHERE USERID = 1"
  13.  
and

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute SQLText, dbFailOnError And dbSeeChanges
Dec 17 '15 #8
jforbes
1,107 Expert 1GB
This should get you closer:
Expand|Select|Wrap|Line Numbers
  1.  Dim RN As String
  2.  RN = Me.RealName.Value
  3.  Dim LID As String
  4.  LID = Me.txtLOGINID.Value
  5.  Dim PW As String
  6.  PW = Me.txtPASSWORD.Value
  7.  Dim SQLText As String
  8.  SQLText = "UPDATE TBL_CURRENTUSER " & _
  9.  "Set USERNAME = '" & RN  & "' " & _
  10.  ", USERLOGIN = '" & LID & "' " & _
  11.  ", Password = '" & PW& "' " & _
  12.  "WHERE USERID = 1" 
  13.  
String/Text values need to be enclosed in quotes.
Dec 17 '15 #9
abcrf
33
That was it, thanks!
Dec 17 '15 #10
NeoPa
32,556 Expert Mod 16PB
ZMBD:
One thing that was pointed out to me awhile back is the use of the "CurrentDB" object several times in a procedure can have some unusual results. It appears that it is best practice to assign a pointer to the CurrentDB object, (set objDB=CurrentDB), instead of repeatedly calling the currentdb - besides, it's a lot easier to type objDB a dozen times instead of CurrentDB. ;-)
Indeed. Because of the conventions of the language it's easy to miss that CurrentDb() is actually a function that returns a DAO.Database object that references the current database. Every time it's run (or referenced in code) it returns a new and different object. Even though they all refer to the same currently open database.
Dec 18 '15 #11
hvsummer
215 128KB
@ZMBD:
not really stable for me because when it fail, I don't know what happen.
I can only read error in error trap, that could lead me to wrong cause/case.
Beside that, expression.execute can open many type of query (string/defs/stacked - update/delete/...) while another method like runSQL can't.
Dec 18 '15 #12
zmbd
5,501 Expert Mod 4TB
HVS:
OK, then it is not that RunSQL method is unstable, denoting a random, uncontrollable or unpredictable result. Instead, it is the fact that for your application the Execute method has features that you require. In a text based communication, semantics take on an importance that IRL-Communications do not always require.

Each method has their strengths and weaknesses. RunSQL requires some error trapping, can be an all or nothing, doesn't work well with ODBC connections (ie to a MySQL or SQLServer) unless working with linked tables, one has to enable/disable the system warnings, etc... The Execute method can handle passthru queries to the ODBC connected database, tends to be faster, can be set to rollback on error, can be included within a transaction state, and can bypass some of the relational requirements (although usually highly inadvisable).

Keep in mind, that for non-ODBC needs, the RunSQL method can generally process the same SQL strings (DoCmd.RunSQL Method) that the Execute method is capable of and can handle transactional calls on the given query-object or SQL without the same need to explicitly start and update the transaction state, along with being able to resolve references to form-control values directly from the form as the VBA object modal is exposed to the RunSQL method whereas it is not exposed to the Execute method as it passes the string directly to the DB-Engine.

Beside that expression.execute can open all type of query (string/defs/stacked - select/update/delete/...) while another method like runSQL can't.
From the above link, The RunSQL method is capable of processing:
A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.
which are the same requirements for the Execute method.

I do want to point out the following change in the Execute method starting with Acc2007
In earlier versions of the Microsoft Jet database engine, SQL statements were automatically embedded in implicit transactions. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. To improve performance, these implicit transactions were removed starting with version 3.5. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.

For best performance in a Microsoft Access workspace, especially in a multiuser environment, nest the Execute method inside a transaction. Use the BeginTrans method on the current Workspace object, then use the Execute method, and complete the transaction by using the CommitTrans method on the Workspace. This saves changes on disk and frees any locks placed while the query is running.

A further discussion for this will require a new thread (besides, there are thousands of articles covering the RunSQLvExecute in the online journals). I only cover this here because it was brought up and I want to clarify that the RunSQL method isn't any more or less "stable" than the Execute method, just, each has different features.
once again for easy reference:
+ DoCmd.RunSQL Method (Access)
+ Database.Execute Method (DAO)
Dec 18 '15 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: calaha | last post by:
Hi all, I have been working with this since last night, and can't quite figure out why it's not working. I have a simple login box form that is set to be my startup form in my Access app (upon...
15
by: carr4895 | last post by:
Hello. I was wondering if someone could help me too with a login form. Upon startup, I have to display a password screen and it should accept a user name and password. User name can be anything...
5
by: MLH | last post by:
A97 aborts creation of MDE reporting that there's a compile error in one form. Sure enough, if I remove the form from the source database and attempt to compile the MDE, it succeeds. But when I...
13
by: knot2afrayed | last post by:
I am trying to fix error- object does not exist- I want it possible to allow object not to exist. I am writing a script on a page that may or may not include a login form. For example-after a...
5
by: AkiMatti | last post by:
Hi! I'm using MS Access 2002 and have troubles calling my function in a module I've created. When I try to even write the call of the function, it instantly gives me an error saying: "Compile...
0
by: sandari | last post by:
The following code (web.config in Visual Studio 2005) is supposed to redirect a user to the appropriate Form depending on their role. However, regardless of the user's role, the only page...
1
by: naharol | last post by:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@page import="java.sql.*"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01...
1
by: ibsaqi | last post by:
Hello friends i am basically a new in programming language...i got readymade new login script and which i want to use for another form for my website.i.e Form that i want to use in my website...
5
Slaxer13
by: Slaxer13 | last post by:
Hi ppl. I am having trouble with a login form. When i click the btnConfirmar, after having both Login and Password it gives me an error 2471: The expression you entered as a query parameter produced...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.