Null Values on left side of = sign

[MajP]

I would not use an expression or a function to do this. Should be done in a query

I had been using the combobox to display 3 fields, only one of which is stored in the table after choosing it via the down arrow on the CBO. The other two fields I had wanted to store in their respective fields on the form via the code Me.txtLastName.Value = Me.cboPatient_Number.Column(1) and Me.txtFirstName.Value = Me.cboPatient_Number.Column(2). Is there a know way to do this or is there another way which might actually work.

If you are storing a value on the form from the combo and that value is related to other fields in another table, then simply join the data table to the rowsource of the combobox. And as already been said, do not store these values.

I would only bother with a function if the resulting query would make the form query not updateable.

 

[foxxrunning]

I know it is against the laws of dbs to store data in two different tables. I have been told this for a number of years. But the person who asked me to do this for him is a doctor, not a db pro. He wants a transaction table where he can look up the various people who have come to his office and check on some items. He needs their names in this table, not just their IDs. I know he can do a query with the ID and list all of their transactions. But, we have encountered problems with that method as when any item in the query table was changed (as he sometimes does) it changed ALL of the transactions for that ID in the table. So we went back to just using a Table for the transactions, and not a query Table. Hope this has explained why I need to have the names in the Transaction table as well as the Names in the Patients table. Which functions would you suggest?

 

[MajP]

I know he can do a query with the ID and list all of their transactions. But, we have encountered problems with that method as when any item in the query table was changed (as he sometimes does) it changed ALL of the transactions for that ID in the table. So we went back to just using a Table for the transactions, and not a query Table.

That is not a very good logic. The fact that you improperly built the query wrong in the past, should not lead to improperly designing your tables in the future. That is like saying "I burnt my hand on the stove, so now everything I cook will be done in the microwave." This makes doing things like a turkey kind of problematic and not ideal. Or you could just not touch the hot stove..

 

[foxxrunning]

I promise I will keep my hands off the stove. Building the query was easy. I think I did not "build it wrong" Thanks for your comments.

 

[CJ_London]

So you are saying you are storing fred smith and your client might want to change Fred to Freddie for one occasion only?

 

[foxxrunning]

Actually, what is needed is that there are Patients who are described for Insurance purposes as a certain code (service code). But these service codes also includes various other things like "no payments", "lost checks" etc etc. So most of the time the transaction is just the service code which is brought in from the Patients table, but sometimes this code must be something else. So when I produced a query table if the code was changed, it was changed throughout the table which is not what we of course wanted. So we switched back to just a table representing the Transactions. I am suspicious that the table is corrupted more than I realized, so I am in the process of recreating the Transactions Table from scratch. Anyway, the reason he wants both First Name and Last Name in this table is so that his secretary can identify each patient and not have to have a paper listing their Patient Numbers and Last and First names. I know this is not allowed in proper db circles, but it is what he wants.

 

[CJ_London]

I’m struggling to understand your table structure and how that relates to a form- both solutions suggested should work. The vba code version should work providing the underlying query is updateable. And if it isn’t then use the other suggestion

 

[CJ_London]

And with respect to your client, you wouldn’t go to him and say I need this operation but you cannot use a scalpel. If you have partial codes, these should be in separate fields

 

[foxxrunning]

Sorry if I have confused the issue. My client is a friend of a friend of mine. I do not do this professionally as you must have figured out by now. The tables are Patients, Transactions, Services. Patients is related one to many with the Transactions table via the Patient Number field.. Transactions are related one to one with the services table via the Service ID. I rebuilt the Transactions table and now at least the original code I had used is working. Thanks for your help with this.

 

[CJ_London]

I’m actually sitting on a beach right now watching the sunset!

from your description the relationship between transactions and services should also be one to many -I.e. you have a many to many relationship between patients and service, linked by transactions.

this would be represented with a form/sub form structure with either patient or service tables being the record source to the main form and the transactions table being the recordsource to the sub form

 

[foxxrunning]

You are correct as well as lucky as hell. Enjoy the sunset. It took me awhile to recreate everything ( as well as the one to many relationship between Patients and Transactions) but now everything is working properly. Thank you all for your help.
CJ which beach are you on?

 

[CJ_London]

Dover beach, Barbados🏝

 

[foxxrunning]

Enjoy!