Help with Record Not On File

[Guest]

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Depends on how you are doing your look up. Post your code so we can give a
good response.

 

[Guest]

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

 

[Guest]

Okay, that is a start, but how do you run the query?

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

Depends on how you are doing your look up. Post your code so we can give a
good response.

 

[Guest]

Sorry to appear so stupid, but as far as Access goes, I am. The query is the
Record Source of a form. When I click on the form, the paramete box pops ups.
Appreciate your patience.

RandyM

Okay, that is a start, but how do you run the query?

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Okay, we can make this work. It will be a good learning experience for you.
First, take the Where condition out of your query. We are going to do this a
little differently.
With no further changes, the form will open with the first record in the
recordset displayed in your form. You will be able to navigate through the
records using the navigation buttons. Of course, what you want to do is to
be able to find a specific tag number. For that, we are going to add a combo
box to the form header section of your form. It will be an unbound control.
It's row source should be "SELECT TagNum FROM tblAnimals"

Now, when you open the form and dropdown the combo box, you will have a list
of all tag numbers. The user can either enter a number or select from the
list. Once they have made a selection, we will need some VBA code in the
After Update event of the combo box to do the lookup and tell us if the tag
number is not found:

For example purposes, we will assume the name of the combo box to be cboTagNum

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "TagNum = " & Me.cboTagNum
If rst.NoMatch Then
MsgBox "Tag Number " & Me.cboTagNum & " Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Now, this is the simplest form of doing this. There are several other
things that can be done, but first, see if you can get this to work for you.

Okay, that is a start, but how do you run the query?

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Klatuu,
It must be a wonderful feeling to know what you're doing! This works
like a charm. I know you said this is the simplest form of doing this, so I'm
all ears for what else you can do. I figure the more I can learn about the
different ways to do the same thing, can only help me in the long run. I'm
all yours for as long as you want to teach me. As the 'grasshopper ' in the
old KungFu TV series would say, "Teach me Master."

Thanks so much,
RandyM

Okay, we can make this work. It will be a good learning experience for you.
First, take the Where condition out of your query. We are going to do this a
little differently.
With no further changes, the form will open with the first record in the
recordset displayed in your form. You will be able to navigate through the
records using the navigation buttons. Of course, what you want to do is to
be able to find a specific tag number. For that, we are going to add a combo
box to the form header section of your form. It will be an unbound control.
It's row source should be "SELECT TagNum FROM tblAnimals"

Now, when you open the form and dropdown the combo box, you will have a list
of all tag numbers. The user can either enter a number or select from the
list. Once they have made a selection, we will need some VBA code in the
After Update event of the combo box to do the lookup and tell us if the tag
number is not found:

For example purposes, we will assume the name of the combo box to be cboTagNum

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "TagNum = " & Me.cboTagNum
If rst.NoMatch Then
MsgBox "Tag Number " & Me.cboTagNum & " Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Now, this is the simplest form of doing this. There are several other
things that can be done, but first, see if you can get this to work for you.

Okay, that is a start, but how do you run the query?

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

:

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Flattery will get you anywhere

Even when I use the wrong field names, you get it right!

Well, it depends on what you want to do. Let's say, for example, you want
to include the Animal's name it the dropdown so the user can see it. (I don't
know whether this is of any value to you, but it is an example).
First, add the field for the animal's name to the query you use for the
combo's row source. Then, make the combo's Column Count 2. You will also
have to set your column widths to make them wide enough to view in drop down
mode. Let's say you need 1/2 " for the number and 1.5" for the Name. It
would be .5";1.5"

Now the user can see both the tag number and the animal's name.

Klatuu,
It must be a wonderful feeling to know what you're doing! This works
like a charm. I know you said this is the simplest form of doing this, so I'm
all ears for what else you can do. I figure the more I can learn about the
different ways to do the same thing, can only help me in the long run. I'm
all yours for as long as you want to teach me. As the 'grasshopper ' in the
old KungFu TV series would say, "Teach me Master."

Thanks so much,
RandyM

Okay, we can make this work. It will be a good learning experience for you.
First, take the Where condition out of your query. We are going to do this a
little differently.
With no further changes, the form will open with the first record in the
recordset displayed in your form. You will be able to navigate through the
records using the navigation buttons. Of course, what you want to do is to
be able to find a specific tag number. For that, we are going to add a combo
box to the form header section of your form. It will be an unbound control.
It's row source should be "SELECT TagNum FROM tblAnimals"

Now, when you open the form and dropdown the combo box, you will have a list
of all tag numbers. The user can either enter a number or select from the
list. Once they have made a selection, we will need some VBA code in the
After Update event of the combo box to do the lookup and tell us if the tag
number is not found:

For example purposes, we will assume the name of the combo box to be cboTagNum

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "TagNum = " & Me.cboTagNum
If rst.NoMatch Then
MsgBox "Tag Number " & Me.cboTagNum & " Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Now, this is the simplest form of doing this. There are several other
things that can be done, but first, see if you can get this to work for you.

Okay, that is a start, but how do you run the query?

:

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

:

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Klatuu,
You can't imagine how much help you have been. I am deeply grateful for
your assistance. I hope it's not presumptious of me to ask you some questions
about what I just did, but that's the only way I know to learn - if you
don't know something and somebody else does and is willing to impart that
knowledge, don't be embarrassed to ask. What is the purpose of the 'Set rst =
Nothing'? I pretty much understand the rest, but that throws me. Also, when I
place the combo box on the form, it automatically puts some of what you had
already in the After Update event. It adds an If Not rs.EOF Then Me.Bookmark
= rs.Bookmark which I commented out. I'm not completely sure of why this
isn't needed anymore. Could you enlighten me on that? And, finally my VBA
code uses rs instead of rst, is this because of the ADO or DAO? As I said,
I'm still learning about Access. I was a Cobol programmer for almost 10 years
and then became a full-time musician for another 10 years and I'm just now
getting back into the 'wonderful world of computers'. I thank you for your
time, your patience, and your willingness to impart your knowledge to us
'newbies'.

Thanks again,
RandyM

Flattery will get you anywhere

Even when I use the wrong field names, you get it right!

Well, it depends on what you want to do. Let's say, for example, you want
to include the Animal's name it the dropdown so the user can see it. (I don't
know whether this is of any value to you, but it is an example).
First, add the field for the animal's name to the query you use for the
combo's row source. Then, make the combo's Column Count 2. You will also
have to set your column widths to make them wide enough to view in drop down
mode. Let's say you need 1/2 " for the number and 1.5" for the Name. It
would be .5";1.5"

Now the user can see both the tag number and the animal's name.

Klatuu,
It must be a wonderful feeling to know what you're doing! This works
like a charm. I know you said this is the simplest form of doing this, so I'm
all ears for what else you can do. I figure the more I can learn about the
different ways to do the same thing, can only help me in the long run. I'm
all yours for as long as you want to teach me. As the 'grasshopper ' in the
old KungFu TV series would say, "Teach me Master."

Thanks so much,
RandyM

Okay, we can make this work. It will be a good learning experience for you.
First, take the Where condition out of your query. We are going to do this a
little differently.
With no further changes, the form will open with the first record in the
recordset displayed in your form. You will be able to navigate through the
records using the navigation buttons. Of course, what you want to do is to
be able to find a specific tag number. For that, we are going to add a combo
box to the form header section of your form. It will be an unbound control.
It's row source should be "SELECT TagNum FROM tblAnimals"

Now, when you open the form and dropdown the combo box, you will have a list
of all tag numbers. The user can either enter a number or select from the
list. Once they have made a selection, we will need some VBA code in the
After Update event of the combo box to do the lookup and tell us if the tag
number is not found:

For example purposes, we will assume the name of the combo box to be cboTagNum

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "TagNum = " & Me.cboTagNum
If rst.NoMatch Then
MsgBox "Tag Number " & Me.cboTagNum & " Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Now, this is the simplest form of doing this. There are several other
things that can be done, but first, see if you can get this to work for you.

:

Okay, that is a start, but how do you run the query?

:

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

:

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Same steps, different order. I was a road musician for 15 years, then became
a COBOL programmer, then got into mini computer, then DOS applications,
followed closely by Windows apps.
Anyway.
Set rst = nothing releases the reference to the recordset. It is just a
good practice.
you can use rs instead of rst. It is only a name. If I have multiple
recordsets to work with I use descriptive names.
Dim rstMaster As Recordset
Dim rstDetail As Recordset
etc.

The If rs.EOF is checking for End of File. I prefer the IF rs.NoMatch which
returns True if the value passed was not found and False if it was. If a
search is performed and no match is found, the recordset will probably be and
EOF, but just in case it is not, the NoMatch is preferred.

I haven't had a lot of experience with ADO, so I am not sure whether there
are differences that would affect this code or not. This code is based on
DAO. In any case, you do need the Me.Bookmark = rs.Bookmark to make the
record found the current record for your form.

I'm pretty sure why you left music to come back to computers. It has to do
with the old joke:
"What is the difference between a large pizza and a musician?"

"A large pizza can feed a family of four."

