Unable to save a record because a record is required

[Banana]

I have a database set thus

Clients (the main form)

ClientsID (PK)
blah blah
Information and Referral (a check box)
blah blah
blah blah

ClientIR (the form that opens up when I & R is checked)

IR ID (PK)
ClientsID (FK to the above form)
Requests (a lookup referencing to IRCategory)

The code is set up so when the I R box is checked, form will open, and unchecking it will delete the I R record of that Client.

The problem is when the IR Box is already open, the request has been selected, I cannot save it as Access says a record is required in the Clients form. There are only two buttons, one goes on to next requests (one client can have more than one requests), other saves and closes.

The Client form is already coded so it will save the Client's record before opening the IR Form, and either button will save the IR record as well.

I had referential intergrity turned on. Turning it off only gave me weird results (I was able to input records, but Access didn't autofill the ClientID in the IR records, and looking at Clients returns a blank IR record)

What am I doing wrong?


Thanks

 

[Banana]

Can anyone help, please? Maybe I'm not giving enough info?

 

[Sammy B]

Might be best if you upload the db (or a version with only a little data). Others may be then able to see what you are on about.

Sam

 

[RuralGuy]

I would convert the LookUp field (Requests) in the ClientIR to just a TextBox. All they do is confuse the programmer! Now that field will show you what is really in it. It is difficult to take a guess on your problem. Can you remove any sensitive data and zip it up and post it so we can have a look/see?

 

[Banana]

Here it is. It's all stripped down except for the problematic objects in question.

 

[Banana]

RuralGuy, I converted the lookup into a textbox and now have a blank value.

I also want to point out that the arrangement should allow for multiple requests, even if they are same category by one clients. The present set-up should allow that, I think.

 

[Sammy B]

Ok, I did remove the relationship that was causing the problem with ClientIR and it will now save the 'Requests' but since you do not tell it to what 'client' it belongs to it defaults to clintID 0. Do not know what you had in mind exactly.

Sam

 

[Banana]

Sammy B,

Yes, I thought that designating the clientID as a FK to the primary table, it'd auto-fill with the corresponding ID. Apparently not.

I even fed a code telling to designate the current ID for the IR requests. Still didn't work.

Lastly, I would prefer to enforce integrity as the IR is dependent on client's record.

 

[Sammy B]

You need to use subforms to tie everything together. Read about them in help. This is the way to go. I have used them many times in the past and they are slick.

Sam

 

[Banana]

Well, I avoided them in first place because I didn't want a subform on top of a master form. I only want a pop up form so whenever the IR is checked, the subform will then pop up as opposed to sitting on Master form all.

Or maybe I didn't know something?

 

[Sammy B]

Ok, I created a form and subform to show how I would do this. The Form is called 'Test Client' and it uses the subform 'Client subform'. I also added a name to the client list just to show something other than ClientID. I also changed a few tables to use 'ClientID consistently as you hade one table with Client_# instead.

Tey this and see how it works.

Sam

 

[Banana]

Sammy,

That's what I had set up but is not what I want.

I don't want a subform lying on a form. I only want it to pop up when the IR button is checked and go away when I'm done with this particular record.

Does that makes sense?

 

[Sammy B]

Ok, why does it have to 'go away'?..

 

[Banana]

2 main reasons, both to do with UI:

1) It looks nice and it is not always used all time

2) There's really no room on the form. The man form is set up for rapid input, and cluttering it up would hurt it...

 

[Sammy B]

In any case I changed the sample I sent to you and it now hides the 'Client Requests" on a button toggle.

Sam