SQL to open correct form

christakis

Registered User.
Local time
Today, 13:03
Joined
Oct 23, 2009
Messages
72
Hi guys,

I have a table:

tblDeviceClasses
->Key
->Name
->Path
->DeviceClassForm

In my main form, I have a listbox which looks up this table and shows a list of the names. The Key is the bound column and is hidden. I want to select one item from the list and when i click a button to open the corresponding form to the item (DeviceClassForm).

I thought I would SQL search the table based on the returned value of the list (key), obtain the name of the form, and then open it. Here is the code I came up. Doesnt work but im preety sure im close.

Code:
Private Sub btnOpenClass_Click()
On Error GoTo Err_btnOpenClass_Click
    Dim stLinkCriteria As String
    Dim strSQL As String
    Dim formName As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from tblDeviceClasses where Key = '" & Me.listClasses & "'"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    formName = rs!DeviceClassForm
    DoCmd.OpenForm formName, , , stLinkCriteria
   
Exit_btnOpenClass_Click:
    Exit Sub
Err_btnOpenClass_Click:
    MsgBox Err.Description
    Resume Exit_btnOpenClass_Click
End Sub

Any help is appreciated

Cheers,
Chris
 
Last edited:
I don't really understand what you're trying to achieve in general, but for grabbing a single field value from a table, I think DLookup will serve you better.
 
I don't really understand what you're trying to achieve in general, but for grabbing a single field value from a table, I think DLookup will serve you better.

Hm... I am not familiar with DLookup... How does it work exactly?

Edit: I tried this but still no luck

Private Sub btnOpenClass_Click()
On Error GoTo Err_btnOpenClass_Click
Dim stLinkCriteria As String
Dim formName As String
formName = DLookup(DeviceClassForm, tblDeviceClasses, Key = Me.listClasses)
DoCmd.OpenForm formName, , , stLinkCriteria

Exit_btnOpenClass_Click:
Exit Sub
Err_btnOpenClass_Click:
MsgBox Err.Description
Resume Exit_btnOpenClass_Click
End Sub

Is the red criterion correct? I want to get the DeviceClassForm field from tblDeviceClasses, where the key of the record is equal to Me.listClasses
 
Last edited:
You have to pass the parameters to DLookup as strings, so:

DLookup("Price", "Fruit", "description='banana'")

Would return the value from the price column, in a table or query called 'fruit', for a row that has the term 'banana' in a column named 'description'

So I think yours would be:

formName = DLookup("DeviceClassForm", "tblDeviceClasses", "Key = " & Me.listClasses)
 
You have to pass the parameters to DLookup as strings, so:

DLookup("Price", "Fruit", "description='banana'")

Would return the value from the price column, in a table or query called 'fruit', for a row that has the term 'banana' in a column named 'description'

So I think yours would be:

formName = DLookup("DeviceClassForm", "tblDeviceClasses", "Key = " & Me.listClasses)

Yes that worked spot on. Thank you.

Christakis
 

Users who are viewing this thread

Back
Top Bottom