Solved

Access Login Form

Posted on 2004-08-11
18
283 Views
Last Modified: 2012-06-27
hi .. when i'm running this code. i'm getting this error.. "No value given for one or more required parameters." i have two text boxes in my form with name as username & password . and just a command button. the table is patron with cardnumber and password as fields in it.
here is the code.. plz help..

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

    Dim con As Object
    Dim rs As Object
    Dim stSql As String

   
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Patron]"
   
    stSql = stSql & " WHERE [CardNumber]= Forms!test1!usernumber & [Password] = Forms!test1!password;"
   
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset
   
    If (rs.EOF) Then
   
    MsgBox "Command not available."
   
    Else
   
    MsgBox "Command available."
    End If
   
    rs.Close
    Set rs = Nothing
    Set con = Nothing

   
Exit_Command9_Click:
    Exit Sub

Err_Command9_Click:
    MsgBox Err.Description
    Resume Exit_Command9_Click
   
End Sub
0
Comment
Question by:ganatra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
18 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 50 total points
ID: 11779270
change  WHERE [CardNumber]= Forms!test1!usernumber & [Password] = Forms!test1!password;"

to

 WHERE [CardNumber]= " & Me.usernumber &  " [Password] = '" & Me.password & "';"

i have assumed usernumber is a number not text (in table data type)

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 11780453
Cheers mate!

Good luck, and happy coding

Dave
0
 

Author Comment

by:ganatra
ID: 11780462
btw you forgot the AND in between..  :)

if i need some more help i'll contact u.. thx..
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 34

Expert Comment

by:flavo
ID: 11780482
oh yes... sorry

Anytime mate.
0
 

Author Comment

by:ganatra
ID: 11780744
hope u r there,

how will i write a query based on the form parameters,

the scenario is that i want to retrieve data from a table based on two parameters,
keyword and author.

i have this query working..

SELECT *
FROM Book
WHERE (((Book.Author)=[Forms]![MainForm]![keyword]));



Now what i need is that this Book.Author should also be read from the form. the form has a combo box name searchBooksCombo with values Author, Title , Description in it. so i want the query to select the Where from the Combo box.

hope you got me..

i'll give 50 points for this also..

give it a try..

i'm waiting..

0
 
LVL 34

Expert Comment

by:flavo
ID: 11780750
is the form open?
0
 
LVL 34

Expert Comment

by:flavo
ID: 11780752
oh sorry, i see you hav it working.. giveme a minute
0
 
LVL 34

Expert Comment

by:flavo
ID: 11780767
WHERE (((Book.Author)=[Forms]![MainForm]![keyword])) and (((book.author) = [forms]![mainform]![searchBooksCombo]));
0
 
LVL 34

Expert Comment

by:flavo
ID: 11780771
also, make sure your "bound column" is set to the author column for this to work

DAve
0
 

Author Comment

by:ganatra
ID: 11780789
no .. i think you didnt got me..

i dont need book.author  hardcoded..


simply say..

SELECT *
FROM Book
WHERE (((Book.xxxxxxxxxxxxxxx)=[Forms]![MainForm]![keyword]));


this xxxxxxxxxxxxxx  should be read from the form combo box. it can be author, genre, description.
hope you got me now..

plz help..
0
 
LVL 34

Expert Comment

by:flavo
ID: 11780817
aaaahhh.....

now this is either going to take some vb to dynamically change the querydef object...

Add a refrence to Microsoft DAO Obj Lirary 3.?? - To do this in the VB window, select Tools - Refrences and tick it from the list

now this code wil do it - add it to the form (MainForm) i assume - say from the command button you use to execute it

private sub cmdMyButton_click()

   Dim qry As QueryDef
    Set qry = CurrentDb.QueryDefs("qSelMyQuery") ' change name to your query name
   
    qry.SQL = "SELECT * FROM Book where " & Nz(Me.cboMyCombo, "Author") & " =  '" & Me.keyword & "';"

end sub

hope this helps.   if you'd rather create a temp qry, this can be done, depends on what you are doing with it

dave

0
 
LVL 34

Expert Comment

by:flavo
ID: 11780818
now this is either going to take some vb to dynamically change the querydef object...

should be

now this is going to take some vb to dynamically change the querydef object...


Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 11780820
also if the user didnt select anything from the combo box, i set it to author...

Dave
0
 

Author Comment

by:ganatra
ID: 11780903
Dave,

the query is changing dynamically, but the form is not showing the results when i click on the command button. i have place a datasheet on the main form . the datasheet is based on the search query.  when i put the keyword and select author from combo, nothing happens at that moment. when i open the form again, the datasheet is showing the results of the query. can this datasheet show dynamic results?

i'm posting a question to give u the 50 points i promised..

ok..

thx..

waiting for ur reply..
0
 
LVL 34

Expert Comment

by:flavo
ID: 11781503
can you post the full code you are using at the moment. It should work, its very hard to debug things like this without the actual db at hand.  

Its about 6:30pm here and im going out for the night, ill get back to you tomorrow at work (about 7am - T minus 11hrs)

Dave
0
 

Author Comment

by:ganatra
ID: 11806629
Hi Flavo,

I want to give you the 50 points i promised.. should i post a new question for it? hope you get this message.

regards,
0
 
LVL 34

Expert Comment

by:flavo
ID: 11806636
yeah, do that.

Did you get it all working??

Dave
0
 

Author Comment

by:ganatra
ID: 11919445
Hi Flavo,

Sorry i was away and couldnt reply you..  listen i have an open question in the java category that i want to close.. i'm not getting any good answers ..its worth 50 points.. here is the link..

http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21094629.html


i can award you these points.... but still if you need points in MSAcces category i will have to post a new question.. if you need the java 50 points ..post a comment on it and i'll accept it rite now..

reply,
...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question