Solved

Code question for Access 2000

Posted on 2011-03-03
8
410 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 50

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

728 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