Unique Form RecordSource (1 Viewer)

[Matty]

Hey All,

I have two tables -- tblClient and tblAuthorization. I've made a form with tblClient as the recordsource, with a subform using tblAuthorization. Since only clients with a certain code (N-02) can have authorizations, I've added that as WHERE criteria on my main form (WHERE Code = "N-02"). Now comes my problem:

If a client's authorization expires, their N-02 code is supposed to be removed. But I still want to show this client on my main form, so I can see those expired authorizations. I thought I could change my WHERE clause to find N-02 clients that also have a record in tblAuthorization, but I get duplicate records (since clients can have multiple authorizations).

I thought I could just use the SELECT DISTINCT syntax in my recordsource, but it causes it to become uneditable.

Is there any way for me to have my main recordsource check the child table for records without giving me duplicate records?

 

[Fizzio]

There seems to be a bit of a conflict between the client and their authorisations.

You state that a client with a code of N-02 can have authorisations but if the authorisation expires, you remove the N-02 code.

Now you have to decide how you want to
a) control the display of the clients
b) decide on how you want to enable / disable authorisations.

If you want to keep your N-02 code to enable authorisations, but be able to see all clients regardless of the N-02 code, there are a couple of options I can suggest for this.

1. Create 2 different recordsources for the form and use a button to dynamically change them
2. Use a button to switch on/off a form filter to show all / N-02 records.

If neither of those tickles your fancy, get back as there are a couple of other options you could explore on the tblAuthorisations side.

 

[Matty]

OK, let's hypothetically say I want to get rid of the N-02 criteria completely. How could I design my main form recordsource to not show duplicate records, but still allow me to edit the main client record?

If I do just a standard join between the two tables, I'll get duplicate clients. If I only choose unique records, I'll get an recordsource that's not updatable.

I'm just weighing my options right now, but I may end up doing that toggle thing you suggested. But ideally I'd like to keep them all showing at once.

Any ideas you have would be greatly appreciated. I think I just need some fresh ideas, since I've been staring at this problem for a while now.

 

[Fizzio]

If you are having to make complex queries to show what appears to be simple data design, it sounds like your structure is not optimal.

A quick tip - always use queries to power your forms, not the tables themselves as these are more versatile to manipulation.

You can easily show all clients (and their authorisations if you want to / not) but just to clarify your design, it should look something like

tblClients
---------
ClientID (PK)
ClientName
etc...

tblAuthorisations
----------------
AuthorisationID (PK)
ClientID (FK)
etc....

The join will obviously be on ClientID.

If you are still struggling and need a demo either let me know or submit your Db for modification.

 

[Matty]

That's exactly what my table structure looks like. The query behind my recordsource currently just selects from tblClient where the code is N-02. The authorizations are on the subform, joined by ClientID (the PK in tblClient and the FK in tblAuth).

I'm going to have to think about this a bit more. The quick and dirty solution would be to have a table that'd be constantly updated with clients who have authorizations. I would then add that table to the main form's recordsource, joining it to tblClient. But I REALLY don't want to do that, because that's just more work/upkeep for the database to do.

I'm sure I'll be adding to this thread with more questions soon, but thanks for the help you've given so far.

 

[Matty]

Okay, I'm back with a new idea.

When a client gets an N-02 code, it's recorded in a table called tblService (this table includes installations for several other codes). I've made a query to find all clients who have ever been installed as an N-02 (they have a record in tblService that says N-02).

In my main form's recordsource, I've joined this query up to the tblClient table, to give me all the records in tblClient that have a matching record in this new query. It's telling me the recordset is not updatable. I'm quite sure it's because I have a query in the recordsource, but is there a way for me to make that recordset updatable?

 

[Fizzio]

If you are only using the join to filter by N-02 codes, make sure that the recordset only includes the fields that you want to update. You do not need to use a query in the main query as I'm sure you will be able to employ the same filtering by simply using the tables in the QBE grid. Can you post your Db (Cut down if you want) as I'd like to have a look at it first hand.