Is it really so Difficult

[RICHARD BROMBERG]

For the last couple of days I have been trying to learn how to solve this
problem.
I'm using Access 2000

I am posting it again and maybe I will be mofe successful.

I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

I am somewhat of a novice so the more complete the answer is the better
chance I have to understand and learn and understand it.

I would really appreciate the simplest way of doing it.

It certainly doesn't look like rocket science, but with the loads of options
abailable

thanks

It has been suggested that I must use Tools : References to include a
reference to a library, Which One ?

Again thanks

[Allen Browne]

Use DLookup() to see if the value already exists in the table.
Details in:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

You need to add a reference only if your code will not compile
(Compile on the Debug menu, in the code window.)
The most likely reference you are missing in A2000 would be:
Microsoft DAO 3.6
More info in:
Solving problems with Library References
at:
http://allenbrowne.com/ser-38.html

And no, it's not so difficult. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RICHARD BROMBERG" <no*****@att.netwrote in message
news:dc*********************@bgtnsc05-news.ops.worldnet.att.net...

For the last couple of days I have been trying to learn how to solve this
problem.
I'm using Access 2000

I am posting it again and maybe I will be mofe successful.

I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

I am somewhat of a novice so the more complete the answer is the better
chance I have to understand and learn and understand it.

I would really appreciate the simplest way of doing it.

It certainly doesn't look like rocket science, but with the loads of
options
abailable

thanks

It has been suggested that I must use Tools : References to include a
reference to a library, Which One ?

Again thanks

[fredg]

On Tue, 25 Jul 2006 03:27:05 GMT, RICHARD BROMBERG wrote:

For the last couple of days I have been trying to learn how to solve this
problem.
I'm using Access 2000

I am posting it again and maybe I will be mofe successful.

I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

I am somewhat of a novice so the more complete the answer is the better
chance I have to understand and learn and understand it.

I would really appreciate the simplest way of doing it.

It certainly doesn't look like rocket science, but with the loads of options
abailable

thanks

It has been suggested that I must use Tools : References to include a
reference to a library, Which One ?

Again thanks

Why not simply use an unbound Combo box.
Set it's rowsource to something like:
Select TableName.[PrimeKeyField] from TableName Order By
[PrimeKeyField];

Change TableName and PrimeKeyField to whatever your actual table and
field names are.

If the PrimeKey value exists it will appear in the combo drop-down.
What do you want to do now?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Lyle Fairfield]

RICHARD BROMBERG wrote:

I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

It has been suggested that I must use Tools : References to include a
reference to a library, Which One ?

The Dummy library. This is where the suggestor has been hanging out.

[ManningFan]

Maybe he just wants to do something pointless. I'd imagine being a
rude know-it-all is pointless to most folks, but you seem to have that
one down to a science.

To the OP, I'd take Allen's suggestion and use a DLookup.

Lyle Fairfield wrote:

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

[pedro8ae]

Fred, what about if this table I am trying to pull up has classes and
all what I want with this form is find a class (using prime key) and
show the rest of the fields on the same form. example the combo box
will ask you for a class name and if it is found will display all the
fields related to this class..

can I use the same procedure with rowsource? if not, any
recommendation?

thanks,


fredg wrote:

On Tue, 25 Jul 2006 03:27:05 GMT, RICHARD BROMBERG wrote:

For the last couple of days I have been trying to learn how to solve this
problem.
I'm using Access 2000

I am posting it again and maybe I will be mofe successful.

I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

I am somewhat of a novice so the more complete the answer is the better
chance I have to understand and learn and understand it.

I would really appreciate the simplest way of doing it.

It certainly doesn't look like rocket science, but with the loads of options
abailable

thanks

It has been suggested that I must use Tools : References to include a
reference to a library, Which One ?

Again thanks

Why not simply use an unbound Combo box.
Set it's rowsource to something like:
Select TableName.[PrimeKeyField] from TableName Order By
[PrimeKeyField];

Change TableName and PrimeKeyField to whatever your actual table and
field names are.

If the PrimeKey value exists it will appear in the combo drop-down.
What do you want to do now?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Tim Marshall]

Lyle Fairfield wrote:

>>I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

What about something like a library or inventory search system?
Especially if one is using natural primary keys that match an actual
part number or library catalog number.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David Gatheral]

Lyle Fairfield wrote:

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

What a pointless post.
DG.

[pedro8ae]

David I do not see right your reference to this post, this is a open
forum and not an expert forum, thank to people like you with a level of
expertice others like me are able to get knowledge by trying new
products and making this community bigger. I encourage everybody to
keep going with questions and dont give up . there is no dum questions
just dummy answers.

PO.
David Gatheral wrote:

Lyle Fairfield wrote:

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

What a pointless post.
DG.

[Lyle Fairfield]

Tim Marshall wrote:

Lyle Fairfield wrote:

>I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

What about something like a library or inventory search system?
Especially if one is using natural primary keys that match an actual
part number or library catalog number.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Well, I can understand that but rather than just entering a number,
wouldn't it be more efficient to choose a number with a combox box, or
to use some progressive search/match procedure such as one that
sequentially narrows down what one is looking for:
1
12
123
rather than typing in a number
123456
finding it doesn't exist, and then trying 123457 ... etc.

If we actually knew the purpose for which the OP wants to to use what
he describe we might be able to suggest a better solution. But we
don't.

[fredg]

On 25 Jul 2006 07:35:46 -0700, pe******@gmail.com wrote:

