Share via

VBA stLinkCriteria for more than one match

Anonymous
2012-02-21T22:33:22+00:00

In the following VBA, I cannot get my first form to open a popup form, using two matching values from each form.  In the future, I may need to use more than two matching values between the two forms, and the use of the word And I hope to avoid.  If I had several matches required between two forms it may be cumbersome.  As it is, I did write this code; I customized it from some vba code from a database template.

Currently, I have two forms, whose source is the exact same SQL statement.  The first form is a "continous" and the next is a "single" mode popup.  On the first form, I am using objectA (a textbox with a number datatype, from the table).  ObjectA is a command button on the first form.  txt1 and txt2 are textboxes, each attached to different field in the database; they are both number datatypes.

Private Sub cmdObjectA_Click()

On Error GoTo Err_cmdObjectA_Click

    Dim stDocName As String

    Dim stLinkCriteria As String

    stDocName = "myPopupForm"

    stLinkCriteria = "[txt1]=" & Me![txt1] And "[txt2]=" & Me![txt2]

    ' DoCmd.Close

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdObjectA_Click:

    Exit Sub

Err_cmdObjectA_Click:

    MsgBox Err.Description

    Resume Exit_cmdObjectA_Click

End Sub

==============

example of many matches to open a popup form, becoming cumbersome

    stLinkCriteria = "[txt1]=" & Me![txt1] And "[txt2]=" & Me![txt2] And "[txt3]=" & Me![txt3] And "[txt4]=" & Me![txt4]

what if a textbox is blank, like [txt4]?

==============

VWP1  20120222 17:30

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-02-23T21:31:15+00:00

First I apologize for the very real arrogance in my reply. I was out of line, and I'm sorry.

I think the only problem remaining is that you need blanks around the word And:

stLinkCriteria = "[fieldname1]=" & Me![fieldname1] & " And " & "[fieldname2]=" & Me![fieldname2]

What I'm guessing is happening is that you're getting a stLinkCriteria like

[fieldname1] = 334And[fieldame2] = 218

The values and the operator are getting run together.

Was this answer helpful?

0 comments

Answer accepted by question author

Anonymous
2012-02-23T01:21:22+00:00

Sure. IF (and I'm making an assumption based on zero input from you, unfortunately) you have Table Fields in the Recordsource for myPopupForm named txt1 and txt2, AND you have two textboxes on this form named txt1 and txt2, then you should be able to use:

stLinkCriteria = "[txt1]=" & Me![txt1] & " AND [txt2]=" & Me![txt2]

If you want to allow for either textbox to be blank, and to retrieve all records for that field if it is blank, the code is a bit more complex:

If Len(Me!txt1 & "") > 0 Then

  stLinkCriteria = "[txt1] = " & Me!txt1

End If

If Len(Me!txt2 & "") > 0 Then

  If Len(stLinkCriteria)>0 Then

      stLinkCriteria = stLinkCriteria & " AND "

  End If

  stLinkCriteria = stLinkCriteria & "[txt2] = " & Me!txt2

End If

Was this answer helpful?

0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-02-21T23:02:57+00:00

    I guess I'm confused.  Are you saying that you want to open the form with This OR That?  So, like:

    stLinkCriteria = "[FieldNameHere] = " & Me.txt1 & " OR [FieldNameHere] = " & Me.txt2

    I put FieldNameHere because that is what you refer to that would be in the popup form's recordsource.  So, does your popup form really have a field in the recordsource named txt1?  I doubt it.  You are probably thinking about the control that is BOUND to that field.  You refer to the FIELD not the control (but you use the control name for the part to match the control value on the current form).

    Also, if the field is TEXT then you need Quotes:

    stLinkCriteria = "[FieldNameHere] = " & Chr(34) & Me.txt1 & Chr(34)& " OR [FieldNameHere] = " & Chr(34) & Me.txt2 & Chr(34)

    or if dates

    stLinkCriteria = "[FieldNameHere] = #" & Me.txt1 & "# OR [FieldNameHere] = #" & Me.txt2 & "#"

    Hope that helps.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-21T22:54:27+00:00

    This is an example of linking to the popup (Form2 and in bold) from the first form Me![whatever].  BUT...this is opening the popup form matching only one textbox of form1 to the same textbox value in the popup form.  I am looking to simply add additional matches.

    Private Sub cmdObjectA_Click()

    On Error GoTo Err_cmdObjectA_Click

    Dim stDocName As String

    Dim stLinkCriteria As String

    stDocName = "myPopupForm"

    stLinkCriteria = "[txt1]=" & Me![txt1]

    ' DoCmd.Close

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdObjectA_Click:

    Exit Sub

    Err_cmdObjectA_Click:

    MsgBox Err.Description

    Resume Exit_cmdObjectA_Click

    End Sub

     - - - -  stLinkCriteria = "[txt1]=" & Me![txt1] And "[txt2]=" & Me![txt2] - - -  is not working

    addendum: In my first inquiry, I meant to say I did not write the initial code (instead of "I did") ...sorry about that.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-21T22:48:25+00:00

    ME only refers to the form which the code is on.  If the values are from different forms, where is the reference to the other form or forms?

    Was this answer helpful?

    0 comments