Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013

    Unanswered: Unable to open Form using Where Clase

    Good Evening All

    I am having an issue opening a Subform from VBA within Access 2007

    I have two forms to keep it simple "Intruder Alarm Reports" and "Continue Report"

    Within "Intruder Alarm Report" there is a combi box which includes the Officer name from a lookup named Attended By.

    Within the "Continue Report" Form I have 3 Combi Boxes "Report" "Officer" and "Date"

    What I want to do is using the continue report through a button open the form which matches the Critia selected in the "Continue Report" Form.

    I have created the following basic code

    Private Sub Command4_Click()
    Dim report As String

    report = Me.Type_of_Incident
    DoCmd.OpenForm report, , , "Attended By= " & Officer.Value

    End Sub

    This does not work I recieve a Run time error 3075 missing operator but removing the where conditioning does open the correct form

    Have searched and tried serveral variations some just open the main form but do not open the form with the Officer name.

    Have checked the properties of the of the intruder alarm form and all naming conventions are correct.

    Also after this will require the Continue report form to sort on date which I also cannot get to work

    Any help will be much appricatiated

    Thank you

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    I cannot imagine anything more confusing than 'report' as part of the name of a Form! What were you thinking about? Then you use 'report,' which is a Reserved Word, as the name of a Variable! At the very least, you need to change the name of the Variable to something else.

    Next, you have

    report = Me.Type_of_Incident

    Does the Control named Type_of_Incident actually hold the name of a Form?

    If the Officer Control is defined as Text, which seems likely, your syntax would be

    DoCmd.OpenForm report, , , "[Attended By]= '" & Me.Officer.Value &"'"

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2013

    Working great

    Missinglinq Thank you kindly

    Sorry about the confusion its three internal forms that the officer fill in Fire Alarm Report, Incident Report and Intruder Alarm Report, but need to be forms so that they can enter the details.

    Hence the report

    Once again Thanks, now to have a play around with the date combi box option now you have restored my faith

    Kind Regards


  4. #4
    Join Date
    Feb 2013

    And so the problems continue

    Thanks to Missingling for the previous help

    I have a new issue the operation works correctly with the following code, and opens the correct form but any form created after May does not load.

    Have tried it with changing the Officer and Type of incident with a date prior to May and correct form loads but after April 30th, only a blank form loads ?

    Have tried changing the feilds in the tables and within the code of the form but still blank form loads

    Any help would be much appriciated

    Also having issue with Recordset to identify if a form does not exist but only just started to investage this

    Private Sub Command4_Click()

    If IsNull(Me.Officer) = True Or IsNull(Me.Date) = True Or IsNull(report) Or IsNull(Me.Type_of_Incident) = True Then
    MsgBox "Feilds cannot be Left Blank"

    Exit Sub
    End If

    DoCmd.OpenForm Me.Type_of_Incident, , , "[Attended By]= '" & Me.Officer.Value & "' AND [Date]=#" & Me.Date.Value & "#"

    If Me.RecordsetClone.RecordCount = 0 Then
    MsgBox "There are zero records in the data source!", vbInformation, "No Records Found"
    End If
    DoCmd.Close acForm, "continue report", acSaveNo
    DoCmd.Close acForm, "Selection", acSaveNo
    End Sub

    Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
    [Forms]![continue report].Move _
    Left:=0, Top:=0, Width:=6300, Height:=3600
    End Sub[/COLOR]

    Thank you
    Last edited by pjawynn; 05-02-13 at 18:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts