Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2011
    Posts
    9

    Question Unanswered: Filtering a form from a subform using VBA code

    I have designed a form(main form) which has a record source that runs subforms. this works perfectly. I also have a macro that is attached to this subform which filters records on a different form. One of my subforms contains personnel IDs and few details. When I click on the ID field the macro runs and opens another form, filtering it by the ID number. This works when the subform is opened independently but when the subform is opened on a record source frame and you try to click on the id field you get an error message that Ms access can't find the subform referred to in a macro.

    How can I filter using a code? I tried using DoCmd.penform command and it works, but I fail to come up with code that can pick the id number from the subform and opens the other form filtering it by the same ID. I need help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by lovelydan View Post
    ... I have designed a form(main form) which has a record source that runs subforms.
    ...
    This works when the subform is opened independently but when the subform is opened on a record source frame
    Can you explain what you mean by these? What's a "record source that runs subforms"? And what's a "subform opened on a record source frame"?
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    9

    Red face sourceObject

    okay. my mainform has an unbound frame. which when i click on a button lets say labelled employee..the unbound frame will open subform called employees in the frame. the button onClick property is attached to a code (Me!subform.sourceObject= "frmEmployees") that opens subform Employees containing empoyeeID,name and cell number..so now i want a code that when i click on employee ID in the subform the subform automatically opens another form containing all the details for that employee ID..

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If Text_EmployeeID is the name of the textbox that contains the employee Id in the subform and Frm_Employee_Details is a form that displays the details about an employee, you could use:
    Code:
    Private Sub Text_EmployeeID_Click()
    
        DoCmd.OpenForm "Frm_Employee_Details", , , "employee=" & Me.Text_EmployeeID.Value
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Aug 2011
    Posts
    9

    Question error 3464

    i tried the code but i get the data type mismatch in criteria expression. by the way what is this "employee=" is it a name of a field in the form i want to open.

    i have employeeID(both plain text) in the subform and in the form i want to open. what might be the problem?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If employeeID is defined as Text, the line becomes:
    Code:
    DoCmd.OpenForm "Frm_Employee_Details", , , "employee='" & Me.Text_EmployeeID.Value & "'"
    employee must be comprised in the data source of the form you open (i.e. there must be a field with that name in the Form RecordSet).
    Have a nice day!

  7. #7
    Join Date
    Aug 2011
    Posts
    9

    Talking bravo..!

    thanx Sin, just saved my day

  8. #8
    Join Date
    Aug 2011
    Posts
    9

    Red face sytax error

    i have added something to the code now i get an error "syntax error(missing operator)in a query expression": where did i go wrong?

    DoCmd.OpenForm "frmNotes", , , "equipGovSerialNumber='" & Me.equipGovSerialNumber.Value & "'" & "ntsTime='" & Me.ntsTime.Value & "'"


    ntsTime field format is Date.
    Last edited by lovelydan; 08-10-11 at 12:26.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is equipGovSerialNumber defined as Text or Memo data type in the table?
    If not, remove the single quotes around Me.equipGovSerialNumber.Value.

    About ntsTime, for a Date/Time value, the usual syntax is #Date/Time Value# with the date in US format (mm/dd/yyyy).
    Have a nice day!

  10. #10
    Join Date
    Aug 2011
    Posts
    9

    Red face sytax error

    equipGovSerialNumber is text and it working fine without the additional part for time..

    i get the sytax error when i add the bolded part for time.. can you write the whole thing as an example so i can get an idea
    __________________

  11. #11
    Join Date
    Aug 2011
    Posts
    9

    Angry sytax error

    this works fine when tested:
    Code:
    Public Sub frmNotes()
    DoCmd.OpenForm "frmNotes", , , "ntsTime=#" & Me.ntsTime.Value & "#"
    End Sub
    
    this also works when tested separately:
    Code:
    DoCmd.OpenForm "frmNotes", , , "equipGovSerialNumber='" & Me.equipGovSerialNumber.Value & "'"
    now the problem comes when im trying to mix them together like:
    Code:
    oCmd.OpenForm "frmNotes", , , "equipGovSerialNumber='" & Me.equipGovSerialNumber.Value & "'" AND "ntsTime=#" & Me.ntsTime.Value & "#"
    i get a (Type mismatch)error, im a learner and this is confusing me.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It took me a long time to spot the evidence

    The AND operator joining both criteria must inside the string expression:
    Code:
    DoCmd.OpenForm "frmNotes", , , "equipGovSerialNumber='" & Me.equipGovSerialNumber.Value & "' AND ntsTime=#" & Me.ntsTime.Value & "#"
    Have a nice day!

Posting Permissions

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