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 
vba
Andrea Buckridge 
     
4 years ago
Is there a way to use vba to copy one field in a record to another field without having to tab to that field?  I don't know if I'm describing this right. I want my Address field to copy itself into another field so that the other field can run other vba without messing up the address itself.

I.E.
    123 Davis St. (in the address field)

would show up as

123Da

in the hidden field after all the vba has run.
Kevin Robertson  @Reply  
          
4 years ago
The following code will loop through all the characters in the address field and add them one by one to your hidden field (excluding any spaces). As per your example above the loop will exit as soon as the length of the string is 5 characters.
Put the code in the After Update event of your address field.

Dim S As String, X As Long
    
For X = 1 To Len(Address)
    S = Mid(Address, X, 1)
    If S <> " " Then txtHidden = txtHidden & S
    If Len(txtHidden) = 5 Then Exit For
Next
Andrea Buckridge OP  @Reply  
     
4 years ago
Thank you!
Andrea Buckridge OP  @Reply  
     
4 years ago
Kevin, does there happen to be a way to include a filter characters function in this code?
Kevin Robertson  @Reply  
          
4 years ago
Yes. Just insert the line of code between the 2 If statements.
For example, using Richard's function from the Tech Help video:
txtHidden = FilterCharacters(txtHidden, ALPHANUM)

Filter Characters
Andrea Buckridge OP  @Reply  
     
4 years ago
Thank you so much
Kevin Robertson  @Reply  
          
4 years ago
You're welcome.
Andrea Buckridge OP  @Reply  
     
4 years ago
Okay, another question.  I have now gotten all the data to update properly when entering a new company to create a unique code for each company.  I have so far avoided and false negatives or positives by taking the first and last 3 from the name, the address and all of the zip. Thus far this has worked well.  I got all the different sets put together in a query, but I can't figure out how to make the query figure out the code and update each new company record with it.  

Thank you again.
Adam Schwanz  @Reply  
           
4 years ago
You could just use a recordset on a button.

Or you could use the query and break down the pieces.

Make new expression to remove spaces
RemoveSpaces: Replace(Address," ","")

New expression for first 5
First5: Left(RemoveSpaces,5)

Then Concatenate that and all your other fields (first3, zip, etc) into one field.
Adam Schwanz  @Reply  
           
4 years ago
Once you get it figured out in the select query, you can use an Update Queries to change the values in the tables to your new code
Andrea Buckridge OP  @Reply  
     
4 years ago
I have all that figures out.  I have two hidden fields in the CompanyNameT that take care of reducing the CompanyName to the first part of the code, and two hidden fields in the CompanyT that do the same with the address in the CompanyT.  Then I used a query to pull the hidden fields' information together into one concatenated code.  Now I have to figure out how to attach the proper code to the proper company.  either by adding it to the CompanyT or a making another table for the codes and relating them.  I think that last would work best, but I don't know how to use an update query...or if I need an append.  I used an append to add all the existing codes to a seperate table that I can now relate to the Company T, but I don't know how to make all newly added customers show up on the CompanyCodeT.
Andrea Buckridge OP  @Reply  
     
4 years ago
I tried an append query earlier, and it kept reproducing all of the ones I had already appended over and over.
Adam Schwanz  @Reply  
           
4 years ago
I would just make a field in your CompanyT called uniquecode or whatever, then just append all of those codes over or use an update query to re-make them in it. Then you can just put an afterupdate event on your FirstName/ZIP/Address along the lines of this

If IsNull(ZIP) or IsNull(Address) or IsNull(FirstName) Then
'Fields aren't filled out we can't concatenate it
Exit Sub
Else
'Fields are all there, concatenate it and put in the record
uniquecode=    'code that does the concatenating
end if
Adam Schwanz  @Reply  
           
4 years ago
So all the old ones would have their code, and any new customers that come in will automatically get their code created. Likewise if you change a name/address/zip it will automatically update it as well.
Andrea Buckridge OP  @Reply  
     
4 years ago
Thank you all so much!

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:58:55 AM. PLT: 0s