help! forms will make me crazy!

[Nadia]

hello every one ..
i read several topics in the forum and they were so good,
but i still need help i many things..

actually i begun to use Access a few days ago, learned how to make tables,relations , and where to write SQL,

but i'm finding problems in the forms designing!

I'm supposed to do a student registration system, as a project in a database course,
and my main problem, that is i can't find how to compare values together!

i mean like in the Login form, how can i compare the value that a user enters with the values in the table, so if the user id, and password that he entered a are valid!

i saw that we can make codes to solve those issues using VBA? or using conditions in Macro ..
but i know nothing about Visual Basic language ..
and Macro seems good, but i still don't know how the conditions are written? -- or what is the right way to make it.



So, if anybody continued reading till this line, could you please give me an advice?
what to learn? from where? where should i start from? the best and the easier way to take!


i am really sorry about bothering you, but i just have few days to finish the work ..

thanks .. at least for letting me write here.

 

[Nadia]

i feel that i posted the thread in a wrong place ;s, excuse me please .. i'm new here as you can see ;D!

 

[Pat Hartman]

Your log in form needs a field for the id and another for the password. It also needs a button to "login".

In the Click event of the button, you could use a DLookup() to validate the userid and password. The code would look something like this:

If DLookup("UserID", "tblUsers", "UserID = '" & Me.UserID & "' AND Password = '" & Me.Password & "'") = Me.UserID Then
    DoCmd.OpenForm "yourmainform"
Else
    Msgbox "Your password and userID are invalid.  Please correct them and try again.",vbOKOnly
    Exit Sub
End If

Obviously, you'll need to change all the names but that's the logic. The DLookup() is using both the UserID and Password to locate a record so you don't have to do separate tests. You especially don't want to give the user information that he can use to his advantage if he's a hacker. For example, if you first looked up the UserID and then looked up the password, you would know which was wrong and you can tell the user that his userID is wrong or his password is wrong. But, that is too much information.

Just a thought - I am somewhat concerned that you have gotten to this point in the class and have no clue how to use VBA. Does your teacher not mention it in class? Or were you just slacking off?

 

[Nadia]

I need to make something clear, i'm supposed to learn how to use MS Access to make a registration system, by my OWN.

the course is about studying SQL, RA, and those stuff! .. and in the course Lab we are using Oracle, but we are just beginners and don't know much.

so, easily teacher said, that i need you to search the internet and learn how to use Access , make the project and submit it to me!

..
and now i will try to understand what you have just explained .. and try to apply it.
thanks.

 

[Pat Hartman]

I'm sorry. That came out harsher than I intended.

And for this I'm sure you're paying thousands of dollars for and the teacher probably has tenure!

If you were to compare Access (VBA) with Oracle Forms, VB.Net, C#, etc. Yes, Access is easy. But easy is relative. If you've never written a program before, you have a lot to learn. We'll be here

Try this site http://www.accessmvp.com/strive4peace/ Crystal has some excellent training material.

 

[Nadia]

He actually wanted us to learn how to learn!, and depend on ourselves, i have no problems with that except the lack of time..
but i'll do my best, and try to learn every thing i need from the site, which seems to be very useful ..

thanks so much, i am really grateful.

 

[VilaRestal]

Can I suggest a variation to the code Pat supplied:

The "If DLookup(...) = Me.UserID" is a little confusing. (It makes it look like the logon form is bound to a table?)

I would do it as (note unbound textboxes):

If DCount("*", "tblUsers", "UserName = '" & TextBox_Username.Value & "' AND Password = '" & TextBox_Password.Value & "'") > 0 Then
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "yourmainform"
Else
    Msgbox "Your password and userID are invalid.  Please correct them and try again.",vbOKOnly
    Exit Sub
End If

And an attempt at a simple explanation of that:

DCount is a domain aggregate function that returns the count of records that it finds according to the criteria supplied in it's parameters:

DCount([field],

,[filter])

