I have a database with users. The users could be teachers, but they could as well be students, so they could do inscriptions on classes.
Relation Diagram here:
When I’m creating the class a form I face a problem. How can I get the user name in order to get the correct teacherID?
I would like to associate the teacher to the class.
Thank you
Base has “User Administration” functionality under “Tools” in the menu, which is active only when the database is “split,” meaning it is not an internal HSQLDB. The functionality allows different users to log in through the same LO database front-end. You can query the back-end for the current user by a variety of methods. HSQLDB provides USER() and CURRENT_USER functions, the latter being standard across platforms, but again LO supplies this functionality only when split (apparently-- that is the only situation when it works for me).
There is not a way to set a form’s default value to a function. Thus, you would use a macro to set the value of a blank control using the back-end’s CURRENT_USER as follows:
REM ***** BASIC *****
Sub UpUsr
priForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
txbx1 = priForm.getByName("txbx1")
Conn = priForm.ActiveConnection
SQL = Conn.CreateStatement()
SQLStr = "SELECT `TeacherID` FROM `Teacher` WHERE `UserID`=CURRENT_USER"
results = SQL.executeQuery(SQLStr)
results.first
usr = results.getString(1)
If txbx1.String = "" Then
txbx1.setString(usr)
txbx1.Commit
End If
End Sub
The portion in quotes is the query that returns the TeacherID name from the database where the UserID matches the CURRENT_USER value for the database, in my case, doug@localhost. The Teachers table will have one UserID for each database credential, and a TeacherID associated with that credential.
Activate the macro from an appropriate Form event, such as Before Record Action and it will update the text box named txbx1 located on the form named MainForm (see Form Navigator).
(if this answers your question, please accept the answer by clicking the check (
) to the left)
I think that I wasn’t totally clear.
When I’m writting the classes form, I just want to assign the correct Teachers Id to the Classes.
However. the name and the other teacher’s data is stored on users table.
How can I fill the teachersID in the Classes Form quering the usersID name? Assuming the user already has a teacher ID.
I can create a list of teachersID, but there are only numbers, I need to relate the TeachersID with UsersID to get the Users name.
What you describe is accomplished by the listbox or combo box control. The name to be displayed is in the first column for a listbox, the bound column is a subsequent column. The Teachers table would the be source for the list, or a query reorganizing the contents of the table.
I’ve already tried this solution. However in the Teachers table I’ve only have the usersId not the usersId name.
Do I need a listbox with a query?
Listbox would be for data entry, the data source can be stored query, table, view, or ad-hoc SQL. If you already have a unique piece of data for teachers, which is not clear from the diagram, then you could pull in teacher names or user ids with a JOIN query. You also could use a JOIN query as the record source for your list.
Thank you ! Could you give an join query example?
I know how to do simple queries, and I’ve already done some at my project, but I’m kind of noob with Obase =)
Ok, there are two basic ways. The simpler syntax is : SELECT "a"."UserID", "b"."UserName" FROM "Tbl1" AS "a" JOIN "Tbl2" AS "b" ON ("a"."UserID"="b"."UserID"). After the SELECT are the columns to display from the two tables that you have glued together and assigned the alias’ "a" and "b". To say which column from which table you use the alias plus a . and the column name. Also look up LEFT JOIN and ‘subquery’ for other examples. Use double-quote or backtick to surround names.