Solved

Code question for Access 2000

Posted on 2011-03-03
8
405 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 47

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

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.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

831 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