Query to find Record

[Scott]

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Ken Sheridan]

You'll need to code this. Add a 'Find Pet' button to the subform and in its
Click event procedure put:

Dim rst As Object
Dim lngPetID As Long
Set rst = Me.Recordset.Clone

lngPetID = InputBox("Enter Pet Identification Number","Find Pet")

rst.FindFirst "PetID = " & lngPetID
Me.Bookmark = rst.Bookmark

A more sophisticated solution would be to use a combo box listing all pets
in the subform's underlying recordset, rather than an InputBox. The user can
then simply select from the list.

Ken Sheridan
Stafford, England

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Scott]

I think the bookmark approach presented here is 'spot on.' This and the
RecordsetClone Property were new to me but I've done some additional research
and think that I understand it now. Unfortunately, I must still be doing
something wrong because it doesn't seem to be working. The actual code that
I inserted in the button (on 'Subform B', the one with both the CustomerID
and PetID fields) reads as follows:
------------------------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

lngPetID = InputBox("Enter identification number.", "Find Pet")

rst.FindFirst "PetID = " & lngPetID
If rst.NoMatch Then
MsgBox "Record not found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------
When I run the program and push the button, I always get the "Record not
found." message. When I stop the code it's clear that the input message is
working as it should (lngPetID's value matches a known record number). After
the line 'rst.FindFirst "PetID = " & lngPetID' line I've added a 'debug.print
PetID' and it gives me the same PetID as is on the form I've located the
button on...not what I'd hoped to see. Just to make sure the recordsetclone
feature was working as I thought it should, I substituted the code shown
above with:
-----------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst.PetID
rst.MoveNext
Loop

rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------------
In the immediate window I get '10459' which is the last record in Table B
(there are over 10,000 records that I'm working with). I would have
expected it to print the PetID for each of the records....

Again, I think the guidance is right...but I'm doing 'something' wrong. Any
additional thoughts would be appreciated.

--
SHB

You'll need to code this. Add a 'Find Pet' button to the subform and in its
Click event procedure put:

Dim rst As Object
Dim lngPetID As Long
Set rst = Me.Recordset.Clone

lngPetID = InputBox("Enter Pet Identification Number","Find Pet")

rst.FindFirst "PetID = " & lngPetID
Me.Bookmark = rst.Bookmark

A more sophisticated solution would be to use a combo box listing all pets
in the subform's underlying recordset, rather than an InputBox. The user can
then simply select from the list.

Ken Sheridan
Stafford, England

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Ken Sheridan]

Is the PetID column a number data type, as I'd assumed? If it were text
you'd need to wrap the value in quotes, e.g.

rst.FindFirst "PetID = """ & strPetID & """"

If it is a number data type I can't see why it should not find the record if
you are sure it exists in the subform's underlying recordset, i.e. it’s a
valid PetID value for the current CustomerID.

Ken Sheridan
Stafford, England

I think the bookmark approach presented here is 'spot on.' This and the
RecordsetClone Property were new to me but I've done some additional research
and think that I understand it now. Unfortunately, I must still be doing
something wrong because it doesn't seem to be working. The actual code that
I inserted in the button (on 'Subform B', the one with both the CustomerID
and PetID fields) reads as follows:
------------------------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

lngPetID = InputBox("Enter identification number.", "Find Pet")

rst.FindFirst "PetID = " & lngPetID
If rst.NoMatch Then
MsgBox "Record not found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------
When I run the program and push the button, I always get the "Record not
found." message. When I stop the code it's clear that the input message is
working as it should (lngPetID's value matches a known record number). After
the line 'rst.FindFirst "PetID = " & lngPetID' line I've added a 'debug.print
PetID' and it gives me the same PetID as is on the form I've located the
button on...not what I'd hoped to see. Just to make sure the recordsetclone
feature was working as I thought it should, I substituted the code shown
above with:
-----------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst.PetID
rst.MoveNext
Loop

rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------------
In the immediate window I get '10459' which is the last record in Table B
(there are over 10,000 records that I'm working with). I would have
expected it to print the PetID for each of the records....

Again, I think the guidance is right...but I'm doing 'something' wrong. Any
additional thoughts would be appreciated.

--
SHB

You'll need to code this. Add a 'Find Pet' button to the subform and in its
Click event procedure put:

Dim rst As Object
Dim lngPetID As Long
Set rst = Me.Recordset.Clone

lngPetID = InputBox("Enter Pet Identification Number","Find Pet")

rst.FindFirst "PetID = " & lngPetID
Me.Bookmark = rst.Bookmark

A more sophisticated solution would be to use a combo box listing all pets
in the subform's underlying recordset, rather than an InputBox. The user can
then simply select from the list.

Ken Sheridan
Stafford, England

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Scott]

Thanks for getting back to me on this. Yes, it's a number data type. I feel
better knowing that someone with more experience has looked at the code and
doesn't find some glaring mistake. Does it matter if I've been using DAO
(presumably not)? I've had a long day doing other things but am going to
re-focus on it know. Assuming I figure out what I'm doing wrong I'll post it
here for others to refer to. Again, many thanks for your assistance. Scott
--
SHB

Is the PetID column a number data type, as I'd assumed? If it were text
you'd need to wrap the value in quotes, e.g.

rst.FindFirst "PetID = """ & strPetID & """"

If it is a number data type I can't see why it should not find the record if
you are sure it exists in the subform's underlying recordset, i.e. it’s a
valid PetID value for the current CustomerID.

Ken Sheridan
Stafford, England

I think the bookmark approach presented here is 'spot on.' This and the
RecordsetClone Property were new to me but I've done some additional research
and think that I understand it now. Unfortunately, I must still be doing
something wrong because it doesn't seem to be working. The actual code that
I inserted in the button (on 'Subform B', the one with both the CustomerID
and PetID fields) reads as follows:
------------------------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

lngPetID = InputBox("Enter identification number.", "Find Pet")

rst.FindFirst "PetID = " & lngPetID
If rst.NoMatch Then
MsgBox "Record not found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------
When I run the program and push the button, I always get the "Record not
found." message. When I stop the code it's clear that the input message is
working as it should (lngPetID's value matches a known record number). After
the line 'rst.FindFirst "PetID = " & lngPetID' line I've added a 'debug.print
PetID' and it gives me the same PetID as is on the form I've located the
button on...not what I'd hoped to see. Just to make sure the recordsetclone
feature was working as I thought it should, I substituted the code shown
above with:
-----------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst.PetID
rst.MoveNext
Loop

rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------------
In the immediate window I get '10459' which is the last record in Table B
(there are over 10,000 records that I'm working with). I would have
expected it to print the PetID for each of the records....

Again, I think the guidance is right...but I'm doing 'something' wrong. Any
additional thoughts would be appreciated.

--
SHB

You'll need to code this. Add a 'Find Pet' button to the subform and in its
Click event procedure put:

Dim rst As Object
Dim lngPetID As Long
Set rst = Me.Recordset.Clone

lngPetID = InputBox("Enter Pet Identification Number","Find Pet")

rst.FindFirst "PetID = " & lngPetID
Me.Bookmark = rst.Bookmark

A more sophisticated solution would be to use a combo box listing all pets
in the subform's underlying recordset, rather than an InputBox. The user can
then simply select from the list.

Ken Sheridan
Stafford, England

:

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Scott]

As a footnote question to my previous submission...
I have normally used a different approach when working with recordsets (only
because it's the way I learned it). I'm sure you've seen it:
Dim db as dao.database
Dim rst as dao.recordset
set db=currentdb
set rst = db.openrecordset("<table name>")
With rst
.movefirst
End With
rst.close

In the example above, when I press the period ('full stop' for you Brits)
before 'movefirst' I get a popup with lots of option. When you use the
format that you've presented, do you normally get the same popup with lots of
options...because I'm not getting that?
--
SHB

Thanks for getting back to me on this. Yes, it's a number data type. I feel
better knowing that someone with more experience has looked at the code and
doesn't find some glaring mistake. Does it matter if I've been using DAO
(presumably not)? I've had a long day doing other things but am going to
re-focus on it know. Assuming I figure out what I'm doing wrong I'll post it
here for others to refer to. Again, many thanks for your assistance. Scott
--
SHB

Is the PetID column a number data type, as I'd assumed? If it were text
you'd need to wrap the value in quotes, e.g.

rst.FindFirst "PetID = """ & strPetID & """"

If it is a number data type I can't see why it should not find the record if
you are sure it exists in the subform's underlying recordset, i.e. it’s a
valid PetID value for the current CustomerID.

Ken Sheridan
Stafford, England

I think the bookmark approach presented here is 'spot on.' This and the
RecordsetClone Property were new to me but I've done some additional research
and think that I understand it now. Unfortunately, I must still be doing
something wrong because it doesn't seem to be working. The actual code that
I inserted in the button (on 'Subform B', the one with both the CustomerID
and PetID fields) reads as follows:
------------------------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

lngPetID = InputBox("Enter identification number.", "Find Pet")

rst.FindFirst "PetID = " & lngPetID
If rst.NoMatch Then
MsgBox "Record not found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------
When I run the program and push the button, I always get the "Record not
found." message. When I stop the code it's clear that the input message is
working as it should (lngPetID's value matches a known record number). After
the line 'rst.FindFirst "PetID = " & lngPetID' line I've added a 'debug.print
PetID' and it gives me the same PetID as is on the form I've located the
button on...not what I'd hoped to see. Just to make sure the recordsetclone
feature was working as I thought it should, I substituted the code shown
above with:
-----------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst.PetID
rst.MoveNext
Loop

rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------------
In the immediate window I get '10459' which is the last record in Table B
(there are over 10,000 records that I'm working with). I would have
expected it to print the PetID for each of the records....

Again, I think the guidance is right...but I'm doing 'something' wrong. Any
additional thoughts would be appreciated.

--
SHB


:

You'll need to code this. Add a 'Find Pet' button to the subform and in its
Click event procedure put:

Dim rst As Object
Dim lngPetID As Long
Set rst = Me.Recordset.Clone

lngPetID = InputBox("Enter Pet Identification Number","Find Pet")

rst.FindFirst "PetID = " & lngPetID
Me.Bookmark = rst.Bookmark

A more sophisticated solution would be to use a combo box listing all pets
in the subform's underlying recordset, rather than an InputBox. The user can
then simply select from the list.

Ken Sheridan
Stafford, England

:

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Scott]

OK, OK...I'm close! If someone can just get me over this hurdle I'll be home
Scott (excuse the expression) free.

There is a one-to-many relationship between the parent and child table. As
noted earlier, the button with the recordsetclone/bookmark program in it was
placed on the child table. I 'think' what's happening is that the program is
searching all the associated records associated with the parent table. So,
if there's only one record in the child table ('Table B') then that's what
the findfirst is trying to find. In other words, I don't think that it's
ever going to the main table (in fact, called PetInfo) to search the records.

It's late and the paragraph above probably doesn't make much sense...I think
I'm starting to figure it out and will broach it again in the a.m. Unless
something strikes you off-the-bat, I wouldn't bother spending too much time
on this...I think that I can figure it out...I think.

Does this observation make sense and, if so, what should I do to search the
actual PetInfo table and then match bookmarks to the current form?
--
SHB

As a footnote question to my previous submission...
I have normally used a different approach when working with recordsets (only
because it's the way I learned it). I'm sure you've seen it:
Dim db as dao.database
Dim rst as dao.recordset
set db=currentdb
set rst = db.openrecordset("<table name>")
With rst
.movefirst
End With
rst.close

In the example above, when I press the period ('full stop' for you Brits)
before 'movefirst' I get a popup with lots of option. When you use the
format that you've presented, do you normally get the same popup with lots of
options...because I'm not getting that?
--
SHB

Thanks for getting back to me on this. Yes, it's a number data type. I feel
better knowing that someone with more experience has looked at the code and
doesn't find some glaring mistake. Does it matter if I've been using DAO
(presumably not)? I've had a long day doing other things but am going to
re-focus on it know. Assuming I figure out what I'm doing wrong I'll post it
here for others to refer to. Again, many thanks for your assistance. Scott
--
SHB

Is the PetID column a number data type, as I'd assumed? If it were text
you'd need to wrap the value in quotes, e.g.

rst.FindFirst "PetID = """ & strPetID & """"

If it is a number data type I can't see why it should not find the record if
you are sure it exists in the subform's underlying recordset, i.e. it’s a
valid PetID value for the current CustomerID.

Ken Sheridan
Stafford, England

:

I think the bookmark approach presented here is 'spot on.' This and the
RecordsetClone Property were new to me but I've done some additional research
and think that I understand it now. Unfortunately, I must still be doing
something wrong because it doesn't seem to be working. The actual code that
I inserted in the button (on 'Subform B', the one with both the CustomerID
and PetID fields) reads as follows:
------------------------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

lngPetID = InputBox("Enter identification number.", "Find Pet")

rst.FindFirst "PetID = " & lngPetID
If rst.NoMatch Then
MsgBox "Record not found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------
When I run the program and push the button, I always get the "Record not
found." message. When I stop the code it's clear that the input message is
working as it should (lngPetID's value matches a known record number). After
the line 'rst.FindFirst "PetID = " & lngPetID' line I've added a 'debug.print
PetID' and it gives me the same PetID as is on the form I've located the
button on...not what I'd hoped to see. Just to make sure the recordsetclone
feature was working as I thought it should, I substituted the code shown
above with:
-----------------------------------------------------
Private Sub cmdFindByPetID_Click()
On Error GoTo Err_cmdFindByPetID_Click


Dim rst As Object
Dim lngPetID As Long
Set rst = Me.RecordsetClone

rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst.PetID
rst.MoveNext
Loop

rst.Close

Exit_cmdFindByPetID_Click:
Exit Sub

Err_cmdFindByPetID_Click:
MsgBox Err.Description
Resume Exit_cmdFindByPetID_Click

End Sub
-----------------------------------------------------------------
In the immediate window I get '10459' which is the last record in Table B
(there are over 10,000 records that I'm working with). I would have
expected it to print the PetID for each of the records....

Again, I think the guidance is right...but I'm doing 'something' wrong. Any
additional thoughts would be appreciated.

--
SHB


:

You'll need to code this. Add a 'Find Pet' button to the subform and in its
Click event procedure put:

Dim rst As Object
Dim lngPetID As Long
Set rst = Me.Recordset.Clone

lngPetID = InputBox("Enter Pet Identification Number","Find Pet")

rst.FindFirst "PetID = " & lngPetID
Me.Bookmark = rst.Bookmark

A more sophisticated solution would be to use a combo box listing all pets
in the subform's underlying recordset, rather than an InputBox. The user can
then simply select from the list.

Ken Sheridan
Stafford, England

:

Help! I have a program in which there are two tables (more than 2, but for
now...). Table A has fields that include CustomerID (autonumber, primary
key). Table B has fields that include CustomerID (Long Integer) and PetID
(autonumber, primary key). Table A--parent--is linked to Table B through
CustomerID.

Because of screen limitation size, I set up tabs and included part of Table
A under Tab 1 (lets call it Subform A) and all of Table B under Tab 2 (lets
call it Subform B). In the area above the tabs I essentially have only the
FirstName and LastName fields. In Subform A I have the remaining fields
associated with Table A (street address, tel. nos., etc.). The parent and
Subform A are linked using properties (Link Child Fields: CustomerID; Link
Master Fields: CustomerID). All-in-all, this approach has worked
well...except....

In the parent area I put a search button. I can click in, say, the LastName
field, press the 'binoculars' and enter the Find criteria...works like a
charm. If, however, I try to do the same thing in any of the subform fields
this approach does not work. I have a particular need to search for records
based on the PetID field in Subform B. I created a query that does this
pretty effectively; the SQL code reads as follows:

SELECT PetInfo.PetID, ClientInfo.LastName, ClientInfo.FirstName,
ClientInfo.BillingAddress, ClientInfo.City, ClientInfo.State,
ClientInfo.ZipCode, ClientInfo.HomePhoneNumber, ClientInfo.WorkPhoneNumber,
PetInfo.PetsName, PetInfo.ColorDescription, PetInfo.PetType, PetInfo.PetSex,
PetInfo.Breed, ClientInfo.CustomerID
FROM ClientInfo LEFT JOIN PetInfo ON ClientInfo.CustomerID =
PetInfo.CustomerID
WHERE (((PetInfo.PetID)=[Enter Pet Identification Number]));

This too works just dandy. My problem is that instead of pulling up a
spreadsheet (dynaset), what I really want to do is to have the program move
to that record in the forms view so that I can more readily edit other fields
and otherwise work with the data. I've researched the knowledgebase and
newsgroups and don't see anything that would seem to help. I've played
around with changing the Select query to a Make Table query that would write
the PetID number to a temporary table that could then be referenced 'somehow'
to move to the desired record. Something tells me I'm going about this the
hard way. Any suggestions on how to tackle this critter would be most
welcome.

 

[Ken Sheridan]

You are quite right about the search being limited to just the records which
relate to the current record in the parent form. The subform's underlying
recordest is dynamic and changes as you navigate to different records on the
parent form. To search just the current records in the subform the DAO
equivalent of the code would be:

Dim rst As DAO.Recordset
Dim lngPetID As Long

Set rst = Me.RecordsetClone
lngPetID = InputBox("Enter Pet Identification Number", "Find Pet")

With rst
.FindFirst "PetID = " & lngPetID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Record not found.", vbOnformation, "Warning"
End If
.Close
End With

Set rst = Nothing

As you see this uses the RecordsetClone property of the form object rather
than the Clone property of the recordset object. There's no need to call the
OpenRecordset method to return a refernce to the recordset as the
RecordsetClone property implicitly does that.

If you want to search all records in the underlying table then you'd need to
unlink the subform by deleting the values for the LinkMasterFields and
LinkChildFields properties. The subform's underlying recordset would now not
change to match the parent form, and you'd be able to search fore any record
in its underlying table. The code would remain as above.

Ken Sheridan
Stafford, England

 

[Scott]

You apparently know your stuff. At the risk of trying your patience...if the
only way to search the 'whole' Table B' is to delete the values for the
LinkMasterFields and LinkChildFields properties...then can you propose an
alternative way to search for the PetID field and to subsequently synchronize
the Master/Child records...or have I painted myself into a corner?

 

[Scott]

I am so proud of myself! (Even though some of you may think this is a bit
like hitting a fly with a hammer...there are probably more elegant ways).

I resolved the problem by putting the search button in the Parent form. In
the coding I (a) ran a make-table query that asked for the desired record
number and then wrote it to a temporary table that contained both the
subform's primary key and the linkchildfields number that matches up with the
parent form; (b) used a openrecordset method to retrieve the parent table's
primary key field number that I'm trying to locate and then (c) used a
recordsetclone to locate the record. Perhaps a bit convoluted but it works!!
For the record, the code used in the program follows:

Dim db As DAO.Database
Dim rstPetInfo As DAO.Recordset
Dim rstClientInfo As DAO.Recordset
Dim lngCustomerID As Long

' Query writes PetID and CustomerID number to a temporary, one record table
(TempPetID)
DoCmd.SetWarnings False
stDocName = "LocatePetID Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Set db = CurrentDb
' Retrieve CustomerID number from TempPetID and add it to variable
'lngCustomerID'
Set rstPetInfo = db.OpenRecordset("TempPetID")
With rstPetInfo
lngCustomerID = !CustomerID
rstPetInfo.Close
End With

' Use recordsetclone to synchronize Parent table primary field with
lngCustomerID
Set rstClientInfo = Me.RecordsetClone
If rstClientInfo.NoMatch Then
MsgBox "Record not found."
Else
With rstClientInfo
.FindFirst "CustomerID = " & lngCustomerID
Me.Bookmark = rstClientInfo.Bookmark
.Close
End With
End If

###

Special thanks to Ken for getting me to the end.
--
SHB

You apparently know your stuff. At the risk of trying your patience...if the
only way to search the 'whole' Table B' is to delete the values for the
LinkMasterFields and LinkChildFields properties...then can you propose an
alternative way to search for the PetID field and to subsequently synchronize
the Master/Child records...or have I painted myself into a corner?