How can I open a Form based on on a Query (1 Viewer)

ppierce

Registered User.
Local time
Today, 11:57
Joined
Nov 18, 2009
Messages
21
I have a search form used to search for an OrderID. When the user clicks a button on this form I want to open another form that will show the order details. My code is shown below.
Code:
Case "Req"
            Me.cmdDone.SetFocus
            GotoSearchTextbox
            If IsNull(Me.txtSearchTerm) Then 'Blank, show them all!
                DoCmd.OpenForm "frmDataEntry"
                DoCmd.Close acForm, Me.Name
                DoCmd.Close acForm, "frmMainMenu"
            Else
            Dim Req As Integer
            Req = Me.txtSearchTerm
             StrSql = "SELECT tblOrders.*, tblFleetVendors.* FROM tblOrders INNER JOIN tblFleetVendors ON tblOrders.VendorId = tblFleetVendors.VendorID"
             StrSql = StrSql & "WHERE (((tblOrders.Org)=DLookUp(""[DivisionOrg]"",""tblSetup""))"
             StrSql = StrSql & "AND ((tblOrders.VendorId)<>774) AND ((tblOrders.OrderID)=" & Req & "));"
                Set db = CurrentDb()
                Set qdf = db.QueryDefs("qryOrders")
                qdf.SQL = StrSql
                Set rst = db.OpenRecordset("qryOrders", dbOpenDynaset, dbSeeChanges)
                intRecords = rst.RecordCount
                If intRecords <= 0 Then
                MsgBox "There are no records that match your search"
                Set rst = Nothing
                Set db = Nothing
                rst.Close
                DoCmd.Close acForm, Me.Name
                Exit Sub
                'We have an OrderID to search
                Else
                DoCmd.Close acForm, Me.Name
                DoCmd.Close acForm, "frmMainMenu"
                DoCmd.OpenForm "frmOrder", _
                WhereCondition:="OrderID=" & Me.txtSearchTerm
              
                End If

            End If

The code compiles and I get no errors, however the results is just a blank screen. I have also tried using this code line but get the same results,
DoCmd.OpenForm "frmDataEntry", , , "OrderID" & Me.txtSearchTerm


What am I doing wrong?
 

Ranman256

Well-known member
Local time
Today, 11:57
Joined
Apr 9, 2015
Messages
4,339
make the query,
click the make form button. Done.

open the form on 1 record using 1 key:
docmd.openform "fMyForm",,,"[id]=" & txtID
 

azhar2006

Registered User.
Local time
Today, 08:57
Joined
Feb 8, 2012
Messages
202
What the colleague (Ranman256) said is very accurate. You can use this code
Code:
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmName"
   
    stLinkCriteria = "[ID]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Note that this section, I think, concerns the implementations of (VB.Net) and not (Access)
 

Users who are viewing this thread

Top Bottom