By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,369 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

allowing null values in rst

P: n/a
What is the DAO method of allowing a recordset to have null values?

Thanks,
marie
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.supernews.com...
What is the DAO method of allowing a recordset to have null values?

Thanks,
marie

Nov 13 '05 #2

P: n/a
Are you having trouble reading data out of a recordset? Is that what
you mean?

David

Nov 13 '05 #3

P: n/a
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.OpenRecordset("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.supernews.com...
What is the DAO method of allowing a recordset to have null values?

Thanks,
marie

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
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.Append tbl
....?

Thanks for your help,
Marie
<da********@lycos.co.uk> wrote in message
news:11********************@c13g2000cwb.googlegrou ps.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

Nov 13 '05 #6

P: n/a
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(strTDFName 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(strTDFName)
Set fld = tdf.Fields(strFldName)

fld.Required = booSetVal
End Sub

Anyone else want to add a tuppence worth?

Dave

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.