Klatuu,
You can't imagine how much help you have been. I am deeply grateful for
your assistance. I hope it's not presumptious of me to ask you some questions
about what I just did, but that's the only way I know to learn - if you
don't know something and somebody else does and is willing to impart that
knowledge, don't be embarrassed to ask. What is the purpose of the 'Set rst =
Nothing'? I pretty much understand the rest, but that throws me. Also, when I
place the combo box on the form, it automatically puts some of what you had
already in the After Update event. It adds an If Not rs.EOF Then Me.Bookmark
= rs.Bookmark which I commented out. I'm not completely sure of why this
isn't needed anymore. Could you enlighten me on that? And, finally my VBA
code uses rs instead of rst, is this because of the ADO or DAO? As I said,
I'm still learning about Access. I was a Cobol programmer for almost 10 years
and then became a full-time musician for another 10 years and I'm just now
getting back into the 'wonderful world of computers'. I thank you for your
time, your patience, and your willingness to impart your knowledge to us
'newbies'.

Thanks again,
RandyM

Flattery will get you anywhere

Even when I use the wrong field names, you get it right!

Well, it depends on what you want to do. Let's say, for example, you want
to include the Animal's name it the dropdown so the user can see it. (I don't
know whether this is of any value to you, but it is an example).
First, add the field for the animal's name to the query you use for the
combo's row source. Then, make the combo's Column Count 2. You will also
have to set your column widths to make them wide enough to view in drop down
mode. Let's say you need 1/2 " for the number and 1.5" for the Name. It
would be .5";1.5"

Now the user can see both the tag number and the animal's name.

Klatuu,
It must be a wonderful feeling to know what you're doing! This works
like a charm. I know you said this is the simplest form of doing this, so I'm
all ears for what else you can do. I figure the more I can learn about the
different ways to do the same thing, can only help me in the long run. I'm
all yours for as long as you want to teach me. As the 'grasshopper ' in the
old KungFu TV series would say, "Teach me Master."

Thanks so much,
RandyM

:

Okay, we can make this work. It will be a good learning experience for you.
First, take the Where condition out of your query. We are going to do this a
little differently.
With no further changes, the form will open with the first record in the
recordset displayed in your form. You will be able to navigate through the
records using the navigation buttons. Of course, what you want to do is to
be able to find a specific tag number. For that, we are going to add a combo
box to the form header section of your form. It will be an unbound control.
It's row source should be "SELECT TagNum FROM tblAnimals"

Now, when you open the form and dropdown the combo box, you will have a list
of all tag numbers. The user can either enter a number or select from the
list. Once they have made a selection, we will need some VBA code in the
After Update event of the combo box to do the lookup and tell us if the tag
number is not found:

For example purposes, we will assume the name of the combo box to be cboTagNum

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "TagNum = " & Me.cboTagNum
If rst.NoMatch Then
MsgBox "Tag Number " & Me.cboTagNum & " Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Now, this is the simplest form of doing this. There are several other
things that can be done, but first, see if you can get this to work for you.

:

Okay, that is a start, but how do you run the query?

:

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

:

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM

 

[Guest]

Actually, it was a different type of necessity. I had been in a band that was
the house band at a club for over 8 years. Then, the Iraq war started, which
took away a bunch of national guardsmen, a restaurant that had been at its
present location for over 40 years closed (this restaurant was at the top of
the road that led to the club we played and when it turned off its lights, it
looked like we were closed too), and then a karaoke club opened up across the
street and that put the final nail in the coffin. I decided I was too old and
tired to start hauling all that equipment around, driving all over god's
green earth, and staying up until the wee hours of the morning. So, I
proceeded to seek gainful employment and was lucky enough to land my current
job. That joke reminds me of the t-shirt that says something like 'no car, no
job, no money, but I'm in a band.' Thanks again for your gracious help. Any
time you see a question of mine, please feel free to answer.))))

Peace,
RandyM

Same steps, different order. I was a road musician for 15 years, then became
a COBOL programmer, then got into mini computer, then DOS applications,
followed closely by Windows apps.
Anyway.
Set rst = nothing releases the reference to the recordset. It is just a
good practice.
you can use rs instead of rst. It is only a name. If I have multiple
recordsets to work with I use descriptive names.
Dim rstMaster As Recordset
Dim rstDetail As Recordset
etc.

The If rs.EOF is checking for End of File. I prefer the IF rs.NoMatch which
returns True if the value passed was not found and False if it was. If a
search is performed and no match is found, the recordset will probably be and
EOF, but just in case it is not, the NoMatch is preferred.

