Method or data member not found

[pape93]

Hi all, I came across this error message while trying to run the following code. Can you help with this problem?

Compile error: Method or data member not found

Sub getFieldCurrency(ObjectName As ComboBox, Rng As Range)
  Dim conn As ADODB.Connection
  Dim curInfo As ADODB.Recordset
  Dim strConn, supName As String
  strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\eps.mdb"
  Set conn = New ADODB.Connection
  conn.Open strConn
  Set curInfo = New ADODB.Recordset
  curInfo.Open "SELECT * FROM cur_tab WHERE cur_cod = '" + LCase(Trim(ePrisoft.ObjectName.value)) + "'", _
        conn, adOpenDynamic, adLockOptimistic, adCmdText
  If (curInfo.BOF Or curInfo.EOF) Then
    'if no records found
  Else
    'else show record
    Rng.value = curInfo("cur_rat")
  End If
    curInfo.Close
    Set curInfo = Nothing
    conn.Close
    Set conn = Nothing
 
End Sub

The following line is highlighted after the error message is displayed:
ePrisoft.ObjectName.value

Any help will be deeply appreciated
thanks much.

 

[Norie]

What is ePrisoft?

Some sort of 3rd part application you are automating?

 

[pape93]

ePrisoft is the name of the form

 

[Norie]

Do you mean a userform?

Then perhaps ObjectName is just being used as a 'placeholder' and you need to put the actual name of the control of interest there.

 

[pape93]

Yes thats what i meant.

thanks

 

[pape93]

I was hoping it could be used that way. so I would just call it similar to a function like this:

getFieldCurrency cboBlCurr, Worksheets("BL").Range("D6")

cboBLCurr being the combobox name and then specifying the range. Can it be done this way?

thanks

 

[Norie]

I just noticed that you were passing ObjectName as an argument/parameter.

You should be able to do that, but in the sub you wouldn't use the userform name since you are passing the combobox itself not just the name.

If you were passign the name, as a a string probably, you would need the userform name and you would also need to use it's Controls collection.

Sub getFieldCurrency(strCombo Name As String, Rng As Range)
...
curInfo.Open "SELECT * FROM cur_tab WHERE cur_cod = '" + LCase(Trim(ePrisoft.Controls(strComboName).Value)) + "'", _
conn, adOpenDynamic, adLockOptimistic, adCmdText

 

[pape93]

Thanks much Norie, it is working perfect now.
I appreciate your help.