vba code to open form using filter

Derek

Registered User.
Local time
Today, 05:53
Joined
May 4, 2010
Messages
234
Hi All

I am trying to open the form for a specific record. The following code works fine:
Code:
 sForm = "frmABC"
        sSQL = "[AuditID]=" & Me![lstSearch]
        DoCmd.OpenForm sForm
        DoCmd.OpenForm sForm, , , sSQL

But why the following isn't working?????
Code:
  DoCmd.OpenForm "frmABC", , , "[AuditID] =" & Me.lstSearch

And also I tried to open another form using below and it worked so just wondering why I am not able to open FrmABC using the below code:
Code:
  DoCmd.OpenForm "frmABC", , , "[AuditID] =" & Me.lstSearch

Thanks
 
LstSearch is the correct name of the list?
LstSearch has the correct BOUNDCOLUMN ? If not, you'll get the wrong value.
AuditID is numeric?
LstSearch is on the form already open?

I use 2nd,and 3rd version and it works fine.
 
LstSearch is the correct name of the list? Yes
LstSearch has the correct BOUNDCOLUMN ? lstsearch is not bound
AuditID is numeric? Autonumber
LstSearch is on the form already open? No

I have a form Frmsearch that has a listbox named lstSearch. When we double click on the item in the listbox then another form gets opened up with that record.
Please see below the code:
Code:
Private Sub lstSearch_DblClick(Cancel As Integer)
Dim sForm As String
Dim sSQL As String
Dim rs As Object
sForm = "frmCallAudit_New"
sSQL = "[AuditID]=" & Me![lstSearch]
DoCmd.OpenForm sForm
DoCmd.OpenForm sForm, , , sSQL
DoCmd.Close acForm, "frmSearch"
End sub
 
do you have AuditID in your listbox? if so, is it in column 1?
 
Yes please see below:
Code:
 Me.lstSearch.RowSource = "SELECT tblAudit.AuditID AS Ref, tblstaff.[Staff Name], tblAudit.[Call Date], tblstaff.[Staff Number] FROM tblstaff INNER JOIN tblAudit ON tblstaff.[Staff Number] = tblAudit.[Staff Number] WHERE (((tblAudit.AuditID)=" & Me.txtReference & ") AND ((tblstaff.[Staff Number])=" & Me.txtStaffNumber & "))"
         Me.lstSearch.ColumnCount = 4
        Me.lstSearch.ColumnWidths = ("2cm;2cm;2cm;0cm")
 
Also I want to disable the controls when the form is opened but its not doing that :
Code:
 Private Sub Form_Open(Cancel As Integer)
Dim ctr As Control
'MsgBox Me.chkSaved
 DoCmd.Maximize
 Me.cmdSummit.Visible = False
 If IsNull(Me.cboAgent) Then
    Me.cboAgent = Forms!frmForm!cboStaff
End If
 MsgBox (Me.AuditID & " " & Me.Saved)
 If Me.chkSaved = True Then
MsgBox "True"
For Each ctr In Me.Controls
'        MsgBox (ctr.Name & " " & ctr.Tag)
    If ctr.Tag = "C" Then
        ctr.Enabled = False
    End If
Next ctr
Else
MsgBox "False"
End If
 
End Sub
I am getting False for chkSaved and AuditID is displaying null too. AuditID is bound to the field. But not sure why the above code doesn't disable the controls .
 

Users who are viewing this thread

Back
Top Bottom