Access Login Form

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
ganatraAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
flavoConnect With a Mentor Commented:
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
 
flavoCommented:
Cheers mate!

Good luck, and happy coding

Dave
0
 
ganatraAuthor Commented:
btw you forgot the AND in between..  :)

if i need some more help i'll contact u.. thx..
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Take the quiz
 
flavoCommented:
oh yes... sorry

Anytime mate.
0
 
ganatraAuthor Commented:
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
 
flavoCommented:
is the form open?
0
 
flavoCommented:
oh sorry, i see you hav it working.. giveme a minute
0
 
flavoCommented:
WHERE (((Book.Author)=[Forms]![MainForm]![keyword])) and (((book.author) = [forms]![mainform]![searchBooksCombo]));
0
 
flavoCommented:
also, make sure your "bound column" is set to the author column for this to work

DAve
0
 
ganatraAuthor Commented:
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
 
flavoCommented:
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
 
flavoCommented:
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
 
flavoCommented:
also if the user didnt select anything from the combo box, i set it to author...

Dave
0
 
ganatraAuthor Commented:
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
 
flavoCommented:
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
 
ganatraAuthor Commented:
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
 
flavoCommented:
yeah, do that.

Did you get it all working??

Dave
0
 
ganatraAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.