Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
316 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 40

Accepted Solution

by:
PatHartman earned 2000 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 52

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

783 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