Variable not defined Error in VBA

Hello,

I am writing a function to read back values from a table. The reason for a VBA function rather than an iif or switch function is that I have 33 possible scenarios. Unfortunately, the data I'm reading back also comes from a different column depending on the process number.

My function is as follows:

Public Function UpdateCompletionDate(Process) As Integer
Select Case Process
Case 3
UpdateCompletionDate = 0
Case 4
UpdateCompletionDate = tblComponent!Cum_Duration_P4
Case 5
UpdateCompletionDate = tblComponent!Cum_Duration_P5
Case 6
UpdateCompletionDate = tblComponent!Cum_Duration_P6
Case 7
UpdateCompletionDate = tblComponent!Cum_Duration_P7
Case 8
UpdateCompletionDate = tblComponent!Cum_Duration_P8
Case 9
UpdateCompletionDate = tblComponent!Cum_Duration_P9
Case 10
UpdateCompletionDate = tblComponent!Cum_Duration_P10
Case 11
UpdateCompletionDate = tblComponent!Cum_Duration_P11
Case 12
UpdateCompletionDate = tblComponent!Cum_Duration_P12
Case 13
UpdateCompletionDate = tblComponent!Cum_Duration_P13
Case 14
UpdateCompletionDate = tblComponent!Cum_Duration_P14
Case 15
UpdateCompletionDate = tblComponent!Cum_Duration_P15
Case 16
UpdateCompletionDate = tblComponent!Cum_Duration_P16
Case 17
UpdateCompletionDate = tblComponent!Cum_Duration_P17
Case 18
UpdateCompletionDate = tblComponent!Cum_Duration_P18
Case 19
UpdateCompletionDate = tblComponent!Cum_Duration_P19
Case 20
UpdateCompletionDate = tblComponent!Cum_Duration_P20
Case 21
UpdateCompletionDate = tblComponent!Cum_Duration_P21
Case 22
UpdateCompletionDate = tblComponent!Cum_Duration_P22
Case 23
UpdateCompletionDate = tblComponent!Cum_Duration_P23
Case 24
UpdateCompletionDate = tblComponent!Cum_Duration_P24
Case 25
UpdateCompletionDate = tblComponent!Cum_Duration_P25
Case 26
UpdateCompletionDate = tblComponent!Cum_Duration_P26
Case 27
UpdateCompletionDate = tblComponent!Cum_Duration_P27
Case 28
UpdateCompletionDate = tblComponent!Cum_Duration_P28
Case 29
UpdateCompletionDate = tblComponent!Cum_Duration_P29
Case 30
UpdateCompletionDate = tblComponent!Cum_Duration_P30
Case 31
UpdateCompletionDate = tblComponent!Cum_Duration_P31
Case 32
UpdateCompletionDate = tblComponent!Cum_Duration_P32
Case 33
UpdateCompletionDate = tblComponent!Cum_Duration_P33
End Select

End Function

Open in new window


When using
[tblComponent]![Cum_Duration_P4]

Open in new window

etc gave me another type of error.

This would feed into an update query to update a date further along.

Thanks,

Sarith
Sarith GadaData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Sarith

This might be a stupid question but is tblComponent an actual table?
0
Sarith GadaData AnalystAuthor Commented:
Hi Norie.

Yes it is. Also, the code is in a module, not on a form.

Thanks,

Sarith
0
Martin LissOlder than dirtCommented:
Try something like

UpdateCompletionDate = Sheets("My Sheet Name").tblComponent!Cum_Duration_P4
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

MacroShadowCommented:
VBA does not recognize tblComponent being a table.

You either use Tables!tblComponent, or you can use a call to DLookup, like this:
Public Function UpdateCompletionDate(Process) As Integer
	Select Case Process
	Case 3
		UpdateCompletionDate = 0
	Case 4
		strFieldNumber = "P4"
	Case 5
		strFieldNumber = "P5"
	Case 6
		strFieldNumber = "P6"
	Case 7
		strFieldNumber = "P7"
	Case 8
		strFieldNumber = "P8"
	Case 9
		strFieldNumber = "P9"
	Case 10
		strFieldNumber = "P10"
	Case 11
		strFieldNumber = "P11"
	Case 12
		strFieldNumber = "P12"
	Case 13
		strFieldNumber = "P13"
	Case 14
		strFieldNumber = "P14"
	Case 15
		strFieldNumber = "P15"
	Case 16
		strFieldNumber = "P16"
	Case 17
		strFieldNumber = "P17"
	Case 18
		strFieldNumber = "P18"
	Case 19
		strFieldNumber = "P19"
	Case 20
		strFieldNumber = "P20"
	Case 21
		strFieldNumber = "P21"
	Case 22
		strFieldNumber = "P22"
	Case 23
		strFieldNumber = "P23"
	Case 24
		strFieldNumber = "P24"
	Case 25
		strFieldNumber = "P25"
	Case 26
		strFieldNumber = "P26"
	Case 27
		strFieldNumber = "P27"
	Case 28
		strFieldNumber = "P28"
	Case 29
		strFieldNumber = "P29"
	Case 30
		strFieldNumber = "P30"
	Case 31
		strFieldNumber = "P31"
	Case 32
		strFieldNumber = "P32"
	Case 33
		strFieldNumber = "P33"
	End Select
	UpdateCompletionDate = DLookup("Cum_Duration_" & strFieldNumber, "tblComponent")
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Sorry, didn't notice that this was Access.
0
Sarith GadaData AnalystAuthor Commented:
Hi MacroShadow,

I did try the tables!tblComponent!... approach but alas did not get anywhere there.

I'll try the lookup method now.

Thank you!
0
Sarith GadaData AnalystAuthor Commented:
This worked perfectly. Thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.