Basic 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

 

[Alansidman]

You need to look at the properties of the underlying table and determine what each field is set to. Text, Number, Date, etc. Required? Indexed? etc.

 

[Deavyin]

Thanks for responding.

I have checked that and made sure that the fields I don't want to be required (namely the date fields) are set to false for required and yet, unless I put everything in, I get that error message. I assumed it had something to do with the way i'm using the Me. command to pull the date from the currently open form.

 

[Alansidman]

Is your form unbound? Is that why you have a save button? In a query, you have to reference the control on a form in this manner: Forms!FormName.Control

A good rule of thumb is to test the query in the QBE to make sure it works and then transfer the SQL statement to your VBA

 

[Deavyin]

Not all of the fields on the form are bound as they are entered by HR while the Discipline form is open. The First Name, Last Name, Employee ID fields are bound to an Employee table and are automatically populated when the discipline form is opened from the general employee form that has an overall view of the employee's information. I hope that makes sense. All of the other fields on the discipline form are unbound and that is why i have a save button and the VBA code you see.

 

[plog]

Ugh. I come here with the best of intentions to help people with the issues they have posted, but I can't help noticing poorly structured databases. Dea, I think you have issues bigger than this.

Why does the table Discipline have EmployeeID, FirstName and LastName fields? I assume there is an Employee table somewhere in there that contains LastName, FirstName and EmployeeID fields as well, correct? If so, the Discipline table doesn't need the name of the employee. Additionally, numerated field names (i.e. FollowUpDate1, FollowUpDate2, etc.) is a sign of a poorly structured database as well.

One more thing before I get to your issue. How come this form isn't directly tied to the recordsource it is trying to save to? Why use this INSERT statement when using Discipline as the form's datasource will relieve you of using any code to interact with this data?

You sound like you have 2 issues--allowing nulls and data mismatch. To allow nulls you need to go into the table and make sure that the fields that can have nulls aren't required. Data mismatches occurr when someone tries to enter letters into a numeric field, or an invalid date (Feb. 30, 2011). Because you are using an INSERT statement to interact with your data (and not having the table as the form's data source as explained above) you need to catch/validate this prior to executing that SQL statement.

 

[Deavyin]

Wow...I'll just look past the rudeness. You can feel free to just not answer my posts if you can't do so in a constructive manner.

Having said that, I get your points and will update accordingly. However, what is the issue with the 'numerated' field names? These are actual follow up dates that are entered, on a case by case basis, by HR. Each employee and each follow up date (meaning to follow up with their supervisor to see if they should be removed from whatever disciplinary action their received) is set by HR when a new disciplinary record is created. I need to have a foreign key tying both the Discipline and Employee tables so that I can run queries off of it for reporting purposes. Yes, I get that we don't need all three (first, last, employee id) but we need at least one.

Also, the reason I went with the insert statement is because each employee will have multiple discipline entries. I also need to be able to run a report that will print out each discipline entry and I don't know how to go about doing that unless I have an insert query adding a new record to the discipline table.

 

[Deavyin]

Alright, I got it to work. I'm still using the Insert query to append to the discipline table and I am still using the unbound fields since each employee can have multiple discipline entries and we need to keep a history of them. Also, and I know it's not a good way of doing things, my company is split into 5 companies and they re-use employee id numbers between companies and that is why i used Employee ID, First Name, and Last Name as foreign fields. I know it's not elegant but it's also not that huge of a DBA.

Thanks Alansidman for your input...you were right and I had to change some of the table data types so they would be able to accept null values when not a required field (basically set them to text).

And Plog...I'm sure you have good intentions and I'm sure you know a lot (I am new at this access stuff and only getting into VBA)...but when the first thing you say in a post is UGH...it won't get people to listen to you. With a little bit of tact I would have said "oh...ok...how best should I go about this given my requirements?" Instead....well...don't even worry about it.

 

[jdraw]

Do you have your relationships established? If so, please post a jpg of your tables and relationships.
And see this post since you have less than 10 posts.
http://www.access-programmers.co.uk/forums/showthread.php?t=212123

 

[Deavyin]

Hey jdraw,

I really have to leave the office right now, but i'll try and do that when I get in tomorrow (can't take the dba home..company is weird like that). I do have relationships established currently. Both the Absence and Discipline tables are joined to the Employee table in a one-to-many relationship based on the employeeID field (which I will need to change since multiple employees can have the same ID: though...i've been talking to HR about changing this to make it easier).

 

[plog]

Mr. Tactless again here. Accept my apologies in advance for anything I say you don't want to hear, but...

I am still using the unbound fields since each employee can have multiple discipline entries and we need to keep a history of them.

You can accomplish this with a bound form. Most likely easier than how you are doing it now. You're table structure is already set up for this as a matter of fact--you have 'DateOfIncident' as a field which allows you to know when each incident occured. Using an unbound form is offering you no benefit.

Since I obviously have nothing constructive to add, I will fall off this post.

 

[jdraw]

since multiple employees can have the same ID

What is the purpose of ID????

 

[Deavyin]

What is the purpose of ID????

every employee, per company, is assigned an employee ID. It's a completely numerical value. Unfortunately, since we have 5 different companies and employees are split amongst them, more than one employee can have the same employee ID. I've been trying to convince HR to use company name then ID so that way they will be unique since no two employees, in the same company, can have duplicate IDs. Something like ADS10 or ADSGA10. I would like to use it as the primary key of the employee table.

 

[jdraw]

Since you are working on something for HR, does HR apply to all current companies? If yes, then I strongly suggest a unique employee identifier be defined to show that an employee in company1 is the same employee if they get transferred to company3. This is such a basic concept that I feel you need to resolve before moving too far with your database.
Now a Primary key in a database does not necessarily have to be anything you report on or users know about. However, HR and Finance must have some way to record pay and benefits, time reporting etc. by Employee-- whatever they use. You can use that to report Employees, but Access can use an autonumber Primary key and be quite content.

You may get some additional info about autonumbers here
http://www.utteraccess.com/wiki/index.php/Autonumbers

 

[Deavyin]

The unique identifier is something I am working on with HR right now. They have been using spreadsheets for years now so the number system they use, as dumb as it is, is engrained and they are not too keen on changing it. I've expressed how important it is, from a DBA standpoint, that the numbers used for each employee be unique since, as you are quite right in assuming, employees do get transferred from company to company. Essentially, we are a trash hauling company and our companies are split between residential, commercial, roll off, recycling, and maintenance and employees (particularly drivers) get moved from one to another. I work in Accounting yet still don't quite know why we split things up that way. Then again, I don't do our taxes so ehh.

Thanks for all the ideas everyone. I was reading a bit about unbound vs bound forms last night and, honestly, it almost seems like a battle of religions and not something i want to get into. For now, I'll just be happy that my database works and it's ridiculously bloated (it's also pretty and full of weird easter eggs; i'm like that).

 

[jdraw]

Sounds like WM?