I haven't had a lot of experience with ADO, so I am not sure whether there
are differences that would affect this code or not. This code is based on
DAO. In any case, you do need the Me.Bookmark = rs.Bookmark to make the
record found the current record for your form.

I'm pretty sure why you left music to come back to computers. It has to do
with the old joke:
"What is the difference between a large pizza and a musician?"

"A large pizza can feed a family of four."

Klatuu,
You can't imagine how much help you have been. I am deeply grateful for
your assistance. I hope it's not presumptious of me to ask you some questions
about what I just did, but that's the only way I know to learn - if you
don't know something and somebody else does and is willing to impart that
knowledge, don't be embarrassed to ask. What is the purpose of the 'Set rst =
Nothing'? I pretty much understand the rest, but that throws me. Also, when I
place the combo box on the form, it automatically puts some of what you had
already in the After Update event. It adds an If Not rs.EOF Then Me.Bookmark
= rs.Bookmark which I commented out. I'm not completely sure of why this
isn't needed anymore. Could you enlighten me on that? And, finally my VBA
code uses rs instead of rst, is this because of the ADO or DAO? As I said,
I'm still learning about Access. I was a Cobol programmer for almost 10 years
and then became a full-time musician for another 10 years and I'm just now
getting back into the 'wonderful world of computers'. I thank you for your
time, your patience, and your willingness to impart your knowledge to us
'newbies'.

Thanks again,
RandyM

Flattery will get you anywhere

Even when I use the wrong field names, you get it right!

Well, it depends on what you want to do. Let's say, for example, you want
to include the Animal's name it the dropdown so the user can see it. (I don't
know whether this is of any value to you, but it is an example).
First, add the field for the animal's name to the query you use for the
combo's row source. Then, make the combo's Column Count 2. You will also
have to set your column widths to make them wide enough to view in drop down
mode. Let's say you need 1/2 " for the number and 1.5" for the Name. It
would be .5";1.5"

Now the user can see both the tag number and the animal's name.


:

Klatuu,
It must be a wonderful feeling to know what you're doing! This works
like a charm. I know you said this is the simplest form of doing this, so I'm
all ears for what else you can do. I figure the more I can learn about the
different ways to do the same thing, can only help me in the long run. I'm
all yours for as long as you want to teach me. As the 'grasshopper ' in the
old KungFu TV series would say, "Teach me Master."

Thanks so much,
RandyM

:

Okay, we can make this work. It will be a good learning experience for you.
First, take the Where condition out of your query. We are going to do this a
little differently.
With no further changes, the form will open with the first record in the
recordset displayed in your form. You will be able to navigate through the
records using the navigation buttons. Of course, what you want to do is to
be able to find a specific tag number. For that, we are going to add a combo
box to the form header section of your form. It will be an unbound control.
It's row source should be "SELECT TagNum FROM tblAnimals"

Now, when you open the form and dropdown the combo box, you will have a list
of all tag numbers. The user can either enter a number or select from the
list. Once they have made a selection, we will need some VBA code in the
After Update event of the combo box to do the lookup and tell us if the tag
number is not found:

For example purposes, we will assume the name of the combo box to be cboTagNum

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "TagNum = " & Me.cboTagNum
If rst.NoMatch Then
MsgBox "Tag Number " & Me.cboTagNum & " Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Now, this is the simplest form of doing this. There are several other
things that can be done, but first, see if you can get this to work for you.

:

Okay, that is a start, but how do you run the query?

:

Thanks Klatuu for responding. I'm not sure what code you're referring to, but
here's the SQL for the query.

SELECT tblOwners.*, tblAnimals.*, tblFees.*, tblAnimals.AnimalTagNum
FROM tblOwners INNER JOIN (tblAnimals INNER JOIN tblFees ON
tblAnimals.AnimalID = tblFees.AnimalID_FeeTbl) ON tblOwners.OwnerID =
tblAnimals.OwnerID_AnimalTbl
WHERE (((tblAnimals.AnimalTagNum)=[Enter TagNum to Lookup]));

If this is not what you need, please tell me exactly what and I'll try to
find it and post it. I'm still feeling my way thru Access, so sometimes
things that are simple to you 'pros' aren't so simple to me.

Thanks again for responding,
RandyM

:

Depends on how you are doing your look up. Post your code so we can give a
good response.

:

I have a query that asks for a tag number and then looks up and opens the
correct record. I would like to display a message if the user enters a tag
number that is not valid, but I don't know how. I think it would probably be
in an event, but I don't know which one and I'm not sure exactly what to
check for. If someone could help me out, I should would appreciate it.

Thanks,
RandyM