Using SQL to Populate Form Labels

[sibleytr]

I'm creating a template MDB for a number of upcoming projects. My current issue is to make my forms more dynamic, in that I will allow the user fill in the name of the form's title and subtitle labels.

TBL name is:
tblInfoFrmTitles
strFrmName
strFrmTitle
strFrmSubTitle

Dim strSql As String
Dim strCurFrmName As String
Dim rs As Recordset
Dim db As Database

strCurFrmName = Me.Form.Name

Set db = CurrentDb()
strSql = "SELECT * FROM tblInfoFrmTitles "
    strSql = strSql & "WHERE strFrmName = "
    strSql = strSql & "strCurFrmName;"

I missing how to pull the data from my recordset for postions 0, 1, and 2.

Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

strFrmName = Nz(rs!strCurFrmName, 0)
strFrmTitle = Nz(rs!strCurFrmName, 1)
strFrmSubTitle = Nz(rs!strCurFrmName, 2)

MsgBox strFrmName & " " & strFrmTitle & " " & strFrmSubTitle

Ideas appreciated

Even if =DLookUp turns out to be the eaiser approach I would still like to know how to do the above for a future components.

 

[PHV]

strSql = "SELECT * FROM tblInfoFrmTitles "
    strSql = strSql & "WHERE strFrmName = '"
    strSql = strSql & strCurFrmName & "'"

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

 

[sibleytr]

Thanks PHV

Here's where I went wrong:
1. Turn off Reference to Active X
2. Watch those single and double quote issues
3. For crying out load edit those variable names when using copy and paste

    Dim strFrmName As String
    Dim strSql As String
    Dim rs As Recordset
    Dim db As Database

    'Determin name of current form
    strFrmName = Me.Form.Name
    
    'Qry for Main and Subtitles of current form
    Set db = CurrentDb()
    strSql = "SELECT * FROM tblInfoFrmTitles "
        strSql = strSql & "WHERE strFrmName = '"
        strSql = strSql & strFrmName & "'"
    Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
    
    'Set str variables using record set
    strFrmMainTitle = Nz(rs!strFrmMainTitle, 1)
    strFrmSubTitle = Nz(rs!strFrmSubTitle, 2)

    'Set public variable for main title
    pubFrmMainTitle = strFrmMainTitle
    
    'Apply main and sub title names to title boxes
    Me.boxMainTitle.Caption = pubFrmMainTitle
    Me.boxSubTitle.Caption = strFrmSubTitle

Thanks PHV and RW