allowing null values in rst

[Modest Marsupial]

What is the DAO method of allowing a recordset to have null values?

Thanks,
marie

[Douglas J. Steele]

Not sure what you mean. Are you asking how an individual field can have Null
or not? That's a result of the Required property of the Field in the
TableDef object.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Modest Marsupial" <Ma*******@mbg. com> wrote in message
news:10******** *****@corp.supe rnews.com...

What is the DAO method of allowing a recordset to have null values?

Thanks,
marie

[davidjpike]

Are you having trouble reading data out of a recordset? Is that what
you mean?

David

[Modest Marsupial]

Sorry about the lack of clarification. I need the entire recordset to be
able to accept null values so that when I update it, I don't get the error:
Run-time error '3314': The field cannot contain a Null value because the
Required property for this field is set to True. Enter a value in the
filed.'

I can't find how to set the Required property for the recordset to False.
Should it be at the end of this statement somewhere:
Set rst = dbs.OpenRecords et("tbl")

When I go to write the recordset to a table, it won't have null values. But
when I am updating the recordset and doing calculations on it, it sometimes
will. So I don't need the table to have the ability to have Null values,
just the recordset.

Thanks, marie

"Modest Marsupial" <Ma*******@mbg. com> wrote in message
news:10******** *****@corp.supe rnews.com...

What is the DAO method of allowing a recordset to have null values?

Thanks,
marie

[davidjpike]

Ahhhhh right,

You're almost there you're looking in the wrong place. Remember
recordsets essentially read data from tables (a nice simple way of
putting it), so allthough the error happens in your code with the
recordset - it doesn't mean this is the problem.

In order to pull this off you'll need to edit the table to accept the
null values.

Open up your table in design view, find the fields you want to be able
to set to null. If you look at the bottom of the screen you will see
they have a "Required" property. Make sure this is set to no.
Does that help you?

Dave

[Modest Marsupial]

Okay, Dave! That would explain why when I finally figured out how to set
the recordset to allow nulls, I still got the same error....grrr.

But I don't want to edit the table in design view: The table has a variable
number of fields, and every time the client runs this program, the table
will change. So I need to do this automatically in my program. Do I do it
when I specify: Set tbl = New ADOX.Table or in cat.Tables.Appe nd tbl
....?

Thanks for your help,
Marie
<da********@lyc os.co.uk> wrote in message
news:11******** ************@c1 3g2000cwb.googl egroups.com...

Ahhhhh right,

You're almost there you're looking in the wrong place. Remember
recordsets essentially read data from tables (a nice simple way of
putting it), so allthough the error happens in your code with the
recordset - it doesn't mean this is the problem.

In order to pull this off you'll need to edit the table to accept the
null values.

Open up your table in design view, find the fields you want to be able
to set to null. If you look at the bottom of the screen you will see
they have a "Required" property. Make sure this is set to no.
Does that help you?

Dave

[davidjpike]

Using DAO:

Don't worry about the error I got major confused when I first
encountered it. I get the impression you have created some kind of make
table query? (Am I right?) If so you may need to make something to
cycle through the fields in the created table and run the below.

This piece of VBA will change any table you give it. It may be better
as a function - make sure you test it, as it's "air-code".

Sub SetRequired(str TDFName As String, strFldName As String, booSetVal
As Boolean)

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As DAO.Database

Set db = CurrentDb
Set tdf = db.TableDefs(st rTDFName)
Set fld = tdf.Fields(strF ldName)

fld.Required = booSetVal
End Sub

Anyone else want to add a tuppence worth?

Dave