Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Code question for Access 2000

Posted on 2011-03-03
8
Medium Priority
?
413 Views
Last Modified: 2012-05-11
I need to find out why this code will not work in Access2000.

Private Sub Text3_Click()
If DCount("[lname]", "lookup_lname1") = 0 Then
    MsgBox "There are no records with that last name"
    Forms![new_records].Visible = True
    Exit Sub
Else
    DoCmd.OpenForm "main data input"

End If


End Sub

0
Comment
Question by:garycoon7
[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
  • 4
  • 3
8 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 35033875

You left out the lookup criteria on the DCount (see rewrite of code below).  I assume you entered the name you want looked up into the textbox on the form.  Proper naming convention encourages you to not use spaces in object names.  Have to enclose object names in brackets if it has spaces.  example         [main data input]       would be better this way......>>>MainDataInput


If DCount("[lname]", "lookup_lname1", [lname] = Forms![main data input]![Text3]) = 0 Then
0
 

Author Comment

by:garycoon7
ID: 35034009
I used the code and the checking did not work, so I went back to the other code and I get and error that says that Access cannot find the form "current record".
Any ideas?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 35035080
"lname" is the column name and "lookup_lname1" is the table name? Or should be the opposite?
If so then should be
If DCount("lookup_lname1", "[lname]") = 0 Then

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 35035705
You have a form named "current record"? Not according to your original post...and besides, that is an access reserved word and would not work anyways.  The syntax I gave you previously is correct, and should work if you have properly disclosed the names of your objects.  Please attach a copy of your databases so that it can be looked at.
0
 

Author Comment

by:garycoon7
ID: 35041798
I'm sorry I have caused so much trouble. I have been retired for 2 years and was trying to help out a friend.
I basically wanted to check my main table and see if a last name was present, if it was then I wanted to open a form to look at the data related to that last name.
If the last name was not present then I wanted to open a form for a new record and enter the data needed to fill in the main table.
I used code that had worked before but does not work now.
If anyone has any interest left I could sure you some help.
Thanks,
Gary Coon
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 35041854
Gary,
Your verbal statement of what you want to do is perfectly clear.  What you are not understanding is that you have to pass the name you want to look up to the DCount function . I assume that you are entering the lname on your form in a textbox control which you have named Text3.  You would then pass the name entered in the textbox to the DCount function.

Assuming that the object names you have given me are correct, all you have to is place your code in the the AfterUpdate event of the textbox control on the form.

Private Sub Text3_AfterUpdate()
     If DCount("[lname]", "lookup_lname1", [lname] = Me![Text3]) = 0 Then
                 MsgBox "There are no records with that last name"
                 Forms![new_records].Visible = True
                 Exit Sub
     Else
                 DoCmd.OpenForm "main data input"

      End If


End Sub

0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 2000 total points
ID: 35041889
Gary,
The thought just occurred to me that Text3 is probably an unbound textbox (not bound to a field in the table), and therefore the AfterUpdate event will never fire.  You have to use an event like the one shown below:

Private Sub Text3_Exit()
     If DCount("[lname]", "lookup_lname1", [lname] = Me![Text3]) = 0 Then
                 MsgBox "There are no records with that last name"
                 Forms![new_records].Visible = True
                 Exit Sub
     Else
                 DoCmd.OpenForm "main data input"

      End If


End Sub

0
 

Author Closing Comment

by:garycoon7
ID: 35043984
Thank you for working with me on this. This is a simple lookup method and is easy to use.
I put it in a combo box and used an on click property to fire it. Works well.
Have a great weekend and I hope you enjoy programming for many years to come.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

688 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