I am using an unbound form to add records to an ADO recordset. What I do is I use the unbound fields as values to add to the new record in a recordset. Here's a snippet of code basically describing what I'm doing:
rst.Open "LaborCost", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
rst.AddNew Array("Date", "EmployeeNo", "RegularHoursWorked", "OvertimeHoursWorked", _
"HolidayPay", "Justification", "Comments"), _
Array(.Date, .cboEmployeeNo, .RegularHoursWorked, .OvertimeHoursWorked, .HolidayPay, _
If you've worked with ADO recordsets before, you know that if you try to add a null value to a field in a recordset you get a type mismatch error. I've just recently started working with unbound forms. I'd like to get any suggestions on what to do.
I've considered If...Then statements for each field to determine whether it holds a value before adding it. I earlier considered If...Then statements for each combination of fields and its being there or not...that could take a lot of code and create a lot of wasted time and code the more and more fields you considered.
Any help you would give would be greatly appreciated! Thanks!
UPDATE: Well, it seems as though I had a blunder with realizing what was going on. In actuality, it was my new method of clearing all fields after adding a record. I'd failed to realize that assigning a value of empty string ("") to a numerical field on the form would cause a type mismatch error when trying to add "" to a numerical field in the recordset. At least, this is what I concluded. Am I correct?
I'm not going to help but I wish to register my interest in the problem of inserting null values under ADO. I often need to do this as the column normally contains a foreign key but if there is no related record then the value has to be null.
When I attempt to insert nulls under ADO I remember getting 'field not nullable' types of message - I suppose these are type mismatches. But there is a boolean property that can be set to make a field/column nullable, only it doesn't seem to work. I admit I gave up before I threw the PC out of the window and solved my requirement another way.
So maybe someone with more patience than me can shed light on this matter.
Since I've been setting all the fields to Null, I've been having no problems with setting text, memo, numerical, etc. fields to Null. It actually occurred with the empty string (""). I'm using Jet tables in an Access 2000 file format. I haven't worked with SQL Server or any other type of database tables, so I'm not sure if setting Null to these recordsets would have problems. What format are your data tables in?
Hopefully, this'll serve as another bump up to the top of the forum and get some fresh eyes looking at it.
I could e-mail a sample of my code to you if you'd like. I'd copy and paste straight out so you wouldn't have to worry about viruses and the like. Let you compare to see what I may be doing that you're not. Let me know.
Encouraged by your comments and being bored with what I ought to be doing I experimented again with writing Nulls to columns I create with ADOX. Low and behold I am able to do it.
For the record (and in case anyone else finds themselves in this particular backwater) the ability of a column to accept Nulls is set through the 'Attributes' property and not through the property in the 'Properties' collection named 'Nullable'. Moreover the syntax for creating a new column ('Append') requires you to specify a 'Name' and optionally supply a 'Type' and a 'DefinedSize'; no option to supply 'Attributes'. Hence the first time round I completely missed the 'Attributes' property but found the 'Nullable' property.
So thanks for your offer but not on this occasion.