Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Open Form, only if matching records are available, +

Posted on 2005-03-24
10
Medium Priority
?
364 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
  • 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 and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

577 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