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

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.
marlind605Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
to check if query returns records,
      if dcount("*","NameOfQuery")>0 then
           msgbox "There are records!"
      else
          msgbox "No Records"
     end if
0
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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
marlind605Author Commented:
I've been off for the holidays but getting back to the project today. I will be looking at the suggestions. Thanks.
0
marlind605Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.