translates into SQL as SELECT COUNT([field]) FROM

WHERE [filter]

So the code translates as:

If any records exist in tblUsers where the username is equal to the contents of the username textbox and the password is equal to the contents of the password textbox Then...
Open the main form
Otherwise
Tell the user they got something wrong

That is a little basic for a logon system. In a decent system you would want to be handling usernames and passwords with apostrophes and quotation marks and the code above will break in those circumstances (it's also vulernable to SQL injection). I suggest you get that working, understand it and then we can look at improving it.

Also, once you have a logon system you presumably want to stop people from bypassing it and there are one or two things that must be done to achieve that. Again we can come to that later.

 

[VilaRestal]

It might help to see it working so I've thrown one together

The only user is x with password y

And I should explain a bit more about SQL injection:

A password

' OR '1' = '1

will always get access to the system (so long as there is at least one record in tblUsers).

If that's new to you then see if you can work out why.

 

[VilaRestal]

The only reliable way to get around SQL injection is to use parameterized queries instead of domain aggregate functions.

I've attached an improved version with that.

The code behind the logon form is as follows:

Option Compare Database
Option Explicit

Private LoggedOn As Boolean

Private Sub cmdEnter_Click()
    If Len(txtUser.Value) > 50 Then
        MsgBox "That username is too long."
        Exit Sub
    End If
    If Len(txtPass.Value) > 50 Then
        MsgBox "That password is too long."
        Exit Sub
    End If
    Dim qdf As QueryDef
    Dim strSQL As String
    strSQL = "SELECT ID FROM tblUsers WHERE Username = txtUsername AND Password = txtPassword"
    Set qdf = CurrentDb.CreateQueryDef("", strSQL)
    qdf.Parameters!txtUsername = txtUser.Value
    qdf.Parameters!txtPassword = txtPass.Value
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    If rs.RecordCount > 0 Then
        LoggedOn = True
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "frmMain"
    Else
        MsgBox "Your Username and Password are invalid.  Please correct them and try again.", vbOKOnly
    End If
    rs.Close
End Sub

Private Sub cmdQuit_Click()
    Application.Quit
End Sub

Private Sub Form_Close()
    If Not LoggedOn Then Application.Quit
End Sub

That will be a little bit hard for a beginner to take in but, as I've shown, it is necessary to go to these lengths to get any logon system to be properly secure.

Knowing how to use parameterized queries in code rather than domain aggregate functions when any of the parameters are supplied by the user is an important skill to have in Access and all SQL front ends.

 

[Nadia]

i really appreciate your help,
will apply what you taught me as soon as possible ,

thanks again ..

 

[gemma-the-husky]

nadia

assume first you have a number of tables, organised so that data is "normalised" - ie there is no redundant data. in this scenario tables are "joined" to other tables by some common data (generally a single field, but could be a combination of fields). you always arrange this so that 1 instance of the master table corresponds to multiple instances of the child table.


[as an aside, this repesents a 1 to many join. if you have a many to many join - so in this instance you have multiple instances (let us say 3 items) of the same data, in the master table, all related to a group of items in the child table (say 5 items), you end up with a cartesian product.

In this case, you have 3 items in table A, and 5 in table B, but you get 15 in the combined set, and you have no way of knowing how those items relate to one another. so you HAVE to decompose any many-to-many relations into 2 1-to-many relations as part of the normalisation process]

now - to retrieve informatiion, you can examine a table directly. you can also construct a query that selects information based on a single table (ie A SUB-SET of the table). you can also produce a query that joins tables together using the common fields already established, and the query can select a sub-set of that data.

so you can display a form or report based on one of these queries, and step through each item in the data-set, one row at a time

you can also process (update) a whole dataset extraxcted in this way, according to a set of rules you define

now, the other way you can use data, is by a vestored read. given a single table, you can find data from a given column, based on a key value (or other information) which idenitifes a row.

dlookup (domain lookup does this)

given a table of login ids and passwords, it enables you to locate a password, for a user with a given id. (or returns null, if the id is not found)

basically the whole of database theory is based on these basic priniciples. normalised data, bulk processing of datasets, and locating specific values based on other key values.

but get the data structure right, and then managing the data, and designing forms becomes very straightforward

hope this helps

 

[VilaRestal]

Thanks for the all thanks Nadia

It's an interesting part of database design and common to all but the most basic databases so definitely a good thing to learn.

You say they will teach you SQL, which is great and will help a great deal. Learning VBA isn't hard for any intelligent person but it can get confusing when it's mixed up with SQL, which in Access it often is. (And add to that learning the concepts of good database design and learning to use the Access designer. It's not easy to get the ball rolling.)

Until they do teach you the basics of SQL and until you do start to pick up the basics of VBA then anything at all advanced like designing a reliable security system is going to be very tricky and a bit voodoo.

But you've made a smart first move by coming to the right place IMO.

The sample databases here are a good way to learn - by example.

With any programming language there's going to be a steep initial learning curve especially if it's your first. But with VBA once you've got the basics nailed then the curve flattens a lot (except when you encounter it's occasional limitations and try to work out ways round them). But it's a big thing and takes time.

I would suggest playing with and adapting LogonDemo1 and not LogonDemo2 for now. I'm sure you can understand what's going on with the first quite easily. The second one I posted isn't really for a beginner but I felt obliged to point out to anyone reading that the first wasn't really secure/reliable at all and how to make it so.

Good luck with your course.

 

[Nadia]

You can say that you reexplained all what i learned in a simple good useful way,

i just didn't get those lines:

now, the other way you can use data, is by a vestored read. given a single table, you can find data from a given column, based on a key value (or other information) which idenitifes a row.

dlookup (domain lookup does this)

and my main problem now is to learn how to use the value the user entered in a query ..

for example, if the user wants to select the cources that a specific student (with id: 777 ) registered:

select crsname
from Enrollment E, Cources C
where E.cid=C.cid and E.sid = 777

how can i pass the "777" from the to the SQL statment?

i wish to have a magic word that solve all the problem

 

[Nadia]

Thanks for the all thanks Nadia

It's an interesting part of database design and common to all but the most basic databases so definitely a good thing to learn.

You say they will teach you SQL, which is great and will help a great deal. Learning VBA isn't hard for any intelligent person but it can get confusing when it's mixed up with SQL, which in Access it often is. (And add to that learning the concepts of good database design and learning to use the Access designer. It's not easy to get the ball rolling.)

Until they do teach you the basics of SQL and until you do start to pick up the basics of VBA then anything at all advanced like designing a reliable security system is going to be very tricky and a bit voodoo.

But you've made a smart first move by coming to the right place IMO.


With any programming language there's going to be a steep initial learning curve especially if it's your first. But with VBA once you've got the basics nailed then the curve flattens a lot (except when you encounter it's occasional limitations and try to work out ways round them). But it's a big thing and takes time.

I would suggest playing with and adapting LogonDemo1 and not LogonDemo2 for now. I'm sure you can understand what's going on with the first quite easily. The second one I posted isn't really for a beginner but I felt obliged to point out to anyone reading that the first wasn't really secure/reliable at all and how to make it so.

Good luck with your course.

another thanks

actually i learned about SQL, but know nothing about VBA ..

and i start playing in the code, just to replace usrename by ID ,
and changing the name of table and attributes but it didn't work!
i'm terrible i know

 

[VilaRestal]

You will be constructing the SQL as string variable in VBA and concatenating the value (777) into it:

Let's say the value 777 comes from a textbox but it could be from anywhere

Dim strSQL As String
Dim iSID as Integer
iSID = TextBox1.Value
strSQL = "select crsname from Enrollment E, Cources C where E.cid=C.cid and E.sid = " & iSID

The question then becomes what do you want to do with that SQL.

You could make it the recordsource of a subform:

SubForm1.Form.RecordSource = strSQL

Or you could open a recordset with it:

Dim rs As DAO.RecordSet
Set rs = CurrentDb.OpenRecordset(strSQL)

The possibilities are endless.

Does that answer your question?

 

[VilaRestal]

I should say I think

strSQL = "select crsname from Enrollment E inner join Cources C on E.cid = C.cid where E.sid = " & iSID

is more efficient. Otherwise it will be making a cartesian join of the two tables and then filtering that. With big tables that could be a lot of processing. (I stand to be corrected on that though, I know all database engines can do some clever planning when running queries.)

 

[Nadia]

You will be constructing the SQL as string variable in VBA and concatenating the value (777) into it:

Let's say the value 777 comes from a textbox but it could be from anywhere

Dim strSQL As String
Dim iSID as Integer
iSID = TextBox1.Value
strSQL = "select crsname from Enrollment E, Cources C where E.cid=C.cid and E.sid = " & iSID

^
that's what i wanted! and so clear ..

The question then becomes what do you want to do with that SQL.

You could make it the recordsource of a subform:

SubForm1.Form.RecordSource = strSQL

Or you could open a recordset with it:

Dim rs As DAO.RecordSet
Set rs = CurrentDb.OpenRecordset(strSQL)

The possibilities are endless.

what's the meaning of recordsource?

if i just want to display the result?
..

and if we want to INSERT a new record to the table from the form, how can we do it?

 

[Nadia]

I should say I think

strSQL = "select crsname from Enrollment E inner join Cources C on E.cid = C.cid where E.sid = " & iSID

is more efficient. Otherwise it will be making a cartesian join of the two tables and then filtering that. With big tables that could be a lot of processing. (I stand to be corrected on that though, I know all database engines can do some clever planning when running queries.)

i don't know much about join types, but will study it. and try to use the best way ..

 

[VilaRestal]

When a form is bound to data (most forms) the recordsource is what table of data it will show. You can change that at runtime by changing a form's recordsource property.

But anyway, in this you're just looking one field from one record so that's not appropriate.

If you just want to get the value of that field then you would use a recordset:

    Dim strSQL As String
    Dim iSID As Integer
    Dim strCRS As String
    iSID = 777
    strSQL = "select crsname from Enrollment E inner join Cources C on E.cid = C.cid where E.sid = " & iSID
    Debug.Print "strSQL = """ & strSQL & """"
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    If rs.RecordCount = 1 Then
        strCRS = Nz(rs!crsname, "") 'NZ replaces a null value with ""
        Debug.Print "strCRS = """ & strCRS & """"
        'TODO: Something with strCRS or maybe even change its value: rs!crsname = "test"
        
    Else
        'TODO: Logical error handling
    End If
    rs.Close

If it was just a single table or query then you wouldn't need all that. You could just use the DLookup function.

So, the best way would be to create a query with the definition

select * from Enrollment E inner join Cources C on E.cid = C.cid

And save it as say qryEnrollmentCources (I keep spelling cources as you did btw - perhaps it would be better to change it to courses before we go too far)

then we can just do

    Dim strSQL As String
    Dim iSID As Integer
    Dim strCRS As String
    iSID = 777
    strCRS = DLookup("crsname", "qryEnrollmentCources","E.sid = " & iSID)
    Debug.Print "strCRS = """ & strCRS & """"
    'TODO: Something with strCRS

I hope that makes sense

 

[Nadia]

If it was just a single table or query then you wouldn't need all that

thanks for saying that!

So, the best way would be to create a query with the definition

select * from Enrollment E inner join Cources C on E.cid = C.cid

And save it as say qryEnrollmentCources

cool!

(I keep spelling cources as you did btw - perhaps it would be better to change it to courses before we go too far)

'TODO: Something with strCRS

??

..

thanks for the tenth time! - i guess