Clearing Fields With A Command Button

[daredevildan25]

I have a form Which is called frmEditShiftReportTable, it is linked directly to tblShiftReports. What i need to do is create a command button that will delete all the entries in the following three fields from the table: Endedat, Comments, and CompletedBy. Is there any way to make a command button do this??

The reason this needs to be done, is because users fill out shift reports based on the shift they are on, (Monday Days, Monday Evenings, Tuesday Days, Tuesday Evenings, Etc.....) Therefore, when is comes back to Monday Days, they nedd to have those three fields blank again to be able to fill in the updated information for that particular day.



Thanks

 

[Mile-O]

That sounds like you should think about normalising your database before continuing any further.

Search for normali*ing.

 

[daredevildan25]

I only have the one table in this dbase, that is why everything is together. Isn't ther any other way to make this command button work?

 

[Mile-O]

Are you saying that you have a database with column headers like Employee, Monday Morning, Monday Afternoon....Friday Afternoon?

And each of these people has been allocated one row in the database table?

If that's the case then you've got then a spreadsheet would seem more suited to what you need than a database, especially if you are deleting data and not storing it.

You'd be better changing you database structure to something like this:

tblEmployees
EmployeeID
EmployeeForename
EmployeeSurname
[other employee data]

tblShifts
ShiftID
ShiftDescription
[other shift data]

tblHistory
HistoryID
EmployeeID
ShiftID
DateOfShift

This way, you can keep historical data relating to shifts, you'll find it easier to keep tabs of your employees, etc.

 

[daredevildan25]

Sorry, i guess i wasnt very clear.

These are the fields in tblShiftReports

Shift ReportID
Day
CourseName
StartTime
FinishTime
EndedAt
Sites
Comments
CompletedBy

Every for months, the courses change, and the tables have to be cleared of all information, and new information entered. the three fields that i want to clear with the command button(EndedAt, Comments, CompletedBy) are the only fields in that table that don't have any information in them already, because that information is not known until the actual end of the shift.
Several courses may run on a Monday Day, but that info is already in the table (ShiftReportID, Day, CourseName, StartTime, FinishTime, and the Sites involved in the course). What we don't know until the end if the Shift, is when the course Actually EndedAt, What Comments need to be made about the course, and who the shift report was CompletedBy.

 

[daredevildan25]

Do you Have any suggestions to this dilema

 

[daredevildan25]

Any Suggestions Mile

 

[Rich]

He's already given you the obvious one

 

[Mile-O]

Have a look at this - I've taken your table structure and added a few more tables, maybe one too many!

 

[daredevildan25]

Hey Mile I saw your example, looks good.

Here is what i am working with. Is there a way to make that button work in this dbase?


The reason for this is that i am working on a co-op placement, and i don't have alot of time left to restart the whole dbase.

I only imported what you would need to see from the dbase, so just use the shift reports button in the main menu, as the others do not work.


Thanks again

 

[Mile-O]

I can't download your database as I am using Access '97.

Although the example I posted has more tables than what you described, the actual code behind the button only deals with the one table (the one you described).

You can just adapt the code on that, changing any field names, etc.

 

[daredevildan25]

i will give it a try, but i am not very good with coding, as i have never learned it in school. Basically what i have to do is copy the code, then go into my dbase and make a button, add an event to the on click area, then paste the code in the vb window right?

 

[Mile-O]

Paste the code and then, if the field names don't match those in your database then just change those.

Here's the code on the button in my example, and I've commented it for you.

Private Sub cmdClear_Click()

    Dim db As DAO.Database ' create a database object
    Dim rs As DAO.Recordset ' create a recordset object
    
    Set db = CurrentDb ' set our database object to this specific database
    Set rs = db.OpenRecordset("tblShiftReports") ' set the recordset object to the table we wish to edit within our database object
     
    With rs ' using our recordset object
        .MoveFirst ' move to the first record in our recordset object
    
        Do While Not .EOF ' while we are not at the end of the recordset
            .Edit ' allow us to edit our recordset
            .Fields("EndedAt") = Null ' set the field [b]EndedAt[/b] to a null value
            .Fields("Comments") = vbNullString ' set the field [b]Comments[/b] to a null value
            .Fields("UserID") = Null ' set the field [b]UserID[/b] to a null value
            .Update ' update the edits to our recordset
            .MoveNext ' move to the next record in our recordset
        Loop ' return the loop started at the Do While statement
        
        .Close ' close the recordset object
    End With ' stop using the recordset object
    
    db.Close ' close the database object

End Sub

The only parts I can see being changed are the these three lines:

.Fields("EndedAt") = Null
.Fields("Comments") = vbNullString
.Fields("UserID") = Null

but only the fieldnames within the inverted commas.

 

[daredevildan25]

i get an error when i hit the clear fields button.

Compile Error
User-Defined Type Not Defined


Then the code opens up and this line is highlighted

Dim db As DAO.Database



Any ideas why?

 

[Mile-O]

While in the module, goto Tools -> References

and make sure that Microsoft Data Access Objects 3.5, or higher is checked.

Also, ensure that this is above Microsoft ActiveX Data Objects in the priorities list.

 

[daredevildan25]

If you mean the Vb Module Window, i don't have the option of clicking on tools and then references in there, the word is there, but it is grayed out.

 

[Mile-O]

that's because you have the code running right now.

Hit the Stop button (little square) and that will stop the code running - now you should be able to do it.

 

[daredevildan25]

ok, it seems to work now, but there is one thing. When i hit the clear fields button, you have to exit the form, then reopen it to have the fields actually emptied. Is there some way that as soon as the button is clicked, the fields are emptied right away?

 

[Mile-O]

Try adding:

Me.Requery

or

Me.Refresh

after the db.Close line

 

[daredevildan25]

They both work, but i have to click the button twice for it to actually clear the fields.