Code question for Access 2000

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

garycoon7Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
puppydogbuddyConnect With a Mentor Commented:
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
 
puppydogbuddyCommented:

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
 
garycoon7Author Commented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
"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
 
puppydogbuddyCommented:
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
 
garycoon7Author Commented:
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
 
puppydogbuddyCommented:
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
 
garycoon7Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.