Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Microsoft Access 2013 Check if query has more then one record if so display form if not populate record.

Posted on 2014-12-19
5
289 Views
Last Modified: 2014-12-30
My database tracks activities with request from many customers. The customer could have just one prior activity or have many. I had choose to have dates displayed let the user choose the date. Then a pop up form would be displayed giving the projects for that customer just in case the customer has more than one project.  The user would select the proper record then the original form would be populated. Works fine but the client has requested that if there is only one project after the customer selects the date the form should be populated with that data. I have the query qryrequestdetsum for the underline form that pops up. How do I structure the code to say if qryrequestdetsum>1 then form Request Details open otherwise populate the fields I need. Requestnumber, requestdetails, dateofrequest, etc. Thanks for the help.
0
Comment
Question by:marlind605
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40509474
to check if query returns records,
      if dcount("*","NameOfQuery")>0 then
           msgbox "There are records!"
      else
          msgbox "No Records"
     end if
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40509819
I do this in several search forms.  The code goes into the Load event of the "list" form so the originating form always calls the list form and the list form either opens itself or opens the single record form and closes itself.  The following event is in a subform that is in datasheet view.  The main form is unbound.  I use unbound main forms to provide header/footer for DS view subforms since DS view does not support headers/footers directly.
Private Sub Form_Load()

    Dim rs As DAO.Recordset
    
'make flow smoother by eliminatining opening the search results form if only one client was found.

    Set rs = Me.RecordsetClone
    If rs.RecordCount = 1 Then
        DoCmd.OpenForm "frmClients", acNormal, , , , , "frmClientSearch" & ";" & Me.ClientID
        DoCmd.Close acForm, Me.Parent.name, acSaveNo
    End If
End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40510663
Something like this:

Private Sub Form_Load()

    Dim dbs As DAO.Database
    Dim qdy As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryRequestdetsum")
    If rst.RecordCount = 0 Then
        ' No records.
    Else
        rst.MoveLast
        If rst.RecordCount = 1 Then
            Me!txtRequestnumber.Value = rst!Requestnumber.Value
            Me!txtRequestdetails.Value = rstRequestdetails.Value
            Me!txtDateofrequest.Value = rst!Dateofrequest.Value
            ' etc.
        Else
            DoCmd.OpenForm "Request Details"
        End If
    End If
    rst.Close

    Set rst = Nothing
    Set dbs = Nothing

End Sub
0
 

Author Comment

by:marlind605
ID: 40521643
I've been off for the holidays but getting back to the project today. I will be looking at the suggestions. Thanks.
0
 

Author Closing Comment

by:marlind605
ID: 40524401
This solution worked great the first time. Thanks. Should save the user some clicks. I appreciate the other two replies but they just didn't quite work as nice as this did.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question