Combobox fills but does not save (1 Viewer)

[foxxrunning]

I have just started using Access and have come upon this problem. I have a combobox on a form which displays information from a Patients file indexed on a PatientID. The form the combobox is on (Transactionfrm) fills out 4 fields from the Patients table when this code is placed in the on change Event.

Private Sub cboPatID_Change()
Me.txtFirst_Name.Value = Me.cboPatID.Column(1)
Me.txtLast_Name.Value = Me.cboPatID.Column(2)
Me.txtService_Code.Value = Me.cboPatID.Column(3)
Me.txtAmount.Value = Me.cboPatID.Column(4)
End Sub

What I would like to happen is when all the fields are filled in is to save all the fields on the form to the Transaction table. I see that there is no reference to the transaction table in this code but do not have any idea how to do this. Can anyone advise me how this might be done? Thanks for any help.
david

 

[theDBguy]

Hi David. Welcome to AWF!


You shouldn't need to save redundant data between multiple tables. All you really need to have is the ID field, because you'll be able to get all the other information based on it, whenever/wherever you need to.

 

[foxxrunning]

Dbguy- I actually am trying to save the "redundant" data as the form is to fill a Transaction table and the data is pulled from a Patient table. So there is a one to many link and there are many "transactions" for one patient. I see that the code I have has no reference to the Transaction table and I think I need to say something in this part of each line referencing the Transaction table (Me.txtFirst_Name.Value = ) Should I be using some other Access function to do this?

 

[theDBguy]

Dbguy- I actually am trying to save the "redundant" data as the form is to fill a Transaction table and the data is pulled from a Patient table. So there is a one to many link and there are many "transactions" for one patient. I see that the code I have has no reference to the Transaction table and I think I need to say something in this part of each line referencing the Transaction table (Me.txtFirst_Name.Value = ) Should I be using some other Access function to do this?

Hi. Going back to the point I made, for a minute. If you only store the ID value in the transaction table, are you not able to use it to find out the other patient data. For example, you can use a query or DLookup() using the ID as a link or criteria.

 

[foxxrunning]

That sounds correct. Do you mean use the Dlookup function in each of the control source fields in each individual field?

 

[theDBguy]

That sounds correct. Do you mean use the Dlookup function in each of the control source fields in each individual field?

Hi. Yes and no. It depends on where you need it. Typically, you would link the two tables together to have all the required information for whatever you need it for. For example, to display it in a report or to export to an Excel spreadsheet. Even for a form, but you can certainly use DLookup(), although it might be inefficient at certain situations.

 

[foxxrunning]

Maybe I am going about this incorrectly. Here is what I want to do. Use a combo box to get the ID for a patient, and then fill in the rest of the Transaction form and finally save the completed form back to the Transaction table. Is the method I am using not the correct way of going about this task? Thanks for your prompt replies.

 

[theDBguy]

Maybe I am going about this incorrectly. Here is what I want to do. Use a combo box to get the ID for a patient, and then fill in the rest of the Transaction form and finally save the completed form back to the Transaction table. Is the method I am using not the correct way of going about this task? Thanks for your prompt replies.

Hi. Without seeing your complete setup, my impression is yes, it is the wrong approach. You can certainly use a combobox to select a patient ID and the rest of the patient data populate the transaction table, but saving those data into the transaction table too should not be necessary. All you need to store in the transaction table is the patient's ID.

 

[foxxrunning]

The Transaction form has all of the fields that are in the Transaction Table. I get the ID from a Patients table. I also want the Last, First name and two other fields that are in the Patients table to populate the Transaction form. Then I want to save all of those fields data to the Transaction Table. I use the combobox to display the fields so that the person doing the data entry would know the ID is correct for the Patient as well as see the other data just as an aid in inputting the data. What would you suggest I use instead of this configuration? Thank you for your help.

 

[Micron]

I skipped over much of the posts because I see a big problem. You're using the Change event, which fires upon every keystroke when the control has the focus. Thus if you attempt to modify data anywhere based on what's in the control, you run the risk of raising errors about incomplete data at best, or screwing up that data at worst. You probably should be using the AfterUpdate event. I guess I should go back and review the rest because now I'm wondering why any code is needed if the form is bound.

EDIT - OK, done. I'm wondering why you don't use a main form (holds patient data) and a subform (shows transaction data for a given patient ID) and link (Master/Child property) via patient ID. One form for patient, one for transactions.

 

[foxxrunning]

I don't believe I need a sub form as what I am really interested in is the data on the Transaction form. That form supplies the data that is stored in the Transaction Table. I will think about this for awhile as it does seem that if I were to display both the Patient information along with the Transaction information and then save the Transaction info to the Transaction table that it would be OK. Thanks.

 

[Micron]

You have described a classic one to many situation: one primary (patient) and all the associated details versus many transactions/visits/orders/etc. related to the one. If that isn't a recipe for a form/subform, I don't know what is. In fact, the subform is where new 'many' records are added. The link is directly made to the 'one' by virtue of design - no code required.
I never was a good sales person, so I'm just going to leave it at that.

 

[foxxrunning]

Sounds like you just made a sale. Thanks. I will try it and see what happens. As a newbie to Access this all is incredibly fun and interesting. Again, thanks for your quick reply and help.

 

[theDBguy]

Hi. We're happy to assist. Let us know if you get stuck. Good luck with your project.

 

[The_Doc_Man]

I walk away for three hours and find this exchange.

David, something you said makes me want to step in and comment.

First, let me say that since you are new to all of this, I know you are doing your best based on what you know, and that is all any of us does at any level. So NO CRITICISM INTENDED, OK?

Second, I would respectfully suggest that you read up on the topic of "normalization" as it applies to databases. The way you said some things makes me think you might be storing excessive data. If you have a transaction regarding a patient, the only thing you need to store in that transaction is the patient's ID code, whatever it is. That is because you can use queries later to dynamically look up patient info using what we call a JOIN query. The ONLY reason I'm telling you this is to keep you from re-inventing a wheel that was long ago invented as a way to save the amount of data you must keep in multiple tables.

When you do your reading on "normalization" I have two specific bits of advices.

1. This forum's SEARCH feature is in the thin menu bar near the top of each page, and SEARCH is 3rd from the right. IF you use this search, you can look for "normalization."

2. The web is chock-full of normalization articles, but on the general web you have to qualify the search to "database normalization" because by itself, normalization applies to at least four or five OTHER topics as well. IF you use a general web search, start only with articles originating from the .EDU domain with the names of reputable colleges and universities. The .COM site articles are MORE likely to be advertising-heavy and would thus detract from the learning value of the site.

Once you learn about normalization a bit more, you would probably realize that you only have to store a small amount of data about your patients in any other table that refers to them.

 

[foxxrunning]

No offense taken. I think I understand the normalization idea. I get that it is not necessary to store the same data multiple times. I am trying to provide a friend a way to store many transactions as they occur to different patients over the year. So one week a patient might come in and have a particular treatment which is recorded and later billed. What I need part of the patient data (specifically the firstname, lastname, and fee) to be drawn from the patient table but stored in the Transaction table for each specific transaction. (Session) So now, if you still think I need to only store the PatientID and nothing more, I will concede that I have been going about this incorrectly.
I also am a certified grandpa of 77 with 4 grandchildren, 3 of whom are over here tonight and are trying to get me off the computer. So I thank you for your help and await any further ideas you might have. Thank you for your earnest reply.

 

[Micron]

What I need part of the patient data (specifically the firstname, lastname, and fee) to be drawn from the patient table but stored in the Transaction table for each specific transaction. (Session) So now, if you still think I need to only store the PatientID and nothing more, I will concede that I have been going about this incorrectly.

My suggestion was made in spite of not picking up on anything you might have posted about your data, so that was a good observation by Doc. The bolded statement above is great evidence of that being the case. Yes, definitely do study normalization with respect to databases because you're not grasping that the only thing the transactions should have in common with the patient table is the patient ID. To put the query into English, it would sound like "show me all the patient info in tblPatients and the tblTransaction details for those patients where the patient ID in tblPatients is equal to the patient ID in tblTransactions (I left out some of the more complicated stuff). If you wanted only one patient, you'd restrict the list to ID 123 (or whatever is appropriate). Anyway, the subject is too broad to cover in a thread. Here are links that I figure are good ones, but find ones that speak to you with clarity. Invest whatever time is required to understand it, because it's the foundation, but it can take work.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

P.S. Grandkids are great, no? I got you beat by the numbers!

 

[foxxrunning]

Reading this on saturday. I will invest the time and follow up on your urls. Thank you for your concern and help.

 

[Micron]

You're welcome. Hope it helps.