UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Stumped, Access 2007    
 
   
Mchadsey
post May 17 2017, 09:25 PM
Post#1



Posts: 71
Joined: 26-March 14
From: Boston


So I have a piece of VBA Code I am trying to use on my current project that is completely stumping me. What I am trying to do is is take the year portion of a date that the user inputs, add the digits together and repeat until there is a single digit, an 11, or a 22 left over, then input that number into a different table. The code I am using is below and the error that keeps coming up is 'label not defined' on Goto Line2.

Function SEYrs()
Years = Year(Form.Del.Birthdate)
Y1 = Mid(Int(FormatNumber(Years)), 1, 1)
Y2 = Mid(Int(FormatNumber(Years)), 2, 1)
Y3 = Mid(Int(FormatNumber(Years)), 3, 1)
Y4 = Mid(Int(FormatNumber(Years)), 1, 1)
Ye = Int(Y1) + Int(Y2) + Int(Y3) + Int(Y4)
If Ye > 9 And Not 11 And Not 22 Then
GoTo Line1
Else
GoTo Line2
End If
Line1 = SEInc():
Line2 = SEFin():
End Function

Function SEFin()
Dim Num As Database
Dim Ele As TableDef
Set Num = CurrentDb
Set Ele = CurrentDb.Table.Delsubelement
Set Deli = CurrentDb.Delination

Num.Execute "Update Ele" & "Set SubYear = Ye" & "WHERE Table.Delination.[Delination Number] = Ele.[Delination Number]"
End Function

Function SEInc()
YS1 = Mid(Ye, 1, 1)
YS2 = Mid(Ye, 2, 1)
YS = Int(YS1) + Int(YS2)
If YS > 9 And Not 11 And Not 22 Then
GoTo Line3
Else
GoTo Line4
End If
Line3 = SEInc():
Line4 = SEFin():
End Function

--------------------
I learned kindness from the unkind, silence from the talkative, and tolerance from the intolerant...Yet strangely I am ungrateful to these teachers
Go to the top of the page
 
theDBguy
post May 17 2017, 09:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 70,391
Joined: 19-June 07
From: SunnySandyEggo


Hi,

When you use something like this:

GoTo Line1

It means you're asking code execution to jump to a label such as:

Line1:

Instead, your code has something like this:

Line1 = SEInc():

which I am not sure is a valid syntax.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post May 17 2017, 10:40 PM
Post#3



Posts: 5,111
Joined: 11-November 10
From: SoCal, USA


your functions contain what i assume are numerous variables, yet there are only two variables explicitly defined, in one of the functions. are all of the others module-level or global variables? if not, suggest you require variable declaration in all your modules in this database and future ones, as a best practice.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
zocker
post May 17 2017, 11:40 PM
Post#4


Utterly Eccentric and Moderator
Posts: 4,050
Joined: 4-March 00
From: Bristol / Ipswich / Spain


Sounds difficult but

If

