Combobox adding record to wrong table

[lydonw]

I'm trying to set up a combobox on the subform that will let a user choose one of multiple values (value from Auth Numbers Query) and when the record is entered it fills a field in tblAuthorization via Authorizations Query.

I can get the combobox to display the correct information, but I have issues on the record entry.


Combobox info:
Control Source quAuthorizations.AuthNumber
Row Source SELECT quAuthNumbers.AuthNumber FROM quAuthNumbers WHERE (((quAuthNumbers.ConsumerID)=[Forms]![frmConsumers]![ConsumerID]));
Row Source Type Table/Query
Bound Column 1

One of 2 things happen, depending on set up.

1) if quAuthorizations.AuthNumber is from tblAuthNumbers.AuthNumber, I get an error saying "The changes you requested to the table were not successful because they would create duplicate values...*

*If I allow duplicates on tblAuthNumbers.AuthNumber, it will add new record with a duplicate AuthNumber, and on tblAuthorizations it will add a new record with a new AuthInstanceID (PK), but it will not input the related AuthNumberID (fk) or AuthNumber (it leaves them blank).

2) if quAuthorizations.AuthNumber is from tblAuthorizations.AuthNumber, I get an error saying "You must enter a value in the tblAuthNumbers.AuthNumber field."

Ideal behavior should be add record to tblAuthorizations.AuthNumberID (fk) from tblAuthNumbers.AuthNumberID (pk) by selecting tblAuthNumbers.AuthNumber in a combobox on the a subform whose control source is quAuthorizations.

I hope like hell that makes sense and someone can help.

 

[MarkK]

Ahh, no, I didn't quite follow that, but here's the thing: get it working so you can do it manually. Like, you've proposed a couple of different tables structures here right? One won't allow duplicates, the other ends up blank, so set it up to the blank one, then go add the record manually. What fields do you have to add for it to work? Now, design your process so it adds those fields.

Also, how do you do the insert? I don't see specific details in your post. Is it a bound form? Then include the fields in that form that you needed to enter into the table when you did it manually.

Does that makes sense, and sort of answer your question, or at least give you something to try?
Cheers,

 

[lydonw]

I am using a Main form bound to quConsumersByActive-Inactive (which is is just tblConsumers filtered by unbound buttons on the form), and a subform bound to quAuthorizations.

I am trying to enter authorization data for the consumer on the main form, but a sticking point seems to be that quAuthorizations gets data from two tables: tblAuthNumber and tblAuthorizations. By including the tblAuthNumber.ConsumerID field in quAuthorizations query, when I enter data through the form the query attempts to add a record for tbl.AuthNumber.ConsumerID. I only want this field to link the main and subform, and don't want it to accept new records.

Part of the problem has to be the many to one to many relationship I have between tblConsumers, tblAuthNumbers, and tblAuthorizations (respectively). The data in the subform comes tblAuthorizations, but I'm not sure how to link it to tblConsumers...

Sorry if I'm poor at explaining this. I've been figuring this out from tutorials and fiddling and have not had the opportunity to figure out to effectively talk about Access.

 

[lydonw]

Actually... It looks like I've been thinking about this all wrong, my issue is one of many to many to many, I think.

Each consumer can have many authorization numbers, and each authorization number can have many authorization instances (we reauthorize the same number multiple times for new periods).

My current table structure is

tblConsumers
pkConsumerID
Consumer Number
Consumer First
...etc

tblAuthNumbers
pkAuthNumberID
AuthNumber
fkConsumerID

tblAuthorizations
pkAuthorizationInstanceID
fkAuthNumberID
AuthStart
AuthEnd
etc.

I had thought these would accomplish a many to one (tblConsumer to tblAuthNumbers) to many (tblAuthorizations), but since tblAuthNumbers can itself have multiple records for each ConsumerID, I've been going about this wrong from the get-go.

I think that's correct, and if it is I have no idea how to fix the structure, which means my form will remain shot

 

[lydonw]

Regarding putting data in manually, that's easy. Because linked tables behave like pivot tables, expanding a record in tblConsumers lets me see tblAuthNumbers, and expanding tblAuthNumbers from there lets me see (and add records to) tblAuthorizations, all linked back to the record on ConsumerID.

This is a fine short term solution for me, but I need a form that allows this with some hand-holding, or the database will be broken by those I need to leave it to.

 

[MarkK]

