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?
 
MacroShadowConnect With a Mentor Commented:
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
 
NorieVBA ExpertCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Martin LissOlder than dirtCommented:
Try something like

UpdateCompletionDate = Sheets("My Sheet Name").tblComponent!Cum_Duration_P4
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.