This is driving me nuts!!!

[Lee]

I'm sorry I keep asking about this but I'm still having
trouble getting this to work. All I want to do is to use
a form to find a record in one table, locate
the 'matching' record in another table (via a common
control called 'Tracking Number') and display that second
matching record in a form. Easy-peasy, lemon-squeezy?
Nope, not for me it ain't! Here's my revised code. Am I
using the wrong type of recordset? If you can help,
please can you spell it out to me why it's not working!
Many thanks...

Dim db As Database
Dim rst As Recordset
Dim rstMain As Recordset
Dim frm As Form
Dim varGoTo As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Candidates")

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!",
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Else
rst.FindFirst "[Surname]=" & "'" & Me!GoToRecord & "'"
End If

If rst.NoMatch Then
MsgBox "No records match the Surname provided.
Try again.", vbInformation, "No records found"
Else
rst.Bookmark = Me.Bookmark
varGoTo = Me.[Tracking Number]

Set rstMain = db.OpenRecordset("Centres")
Set frm = Forms("Data Input Screen")

rstMain.FindFirst "[Tracking Number]=" & varGoTo
frm.Bookmark = rstMain.Bookmark

End If

rst.Close
rstMain.Close

I do hope you guys can help with this before I lose ALL
my hair over this.
Regards
Lee

 

[Dirk Goldgar]

I'm sorry I keep asking about this but I'm still having
trouble getting this to work. All I want to do is to use
a form to find a record in one table, locate
the 'matching' record in another table (via a common
control called 'Tracking Number') and display that second
matching record in a form. Easy-peasy, lemon-squeezy?
Nope, not for me it ain't! Here's my revised code. Am I
using the wrong type of recordset? If you can help,
please can you spell it out to me why it's not working!
Many thanks...

Dim db As Database
Dim rst As Recordset
Dim rstMain As Recordset
Dim frm As Form
Dim varGoTo As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Candidates")

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!",
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Else
rst.FindFirst "[Surname]=" & "'" & Me!GoToRecord & "'"
End If

If rst.NoMatch Then
MsgBox "No records match the Surname provided.
Try again.", vbInformation, "No records found"
Else
rst.Bookmark = Me.Bookmark
varGoTo = Me.[Tracking Number]

Set rstMain = db.OpenRecordset("Centres")
Set frm = Forms("Data Input Screen")

rstMain.FindFirst "[Tracking Number]=" & varGoTo
frm.Bookmark = rstMain.Bookmark

End If

rst.Close
rstMain.Close

I do hope you guys can help with this before I lose ALL
my hair over this.

Okay, you shouldn't be so impatient, but you're close. Try this:

'----- start of revised code -----
Dim db As DAO.Database
Dim rstFind As DAO.Recordset
Dim rstMain As DAO.Recordset

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!", _
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Exit Sub
End If

Set db = CurrentDb
Set rstFind = db.OpenRecordset( _
"SELECT [Tracking Number] FROM Candidates " & _
"WHERE [Surname]=" & _
Chr(34) & Me!GoToRecord & Chr(34)

If rstFind.EOF Then
MsgBox "No records match the Surname provided. " & _
"Try again.", vbInformation, "No records found"
Else
With Forms("Data Input Screen")
Set rstMain = .RecordsetClone
rstMain.FindFirst _
"[Tracking Number]=" & rstFind.[Tracking Number]
If .NoMatch Then
' No match was found. Is this possible?
Else
.Bookmark = rstMain.Bookmark
End If
Set rstMain = Nothing
End With
End If

rstFind.Close
Set rstFind = Nothing
Set db = Nothing
'----- end of revised code -----

 

[Casey]

Lee,

On your form, you have to have a control that shows
the the tracking number, as you mentioned.

What you want to do is use that control as a
reference in the querying of the other table that you are
interested in.

Actually what you should do is create a new query
based on the table you are interested in deriving
information from. In the field criteria of the query, for
the field that you are interested in communicating your
tracking no. with, put the following, but use your real
form name and tracking control name:

[Forms].[realformname].[trackingcontrolname]

Now, make sure you have placed all of the fields from
your destination table in the query besides the tracking
no., and create a form using that query as the basis of
the form.

Now, on your main form, create a button, and for the
onclick property of the button, create a macro which opens
the new form that holds your information you are
interested in.

The information you wanted to see in the other table
should be visible on your new form when opened from your
main form.

I know that you requested code for an answer to your
question, but this is a way that you can find your
information via forms and querying.

I hope I did help.

Casey



..pulling from via your tracking no. on your main form

 

[Lee]

Hello Dirk,
Thanks everso much for doing this - I'm sorry I've been
getting rather impatient but it seems everything I try
never works!
Just in case you wish to know, the code you provided
needed an exclamation mark instead of a point where I've
put three stars either side. I'm not looking a gift
horse in the mouth you understand but just thought I
should mention it! ;-)
BTW I've also removed the 'no match' bit as you suggest -
as you say, it's superfluous.
Once again, thanks for your support.
Best regards,

Lee

-----Original Message-----

I'm sorry I keep asking about this but I'm still having
trouble getting this to work. All I want to do is to use
a form to find a record in one table, locate
the 'matching' record in another table (via a common
control called 'Tracking Number') and display that second
matching record in a form. Easy-peasy, lemon-squeezy?
Nope, not for me it ain't! Here's my revised code. Am I
using the wrong type of recordset? If you can help,
please can you spell it out to me why it's not working!
Many thanks...

Dim db As Database
Dim rst As Recordset
Dim rstMain As Recordset
Dim frm As Form
Dim varGoTo As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Candidates")

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!",
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Else
rst.FindFirst "[Surname]=" & "'" & Me!GoToRecord & "'"
End If

If rst.NoMatch Then
MsgBox "No records match the Surname provided.
Try again.", vbInformation, "No records found"
Else
rst.Bookmark = Me.Bookmark
varGoTo = Me.[Tracking Number]

Set rstMain = db.OpenRecordset("Centres")
Set frm = Forms("Data Input Screen")

rstMain.FindFirst "[Tracking Number]=" & varGoTo
frm.Bookmark = rstMain.Bookmark

End If

rst.Close
rstMain.Close

I do hope you guys can help with this before I lose ALL
my hair over this.

Okay, you shouldn't be so impatient, but you're close. Try this:

'----- start of revised code -----
Dim db As DAO.Database
Dim rstFind As DAO.Recordset
Dim rstMain As DAO.Recordset

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!", _
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Exit Sub
End If

Set db = CurrentDb
Set rstFind = db.OpenRecordset( _
"SELECT [Tracking Number] FROM Candidates " & _
"WHERE [Surname]=" & _
Chr(34) & Me!GoToRecord & Chr(34)

If rstFind.EOF Then
MsgBox "No records match the Surname provided. " & _
"Try again.", vbInformation, "No records found"
Else
With Forms("Data Input Screen")
Set rstMain = .RecordsetClone
rstMain.FindFirst _
"[Tracking Number]=" & ***rstFind. [Tracking Number]***
If .NoMatch Then
' No match was found. Is this possible?
Else
.Bookmark = rstMain.Bookmark
End If
Set rstMain = Nothing
End With
End If

rstFind.Close
Set rstFind = Nothing
Set db = Nothing
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Lee]

Hi Casey,
Thanks for your response. As you will see from Dirk's
response I've gone with his suggested code but I'm
grateful for your ideas. I've copied and pasted your
response into a Word document that I keep of various
ideas and suggestions that people offer in this newsgroup.
Kind regards,
Lee

-----Original Message-----
Lee,

On your form, you have to have a control that shows
the the tracking number, as you mentioned.

What you want to do is use that control as a
reference in the querying of the other table that you are
interested in.

Actually what you should do is create a new query
based on the table you are interested in deriving
information from. In the field criteria of the query, for
the field that you are interested in communicating your
tracking no. with, put the following, but use your real
form name and tracking control name:

[Forms].[realformname].[trackingcontrolname]

Now, make sure you have placed all of the fields from
your destination table in the query besides the tracking
no., and create a form using that query as the basis of
the form.

Now, on your main form, create a button, and for the
onclick property of the button, create a macro which opens
the new form that holds your information you are
interested in.

The information you wanted to see in the other table
should be visible on your new form when opened from your
main form.

I know that you requested code for an answer to your
question, but this is a way that you can find your
information via forms and querying.

I hope I did help.

Casey



..pulling from via your tracking no. on your main form

-----Original Message-----
I'm sorry I keep asking about this but I'm still having
trouble getting this to work. All I want to do is to use
a form to find a record in one table, locate
the 'matching' record in another table (via a common
control called 'Tracking Number') and display that second
matching record in a form. Easy-peasy, lemon-squeezy?
Nope, not for me it ain't! Here's my revised code. Am I
using the wrong type of recordset? If you can help,
please can you spell it out to me why it's not working!
Many thanks...

Dim db As Database
Dim rst As Recordset
Dim rstMain As Recordset
Dim frm As Form
Dim varGoTo As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Candidates")

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!",
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Else
rst.FindFirst "[Surname]=" & "'" & Me!GoToRecord & "'"
End If

If rst.NoMatch Then
MsgBox "No records match the Surname provided.
Try again.", vbInformation, "No records found"
Else
rst.Bookmark = Me.Bookmark
varGoTo = Me.[Tracking Number]

Set rstMain = db.OpenRecordset("Centres")
Set frm = Forms("Data Input Screen")

rstMain.FindFirst "[Tracking Number]=" & varGoTo
frm.Bookmark = rstMain.Bookmark

End If

rst.Close
rstMain.Close

I do hope you guys can help with this before I lose ALL
my hair over this.
Regards
Lee
.

.

 

[Lee]

Phew! Thanks for providing such a comprehensive response
Andrew. I've copied your reply and will go through it
carefully tomorrow when I'm feeling strong (just
kidding!). Right now I've had enough of this lark and
reckon I should catch up on my ugly sleep. Thanks again.
Night night.
Regards,

Lee

-----Original Message-----
Well, you are using "the wrong type of recordset", but you can do this
without using a recordset at all if you just modify the recordsource of your
form so that it shows the records that match the value in "GoToRecord":

eg Me.RecordSource = "SELECT * FROM Candidates WHERE Surname = '" &
Me.GoToRecord & "'"

(This would go in the AfterUpdate event of the control bound to GoToRecord).

If you want to use recordset bookmarks in the way that you are trying then
you have to use recordsets that are clones of one another. Otherwise the
bookmarks will not match, and it won't work. In your code you open a
recordset based on table called "Candidates" - persumably this is also the
data source for your form. Unfortunately the bookmarks in your recordset
will not be the same as the bookmarks in the form's recordset - even though
they are based on the same data. If you really want to use a recordset in
this way then you should use:

Set rst = Me.RecordsetClone

If you do this then I think the first part of your code will work as you
expect it to - down to the bit where you open the second recordset, rstMain.
After that it goes a bit wobbly again. You've got a similar problem with
recordsets as you had before, but here you also have tried to open a form by
using a form object - this isn't the way to do it. This bit of code will
most likely complain that it can't find the form "Data Input Screen". This
is because you have used the forms collection which is a collection of open
forms. I suspect that you won't know if this form is open or not, and that
you really want your code to open it. You use DoCmd.OpenForm to open a form.

If the form does happen to be open the code still won't work due to the
recordset clone issue. You can, however, easily open the form at the right
record by using the where clause of the DoCmd object.

DoCmd.OpenForm "Data Input Screen",,,"[Tracking Number] = " & varGoTo

You could also open the form and then modify its recordsource, or base the
form on a query and modify the SQL property of the query before you open the
form. This latter method is good as the form only needs to retrieve the
record you want - not everything in the table.

If you're using Access 2000 and you really want to use a recordset then you
can set an open recordset to be the recordset of a form. So you could use
your existing code that opens rstMain, and moves to the right record and
then put:

Set forms("Data Input Screen").Recordset = rstMain (Making sure that you've
added the code to open the form as well)

If you are going to do this then I would not open the entire table in
rstMain when you're only wanting to view one record. Just open the record
you want:

set rstMain = db.OpenRecordset("SELECT * FROM "Centres" WHERE [Tracking
Number] = " & varGoTo)

This will also save you the effort of having to move to the right record.

(BTW Why have you dimensioned varGoTo as a variant? Don't you know what type
of data it is? I assume it's a number so you should dimension it as long).

Sorry this has been such a long message - I hadn't intended to write this
much. One final suggestion before I go - why not make "Data Input Screen" a
subform on your first form linked to the main form by "Tracking Number"?
This should allow you to eliminate most of the code!

Andrew.

I'm sorry I keep asking about this but I'm still having
trouble getting this to work. All I want to do is to use
a form to find a record in one table, locate
the 'matching' record in another table (via a common
control called 'Tracking Number') and display that second
matching record in a form. Easy-peasy, lemon-squeezy?
Nope, not for me it ain't! Here's my revised code. Am I
using the wrong type of recordset? If you can help,
please can you spell it out to me why it's not working!
Many thanks...

Dim db As Database
Dim rst As Recordset
Dim rstMain As Recordset
Dim frm As Form
Dim varGoTo As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Candidates")

If IsNull(Me!GoToRecord) Then
MsgBox "First enter a name to look up!",
vbExclamation, "Oops!"
Me!GoToRecord.SetFocus
Else
rst.FindFirst "[Surname]=" & "'" & Me!GoToRecord & "'"
End If

If rst.NoMatch Then
MsgBox "No records match the Surname provided.
Try again.", vbInformation, "No records found"
Else
rst.Bookmark = Me.Bookmark
varGoTo = Me.[Tracking Number]

Set rstMain = db.OpenRecordset("Centres")
Set frm = Forms("Data Input Screen")

rstMain.FindFirst "[Tracking Number]=" & varGoTo
frm.Bookmark = rstMain.Bookmark

End If

rst.Close
rstMain.Close

I do hope you guys can help with this before I lose ALL
my hair over this.
Regards
Lee

.