Update and Create statements

  • Thread starter Thread starter Brian C
  • Start date Start date
B

Brian C

I don't know SQL but need some code to do the following
but don't know where to start

I have a Teachers Table keyed on teachers initials which
looks like this:

Initials
Name
Telephone
Subject

I have a form (SetupTeacher) with the input boxes:

[SetupTeacher]!Initials
[SetupTeacher]!Name
[SetupTeacher]!Telephone
[SetupTeacher]!Subject

I need some SQL that selects a record based upon Initials
to update the other three fields.

If the record doesn't exist then I need to create a new
record with these details

I want to run the query from the form command button using:
DoCmd.OpenQuery "UpdateTeacher"
where my SQL is in the UpdateTeacher query

What would the SQL look like to update the record?
How do I test whether the record doesn't exist?
What does the SQL look like to create a new record?
How do I test a returned column, i.e. IF SUBJECT
= "French"



A working SELECT statement on this table would look like
this:

SELECT TEACHER.Initials, Teacher.Name,
TEACHER.Telephone, Teacher.Subject
FROM TEACHER
WHERE ((TEACHER.Initials)=forms![SetupTeacher]![Initials]);

In these queries do I always have to put tablename.column
or could I just put columen name (SELECT Initails FROM
TEACHER WHERE)?
 
Here is a different sugestion. Use the combobox wizard to look up a value on
the form. Use the current event of the form to sync the combobox display
value: Also change Name to TeacherName.
Public Sub Form_Current()
cboFindTeacher = Initials
End Sub
' set the limit to list property to true
' fill the cboFindTeacher using row source SELECT Initials FROM tblTeacher
' cboFindTeacher the name of the combobox

Public Sub cboFindTeacher_AfterUpdate()
Dim rs as Object

Set rs = RecordSetClone
rs.FindFirst "[Initials] =" & "'" & cboFindTeacher & "'"
If Not rs.EOF Then me.RecordSet.BookMark = rs.BookMark
Set rs = Nothing
End Sub

Public Sub cboFindTeacher_LotInList(NewData As String, Response As Integer)
DoCmd.GoToRecord acNew
Initial = NewData
Response = acDataErrContinue
TeacherName.SetFocus
ReFresh
End Sub
 
Back
Top