Fred, what about if this table I am trying to pull up has classes and
all what I want with this form is find a class (using prime key) and
show the rest of the fields on the same form. example the combo box
will ask you for a class name and if it is found will display all the
fields related to this class..

can I use the same procedure with rowsource? if not, any
recommendation?

thanks,

fredg wrote:

>On Tue, 25 Jul 2006 03:27:05 GMT, RICHARD BROMBERG wrote:

>>For the last couple of days I have been trying to learn how to solve this
problem.
I'm using Access 2000

I am posting it again and maybe I will be mofe successful.

I have a text box and a table. I want to enter a number into the text box
and using this number determine if there is a record in the table with a
primary key that matches it.

I am somewhat of a novice so the more complete the answer is the better
chance I have to understand and learn and understand it.

I would really appreciate the simplest way of doing it.

It certainly doesn't look like rocket science, but with the loads of options
abailable

thanks

It has been suggested that I must use Tools : References to include a
reference to a library, Which One ?

Again thanks

Why not simply use an unbound Combo box.
Set it's rowsource to something like:
Select TableName.[PrimeKeyField] from TableName Order By
[PrimeKeyField];

Change TableName and PrimeKeyField to whatever your actual table and
field names are.

If the PrimeKey value exists it will appear in the combo drop-down.
What do you want to do now?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Add a combo box to the Form Header.
Use the Combo Wizard to create the combo.
Select the 3rd option on the first page of questions, something like
"Find a record ... etc.."

When done, start entering the class. When you have the correct class,
press Enter. The combo will then find the record associated with that
class.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Lyle Fairfield]

David Gatheral wrote:

Lyle Fairfield wrote:

Why? Tell us what you want to accomplish with this. As it stands it
sounds like pointless idiocy.

What a pointless post.

There is a point. The point is that it's not so respectful to help
someone do something foolish, when one can help one to do something
wise.
Doing so simply reenforces their poor practice; it adds to the body of
knowledge that is the CDMA archives, information and procedures that
are suspect and inefficient.

[Rich P]

Hi Richard,

I will give your question a shot. First, I am assuming that the textbox
you are referring to is a textbox on a form into which you want to enter
a number - and ID number perhaps, and you want to check if there is a
record in a table with that ID - and I assume that the table is not a
recordsource for the form. My answer to your question - which I
interpret to be "how is this done?" will be based on the assumptions I
have stated. Note: if you scenario is different than what I have
described, you need to specify what your scenario is.

So, in the form that contains your textbox - call it Text0, you can add
a command button. In the command button click event you can add this
code:

Private Sub Command4_Click()
Dim i As Integer
On Error Resume Next
i = DLookup("ID", "Table1", "ID = " & Text0)
If i <0 Then MsgBox "Yes exists"
If i = 0 Then MsgBox "Not exists"

End Sub

This is a real basic searcher using the Access builtin DLookUp function.
The DLookUp function has to be set equal to something. Here I am
assuming your table has an ID field (the key field) which would be an
integer (but it could be alpha numeric also - just my example is using
an integer). So I have a variable i, which is defined as an integer.
The DLookup function takes 3 argument - the field you want to search,
the table which contains this field, and the criteria for your search.

"ID", "Table1", "ID = " & Text0

Note: when I was starting out with Access the Tooltip help text for the
Dlookup function always used to confuse me. It would stay "Expr As
String", "Domain As String", "Criteria As String". I could figure out
Criteria, but Expr means the field you are searching on. Domain means
the table (in VB.Net they made things way more Plain English - it is
just OOP based).

For the criteria I have "ID = " & Text0. I could have hardcoded the
value for the criteria
"ID = 12345", but then you can't change the criteria. So you specify
part of the criteria inside the quotes
"ID = "
then you concatenate the variable part Text0 using the & ampersand
symbol. Text0 can be anything, 1, 2, 3, 123, 12345, abc...Note: when
using chars for the criteria you have to delimit chars with single
quotes. Now your criteria would look like this:

"ID = '" & Text0 & "'"

You don't have to do this for numbers. And for dates you delimit dates
with the # pound symbol.

"ID = #" & txtDate & "#"

Then I also use the statement "ON Error Resume Next". If the value you
are searching does not exist, DLookUp will throw an error. If it throws
an error you trap the error using the On Error statement and then tell
it what to do - Go to a label "On Error GoTo lblErr" or just ignore the
error "On Error Resume Next". Here I ignore the error and just check if
i received a value or not. If yes - state yes If i <0 then MsgBox
"Yes Exists"

The only downside with this simple example is if you are searching for
0. The problem is that i will be 0 by default. If you don't find a 0,
i will still be 0. A more sophisticated version of this example would
be a boolean search or you could change the error trap to go to a lable
"on error goto errlbl

Private Sub Command4_Click()
Dim i As Integer
On Error GoTo Errlbl
i = DLookup("ID", "Table1", "ID = " & Text0)
MsgBox "Yes Exists"
Exit Sub

Errlbl:
MsgBox "Not Exists"
End Sub

This example will tell you if there is no 0 because you will get an
error - then go to the Errlbl. If the number does exist then it doesn't
go to the Errlbl - if you did not forget to use the "Exit Sub" statement
before the Errlbl. If you omit the "Exit Sub" statement then even if
there was no error the code will still go to the Errlbl because you did
not exit the subroutine yet. Whenever you use On Error GoTo someLabel -
you must always include "Exit Sub". Also, when using a lable, you have
to follow it with a : semicolon.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***