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
284 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 119

Expert Comment

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

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now