Y1 = Mid(Int(FormatNumber(Years)), 1, 1)
(AND
Y2 = Mid(Int(FormatNumber(Years)), 2, 1)
(AND
Y3 = Mid(Int(FormatNumber(Years)), 3, 1)
(AND
Y4 = Mid(Int(FormatNumber(Years)), 1, 1)

Then Y1 = Y4 ........?

HTH

Zocker

--------------------
Everything in moderation.......especially moderation.
Go to the top of the page
 
BruceM
post May 18 2017, 07:17 AM
Post#5


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


To add a little to what Tina wrote, add Option Explicit at the top of each code module, below Option Compare Database, and compile the code. To make Option Explicit default for new code modules, in the VBA Editor: Tools >> Options >> Editor tab, and check Require Variable Declaration. If you do not require variable declaration, and you use undeclared variables, Access assumes they are variants. In SEInc it would be like this, assuming the variables are not declared at the module level or as global variables:
CODE
Function SEInc()
  YS1 = Mid(Ye, 1, 1)  ' YS1 = Mid(Null,1,1), which results in YS1 = Null
  YS2 = Mid(Ye, 2, 1)  ' Same as YS1
  YS = Int(YS1) + Int(YS2)  ' YS = Null + Null, which results in YS = Null
  If YS > 9 And Not 11 And Not 22 Then
      GoTo Line3  ' This could be Call SEInc(), but it is good if this never happens, since you would just run the procedure again and get caught in a loop
    Else
      GoTo Line4
  End If
End Function


Your SQL will not work even if you get as far as the Execute statement since it would be this (spaces are missing):

"Update EleSet SubYear = YeWHERE Table.Delination.[Delination Number] = Ele.[Delination Number]"

In general, a sub does something, and a function has a return value (the built in function Date(), for instance, returns the current date). A function can do something also, but since the default return value is a variant, and the function is never assigned a value, you could end up with the function returning null where you do not expect it.

I think we are going to need a clearer idea of what you are trying to do, including a description of the variables.
Go to the top of the page
 
Mchadsey
post May 18 2017, 08:07 AM
Post#6



Posts: 71
Joined: 26-March 14
From: Boston


So the reason I had the Line1 = SEInc() was whenever I tried to Use the Line1: I got the following error. I did realize that I don't need to use two other functions so I might have fixed that piece though. If you know why I kept getting that error though it would be good to know.


Attached File(s)
Attached File  Error.PNG ( 14.71K )Number of downloads: 1
 

--------------------
I learned kindness from the unkind, silence from the talkative, and tolerance from the intolerant...Yet strangely I am ungrateful to these teachers
Go to the top of the page
 
BruceM
post May 18 2017, 08:24 AM
Post#7


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


GoTo sends the code to a label, which is text at the left margin followed by a colon. I think spaces are not allowed in labels, but underscores are.
QUOTE
the reason I had the Line1 = SEInc() was whenever I tried to Use the Line1: I got the following error.

You can't set a label as equal to anything. At the label you would have some valid instruction, or the instruction could be at the following lines. The point is that GoTo sends the code to a label, and the code is processed line by line starting at that point.

My point was that even if you set it up correctly, if you call SEInc from within SEInc, the procedure will run forever or until you do a force shut down with Ctrl + Alt + Delete. There are valid reasons for a procedure to call itself (recursive procedures), but something has to be different the next time through the code.

Have you declared the variables? Have you added Option Explicit and compiled the code?

Go to the top of the page
 
Mchadsey
post May 18 2017, 08:36 AM
Post#8



Posts: 71
Joined: 26-March 14
From: Boston


In my database I am ultimately trying to do calculations based off of a birth date input by the user. This set of code specifically is meant to be based off the year. It is meant to take the digits in the year and add them together, then again, and so on until all that is left is a single digit, an 11, or a 22. I originally was trying to do this with queries which worked for the most part but not really well.

Example the User would put in 10/5/1987. It would then take the year 1987, add 1+9+8+7, take the 25 (since it is not 1-9,11,22) add the 2+5, Take the resulting 7 and put it into an existing record in another table. Someone with the year 1954 though would get 19, which would add to 10, which would need to loop around one last time to get the 1. That's the reason I used the Mid function since I knew there would be a set number of digits, four digits for the first addition and two digits for the additions after.

--------------------
I learned kindness from the unkind, silence from the talkative, and tolerance from the intolerant...Yet strangely I am ungrateful to these teachers
Go to the top of the page
 
BruceM
post May 18 2017, 09:54 AM
Post#9


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


It would help if you answer questions asked by people who are trying to help. It matters if you can compile the code (Debug >> Compile). If you cannot, the code will not work. I cannot imagine the code will compile for a number of reasons, including that Table and Delination are not properties of CurrentDb.

Here is an example of a function that turns the year into the result you are seeking. You can use MsgBox iResult instead of Debug.Print if you like.

CODE
Public Function TestIt(ByVal intYr As Integer) As Boolean

  Dim strYr   As String
  Dim i       As Integer
  Dim iResult As Integer

  strYr = CStr(intYr)
  
  For i = 1 To Len(strYr)
    iResult = iResult + CInt(Mid(strYr, i, 1))
  Next i
  
  If (iResult > 9) And (iResult Mod 11 > 0) Then
      TestIt = True
      Call TestIt(iResult)
    Else
      TestIt = False
  End If
  
  If TestIt = False Then
      Debug.Print iResult
  End If
  
End Function


All the function does is to display a number either in the Immediate code window (Debug.Print) or a message box. It is not clear what you intend to do after that. If the only intent is to write the value to a table you can do so in the same procedure instead of doing Debug.Print. However, you can calculate the value any time you need it. Why do you want to write it to a table?

However, if that is what you want to do, just write it to the table via a SQL statement. There is no need for TableDefs. If you can construct the Update SQL in query design view to do what you need, just switch to SQL view to see the actual SQL.
Go to the top of the page
 
theDBguy
post May 18 2017, 10:34 AM
Post#10


Access Wiki and Forums Moderator
Posts: 70,391
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I like playing with recursion and just thought I would share my take on this problem.

CODE
Public Function AddDigits(Digits As Long) As Long
'5/18/2017
'thedbguy@gmail.com
'Add digits until result is either a single digit or 11 or 22

Dim lngAnswer As Long
Dim strDigits As String
Dim ctr As Long

strDigits = CStr(Digits)

For ctr = 1 To Len(strDigits)
    lngAnswer = lngAnswer + Val(Mid(strDigits, ctr, 1))
Next

Select Case lngAnswer
    Case 1 To 9, 11, 22
        'done
    Case Else
        lngAnswer = AddDigits(lngAnswer)
End Select

AddDigits = lngAnswer

End Function

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post May 18 2017, 10:55 AM
Post#11


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


Hi DBGuy,

I'm not very familiar with recursion, but here's an interesting thing I noticed. When I originally wrote the procedure it was a Sub since, as I understand, the point is to write the result to a table. As I noted, I wonder if that is necessary, but for now I assumed it was. Anyhow, 1987 would be 25 on the first pass, then 7. However, the Debug.Print was printing 7, then 25. When stepping through I noticed that it printed 7, then bounced back to the End If line and printed 25. It seems it "remembered" the result from the first pass, so I had to convince it not to return that result. Remember, it was a Sub at this point.

Changing to a function, and testing the function's return value, was the way I found, but of course it could have been testing for 0-9, 11, or 22 instead of True/False.

Just wondering about why it printed both results when two passes were needed. For 1999 it needed three passes, and all three were printed. My guess is that the sub somehow kept track of how many times it had run, and retained the values produced in each pass.
Go to the top of the page
 
theDBguy
post May 18 2017, 10:58 AM
Post#12


Access Wiki and Forums Moderator
Posts: 70,391
Joined: 19-June 07
From: SunnySandyEggo


Hi Bruce,

Interesting observation. Did you also declare the argument as ByVal when it was a Sub?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post May 18 2017, 11:35 AM
Post#13


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


Yes, ByVal. I'm always specific about that.

Well, I just discovered something I didn't know about before. Whatever the reason, I know how to prevent it, but maybe it will be useful some day.
Go to the top of the page
 
Mchadsey
post May 18 2017, 08:36 PM
Post#14



Posts: 71
Joined: 26-March 14
From: Boston


Sorry BruceM, My last post came before I saw yours and I had to go to work right after. I had not declared variables in the right way. My first try included declaring variables, but because I am basically trying to build my code through googling each piece I ended up deleting that part among others and I honestly forgot to put it back in. I didn't even know about the compiler until you mentioned. Frankly most of my database work is centered in Query's and Macros, I don't use much code. This project that is not an option so I am using it as a way of teaching myself at least some coding. Thank you for letting me know about the compiler by the way.

Part of the original reason that I was putting the info into a table was because It was taking so many queries to do what I needed that it was slowing my DB down to the point of crashing it and I do need that information later. A part of me wants to keep the table now because its in there and it was the solution to an earlier problem. I am open to a better way.

Thank you all for your help.

--------------------
I learned kindness from the unkind, silence from the talkative, and tolerance from the intolerant...Yet strangely I am ungrateful to these teachers
Go to the top of the page
 
BruceM
post May 19 2017, 06:34 AM
Post#15


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


Two different code options (similar, but differing in some details) have been suggested. DBGuy's code could be used to return the value you need in a query. If the query has a date field, add a query column:

YearCalc: AddDigits([DateFieldName])

You say it takes a lot of queries, but it shouldn't have to do that. There are more direct routes.

As for compiling and variable declaration, note the suggestions in Post #5. See this posting for more debugging techniques. Variable declaration and compiling are very important tools for writing solid code. I have no idea why it isn't default to require variable declaration. I don't even have a guess.

Your SQL is an attempt at updating records. Could you describe in non-database terms the nature of the data in the table that is to be updated, and in the other table mentioned in the SQL? Also, note again that the SQL only needs table names, and maybe joins. The CurrentDb.Table etc. code is not likely to do what you want, and in any case is not needed.

I need to say again that it is unlikely you need to write the calculated value to a table. In general you should avoid writing calculation results to table fields, but rather you should calculate on the fly as needed.
Go to the top of the page
 
Mchadsey
post May 19 2017, 08:51 AM
Post#16



Posts: 71
Joined: 26-March 14
From: Boston


After reading your comment BruceM I looked over my database again and you're right I don't need to store the calculated fields. I got caught up because I knew that I would always need those 8-12 calculated values so I thought I should put them into a table. DBGuy thank you so much that code was exactly what I needed.


--------------------
I learned kindness from the unkind, silence from the talkative, and tolerance from the intolerant...Yet strangely I am ungrateful to these teachers
Go to the top of the page
 
BruceM
post May 19 2017, 09:46 AM
Post#17


UtterAccess VIP
Posts: 6,742
Joined: 24-May 10
From: Downeast Maine


DBGuy and I are both glad to help. Glad to hear you agreed it is not necessary to store the values, and that the procedure DBGuy provided is working for you. Good luck with the project!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th August 2017 - 07:30 PM