Basica Access VBA question

[Deavyin]

Hey Guys.



New to the forum here and I have a question on something I am programming in access.
It is within an attendance DBA for our HR department. The issue is on the below 'save' button that should insert several fields, using data on the current form, into a discipline table. The problem is, if I don't have all of the fields entered (which they won't always have) then I get an 3464 data type mismatch error message. I need to find out how to allow null values even though I'm pulling data from the current form that is open. Any help you could offer would be appreciated.

Private Sub Command38_Click()
Dim strSQL As String
    strSQL = "INSERT INTO Discipline ( DateOfIncident, Category, ActionTaken, LossOfBonus, Description, FirstName, LastName, Supervisor, StartOfProbation, EndOfProbation, FollowUpDate1, FollowUpDate2, FollowUpDate3, EmployeeID )" & " VALUES ('" & Me.DateOfIncident & "' , '" & Me.Category & "' , '" & Me.ActionTaken & "' , '" & Me.LossOfBonus & "' , '" & Me.Description & "' , '" & Me.DisciplineFirstName & "' , '" & Me.DisciplineLastName & "' , '" & Me.SupervisorName & "' , '" & Me.StartOfProbation & "' , '" & Me.EndOfProbation & "' , '" & Me.FollowUpOne & "' , '" & Me.FollowUpTwo & "' , '" & Me.FollowUpThree & "' , '" & Me.DisciplineEmployeeID & "');"
    CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.Close acForm, "NewDiscipline", acSaveYes

End Sub

[pbaldy]

Is there a reason you don't use a form bound to the table? It would be simplest. That said, one way would be to use the AddNew method of a recordset, which would handle them better. Another would be to build your string conditionally. In other words, you only add a field and the value to the string if the control on the form has a value.

[Deavyin]

Hey Paul,

Thanks for answering! Honestly, I am very new to this VBA stuff and was kind of thrust into it so I'm trying to figure out the best ways as I go. The reason I used the unbound form, is because each discipline record is unique and each employee can have more than one. We also want to be able to track all of the past discpline entries so we can determine if the employee should lose their yearly bonus or not. We do a very similar thing for the absence form.

essentially, the DBA works like this: HR opens it up to an employee form where they can search for a specific employee. There are 3 tabs: 1. the employee tab with general employee info. 2. the absence tab that lists all of the employees past absences in a subform. 3. discipline tab that functions the same as the absence tab. One both the discipline tab and the absence tab there are two buttons. 1. add and 2. delete. If you click add, you get the unbound form that auto populates the currently selected employee (first name, last name, employee id) and allows you to enter in the rest of the fields. When you click save, it appends a new record to the discipline table (I used employee id, first name, and last name as foreign fields because we are broken into multiple companies and, for some dumb reason, we re-use employee id's). Also, each record get's a unique auto-id that HR can use if they want to delete an individual record.

That is kind of why i ended up going with the unbound form instead of binding the form to a table.

I hope what I said made sense and wasn't too verbose. If there is a better way of going about things, that would affect the same result, I would love to hear about it and give it a try.

thanks again!

[pbaldy]

The "typical" design would be a form and subform in continuous (or datasheet) view. The form would be bound to the employee table and the subform to the discipline table. Master/child links keep them in sync with each other so that the subform displayed records related to the employee being displayed. That lets Access do most of the work for you. You can do what you're doing (unbound), but it's more work. I use bound forms most of the time.

[Perceptus]

Quick look at your code.

If you are not using a bound form you should check to make sure the data is like you want it.

[RayMilhon]

Was looking at your post and would like to make 1 recommendation. Don't use employee name as a foreign key

1. Create a table for the different companies
2. In your employee table include a field for the companyid.
3. Make your Primary Key in the employee table a combination of the employee id and the company id. it'll save you a lot of headaches later on.

[ssanfu]

I would also re-look your structure. It does not appear to be normalized. In the table "Discipline", you have three (date?) fields: "FollowUpDate1", "FollowUpDate2" & "FollowUpDate3". Your insert SQL has them delimited with the text delimiter (') instead of the date delimiter (#). With repeating fields, this is an indication the the table is not normalized. If you do 3 follow ups, what are the results of the followups? There is no place/field (that I can see) to indicate the results of the follow up.

My $0.02 worth

[June7]

The issue is that the SQL variables are delimited as text and therefore can't be Null and even though the table can accept Null the SQL can't pass the Null because of the text delimiters. It's a catch-22 that can be handled with conditional code as indicated by Paul in post2.

Other approaches are:

1. bound form

2. open recordset of Discipline table and use AddNew method to create new record in the recordset then populate each field of the record - won't matter if textbox is Null
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Discipline;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
rs.AddNew
rs!DateOfIncident = Me.DateOfIncident
...
rs.Update
rs.Close