Passing variables 3

[access345]

I created a module that will not work.

Public Sub LookupValue()


Dim X, Y As String
On Error GoTo ERR_txtRefDesig

Dim FormName As String
Dim TableName As String
Dim FormEntryTextBox As String
Dim DisplayTextBox1 As String
Dim EntryTextBoxFromTable1 As String
Dim LookupValueFromTable1 As String


TableName = TblPartsList
FormName = frmInitialInspectionDefect
EntryTextBoxFromTable1 = "RefDesignation"
FormEntryTextBox = "txtRefDesig"
DisplayTextBox1 = "txtPartDescription"
LookupValueFromTable1 = "PartDescription"


Forms![FormName]![DisplayTextBox1] = DLookup("[LookupValueFromTable1]", "TableName", "[EntryTextBoxFromTable1] Like '*" & Forms![FormName]!FormEntryTextBox & "*'")

Forms![frmInitialInspectionDefect]![txtPartDescription] = DLookup("[PartDescription]", "TblPartsList", "[RefDesignation] Like '*" & Forms![frmInitialInspectionDefect]!txtRefDesig & "*'")
Exit Sub
ERR_txtRefDesig:
MsgBox "Information not available. Contact Administrator x412"
Exit Sub

End Sub


When I step through the procedure it tells me Access can't find my form. Any sugesstions?

 

[missinglinq]

This whole LookupValue() has turned into a real hairball, hasn't it? Is the form open when you're stepping thru the proceedure? The first thing that comes to mind when a form or something on it can't be found or referenced is that the form isn't open. I believe I'm correct in saying it has to be open for this to work.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[access345]

Yes the form is open when I run the code.

I have modified the code so the form is recognized.

Public Sub LookupValue()



On Error GoTo ERR_txtRefDesig

Dim FormName As String
Dim TableName As String
Dim FormEntryTextBox As String
Dim DisplayTextBox1 As String
Dim EntryTextBoxFromTable1 As String
Dim LookupValueFromTable1 As String


TableName = "TblPartsList"
FormName = "frmInitialInspectionDefect"
EntryTextBoxFromTable1 = "RefDesignation"
FormEntryTextBox = "txtRefDesig"
DisplayTextBox1 = "txtPartDescription"
LookupValueFromTable1 = "PartDescription"


Forms!["FormName"]!["DisplayTextBox1"] = DLookup("[LookupValueFromTable1]", TableName, [EntryTextBoxFromTable1] Like "*" & Forms!["FormName"]!FormEntryTextBox & "*'")

'Forms![frmInitialInspectionDefect]![txtPartDescription] = DLookup("[PartDescription]", "TblPartsList", "[RefDesignation] Like '*" & Forms![frmInitialInspectionDefect]!txtRefDesig & "*'")
Exit Sub
ERR_txtRefDesig:
MsgBox "Information not available. Contact Administrator x412"
Exit Sub

End Sub

When I step through the code everything seems to be Recognized but I still get the error message.

 

[SgtJarrow]

I see a bunch of typo errors...Compare all the lines I put single quotes in front of to the original.....

Public Sub LookupValue()

Dim X, Y As String
On Error GoTo ERR_txtRefDesig

 Dim FormName As String
 Dim TableName As String
 Dim FormEntryTextBox As String
 Dim DisplayTextBox1 As String
 Dim EntryTextBoxFromTable1 As String
 Dim LookupValueFromTable1 As String
 
 
 'TableName = "TblPartsList"
 'FormName = "frmInitialInspectionDefect"
 EntryTextBoxFromTable1 = "RefDesignation"
 FormEntryTextBox = "txtRefDesig"
 DisplayTextBox1 = "txtPartDescription"
 LookupValueFromTable1 = "PartDescription"
 
 
'Forms![FormName]![DisplayTextBox1] = DLookup(LookupValueFromTable1, TableName, EntryTextBoxFromTable1 & " Like '*" & FormEntryTextBox & "*'")

'Forms![frmInitialInspectionDefect]![txtPartDescription] = DLookup("[PartDescription]", TableName, "[RefDesignation] Like '*" & Forms![frmInitialInspectionDefect][txtRefDesig] & "*'")
Exit Sub
ERR_txtRefDesig:
    MsgBox "Information not available. Contact Administrator x412"
    Exit Sub

End Sub

And that is just to start. You seem to be mixing your variables and your variable definitions up quite a bit.

