returning the searched for record (focused) + the others

[filfil]

I'm using this code:

DoCmd.OpenForm stDocName, , , "Contract_No = '" & Me.Contract_No & "' and Cont_Month=#" & Me.contMonth_view & "#"

to return the record according to the matched Contract_No & Cont_Month (Date)..

it does return the record, but only that record.. what i want is to return all records that match the Contract_No, but go to the record that match the Cont_Month, so that the user can go to next or previous records that have different Cont_Month.

note: each Contract_No has different Cont_Months.. no similarities << i don't know if saying this is important!

anyways, your help is appreciated.. thanks in advance ^^

 

[edtab]

Do a little research on the following and you'll get your answer:

- recordsetclone
- bookmark
- findfirst

 

[filfil]

thanks, i'll do so

 

[filfil]

ok, i'm using this code now, but it gives me "error: invalid qualifier" on line: Set rst = stDocName.RecordsetClone

Private Sub cmd_view_Click()

Dim stDocName As String
'Dim stLinkCriteria As String
'Dim rst As String
Dim rst As DAO.Recordset
Dim strCriteria As String

If IsNull(Me.contMonth_view) And Not IsNull(Me.Contract_No) Then
    'MyEmpID = Me.EmpID
    'rst = DLookup("UserName", "User", "[EmpID]=" & Me.EmpID.Value)
    Me.Visible = False
    'stDocName = "Frm_Admin_ViewByContNo"
    'DoCmd.OpenForm stDocName
    'stDocName's value is the form to be openned name.
    stDocName = "Frm_Cont_Monthly_Details_View"
    ' Pass the criteria as a string, by using quotes
    DoCmd.OpenForm stDocName, , , "Contract_No = '" & Me.Contract_No & "'"
    

ElseIf Not IsNull(Me.contMonth_view) And Not IsNull(Me.Contract_No) Then
    Me.Visible = False
    
    stDocName = "Frm_Cont_Monthly_Details_View"
    strCriteria = "[Cont_Month]=#" & Me.contMonth_view & "#"
    
    
    
    
    rst.FindFirst (strCriteria)
    If rst.NoMatch Then
        MsgBox "No entry found"
    Else
        DoCmd.OpenForm stDocName
        Set rst = stDocName.RecordsetClone
        stDocName.Bookmark = rst.Bookmark
    End If
    
    Set rst = Nothing
    
    ' Pass the criteria as a string, by using quotes
    ''''DoCmd.OpenForm stDocName, , , "Contract_No = '" & Me.Contract_No & "' and Cont_Month=#" & Me.contMonth_view & "#"
    'DoCmd.OpenForm stDocName, , , "Contract_No =" & Me.Contract_No
    '''DoCmd.OpenForm stDocName

ElseIf IsNull(Me.Contract_No) Then
  Me.Visible = True
  MsgBox "Please select a Contract Number to view its details", vbOKOnly, "Required Data"
  Me.Contract_No.SetFocus
End If

Debug.Print contMonth_view

End Sub

any help please?
thanks

 

[filfil]

sorry, it's solved .. may i should try more before posting!

Private Sub cmd_view_Click()

Dim stDocName As String
'Dim stLinkCriteria As String
'Dim rst As String
Dim rst As DAO.Recordset
'Dim rst As Object
Dim strCriteria As String

If IsNull(Me.contMonth_view) And Not IsNull(Me.Contract_No) Then
    'MyEmpID = Me.EmpID
    'rst = DLookup("UserName", "User", "[EmpID]=" & Me.EmpID.Value)
    Me.Visible = False
    'stDocName = "Frm_Admin_ViewByContNo"
    'DoCmd.OpenForm stDocName
    'stDocName's value is the form to be openned name.
    stDocName = "Frm_Cont_Monthly_Details_View"
    ' Pass the criteria as a string, by using quotes
    DoCmd.OpenForm stDocName, , , "Contract_No = '" & Me.Contract_No & "'"
    

ElseIf Not IsNull(Me.contMonth_view) And Not IsNull(Me.Contract_No) Then
    Me.Visible = False
    
    stDocName = "Frm_Cont_Monthly_Details_View"
    strCriteria = "[Cont_Month]=#" & Me.contMonth_view & "#"
    
    
    
    DoCmd.OpenForm stDocName, , , "Contract_No = '" & Me.Contract_No & "'"
    Set rst = Forms![Frm_Cont_Monthly_Details_View].RecordsetClone
    rst.FindFirst strCriteria
    If rst.NoMatch Then
        DoCmd.close
        DoCmd.OpenForm "Frm_Main_Admin"
        MsgBox "No entry found"
    Else
        
        
        Forms![Frm_Cont_Monthly_Details_View].Bookmark = rst.Bookmark
    End If
    
    Set rst = Nothing
    
    ' Pass the criteria as a string, by using quotes
    ''''DoCmd.OpenForm stDocName, , , "Contract_No = '" & Me.Contract_No & "' and Cont_Month=#" & Me.contMonth_view & "#"
    'DoCmd.OpenForm stDocName, , , "Contract_No =" & Me.Contract_No
    '''DoCmd.OpenForm stDocName

ElseIf IsNull(Me.Contract_No) Then
  Me.Visible = True
  MsgBox "Please select a Contract Number to view its details", vbOKOnly, "Required Data"
  Me.Contract_No.SetFocus
End If

Debug.Print contMonth_view

End Sub