Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Unbound Forms and ADO

    Hey gals and guys:

    UPDATE at bottom.

    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
    With Me
    rst.AddNew Array("Date", "EmployeeNo", "RegularHoursWorked", "OvertimeHoursWorked", _
    "HolidayPay", "Justification", "Comments"), _
    Array(.Date, .cboEmployeeNo, .RegularHoursWorked, .OvertimeHoursWorked, .HolidayPay, _
    .cmbJustification, .Comments)
    End With
    rst.Update

    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?
    Last edited by DocX; 09-07-04 at 00:53.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    DocX

    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.

    PS It does not occur with DAO.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    Rod,

    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.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    DocX

    Likewise I am using Access v2002 with Microsoft Jet and Access 2000 file formats. If you have success with nulls then you have encouraged me to try again.
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Jul 2004
    Posts
    156
    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.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  6. #6
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    DocX,

    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.
    Rod

    fe_rod@hotmail.com

  7. #7
    Join Date
    Jul 2004
    Posts
    156
    Thanks, Iron Rod!

    I will have to remember that from now on. I don't normally create tables or append to them using ADOX, but I'm sure it will come up. I usually just create tables with Access and use those tables.

    Thanks again!
    DocX

    The teachings of God's Begotten: 2 John 1:9

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •