Subform criteria problems

[reidgivens]

I am very novice with access.

I am trying to create a search type of form, that when fillout out and clicked will take the user to a list of results. So far so good, i have this. The next part is, when they get to the list of results, I want them to click a button and go to the customers records form to view or edit.

My problem is each customer can have multiple entries in a sub form, and i need the button to take them to the right customer, and the right subform entry.

This is for a car shop. Each customer may have 2 or 3 cars, so how do you have it show up with the right car in the subform.

This is what i have so far.

--------------------------
Private Sub view_Click()
On Error GoTo Err_view_Click



Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "customers"

stLinkCriteria = "[c_id_num]=" & Me![c_id_num] & " AND [v_id_num]=" & Me![v_id_num]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_view_Click:
Exit Sub

Err_view_Click:
MsgBox Err.Description
Resume Exit_view_Click

End Sub

------------------------------------------
c_ic_num is the customers id and v_id_num is vehicle id

please help

 

[kraxmo]

Unfortunately, you cannot automatically filter a subform via the DoCmd.OpenForm statement, but you can accomplish the same thing a couple of ways:

1. In the Form_Load event, apply a constant-valued subform filter based.

    Me!subformcontrolname.Form.Filter = "[v_id_num]=" & Me![v_id_num]
    Me!subformcontrolname.Form.FilterOn = True

2. Pass variable subform filter critieria via the DoCmd.OpenForm OpenArgs parameter. In the Form_Open event, Me.OpenArgs can be assigned to a module-scoped string variable and referenced in the Form_Load event as in #1.

    Private Sub view_Click()
        Dim stLinkSubCriteria As String
        ...
        stLinkSubCriteria = "[v_id_num]=" & Me![v_id_num]
        DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stLinkSubCriteria
        ...
    End Sub

    'In your form's code module
    Dim mstrSubFormFilter As String

    Sub Form_Open()
        mstrSubFormFilter = Me.OpenArgs
    End Sub

    Sub Form_Load()
        Me!subformcontrolname.Form.Filter = mstrSubFormFilter
        Me!subformcontrolname.Form.FilterOn = True
    End Sub

Jim Kraxberger
Developing Access solutions since 1995

 

[reidgivens]

I used the second option and it works like a champ. Thanks alot.

 

[reidgivens]

Back to this again. Theis worked great, but now i need to have to open a report set up the same way. Almost the same way, i didn't do it with any subreports, but I guess I might have too.

How can I take this for-subfor-subsubform, and have it output it to a report.

Did that make any sence? Reid Givens
Webdeveloper

http://www.reidgivens.com

 

[reidgivens]

I currently tried to place a button with this procedure on it, but it doesn't work

-----------------
Private Sub invoice_Click()

Dim stDocName As String
Dim criteria As String
Dim custid As String
Dim vehid As String
Dim invid As String

custid = "[c_id_num]=" & Me![c_id_num]
vehid = "[v_id_num]=" & [Forms]![customers]![vehicles]![v_id_num]
invid = "[i_id_num]=" & [Forms]![customers]![vehicles]![invoice]![v_id_num]![i_id_num]

criteria = custid & "AND" & vehid & "AND" & invid

stDocName = "customers"

DoCmd.OpenReport stDocName, acPreview, , , , criteria

End Sub

-----------------------------


but this give me a this error: Proporty let procedure not defined and Proporty get procedure did not return an object


does this help any, or am I way off
Reid Givens
Webdeveloper

http://www.reidgivens.com