One thought I have is don't--except in rare circumstances--bind a form to a query that draws from multiple tables.

But before any user interface stuff, the fundamental thing that has to be working is your table design, so if I was to get more involved I'd want to understand that, and only that.

Consumer: makes sense to me. I don't get how an AuthorizationNumber object differs from an Authorization. I would expect that an AuthorizationNumber would be a field in the Authorization table. Here's the table structure I would expect based on the names of the things you have there...

tConsumer
ConsumerID (PK)
ConsumerNumber
FirstName
...

tConsumerAuthorization
ConsumerAuthorizationID (PK)
ConsumerID (FK)
AuthorizationID (FK)
AuthStart
AuthEnd
...

tAuthorization
AuthorizationID (PK)
AuthroizationNumber

See, it's really your junction table that is the most active. Your consumer list is pretty stable and your authorizations, but what you really want to track is a consumer's relationship to an authorization. That's the part of your data that's really dynamic.

Or do I misunderstand your purpose?

 

[lydonw]

So attached is what my Consumers table looks like with AuthNumbers and Authorizations expanded. (ConsumerID is hidden in tblAuthNumber; AuthNumberID in tblAuthorizations)

And I really don't want to have a query that is drawing from multiple tables, because clearly it doesn't work (or at least not well, in this instance).

I need to be able to track a consumer's relationship to both their AuthNumbers, and each Authorization per AuthNumber.

I think your layout makes more sense when I think about it. So now when I use a query to throw data into tblAuthorizations, it will link tblConsumers.ConsumerID and tblAuthNumbers.AuthNumberID, correct?

So when I look at it in a view similar to the uploaded picture, it will be

ConsumerID Name Blah blah blah
ConsumerID Name Blah blah blah
AuthorizationID AuthNumberID Start End Blah Blah
AuthNumberID AuthNumber
ConsumerID Name Blah blah blah


I'd post another picture but I deleted some data in a rush and need to re-enter (something I'll farm out).

So that makes sense. It also seems like I should have an easier go of creating a subform with the query whose fields are all from tblAuthorizations.

Need to head out for the day but I'll check back in monday with results. Also, THANKS. I came into the project knowing some mail merge - boss seems to think "make a database" is the same as "make a spreadsheet."

 

[MarkK]

What are the real-world objects you are dealing with? Consumer is one, OK. But what is an AuthorizationNumber object? How does that merit a whole table? How is an AuthorizationNumber not a field in the Authorization table? I don't get that.

 

[lydonw]

Ok, so my company does mental health work with our consumers.

Before we start working with a consumer we need to get an authorization number for them. Following this, we use this auth # to request services for the consumer that covers a given period (usually 8 weeks, but it varies). At the conclusion of the period, we usually resubmit with the same auth #, but for a new period.

Occasionally we need to request a new auth # for a consumer, and we begin using that one in our requests.

Each consumer may have multiple auth #'s, and each # may be authorized for multiple periods (which I refer to as AuthInstanceID (PK) in tblAuthorizations).

I need Auth Numbers to have its own table, if for no other reason, than the fact that I need to be able to add and see a consumer's authorization numbers independent of their authorizations. For one, it may happen that we get an auth #, don't use it (or get a new one), and this would leave many nearly-blank fields in a unified table. Second, I need to be able to select Auth #s independent of whether or not they have been authorized yet - if I had a drop down of only existing #s, new auth #s would not be visible.

Hope you can follow all that.

 

[lydonw]

So I switched the tables around as you suggested lagbolt, but the problem I'm having now is that my Authorizations subform (in Consumers main form), doesn't have a way to select which authorization to use when adding a new record.

Attached is what the form looks like. Only date sorting is working, so that stuff can be disregarded. The first field of the subform needs to be the auth number, ideally with a drop down of that particular consumer's authorizations. Currently it is displaying the authnumberID of each auth instance. I tried to set that that field as a combobox sourced from the quAuthNumbers (or tblAuthNumbers), but without a ConsumerID field, access can't figure out the join.

 

[lydonw]

Ok, so the problem I am having with the structure you suggested is that unless a particular authorization number has been used, it is not related to a ConsumerID. This means that I cannot add new auth #s without their Auth data.

I'm trying to set this up so that new Auth #s have to be entered seperately, in their own form, while locking down the auth numbers field on the subform (so as to prevent people from typing them in wrong).

