Setting field to null

[Daryl2106]

Hi,



I have a form to enter attendance in that pulls students from a StudentEnrollmentTable based on FacultyName which is selected from a combobox that runs the following code:

Private Sub cboInstructorName_Click()
Me.Requery
End Sub

A query (StudentAttendanceBYFaculty) is run each time a faculty name is selected. On the form their is a field name TempClassesAttended which is bound to a field of the same name in the StudentEnrollmentTable. Teachers will enter attendance data and run an append query to append the current form records to the StudentAttendanceTable. Each time the form is repopulated the most recent TempClassesAttended values are pulled into the form. This is what is expected.

Now I want to load Null values into the TempAttendance field on the form each time the Faculty selects their name and runs the event. I looked at code online and it seems easy enough, but I don't know enough to make it work. This is the code suggested:

UPDATE TableName SET FieldName = Null

OR

UPDATE MyTable
SET MyField = Null

I don't now how to include this into the current event so that the event will return the faculty records with Null values in the TempAttendence field.

Thanks and take care,

Daryl

[June7]

For VBA code:

CurrentDb.Execute "UPDATE TableName SET FieldName = Null"

But why is this needed? Why isn't the field already null?

[Daryl2106]

Hi June,

Thnaks for the reply. I tried the code (changing TableName and FieldName to the reflect my DB) and it didn't work. I am assuming that CurrentDb.Executeand the quotes are included exactly as given.

Private Sub cboInstructorName_Click()
Me.Requery
CurrentDb.Execute "UPDATE StudentEnrollmentTable SET TempClassesAttended = Null"
End Sub

The reason the field is not null is because it is bound to a field (TempClassesAttended) in the StudentEnrollmentTable that serves as tempory storage of the data being entered until an Append query is run. The TempClassesAttended retains the data and the next time those same records are called up (to enter a new set of records based on a new date) with the most recent data.

Maybe there is a better way. I tried using an unbound field bu each time a piece of data once entered into a single students record, it propagated on the form through all other records.

Take care,

Daryl

[alcapps]

just noticed you had a me.requery before the update? not sure if that should be reversed. Just wondering.

june7 code is valid..

sometimes you need to set the currentdb like so..

dim db as DAO.database
or
dim db as database

set db = currentdb()

db.execute "Update StudentEnrollmentTable SET TempClassesAttended = Null", dbfailonerror



not sure but give it a try.. and check to see if you need the me.requery before or after the update query.

[Daryl2106]

HI & thanks,

Just wondering if "database" below is the actual name of my db? As well, does the name of my db go in the ()?

dim db as DAO.database
or
dim db as database

set db = currentdb()

[alcapps]

You setting an object type.
Just type it the way it is.
No db name needed in ().

Give it a try

hope that helps.

[alcapps]

Private Sub cboInstructorName_Click()
Dim db as database

set db = currentdb()


Db.Execute "UPDATE StudentEnrollmentTable SET TempClassesAttended = Null", dbfailonerror


End Sub

i left out me.requery you can put that where it needs to go.

[Daryl2106]

Hi,

Here is a screen capture of what I have and what I am getting.

[alcapps]

I am not able to look at d files currently. I'll look as soon as I can.

[alcapps]

dim db as dao.database


that was the or.. in my other post

[alcapps]

if that doesn't work then try setting the references

in the module..
go to tools and select references

then find Microsoft DAO 3.6 and click on that one..

hope that works for you...

[alcapps]

i found out that dropbox on my iPad allowed me open the screenshot file you sent. Is why i was able to respond so quickly..
gotta love technology..

[Daryl2106]

Thanks so much for all your help. I'll give that a try tomorrow and let you know how I get along.

Take care,

Daryl

[June7]

I use CurrentDb.Execute a lot. There is no reason to declare and set database object. I just verified by testing code in the VBA Immediate window and records were modified.

[alcapps]

I agree with june7,
Currentdb.execute is valid.
dimensioning the object db as database should work as well.
not sure why that is not working.

if you go to modules then click on tool and references is any of the references marked as missing?
sometimes that is the behavior of a missing reference.

just a thought.

Hope this helps