?
Solved

Open Form, only if matching records are available, +

Posted on 2005-03-24
10
Medium Priority
?
359 Views
Last Modified: 2008-03-10
I use the code below to open a single record view form. What additional code can I add to do two things.
1. If there are no matching records, a message pops up saying "No Matching Records", and the form doesn't open.
and
2.  If there are multiple matching records, a message pops up saying "Multiple Matching Records", and then the form opens upon aknowledgement .

.....................................
Private Sub Command602_Click()
On Error GoTo Err_Command602_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Keys1Form"
   
    stLinkCriteria = "[LastName]like" & "'" & "*" & Forms![Main1]![KeyFind] & "*" & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
       
Exit_Command602_Click:
    Exit Sub

Err_Command602_Click:
    MsgBox Err.Description
    Resume Exit_Command602_Click
   
End Sub
...........................................................

Thanks,
Mark

0
Comment
Question by:wesellnow
[X]
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
  • 7
  • 3
10 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13623216
Did the dcount in your previous not do the trick? You can modify it as follows:

If DCount("*","qryCancelContact",strLinkCriteria)=0 Then
   MsgBox "No matching records!"
ElseIf DCount("*","qryCancelContact",strLinkCriteria)=1 Then
   DoCmd.OpenForm "CancelContact1", , ,strLinkCriteria
Else
   MsgBox "Multiple matching records!"
   DoCmd.OpenForm "CancelContact1", , ,strLinkCriteria
End If
0
 

Author Comment

by:wesellnow
ID: 13626021
The DCount worked fine.

The issue is slightly different. Sometimes the name we search on when opening "Keys1Form" does not exist in the customer database. We need to not open the form in this case. The count we are working on now pertains to this form rather than the "CancelContact1" form.

Thanks,

<email address removed by OzzMod>

 
0
 

Author Comment

by:wesellnow
ID: 13626030
Reread the original question above and it will come clear to you.
Thanks,

<email address removed by OzzMod>

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13626082
If DCount("*","qryKeys1Form",strLinkCriteria)=0 Then
   MsgBox "No matching records!"
ElseIf DCount("*","qryKeys1Form",strLinkCriteria)=1 Then
   DoCmd.OpenForm "Keys1Form", , ,strLinkCriteria
Else
   MsgBox "Multiple matching records!"
   DoCmd.OpenForm "Keys1Form", , ,strLinkCriteria
End If

Replacing qryKeys1Form with the name of the query upon which Keys1Form is based.
0
 

Author Comment

by:wesellnow
ID: 13626596
I am having trouble integrating the code you have given me with what is in place now:

Private Sub Command602_Click()
On Error GoTo Err_Command602_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Keys1Form"
   
    stLinkCriteria = "[LastName]like" & "'" & "*" & Forms![Main1]![KeyFind] & "*" & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
       
Exit_Command602_Click:
    Exit Sub

Err_Command602_Click:
    MsgBox Err.Description
    Resume Exit_Command602_Click
   
End Sub
0
 

Author Comment

by:wesellnow
ID: 13626600
The underlying query for "Keys1Form" is Keys1
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 13626617
Replace this:
    DoCmd.OpenForm stDocName, , , stLinkCriteria

with:
If DCount("*","Keys1",strLinkCriteria)=0 Then
   MsgBox "No matching records!"
ElseIf DCount("*","Keys1",strLinkCriteria)=1 Then
   DoCmd.OpenForm stDocName, , ,strLinkCriteria
Else
   MsgBox "Multiple matching records!"
   DoCmd.OpenForm stDocName, , ,strLinkCriteria
End If
0
 

Author Comment

by:wesellnow
ID: 13626677
I am getting this segment highlighted:   strLinkCriteria

with error "variable not defined"

Thanks,

<email address removed by OzzMod>

0
 

Author Comment

by:wesellnow
ID: 13626694
I got it, just needed to remove the r from the strLinkCriteria

Works perfectly
0
 

Author Comment

by:wesellnow
ID: 13626709
Thanks for sticking with me on this. Busy day.

Mark
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

801 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