Solved

Code question for Access 2000

Posted on 2011-03-03
8
403 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
  • 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 45

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now