I don't see a way around this in the current table structure. Any ideas?

 

[lydonw]

I've given up on having a seperate form to enter in auth #s, and instead will just set up a msgbox to pop up when a user types in a unique auth # (If I can figure that out).

So I killed the Auth Numbers table, added an AuthNumber field on tblAuthorizations.

The problem is the auth numbers combobox in the authorizations subform is displaying all of the auth #s for the given consumerID, including duplicates. I've tried adding adding SELECT DISTINCT in the row source for the combobox, but it seems "DISTINCT" only refers to the unique id of the PK, so it does not filter out the duplicates.

 

[MarkK]

Feel free to post your database if you want. I'm not totally clear from what you've written exactly what you're working on at the moment.
Cheers,

 

[lydonw]

I'd love to post my database but the data is confidential, HIPPA stuff. Is there a way to randomize the data? If not, should I just save a duplicate and clear all of the data?

 

[bob fitz]

Feel free to post your database if you want. I'm not totally clear from what you've written exactly what you're working on at the moment.
Cheers,

Take out all real data.
Enter a few dummy records so we can see how it works.
If you are using A2007 or A2010, try to convert it to A2003 mdb version.
Compact it.
Create a zip file and attach it to a post.

 

[lydonw]

Ok, so here is my database with rubbish data. I'm satisfied enough with the authorizations subform on Consumers, but I've hit a version of the same snag in regard to the timesheet subform on Employees.

It's another combobox issue. If you look on the timesheet subform on frmEmployees, there is a combobox field "Service." The way this should work is that it should drop down the options for PSR or BST, which have a serviceID of 1 and 2 respectively.

This combobox needs to set up so that the drop down will display PSR and BST when the employee's role is QMHA, and only BST when the employee's role is QBA.

Any other tips, since you can look at the database, would be appreciated.

Edit* Attachment wasn't on there. Also, the original problem of a combobox populated by a different query is still present in frmConsumers Timesheet Employee field

 

[MarkK]

Addressing the 2nd issue first, a continuous form or datasheet only has one detail section, even though it looks like there are as many as there are records, so you can't do a decent job of cascading combos in that case because if you change properties of any one control, you change that property for them all. Consider in that case leaving the combo with all options and pop up a descriptive error in the BeforeUpdate handler or something to simply disallow illegal choices, or work around it some other way.
More later,

 

[MarkK]

And in respect to the second issue, I downloaded your DB and it looks fine to me. The reality is that many authorizations can have the same authorization number, even when the period covered is different, and where the period covered is in fact different, it makes sense that that is a distinct instance of the concept Authorization. That seems fine to me, and your authorizations subform should have multiple records for a single auth number because the period covered is more important than the number.
Does that make sense? It's like the Authorization number, as a piece of data, is not really that important. It exists for any single authorization but it doesn't serve to distinguish anything from anything else.

 

[LisaG]

LydonW - did you ever get your issue resolved? I have the exact same issue. Adding records to the child table adds a blank record to the parent table with no ID. If you have the fix to this, I would GREATLY appreciate it!

 

[lydonw]

I did resolve the issue. I rebuilt. As much as I dreaded doing it, it didn't take as long as I anticipated and my forms were made much better overall. I found that all the things I learned while making this (my first big project) helped me to make a more efficient, better designed project.

My current setup:

Consumers form based on a quConsumers
button with an onclick event:

DoCmd.OpenForm "frmAuthNumber-Entry", , , "ConsumerID = " & Me.txConsumerID

frmAuthNumber-Entry has an On Open event to account for the problem I think you are describing. Essentially, when I tried to add an Authorization number record to a consumer whose ConsumerID was not yet in the AuthNumbers table, the form would add the record but leave ConsumerID blank. To fix this, I made an On Open event that checked if the consumer already had at least one authorization. If they didn't, it opened a form specifically made for entering the First auth number. If they did, it opened a form for entering all following auth numbers.

My On Open event:

If DCount("ConsumerID", "tblAuthorizationNumbers", "ConsumerID = " & [ConsumertoUpdate]) = 0 Then
DoCmd.OpenForm "frmAuthNumber-First"
DoCmd.Close acForm, "frmAuthNumber-Entry"
End If

*ConsumertoUpdate is a nonvisible text field set to control source ConsumerID (which was set in the On Click event earlier)

I hope this helps. I can describe the actual layout of my Entry and First forms if need be. Good luck!