stLinkCriteria issue (1 Viewer)

[hardhitter06]

Hi,

I've attached two photos to make this explination a bit easier.

On my main form (when the database opens) allows the user to search by Fed ID using a textbox and a button with code which will return all records that match that FED ID and fall within a year from Today's date.

Photo 1 shows the results of Fed ID XXXX49175. These 4 records fall within a year from Today's date under that Fed ID. Also notice that some of the UNSPSC codes are different.

My goal/plan is to be able to click the "More UNSPSC Info" button which will return all records that match the previously searched FED ID along with the UNSPSC number within that line.

For example, under the 3/20/12 record, if I clicked "More UNSPSC Info", a second form (Photo 2) will return two records matching that Fed ID and UNSPSC number. If I clicked Record 2 or 3 from Photo one, only one record would be returned.

Here is the code for my button but I need help using the stLinkCriteria I believe.

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSplitOrderingSearchAdvance"
    strIdNumber = Me.UNSPSC
    stLinkCriteria = "[UNSPSC]"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command30_Click:
    Exit Sub

Err_Command30_Click:
    MsgBox Err.Description
    Resume Exit_Command30_Click
End Sub

Thank you for your help.

 

[spikepl]

So what is wrong with the documentation? Or the numerous examples on the web?

 

[hardhitter06]

I just don't get how to write that piece of code given my situation.

 

[Steve R.]

Here is the code for my button but I need help using the stLinkCriteria I believe.

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSplitOrderingSearchAdvance"
    strIdNumber = Me.UNSPSC
    stLinkCriteria = "[UNSPSC]"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command30_Click:
    Exit Sub

Err_Command30_Click:
    MsgBox Err.Description
    Resume Exit_Command30_Click
End Sub

You need to revise the code for stLinkCriteria. See the sample below for guidance. You would need to adapt the code to fit your situation. Specifically, use Access Help for how to use quotation marks. Looking up the syntax for Dlookup is helpful. For the stLinkCriteria to work you need to compare it to something. In your sample there is no comparison. In the sample below, DCMnum is a control on the form and DCMnum01 is the number that is being looked up. (Do not attempt to use the sample without modifying it for your situation.)

    strCriteriaPVT = "DCMnum = '" & Me.DCMnum01 & "'"    
    DoCmd.OpenForm csEditForm, acNormal, , strCriteriaPVT, , , strFormNamePVT

 

[hardhitter06]

So would my control be Fed ID or UNSPSC?

I use the Fed ID to get a list of records that may or maybe have the same UNSPSC numbers. From there within a specific line (record), I want to click the button to only pick out other records with that same UNSPSC under the FED ID.

 

[Steve R.]

I want to click the button to only pick out other records with that same UNSPSC under the FED ID.

Unless UNSPSC=FED_ID, this would seem to involve a "complex" structure for the selection criteria along the lines of "Select Those Records Where FED_ID = X AND UNSPSC =Y". Look in Access Help under Domain Aggregate Functions. Another one: "Restrict Data to a Subset of Records". The examples given for Dlookup may be your best bet.

 

[Simon_MT]

If you are trying to link to a Web page you have to decontruction the URL including the QueryString. Here is a simple example:

http://www.trevorsutton.com/Originals_Groupings.aspx?Grouping=09TSP&OG=1

This is represented

Function Web_OriginalsGrouping()
    With CodeContextObject
        Dim WebLink As String
        WebLink = GetWebPath & "Originals_Groupings.aspx?Grouping=" & .[Artist Grouping] & "&OG=1" & ""
        Application.FollowHyperlink WebLink, , True
        HideWebToolBar
    End With
End Function

Simon