Require an entry in either of two fields, but not both

[jkk]

Hello, I suspect my problem requires some VBA code, which I know nothing about. In any case, here's the scenario.

In a table, I'd like to require an entry in either Field A or Field B. But an entry should be in only one of the two fields.

Thanks.

[June7]

Then should be only one field with options.

Then can set the field as Required in table setup.

[jkk]

Thanks for the reply. Perhaps I'm not understanding, or perhaps I didn't explain my question clearly. I'll try again.

I'd like an entry to be required in either Field A, or Field B. For a particular record, an entry must be made in either Field A, or Field B. But only one of said fields can have an entry.

I guess I'm going have to explain the table structure to make it clear what I'm trying to accomplish.

* The table in question is called "Interactions."
* Field A is a foreign key field that will pull data from another table. Field A and the foreign-key table are both called "Customers."
* Field B is identical in structure, but both the field itself and the foreign-key table are called "Vendors."

So, in the Interactions table, I'd like to record data based on interactions with either Customers or Vendors. A record in the Interactions table must have an entry in one of those fields, but can't have an entry in both.

So here's the crux of my problem: I understand how to set fields as Required. However, what I don't know how to do is to set up the "either/or" logic I described above.

Thanks again.

[June7]

An odd structure. But okay, need VBA. The real trick is figuring out what event to put code in, perhaps the form BeforeUpdate. Maybe like:

If (Not IsNull(Me.FieldA) And Not IsNull(Me.FieldB)) Or (IsNull(Me.FieldA) And IsNull(Me.FieldB)) Then
MsgBox "Must enter data to either FieldA or FieldB, not both."
Cancel = True
Me.FieldA = Null
Me.FiledB = Null
Me.FieldA.SetFocus
End If

[jkk]

Hi, thanks again, June7. I'm leaving the office for the weekend, will give your suggestion a try next week.

Hope your weekend is great!

[jkk]

June7, I tried to insert the VBA code for the form, but no go. I suspect it's my ineptness with VBA, rather than your code. At this point, it's clear that I should spend some time engaging with a VBA tutorial - which I dearly wish to do, however, I lack such time at present.

So I'm going to have to abandon this aspect of my Access project for now, and unfortunately, this thread. I'm inclined to leave the thread open in case someone else wants to pick it up. But I'll leave it to your discretion as a forum moderator whether or not to leave it open.

In any case, thanks for the help offered.

[Bob Fitz]

I tried to insert the VBA code for the form, but no go.

What exactly does that mean. What actually happened.

[jkk]

Bob Fitz, I mean nothing happened. The code had absolutely no effect. The form for the Interactions table is also used as a subform, and I suspect this may be adding more complexity than I have skills to negotiate. I tried a few things in the VBA window to get some kind of result. But really, I'm just mucking about.

Bottom line is I wasn't able to apply the code successfully, and I need to learn some basic VBA skills before proceeding further. If y'all want to turn this thread into a VBA tut (which I doubt, but. . .) be my guest. As I said, I don't have time right now go further with this. Sorry if I've wasted anyone's time. I'm out.

Cheers.

[Bob Fitz]

Perhaps you could post a copy of the db. We may be able to make the required changes and you could study them at your leisure.

[June7]

How did you 'insert the VBA'?

In the BeforeUpdate event property of form, select [Event Procedure]. Then click the ellipsis (...) to open the VBA editor with the event procedure. Type code.

Refer to link at bottom of my post for debugging guidelines.