help! forms will make me crazy!

[VilaRestal]

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

That's a quite different issue.

It depends on what you mean.

Normally you would let access handle new records by binding a form to a table or query (its recordsource property) and going to a new record and filling it in.

You can however execute SQL Insert statements with the CurrentDb.Execute method:

    Dim strSQL As String
    Dim iSID As Integer, iCID As Integer
    Dim strCRS As String
    iSID = 777
    iCID = 1
    strCRS = "Test"
    strSQL = "insert into Enrollment (cid, sid, crsname) values (" & iSID & "," & iCID & ",'" & strCRS & "')"
    Debug.Print "strSQL = """ & strSQL & """"
    CurrentDb.Execute strSQL

You can elso execute UPDATE and DELETE SQL commands.

But generally use the tools Access gives you for creating, modifying and deleting records. To start out with Access executing SQL left right and centre is a strange way to start. Most people would regard that as quite an advanced thing to be doing

By the way, Access's own SQL has some limitations and peculiarities and I'm not very familiar with what it can and can't do that Transact SQL can. I tend to work with T-SQL these days.

 

[Pat Hartman]

Vila,
Although TextBox_Password.Value is a possible syntax, you only get intellisense if you use "Me." when working with controls on a form (bound or unbound is irrelevant). The .Value property is the default property of controls in Access VBA and so is usually omitted. This confuses folks coming from VB where .Text is the default property.

 

[ScottK1396]

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!

Glad to see it hasn't changed in 20 years.
We've all had those. I remember an intro course, where the foreign teaching assistant told us how to start PFS:Write, gave us a sample letter, and said "make it look like this". 80% of the class used the spacebar to place the text. They all passed.

 

[Nadia]

I am not glad at all

 

[Nadia]

hello again, i wrote this statement using SQL :

DELETE *
FROM [section]
where sec_no=1 ;

it worked!

but when i used the VBA to do it like this:

Private Sub delete_Click()
Dim strql As String
Dim iSECID As Integer
iSECID = textbox1.Value
strql = "delete * from [section] where sec_no = " & iSECID
End Sub

and tried to run it, i get somthing about MACRO, and didn't know what to do .. help again guys!

 

[VilaRestal]

Firstly, the syntax would be DELETE FROM [section] WHERE sec_no = 1
(No * - deleting a record always deletes all fields)
Secondly the sub you posted doesn't do anything except assigns values to variables.

So, to execute it:

Private Sub delete_Click()
    Dim strql As String
    Dim iSECID As Integer
    iSECID = textbox1.Value
    strql = "delete from [section] where sec_no = " & iSECID
    DoCmd.RunSQL strql
    'or CurrentDb.Execute strql
End Sub

Or more simply:

Private Sub delete_Click()
    DoCmd.RunSQL "delete from [section] where sec_no = " & Me.textbox1
End Sub

(I prefer putting .Value after controls because it bothers me not to but as Pat says - you don't need to, it is the default property)

Some error handling would be wise too in case Access can't delete that record for any reason and spare the user the bug out message.

 

[Nadia]

Same thing happened! .. i have to choose a Macro, i will try to find the problem

thanks a lot Vila ..

 

[VilaRestal]

For that code to work you will need a button called Delete. Access should automatically assign that event procedure with that code in the form's module but it's worth checking it has 'Event Procedure' in its Click event property.

That it's asking for a macro suggests the button isn't hooked up to the code for some reason.

 

[Nadia]

it's already hooked up to the button, this is really weird!
now i am working on the relations and those stuff to make sure that they all right.

didn't imagine that the work on this project would be complicated like this!
will need more answers, hope that you would help me on it,

and thanks for the help so far Vila

 

[Nadia]

Is there a way to make a list of available courses (with there sections, and time) , and when the dean choose one of them to a specific student, a new record is added to the enrollment table,
the record will include Student_id, Course_id, Section_no , etc..


can't find a way except using a query to display the available courses with there sections
and don't know what to do next!

 

[VilaRestal]

I'm not sure what you mean by "when the dean choose one of them to a specific student"

I guess you'd want a parent form with two subforms - left and right.

Left shows list of courses.

Right shows the enrollments on the course that's selected on the left.

The dean would click the course on the left and then, in a new record on the record, select a student from a combobox bound to the Student_ID field of the enrollment record.

That's not too hard to do.

Before I try and explain how. Does that sound about right? What you were asking?

 

[Nadia]

yes exactly!

but about selecting a student_id , would it be a quick process?
i thought that he should enter the student ID before seeing available courses and choose one of them ..

/

and another question please, .. how can the combo box contain limited list of student,
list of student in specific department, so the head of department can choose just from this list ..

 

[VilaRestal]

but about selecting a student_id , would it be a quick process?

Nothing arduous, just a combobox with two columns - student_id and student_name where the bound column is column 1 and column1 has no width so doesn't get seen by the user.

i thought that he should enter the student ID before seeing available courses and choose one of them ..

OK so maybe just a combobox showing students. Once selected a subform shows the courses they're enrolled on and with another combobox showing courses they're not enrolled on that they can add them to.

Making it impossible for the user to add a student to a course they're already enrolled on and/or add a course to a student they're already enrolled on is better than letting them try and have Access throw errors in their faces. That's going to be the tricky bit.

how can the combo box contain limited list of student,
list of student in specific department, so the head of department can choose just from this list ..

Comboboxes have a rowsource that is a query like any other. It can be filtered in all sorts of ways.

There's a few elements to this so take it one step at a time and ask for help when you get stuck.

And probably best to start a new thread for each one in the appropriate section. This one's getting quite long and has a very ambiguous name.

 

[Nadia]

i am a little bit confused now, where should i start from?

and ok .. i will

 

[VilaRestal]

1) Start with an unbound form and draw on the combobox to select the student and try and populate that with an appropriate list for the current user.

2) Then you'll want a subform (or even just a listbox - a listbox might be better - simpler) that will show the enrollments of the selected student.

3) Then a combobox to select a new course to enroll him/her onto with a button to commit it.

2 and 3 will need some code: the rowsources of the controls will change depending on which student is selected in the student combobox. And the Enrol button will of course need a little code too.

Each time you find a difficulty, search these forums first. I'm happy to help as I'm sure others are but you'll learn better by investigating solutions for yourself where you can.

 

[Nadia]

..................
Ok ..