Results 1 to 11 of 11
  1. #1
    perryp38 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    5

    How can I open a form based on a Visual Basic Query


    I have been assigned the task of fixing a problem with an Access 2010 Application build by a previous employee. Here is the design, There is a main menu that has a button to search by Req Number. Clicking this button will open a pop up form where you enter the ID number to search for. After entering the number and clicking the search button the code is supposed to open another form named frmDataEntry filtered by the ID number entered in the search box. The code runs without an error. The problem is it opens the DataEntry form and shows all records. I have included my code block beloe

    Code:
    Case "Req"            Me.cmdDone.SetFocus
                GotoSearchTextbox
                If IsNull(Me.txtSearchTerm) Then 'Blank, show them all!
                    StrSql = "SELECT tblOrders.*, tblfleetvendors.PVAdr1, tblfleetvendors.PVAdr2, tblfleetvendors.PVCity, tblfleetvendors.PVState, tblfleetvendors.PVZip"
                    StrSql = StrSql & " FROM tblOrders INNER JOIN tblfleetvendors ON tblOrders.VendorId = tblfleetvendors.VendorID"
                    StrSql = StrSql & " WHERE (((tblOrders.Org)=DLookUp(""[DivisionOrg]"",""tblSetup""))  AND ((tblOrders.VendorID <> 774)))"
                    Set db = CurrentDb()
                    Set qdf = db.QueryDefs("qryOrders")
                    qdf.SQL = StrSql
                    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"
                    rst.Close
                    Set rst = Nothing
                    Set db = Nothing
                    rst.Close
                    DoCmd.Close acForm, Me.Name
                    Exit Sub
                    Else
                    Form!frmDataEntry.RecordSource = StrSql
                    DoCmd.OpenForm "frmDataEntry"
                    DoCmd.Close acForm, Me.Name
                    DoCmd.Close acForm, "frmMainMenu"
                    End If
    
    
                End If
    Can someone help me out and tell me why this code does not work as ?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you make the query, then click the button that makes the form. It will put in all the fields.
    to open the form on 1 record, use the key, ex:

    docmd.openform "fMyForm",,,"[id]=" & txtID

  3. #3
    perryp38 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    5
    Okay I change my code to
    Code:
    DoCmd.OpenForm "frmDataEntry", , , "[OrderID=]" & Me.txtSearchTerm
    But now I get a blank page any idea why ?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Typo? Try "[OrderID]=" & Me.txtxSearch...
    Cheers,

  5. #5
    perryp38 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    5
    Not sure if it matters but my database is a SQL Server and not an Access Data Tables

  6. #6
    perryp38 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    5
    Made the change to use
    DoCmd.OpenForm "frmDataEntry", , , "[OrderID]=" & Me.txtSearchTerm

    but still am getting a blank scree for results

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your code is way more complex than it needs to be and I suspect you have turned off error reporting elsewhere in your code

    for this line to work

    Form!frmDataEntry.RecordSource = StrSql

    The form needs to be open - but you open it on the next line

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Second SQL needs a space in front of WHERE.


    Open form before setting its RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    perryp38 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    5
    Ajax thank you for your response. I appreciate you taking your time to assist with this problem. I do not normally program using Access VB, I mostly use C#. This is a legacy application that another developer created, who has now retired. I have been assigned the task of making the search function work.

    So based on your input I have made the change and the code is now finding the records as expected. Thanks again and please fill free to respond back with any suggestion to improve the code in any way. I have posted the working version of the code 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 'OrderId entered  do the search
                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
                    Else
                    DoCmd.Close acForm, Me.Name
                    DoCmd.OpenForm "frmDataEntry"
                    Form!frmDataEntry.RecordSource = StrSql
                    DoCmd.Close acForm, "frmMainMenu"
                    End If
                End If
    Last edited by perryp38; 07-20-2020 at 06:05 AM. Reason: spelling

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your dlookup implies there is only one record in tblSetup - so your sql would be more efficient if you included the table in your query. Probably won't have a huge difference



    Code:
    StrSql = "SELECT tblOrders.*, tblSetuptblFleetVendors.* FROM tblSetup, tblOrders INNER JOIN tblFleetVendors ON tblOrders.VendorId = tblFleetVendors.VendorID" 
                StrSql = StrSql & " WHERE tblOrders.Org=DivisionOrg"
                 StrSql = StrSql & "AND tblOrders.VendorId<>774 AND tblOrders.OrderID=" & Req 
    
    'create a recordset
    
    Set db = CurrentDb()
    set rst=db.openrecordset(strsql)
    'check for a record count
    rst.movenext
    if rst.recordcount=0 then
        MsgBox "There are no records that match your search"
        Set rst = Nothing
        Set db = Nothing
        rst.Close
        exit sub
    else
        'check if form is open, if not, open it
        If not CurrentProject.AllForms("frmDataEntry").IsLoaded then DoCmd.OpenForm "frmDataEntry"
        set forms!frmDataEntry.recordset=rst
       DoCmd.Close acForm, Me.Name
       DoCmd.Close acForm, "frmMainMenu"
    end if

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I open Access 2007 from a Visual Basic program?
    By Dick_in_Dallas in forum Access
    Replies: 3
    Last Post: 11-23-2014, 01:36 PM
  2. Replies: 2
    Last Post: 03-17-2014, 12:51 PM
  3. Replies: 1
    Last Post: 06-29-2011, 07:22 AM
  4. Button that open Visual Basic Editor?
    By 95DSM in forum Programming
    Replies: 5
    Last Post: 12-20-2010, 10:40 AM
  5. Query in visual basic
    By Lucas83 in forum Programming
    Replies: 1
    Last Post: 06-10-2010, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums