Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Sequential Numbers < Deleted AutoNumber | Customer Codes >
Back to Sequential Numbers    Comments List
Upload Images   Link   Email  
Question for VBA Beginner
Faith Harvey 
       
2 years ago
Can you please show us (me) where the spaces are in the Sequential Numbers coding? Or at least the final code (Extended Cut)? I can't get it to work for me, and I think it's because I either have a space where there shouldn't be one, or I have missed out a space where one is required. I've gone through the Extended Cut video 5x now, and it's still not working.
I've even gone through your Introduction to VBA video, hoping that would help, but I still can't find my error.
Kevin Robertson 
          
2 years ago
Are you getting an error message? What is it?
We can't identify any mistake without first seeing what you have done.
Scott Axton 
          
2 years ago
It's still not working doesn't tell us anything.  Are you receiving an error message?
Post some screen shots of what you are doing / experiencing.  (Sorry my ESP license is expired - LOL)

Side note - this is a great reason to upgrade to a Gold membership.  When you hit a wall, you can download the db that Richard created and compare to yours.
Faith Harvey OP 
       
2 years ago

Faith Harvey OP 
       
2 years ago
No error code, just no number.  Well, to be exact, it returns a "0" each time.  

I am using this for our document register.  We have multiple projects going at any given time, and we are slowly rolling this register out across all projects.  I need the document number counter to be specific to each project.  

The code I used is:
Private Sub ProjectNoCombo_AfterUpdate()

    DocID = Nz(DMax("DocID", "DocumentRegisterT"), 0) + 1
    DocSeqID = Nz(DMax("DocSeqID", "DocumentRegisterT", "ProjectNo =" & ProjectNo), 0) + 1
    Me.Refresh

End Sub

I put together the following to help keep things straight in translating the details from the video to my specific application:
PCResale  =  ProjectControl
OrderT    =  DocumentRegisterT
CustomerOrderNumber    =  DocSeqID
CustomerID    =  ProjectNo
CustomerT    =  ProjectListT
CustomerF    =  NA
OrderNumber    =  DocID
Combo8  =  ProjectNoCombo



Richard Rost 
          
2 years ago
What are all of your field types?
Faith Harvey OP 
       
2 years ago
RecordID (Record) = AutoNumber
ProjectNo (Project) = Short Text
DocTypeCode (Doc Code) = Short Text
DocSeqID (Doc Num)= Number (Long Integer, Format 0000)

I don't think any of the others are relevant to this, but if I'm wrong, let me know.
Kevin Robertson 
          
2 years ago
If ProjectNo is Short Text you'll need quotes.
DocSeqID = Nz(DMax("DocSeqID", "DocumentRegisterT", "ProjectNo =""" & ProjectNo & """"), 0) + 1

Double Double Quotes
Richard Rost 
          
2 years ago
That's exactly where I was going, Kevin. :)
Faith Harvey OP 
       
2 years ago
Okay, I've done that, but it still isn't working.  I'm going to go through the DDQ video (and the pre-requisite videos) and see if I can find my error.  Will let you know where I am with this once that is complete.  I am watching these videos as part of my work day, so it may take a few days. Thank you to everyone who has offered help.
Faith Harvey OP 
       
2 years ago
Okay, so I've gone through the Concatenation video, the DDQ video, and have been working on the DLookup video.  Now I can't get the DLookup code to work either.  I have downloaded your (Rick's) TechHelp Free Template database.  I've followed the instructions in the video precisely (as far as I can tell) but when I put the DLookup VBA code in, I get a #Name? error in place of the Sales Rep Number.  

DLookup VBA Code:  =DLookUp(['Phone'],['SalesRepT'],['SalesRepID='] & [SalesRepCombo])

I have now additionally gone to the learn.microsoft.com website and gone through their Getting started with VBA in Office instructions, looking for some explanation of what I am doing wrong.  I still can't find my error.

I have a couple more videos to go through before going back to the original question, but now I'm wondering if there could be something wrong foundationally.  Is there something I need to do in the settings/options when starting off from a blank database to make things like this work properly?  A bit like activating the Developer tab in other MSOffice applications?
Kevin Robertson 
          
2 years ago
The quotes and the equals sign should be outside the brackets.
Since you don't have spaces in your names you don't actually need the brackets.
Use double quotes instead of single quotes.
=DLookUp("Phone","SalesRepT","SalesRepID=" & SalesRepCombo)
Faith Harvey OP 
       
2 years ago
That's weird.  I've never used single quotes.  Always double quotes.  And I've always typed in exactly what Richard types in, but Access adds the brackets upon saving.

I do realise this sounds doubtful, but I have been taking copious notes as I go through these videos, in MS Word.  I've built my codes there and then c/p them into the relevant spots in the db.  So I have a record of what was typed in.  I've just placed your code in my notes for a side-by-side comparison, and they look exactly the same.   I've had two colleagues look at them, and they agree they are exactly the same.  But yours works and mine doesn't.  

To double check this, I c/p my code back in, and it doesn't work.  But yours does.  Now I'm going to have to go to IT and find out if there is something within the workings of my computer that is changing double-quotes to single-quotes behind the scene, regardless of what it looks like on the screen.  I don't even know if this is possible, but this might also be the answer to my original question.  I will get back to you once I've done this.  

Thank you so much for your help and your patience with me.
Richard Rost 
          
2 years ago
Be careful using Microsoft Word. You're better off using notepad. Microsoft Word can convert double quotes into those fancy curved double quotes that don't copyright into your VBA editor.
Alex Hedley 
            
2 years ago
Use markdown (.md)!
Faith Harvey OP 
       
2 years ago
Thanks Richard.  I will bear that in mind.  I will try moving to Notepad tomorrow, see if that makes a difference.

I don't know what markdown is.  I'll have to investigate that tomorrow as well, Alex.  Thank you.

Still waiting for the IT guy to have time for me, anyway....
Faith Harvey OP 
       
2 years ago
That worked!!  In both cases!  Oh my gosh, the relief!  Thank you so much guys.  I had the code right all along, the problem was that I was typing it in Word and then c/p it over.  Lesson learned!

It's incredibly frustrating to know that I can't take my notes in Word though.  I need to be able to use colour coding (in the notes bit, not the code bit) for clarity.  Ugh...

Just one more small (I hope) related question.  If I take notes in Word, and then c/p the coding into Notepad, will it take any of the problematic coding from Word with it?  I was afraid to try it...

Thank you all so much for the help.  Tips coming soon.
Dan Jackson 
            
2 years ago
Try Notepad++. I haven't used it myself but is a good compromise
Dan Jackson 
            
2 years ago
To answer your question, no it shouldn't. Notepad is plain text only so any addional formatting will clearly show. I would keep an eye out for extra spaces tho

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

 
 
 

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 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/23/2025 5:58:14 PM. PLT: 2s