Open Where command button with a two field primary key

[SOMSteve]

In establishing a request to move items out of a particular office, a Disposal Request form is filled out. The table "Disposal Request" is keyed off the Office # and the Disposal Request # (Disposal Request # starts at 1 for each office, thus it can't be a standalone key). The Item Table has both Disposal Req # and Disposal Req Agency as foreign keys. I am trying to open the "frmItem" using a button and have got stuck on how to compose the Where statement based off both fields. I have tried a number of different approaches, but none have worked. Being new to coding has also limited my options to only very basic ones. I've copied my code below (with my attempt to add the second field to the Where expression).

Also, I want to set the default values for those two fields to equal the Disposal Request form's values so users can just key in items to the items form if they open it from the Disposal Request form (not the only way they can access the Item form). I would set the default values on the Items form to equal the Disposal Request form, but items also come in from two other sources. That's why I'm trying to put this in the code. A sub form would work, but I'm trying to avoid this b/c there are a lot fields in the Item table.

I've looked on the forum for help on these issues but wasn't able to find anything I could directly apply, if I missed something feel free to point me in the right direction.

I'm running windows 2000 SP-3.

Private Sub cmdAddItems_Click()
On Error GoTo Err_cmdAddItems_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmItem"
    
    stLinkCriteria = "[DisposalReqOffice] & [DisposalReqNum]=" & Me![cboOfficeNum] & Me![txtDisposalReqNum]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddItems_Click:
    Exit Sub

Err_cmdAddItems_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddItems_Click
    
End Sub

Any help would be appreciated, thanks!

-Steve

 

[PHV]

What about this ?
stLinkCriteria = "DisposalReqOffice=" & Me![cboOfficeNum] & " AND DisposalReqNum=" & Me![txtDisposalReqNum]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

Still doing the same thing, it just opens the frmItem to a blank form where I can add new records. Any other ideas suggestions?

 

[PHV]

OOps, sorry, misunderstood your issue.
What about something like this ?
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoEvents
With Forms(stDocName)
!DisposalReqOffice.DefaultValue = Me![cboOfficeNum]
!DisposalReqOffice.Value = Me![cboOfficeNum]
!DisposalReqNum.DefaultValue = Me![txtDisposalReqNum]
!DisposalReqNum.Value = Me![txtDisposalReqNum]
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

You were on the right track with both responses, I had two separate issues;
-the first was opening frmItem to display only records that matched frmDisposalRequest on two fields (DisposalReqOffice and DisposalReqNum)
-the second was setting the default value of the form to equal the fields cboDisposalReqOffice and txtDisposalReqNum on frmDisposalRequest (but only when frmItem is opened from frmDisposalRequest, otherwise the fields have no default value).

On your first posting, I wasn't able to get the criteria to work so even after using your recommendation it didn't bring up records on frmItem that matched those on frmDisposalRequest.

On your second posting about setting the default values it looked good, but I got the error message "Object doesn't support this property or method."

Both of your suggestions were exactly what I was looking for (despite me not making it too clear), but so far neither issue has been resolved. I've posted my updated code with your recommendations. Any other ideas on how to fix these two issues?

Private Sub cmdAddItems_Click()
On Error GoTo Err_cmdAddItems_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmItem"
    'Attempting to only open frmItem to fields that match frmDisposalRequest on these two fields
    stLinkCriteria = "DisposalReqOffice=" & Me![cboOfficeNum] & " AND DisposalReqNum=" & Me![txtDisposalReqNum]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    'Attempting to set the default values of DisposalReqOffice &
    'DisposalReqNum on frmItem to match them on frmDisposalRequest
    DoEvents
    With Forms(stDocName)
      !DisposalReqOffice.DefaultValue = Me![cboOfficeNum]
      !DisposalReqOffice.Value = Me![cboOfficeNum]
      !DisposalReqNum.DefaultValue = Me![txtDisposalReqNum]
      !DisposalReqNum.Value = Me![txtDisposalReqNum]
    End With

Exit_cmdAddItems_Click:
    Exit Sub

Err_cmdAddItems_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddItems_Click
    
End Sub

Thanks for all your help so far!

 

[PHV]

Does frmItem have controls named DisposalReqOffice and DisposalReqNum ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

Yes; the Item table (upon which frmItem is based) has DisposalReqOffice and DisposalReqNum as fields that come from the Disposal Request table as foreign keys. In the form these two fields have "cbo" in front of their name, but are otherwise the same.

 

[PHV]

So, seems you wanted this:
With Forms(stDocName)
!cboDisposalReqOffice.DefaultValue = Me![cboOfficeNum]
!cboDisposalReqOffice.Value = Me![cboOfficeNum]
!cboDisposalReqNum.DefaultValue = Me![txtDisposalReqNum]
!cboDisposalReqNum.Value = Me![txtDisposalReqNum]
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

Sorry for the delay in responding, I was out over the weekend and just got back. I tried the new coding and it works for the most part. There are two problems that I'm trying to overcome now:
[tab]1) It only opens to one item that matches based on the criteria of disposal request number and agency number (it is always the most recent item added that matches).
[tab]2) The default values of Agency Number and Disposal Request Number are only set when the item form is opened with no matching records. After a record is added to the Item table the default value for Agency Number still accurate, but the one for Disposal Request no longer matches.

Any ideas on why this is? I'm going to keep trying to find a solution, but any more guidance would be appreciated. Thanks again for all your help!

-Steve