Can one of two fields in a table be required?

[dionu]

I am trying to set up a table that requires one field or the other not be null. How can I do this?

Also, I am creating a form that has a text box in it that should be filled with the data from the field that is not null. Any ideas on how to set up the control source or whatever so that it pulls the information from one field or the other depending on which one has data in it?

 

[nickjar2]

I think what you need to do is the following (or along these lines anyway)

In your form, create an unbound field. In the control source, enter something like:

=iif(isnull([field1],[field2],[field1)

The syntax maybe incorrect because I haven't got Access in front of me.

The first Q I don't think can be done at the Table level. You could set up some code that says:

if isnull(txtName) or isnull(txtName2) then
msgbox "Message here!"
exit sub
end if

This will then ensure that at least one of the text boxes has been filled in. You could place this code before updating the record ie. on the before_Update of the form or something Hope this is ok, please let me know.

Nick (Everton Rool OK!)

 

[UncleJack]

Your requirement is best met by controls on a form rather than at the table level. Use bound controls on the form for the data elements you want to add to the table. An easy way to cause the write to occur is through a command button. In the code behind the command button make a test to see if both of the fields are null and if so do not enter the record:

Private Sub comAddRecord_Click()
If IsNull(Me!Field1) and IsNull(me!Field2) then exit sub
docmd.openquery "AddRecordtoTable"
End Sub

The query to add the record should specify both fields on the form.

In the OnCurrent event of the form put this kind of code:
If IsNull(Me!Field1) then
txtActiveRecord = Me!Field2
Else
txtActiveRecord = Me!Field1
End if

Uncle Jack