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
288 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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