If you are defining the NAME of a control on a form as a variable and then referencing it, try using: Forms("formname")(variablename) Biscally this says Forms Collection, the ACTUAL name of the form (beacuse of the double quotes) and what value is in the variable for the control. If I wrote: Forms(formname)("controlname) this would look for a form with the name of whatever was in the variable formname and then find the EXACT control of "controlname" (no quotes) on that form.

You also might want to check out how to define a FORM as a variable....using something like Dim theForm As Form and then theForm = Forms("exactformname") This may shorten your code in places, becuase then you can refer to controls on the form as theForm.exactcontrolname or theForm(variablecontrolname)

Hope this will help shed some light....if not let me know and can give some more examples...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

And unless I am way off here....I was able to reduce your whole sub routine down to one line, not counting the error handling.....

Public Sub LookupValue()

    ' Unless you have a specific reason not to, put you code handler at the top to catch ALL errors
    On Error GoTo Err_txtRefDesig

    ' You should define these separately or you have to use
    ' Dim X As String, Y As String
    ' Not necessary based on the code you supplied
    'Dim X As String
    'Dim Y As String
    
    ' These are not really necessary unless you are chainging them
    ' Define your variables
    'Dim FormName As String
    'Dim TableName As String
    'Dim FormEntryTextBox As String
    'Dim DisplayTextBox1 As String
    'Dim EntryTextBoxFromTable1 As String
    'Dim LookupValueFromTable1 As String
 
    ' These are not really necessary unless you are changing them
    ' Set your variables
    'TableName = "TblPartsList"
    'FormName = "frmInitialInspectionDefect"
    'EntryTextBoxFromTable1 = "RefDesignation"
    'FormEntryTextBox = "txtRefDesig"
    'DisplayTextBox1 = "txtPartDescription"
    'LookupValueFromTable1 = "PartDescription"
 
    ' Get the values
    Forms![frmInitialInspectionDefect]![txtPartDescription] = DLookup("PartDescription", "TblPartsList", "[RefDesignation] Like '*" & Forms![frmInitialInspectionDefect]![txtRefDesig] & "*'")
    ' If using the variables
    'Forms(FormName)(DisplayTextBox1) = DLookup(LookupValueFromTable1, TableName, EntryTextBoxFromTable1 & " Like '*" & FormEntryTextBox & "*'")

' Make sure you label your out call when you have error handling
ExitLookup:
    Exit Sub
    
' Put your error handling here and then return to the ExitLookup point instead of existing out the bottom
Err_txtRefDesig:
    MsgBox "Information not available.  Contact Administrator x412"
    Resume ExitLookup

End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[access345]

Robert,

This is my first time setting up a call routine, so thank you for your advice. I am going to be changing the names of the forms and the controls another 12 times. My real goal was to simply define how the DLookup function works. Since I have been working on just this for the last three days I thought it would be important to clearly define this function. So I could easily use it for the next 12 times that I am going to need it.

Tom

 

[access345]

I renamed the 'Dim FormName As Form and the defining statement as FormName = frmInitialInspectionDefect

Dim activeForm As Form
activeForm = Forms("frmInitialIspectionDefect")

I get a compile error: Invalid use of property

 

[SgtJarrow]

Two things...I think activeForm may be a reserved word....try using myActiveForm instead. Also, the form MUST be open for you to be able to assign it as a variable.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

And for an object you have to use the Set instruction:
Dim myActiveForm As Form
Set myActiveForm = Forms("frmInitialIspectionDefect")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

I incorporated all your sugestions. I don't get any compile errors anymore but I still get the error message to contact me.

 

[SgtJarrow]

First try stepping through the code to see what line the error is on...the post us your current code and what line the error was on.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[access345]

Public Sub LookupValue()

On Error GoTo ERR_txtRefDesig5


'Dim FormName As String
Dim myActiveForm As Form
Dim TableName As String
Dim FormEntryTextBox As String
Dim DisplayTextBox1 As String
Dim EntryTextBoxFromTable1 As String
Dim LookupValueFromTable1 As String


TableName = "TblPartsList"
'FormName = "frmInitialIspectionDefect"
Set myActiveForm = Forms("frmInitialInspectionDefect")
EntryTextBoxFromTable1 = "RefDesignation"
FormEntryTextBox = "txtRefDesig"
DisplayTextBox1 = "txtPartDescription"
LookupValueFromTable1 = "PartDescription"



' If using the variables
Forms(myActiveForm)(DisplayTextBox1) = DLookup(LookupValueFromTable1, TableName, EntryTextBoxFromTable1 & " Like '*" & FormEntryTextBox & "*'")

'Forms![frmInitialInspectionDefect]![txtPartDescription] = DLookup("[PartDescription]", "TblPartsList", "[RefDesignation] Like '*" & Forms![frmInitialInspectionDefect]!txtRefDesig & "*'")


Exit Sub
ERR_txtRefDesig5:
MsgBox "Information not available. Contact Administrator x412"
Exit Sub

End Sub


When I step through the code it stops at the line
Forms(myActiveForm)(DisplayTextBox1) = DLookup(LookupValueFromTable1, TableName, EntryTextBoxFromTable1 & " Like '*" & FormEntryTextBox & "*'")

When I hold the mouse over each part the only part that does not give a message is the myActiveForm variable

 

[SgtJarrow]

Only thing I can think of at this time is that EntryTextBoxFromTable1...does it possibly have spaces in it???
Try changing this line:
Forms(myActiveForm)(DisplayTextBox1) = DLookup(LookupValueFromTable1, TableName, EntryTextBoxFromTable1 & " Like '*" & FormEntryTextBox & "*'")

to

Forms(myActiveForm)(DisplayTextBox1) = DLookup(LookupValueFromTable1, TableName, "[" & EntryTextBoxFromTable1 & "] Like '*" & FormEntryTextBox & "*'")

Also, if you try the line that does not use the variables...does it work???


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

myActiveForm(DisplayTextBox1) = DLookup(LookupValueFromTable1, TableName, EntryTextBoxFromTable1 & " Like '*" & myActiveForm(FormEntryTextBox) & "*'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SgtJarrow]

PHV gets a star from me for flying as copilot on me with this one... He has been catching my mistakes and assisting as much as I am. Thank you.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[access345]

PHV's code worked perfectly. Thanks to all for your patience for helping me solve this problem.