Change staff assignment on the fly

[Guest]

This is going to be long...Please bear with me. I want to be as clear as
possible.

I have a table that includes client information, including which staff
member they are assigned to. One *generic* client is not assigned to a staff
member and is used only for administrative purposes, however all staff
members need to be able to add records for this *generic* client under their
staff ID. I have a form to add records for clients with 2 combo boxes, one
with staff member and another that is limited by the first combo. Only the
clients whose staff member who was chosen in the 1st combo show up in the 2nd
combo. Here's where the problem is: I have the underlying query set up so
that regardless of which staff member was chosen, the generic client will
show up in their client list. However, I cannot add a record for that
generic client. I get the error message "Microsoft Jet database could not
find a record in TblStaff with a matching key". I'm assuming that this is
because the client is not assigned to anyone. Is there a way to temporarily
assign this client to a particular staff member when the staff member is
initially chosen in the first combo box?

Thanks for any help!

 

[Guest]

It would be helpful to know what tables you have and what relationships you
have defined between them. The whole idea of a 'generic client' smacks of
poor design.

Dorian

 

[Guest]

Well you write " I have the underlying query set up so

that regardless of which staff member was chosen, the generic client will
show up in their client list. "

so first thought - if you are in a query result - then you can't add new
records here - you can change the values of fields in the query's results
matrix only...but one doesn't add new records via the query results...


When a staffer selects "their" clients via your 2 comboboxes...and they
select a client - - does each client have a unique table? Not quite clear
how Staffer is adding new records for non-generic clients, but whatever
method is being used it should also work for generic clients...

sorry can't be more helpful at this point.....

 

[Guest]

It's unavoidable. It's a billing issue, meaning that although all of the
services we provide are billiable, not all of them are billiable under
clients. However, the company we use to send our billiables to the state
requires all items to be in the Contacts table. As far as tables go, there
are several involved: tblContacts, tblCurrentStaff, tblClientContactInfo and
tblClientCaseInfo. tblClientContactInfo has a one-to-many relationship with
tblClientCaseInfo through use of a ClientID number. tblClientCaseInfo has a
one-to-many relationship with tblContacts through use of a ClientCaseID
number. tblCurrentStaff has a one-to-many relationship with
tblClientCaseInfo through use of a StaffID number.

 

[Guest]

The underlying query is only driving the combo boxes. The only data being
saved anywhere as a result of this query are the clientID and the staffID
which are being saved in tblContacts. No, each client doesn't have their own
table. All client data is held in two tables: tblClientContactInfo and
tblClientCaseInfo. Records for all contacts with any given client, and any
given case for those clients are held in tblContacts.

 

[Guest]

I think I kind of understand. Your explanation is good but there are some
limits to this syle format of forum.

I can definitely understand the need for Generic - in terms of not have a
single assigned Staff member. This is not that unusual I don't think.

I guess I would really have to have your actual DB to resolve - - I can not
really take the time to fully solve this for you though. But your error
message is giving you a good clue. It is stating ""Microsoft Jet database
could not
My guess is that your design pulls up the client record requiring a unique
TblStaff field value....which will work for those with a uniquely assigned
staff member - and therefore won't work for the generics. I think you need
to redesign this method. Whatever that maybe am not sure. Maybe as simple
as adding an OR i.e. StaffNumber OR Generic Not really sure. But
hopefully this will point you in the right direction.

 

[Guest]

Thanks...I'll keep looking into other options.

I think I kind of understand. Your explanation is good but there are some
limits to this syle format of forum.

I can definitely understand the need for Generic - in terms of not have a
single assigned Staff member. This is not that unusual I don't think.

I guess I would really have to have your actual DB to resolve - - I can not
really take the time to fully solve this for you though. But your error
message is giving you a good clue. It is stating ""Microsoft Jet database
could not

My guess is that your design pulls up the client record requiring a unique
TblStaff field value....which will work for those with a uniquely assigned
staff member - and therefore won't work for the generics. I think you need
to redesign this method. Whatever that maybe am not sure. Maybe as simple
as adding an OR i.e. StaffNumber OR Generic Not really sure. But
hopefully this will point you in the right direction.