Open new form based on two criteria

[cableguy47905 via AccessMonster.com]

I have a main form that I need to be able to open another form that is based
on two different fields on the main form.

This is what I have:

Private Sub cmdOpenForm_Click()
On Error GoTo Err_cmdOpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SFRM_AllLocationsperContract"

stLinkCriteria = (((TBL_LocationContract.ContractNumber) = "&[Forms]!
[FRM_CompanyAddContracts]![txtContractID]&") And ((TBL_LocationContract.
CompanyID) = "&[Forms]![FRM_CompanyAddContracts]![txtCompanyID]&"))
DoCmd.OpenForm stDocName, , , WhereCondition:=stLinkCriteria

Exit_cmdOpenForm_Click:
Exit Sub

Err_cmdOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenForm_Click

End Sub

I get the error that "Object is required"

I got the criteria from opening up the query and entering in the two fields
that are needed into the criteria. Then opened the query in SQL view and
copied that into the code. I know the syntax is off, but I can't seem to get
a good result.

I have also tried to put in :

stLinkCriteria = (("[ContractNumber] = &[Forms]![FRM_CompanyAddContracts]!
[txtContractID]&") And ("[CompanyID] = " & [Forms]![FRM_CompanyAddContracts]!
[txtCompanyID]))

into the stLinkCriteria, but then i get type mismatch. The ContractNumber is
text and CompanyID is a number.

Can someone help with this?

 

[Guest]

Cableguy,

I think you need single quotes around your text fields to indicate they are
text. I also find using a MsgBox statement very clarifying on criteria
strings just to make sure you haven't made a typo with the quotes, &
operators, etc.:

Try:

stLinkCriteria = (((TBL_LocationContract.ContractNumber) = " & "'" & [Forms]!
[FRM_CompanyAddContracts]![txtContractID]& "'" &") And
((TBL_LocationContract.
CompanyID) = " & "'" &[Forms]![FRM_CompanyAddContracts]![txtCompanyID] &
"'" &"))

' Temporary debugging code; delete when correct criteria string obtained
MsgBox stLinkCriteria

DoCmd.OpenForm stDocName, , , WhereCondition:=stLinkCriteria

Hope that helps.
Sprinks

 

[John W. Vinson]

stLinkCriteria = (((TBL_LocationContract.ContractNumber) = "&[Forms]!
[FRM_CompanyAddContracts]![txtContractID]&") And ((TBL_LocationContract.
CompanyID) = "&[Forms]![FRM_CompanyAddContracts]![txtCompanyID]&"))
DoCmd.OpenForm stDocName, , , WhereCondition:=stLinkCriteria

Exit_cmdOpenForm_Click:
Exit Sub

Err_cmdOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenForm_Click

End Sub

I get the error that "Object is required"

Well, your stLinkCriteria is total hash, I fear.

It's supposed to be a Text String containing a valid SQL WHERE clause. You
have ampersands (which concatenate text strings) inside text strings, table
references *outside* text strings, missing blanks...

Try

stLinkCriteria = "TBL_LocationContract.ContractNumber = " _
& [Forms]![FRM_CompanyAddContracts]![txtContractID] _
& " And TBL_LocationContract.CompanyID = " _
& [Forms]![FRM_CompanyAddContracts]![txtCompanyID]

This will evaluate to something like

TBL_LocationContract.ContractNumber = 318 And TBL_LocationContract.CompanyID =
225

which should find that contract and company if they exist.

This assumes that ContractNumber and CompanyID are both Number datatypes. If
one is text, you'll need to include ' delimiters; e.g. if ContractNumber is
actually a text field,

stLinkCriteria = "TBL_LocationContract.ContractNumber = '" _
& [Forms]![FRM_CompanyAddContracts]![txtContractID] _
& "' And TBL_LocationContract.CompanyID = " _
& [Forms]![FRM_CompanyAddContracts]![txtCompanyID]


John W. Vinson [MVP]