DLookup 2

[accessvbahelp]

I am very new at writing VBA code and I can't figure out why this is not working. When the approve button is clicked the revision letter that is in the main table needs to index by one. The revision letter must always be letters. I have a seperate table in Access called TblRev that references a number to the correct revision letter (example 1=A, 2=B,... 27=AA, 28=AB, etc)the column with the numbers is called Number and the column with the letters is called Revision. I am trying to do a DLookup to reference the table but I can't get it to work. This is what I have so far.

'This section also updates the revision status from "AREV" to "B" as an example.

Dim strNewRev As String
Dim NewRevAlpha As Integer

'Updates revision letter
Rev = Forms("frmMain")!Revision.Value  'Gets revision letter from form
NewRev = Revision 'Defines the revision code for the drawing under revision
Me.AllowEdits = True 'Allows the data to be edited
NewRev = DLookup("Number", "tblRev", "Revision")
NewRev = NewRev + 1 'Indexes the alpha by one
NewRev = DLookup("Revision", "tblRev", "Number")
Me!Revision.Value = NewRev 'Places updated revision character in table
Me.AllowEdits = False 'Disables table edits
Me.Refresh 'Updates data in form and table
End Sub

Please be patient with me I a very new at writing code.
Thank you all for your help.

 

[randy700]

Perhaps change this...

NewRev = DLookup("Number", "tblRev", "Revision")

to this...

NewRev = DLookup("Number", "tblRev", "Revision = '" & Forms!frmMain.Revision & "'")

And change this...

NewRev = DLookup("Revision", "tblRev", "Number")

to this...

NewRev = DLookup("Revision", "tblRev", "Number = " & Forms!frmMain.Number)

Randy

 

[accessvbahelp]

Thank You randy700. I made the changes you suggested but I am getting this error message : Run-time error '2456': Application-defined or object defined error. When I click to debug the following code is highlighted

NewRev = Dlookup("Number", "tblRev", "Revision ='" & Forms!frmMain.Revisions & "'")

Do you know why this is?
Thanks again!

 

[accessvbahelp]

I changed it back to my original code and my table is updated with the letter A everytime. I can't get the second DLookup to use the new revision and reference the correct letter from that.
Thanks for all your help.

 

[hmlhml]

I still think the solutions suggested in your previous thread with this question:

http://www.tek-tips.com/viewthread.cfm?qid=1666439

were better than what you are doing now.

Your code as posted at the start of this thread attempts to increment NewRev instead of Number, in addition to the problems Randy mentioned.

 

[MazeWorX]

accessvbahelp .. AceMan in your previous post came up with a good solution .. why are you insisting on using your existing obviously not working code. Why ask for the help if you ignore it??

besides as randy has tried to explain you code syntax is incorrect

DLookup(expr, domain, [criteria]) you need to treat the criteria like a 'Where' clause. it is a comparison, just dumping 'number'in there does nothing. you need to compare something to something

Think of it like a query
DLookup: Select this FROM here WHERE this = that

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[accessvbahelp]

You are correct! I am asking for help and I don't use it.
Truth is I am very new to VBA. I got thrown into a project at work with no training or experience and I am trying to get through it. I really do appreciate all the help I am receiving on here! I will try the advice from AceMan again. I remember I tried it and I received an error code, I can't remember what the error code was because I have tried so many things. You are correct the DLookup is obviously not working so I need to move on. Thank you for pushing me. I need a kick in the butt!!!

 

[MazeWorX]

Try his code again and if you get an error reach out and we'll walk you through it. take note of the error you get, which line of code is highlighted etc everything helps when trying to trouble shoot code

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MazeWorX]

Just for fun (really i just needed a break from my own projects) I created an example of Ace's code with some modifications it will increment by one until it reaches Z then change over to AA - AZ BA - BZ and so on to ZZ. This will reach a max of ZZ it could be modified to go higher but that's 626 revisions see the attachment

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

accessvbahelp . . .

I finally got around to nailing this sucker (what I was after ... knew it could be done). With the following code revisions can go as high as you like:

[blue]Public Function NextRev(ByVal Rev As String)
   Dim idx As Integer, curAsc As Integer
   Dim Carry As Boolean, TipOvr As String
   
   If Rev <> "" Then
      Rev = UCase(Rev)
      
      [green]'Insure Rev contains all letters[/green]
      For idx = 1 To Len(Rev)
         curAsc = Asc(Mid(Rev, idx, 1))
         
         If Not (curAsc > 64 And curAsc < 91) And _
            Not (curAsc > 96 And curAsc < 123) Then
            MsgBox "'Rev' Is Not All Letters!"
            Exit Function
         End If
      Next
   
      idx = Len(Rev)
      Carry = True
      
      [green]'Parse Rev & handle carryovers[/green]
      For idx = Len(Rev) To 1 Step -1
         
         If Carry Then
            curAsc = Asc(Mid(Rev, idx, 1)) + 1
            
            If curAsc > 90 Then [green]'carryover[/green]
               Mid(Rev, idx, 1) = "A"
               Carry = True
            Else [green]'no carryover[/green]
               Mid(Rev, idx, 1) = Chr(curAsc)
               Carry = False
            End If
         Else
            Exit For
         End If
      Next
      
      [green]'if carryover runs thru all the way to the 1st character,
      'all the characters will be an "A". We concatenate an
      'additional "A" to include the next decade of 26 letters.[/green]
      TipOvr = String(Len(Rev), "A")
      
      If Rev = TipOvr Then
         NextRev = Rev & "A"
      Else
         NextRev = Rev
      End If
   Else
      MsgBox "No Rev Arguement!"
   End If
         
End Function[/blue]

Just call the function with the current revision:

[blue]   [i]stringvariable[/i] = NextRev(Revision)
[green]'or[/green]
   [i]stringvariable[/i] = NextRev("abczzz") [green]'returns "ABDAAA"[/green][/blue]

I'm hoping you can eliminate your table with this.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

accessvbahelp .. you owe Ace a star for that one nice A M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[accessvbahelp]

You guys are amazing! Thank you so much!!!!