Lookup and add

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have created a database in which the user will enter information into a
form and the record goes into a table. One of the fields on the form is Name.
As soon as the user enters a name, I would like for access to go to the table
of data that has been previously entered and compare what was entered on the
form to what is already in the list. By doing that, if the name entered on
the form is not in the table, I want a text box on the form to inform the
user that the name has been entered 0 times. And if the name is in the
table-let's say twice, I want for the text box to inform the user that the
name has been entered 2 times and this will be the 3rd time the name will be
put into the table.
Is that at all possible? Does anybody know where to start with something
like this?
All help is greatly appreciated.
 
Hi,
I have created a database in which the user will enter information into a
form and the record goes into a table. One of the fields on the form is Name.
As soon as the user enters a name, I would like for access to go to the table
of data that has been previously entered and compare what was entered on the
form to what is already in the list. By doing that, if the name entered on
the form is not in the table, I want a text box on the form to inform the
user that the name has been entered 0 times. And if the name is in the
table-let's say twice, I want for the text box to inform the user that the
name has been entered 2 times and this will be the 3rd time the name will be
put into the table.
Is that at all possible? Does anybody know where to start with something
like this?
All help is greatly appreciated.
Click to expand...

use DCount
 
Hi,
I have created a database in which the user will enter information into a
form and the record goes into a table. One of the fields on the form is Name.
As soon as the user enters a name, I would like for access to go to the table
of data that has been previously entered and compare what was entered on the
form to what is already in the list. By doing that, if the name entered on
the form is not in the table, I want a text box on the form to inform the
user that the name has been entered 0 times. And if the name is in the
table-let's say twice, I want for the text box to inform the user that the
name has been entered 2 times and this will be the 3rd time the name will be
put into the table.
Is that at all possible? Does anybody know where to start with something
like this?
All help is greatly appreciated.
Click to expand...

Yes; you can use the AfterUpdate event of the textbox on the form. You'ld use
code like

Private Sub txtName_AfterUpdate()
Dim iCount As Integer
iCount = DCount("*", "[tablename]", "[Name] = """ & Me!txtName & """")
Me!txtCount = iCount
End Sub

Note that Name is a reserved word and should not be used as a fieldname - if
you do so, you must *always* enclose it in square brackets. Note also that
names are not unique - you might have two people in the database named Jim
Smith; do you want those two records to show up as a count of two, or do you
want 2 to appear only if Jim Smith of 315 Popsqueak Lane is entered twice?

John W. Vinson [MVP]
 
Back
Top