Open form with List Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

IHow do I open a sub form on my main form, frmDiscipline, using code. I
really tried to do it myself and came up with this.

Dim lngFormNumber As Long
Dim strFormName As String

'The lstbox is called lstViolationSelect and the field that is holding
information is ViolationID THIS IS WHERE I AM GETTING THE ERROR

lngFormNumber = Me![ViolationID]

Set db = CurrentDb
Set rst = db.OpenRecordset("Select FormName FROM tblFormList WHERE
FormNumber = lngFormNumber")

strFormName = rst.Fields("FormName")
rst.Close

DoCmd.OpenForm strFormName, , , , acFormAdd
 
Rip,

To begin with, in your recordset SQL string, lngFormNumber should be
outside the quotes so it is treated as a variable; enclosed in the
quotes it is treated like a part of the test string. So, it should be:

strSQL = "Select FormName FROM tblFormList"
strSQL = strSQL & " WHERE FormNumber = " & lngFormNumber
Set rst = db.OpenRecordset(strSQL)

That said, using a recordset operation for this is kind of an overkill.
You could use a plain DLookup instead:

strFormName = DLookup("[FormName]", "tblFormList", "[FormNumber] = " &
lngFormNumber)
(awatch out for wrapping in your newsreader; keep expression in one line).

HTH,
Nikos
 
Back
Top