Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Update a Field Based on Another
Rajashree Natarajan 
      
4 years ago
I have a medical billing form where I enter the procedure codes and the price its billed to the insurance. For most of the procedures I have a default of $100 . I have three procedure codes that cost $500, $700 and $1000. My staff forgets to update the price when entering the code and it charges the default $100. Is there a way to automatically enter the price when those codes are entered? It will save a lot of loss of finances. Please help.
Adam Schwanz  @Reply  
           
4 years ago
Use a AfterUpdate when the procedure is entered to set the price.
Adam Schwanz  @Reply  
           
4 years ago
Depending on your setup you may need to DLookup as well
Scott Axton  @Reply  
        
4 years ago
This sounds to me to be a perfect example of needing to set up a relational table to get your values from.
I would create a ProcedureT and put in the ProcedureCode as well as the ProcedureCharge and use a ComboBox to populate the line items in you billing.  See Relational Combo.

Go watch the Invoicing video as well. Especially the extended cut.  Think of your procedures as your product list.
Rajashree Natarajan OP  @Reply  
      
4 years ago
Can you give me the code for after update? I don't know VBA. I need the price to update only when three procedures are entered. Otherwise all others are $100. I didn't think I wanted a table for just this. I made the default to $100 so it saves time entering it. It's only when the three codes are entered, my biller in her hurry forgets to update the price. It would be nice to have it done automatically.
Scott Axton  @Reply  
        
4 years ago
Go watch the videos I linked above.  They show you what to do.
Scott Axton  @Reply  
        
4 years ago
Actually, you pretty much DO need another table.  What happens when your 'default' pricing changes to $125? Or when the amount you bill becomes $650, $900, $1200?
You don't want to hard code it into your code/forms/reports and have to go in and edit those every time you need to update your pricing.
It is much easier in the long run to set up a table that you lookup from than to make code changes.

With regard to not knowing VBA.  What are you doing to change that?  I don't see that you are continuing on in your learning journey.  You basically stopped at Beginner lessons.  It is far easier for you to just say, "Can you give me the code?"

Yeah we could, but what happens when we aren't here to answer your questions?  
What did you learn to help you with the next issue?
Rajashree Natarajan OP  @Reply  
      
4 years ago
I am on my expert lessons now. I just finished the first and Richard sent me the second. I am definitely learning. This is something I do on the side as my interest. Being a medical practitioner takes up my life along with home, kids and parents. So my learning is slow but sure. I agree with you in regards to the tAble and will make it. But how would I default the codes to certain prices even if I have a table. Relational combo will require one more step for my staff to choose from drop down. That's what I am trying to avoid since they may choose something diff in error and we will submit that to the insurances.I want to automatically update pricing when those codes are entered. Also, please avoid judging me.
Scott Axton  @Reply  
        
4 years ago
Once you have your ProcedureT (or what ever you want to call it) you would make a simple little data entry form.  You wouldn't even have to make it fancy - just functional.
I could see 5 or so fields.  
ProcedureID - AutoNumber
ProcedureCode - Short Text
UnitPrice - Currency  - Default = $100
Description - Short Text
Notes - Long Text
IsActive - Yes/No
Any other data you might need to have.

I don't know how many procedures you have but I can't imagine once you get the procedures filled in the first time there would be a ton to enter day to day.  Have your data entry people help you.  Give them some responsibility and ownership.

Now, change your invoicing entry to have a ComboBox with your ProcedureCodes. The Clerk would only have to input in a ProcedureCode and tour Add button would populate the rest of your billing fields. This will create increased accuracy and less overall typing.
Scott Axton  @Reply  
        
4 years ago
The other advantage in doing it this way is if your prices do go up. You make a change to the ProcedureT and the new pricing will automatically be right.  The clerks don't have to remember the correct charges.

Again, if time is short, just watch the extended cut of the invoicing.  That will show you how to make all the above work.

I apologize if I was short to the point of being rude.  I love helping people understand and learn.  I don't like feeling taken advantage of.  I may have miss read your intentions an if that is the case I'm sorry!
Rajashree Natarajan OP  @Reply  
      
4 years ago
No worries Scott. I am a newbie. There is no data entry person. I am a very small busy practice with two employees. I am learning and doing the database myself. Thanks to all of you, I have come a very long way. I did make the table go all the procedure codes with their charges and defaulted to $100. I am just wondering how will entering a procedure code from the combo list update the price automatically. I am missing something that I believe is beyond me currently. What do I do to update the charge automatically. Do I do a Dlookup after Update?
Scott Axton  @Reply  
        
4 years ago
Rajashree the Billing form you have is where you will place the Combo Box to lookup the information from the Procedure Table.  

Richard shows how to do all this, in detail, in the Invoicing video.  I'm not trying to side step your question or not answer it.  The truth is there just isn't enough room in these little forum posts to type it all out.

Additionally, since you are also a Gold member, you can download and play with the member version of the database Richard builds.  There is no better way to learn than to pull apart and explore a database that gives you a direct example.

I know your time is valuable and in short supply.  Just take it at your own pace. Schedule in the time just like you would a patient.
Actually put it on your schedule.  "Tomorrow at 4:00PM to 5:00PM - Business development" or what ever. You don't have to do it all in one sitting, but do keep setting aside time for YOU.
Rajashree Natarajan OP  @Reply  
      
4 years ago
Thank you Scott..will do.
Richard Rost  @Reply  
          
4 years ago
I'm a firm believer of not re-inventing the wheel. If a video explains how to do something, then by all means, watch the video. You're here to learn. It might not be EXACTLY what you need, but it'll get you there. Like Adam and Scott said, you need AfterUpdate and DLookup. That's it. Your person types in the code, which I'm guessing is a number. In the AfterUpdate event for that field, you can DLookup the price. If it's not found, use 100. This is a learning site. We're here to help you LEARN. Like Scott said... next time you have a similar problem, you'll know how to fix it yourself.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/17/2026 8:43:27 AM. PLT: 1s