Record Find Problem

[Guest]

Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Graham Mandeno]

Hi Robert

For a start, you appear to be trying to compare the phone number that has
been entered with the LastName field, not the HomePhoneNumber field:

strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"

Also, you are not actually comparing with what the user has typed in, but
with the string "strHome_Phone_Number". Presumably there are no records
with that as a phone number!

So, probably what you want is something like this:


strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '" _
& strHome_Phone_Number & "';"

(Note the two single quotes - one after the = and one before the
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Graham Mandeno]

Actually, having read a bit more of your original post, I can't see how the
code is going to work at all. You are trying to equate bookmarks from two
entirely different recordsets. If it's an MDB application then furthermore
you are equating bookmarks from an ADODB recordset and a DAO recordset.

You don't say if this is an MDB or an ADP. If it's an MBD, then try this
code:

With Me.RecordsetClone
.FindFirst "[HomePhoneNumber] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With

Once again, note the single quotes.

This might also work for an ADP but I'm not sure.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Robert

For a start, you appear to be trying to compare the phone number that has
been entered with the LastName field, not the HomePhoneNumber field:

strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"

Also, you are not actually comparing with what the user has typed in, but
with the string "strHome_Phone_Number". Presumably there are no records
with that as a phone number!

So, probably what you want is something like this:


strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '" _
& strHome_Phone_Number & "';"

(Note the two single quotes - one after the = and one before the
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Guest]

Graham,
I actually do want to compare phone numbers but was doing some debugging on
my own and changed it to last name for a search. I will use phone number, but
when I try you code I get a metthod error.
Object does not support this property or method.
Whats up with that?

Actually, having read a bit more of your original post, I can't see how the
code is going to work at all. You are trying to equate bookmarks from two
entirely different recordsets. If it's an MDB application then furthermore
you are equating bookmarks from an ADODB recordset and a DAO recordset.

You don't say if this is an MDB or an ADP. If it's an MBD, then try this
code:

With Me.RecordsetClone
.FindFirst "[HomePhoneNumber] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With

Once again, note the single quotes.

This might also work for an ADP but I'm not sure.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Robert

For a start, you appear to be trying to compare the phone number that has
been entered with the LastName field, not the HomePhoneNumber field:

strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"

Also, you are not actually comparing with what the user has typed in, but
with the string "strHome_Phone_Number". Presumably there are no records
with that as a phone number!

So, probably what you want is something like this:


strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '" _
& strHome_Phone_Number & "';"

(Note the two single quotes - one after the = and one before the
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Guest]

Here is the latest code!

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for the Customer you want to locate")
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.Open "Select * from tblCustomers"
With Me.RecordsetClone
.FindFirst "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With






'Me.Recordset.Find "LastName = " & strCustomerName, Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Customer Name " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Graham,
I actually do want to compare phone numbers but was doing some debugging on
my own and changed it to last name for a search. I will use phone number, but
when I try you code I get a metthod error.
Object does not support this property or method.
Whats up with that?

Actually, having read a bit more of your original post, I can't see how the
code is going to work at all. You are trying to equate bookmarks from two
entirely different recordsets. If it's an MDB application then furthermore
you are equating bookmarks from an ADODB recordset and a DAO recordset.

You don't say if this is an MDB or an ADP. If it's an MBD, then try this
code:

With Me.RecordsetClone
.FindFirst "[HomePhoneNumber] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With

Once again, note the single quotes.

This might also work for an ADP but I'm not sure.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Robert

For a start, you appear to be trying to compare the phone number that has
been entered with the LastName field, not the HomePhoneNumber field:

strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"

Also, you are not actually comparing with what the user has typed in, but
with the string "strHome_Phone_Number". Presumably there are no records
with that as a phone number!

So, probably what you want is something like this:


strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '" _
& strHome_Phone_Number & "';"

(Note the two single quotes - one after the = and one before the
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Graham Mandeno]

Hi Robert

You didn't answer my question about whether this is an MDB or an ADP. I am
now assuming the latter.

This means that the form's Recordset and RecordsetClone properties will be
ADODB Recordsets, not DAO Recordsets. The two objects have markedly
different properties and methods. Specifically, the differences that are
pertinent to your code are:

1. DAO has a FindFirst method, while with ADO you must use MoveFirst
followed by Find.

2. DAO has a NoMatch property, while with ADO you must use EOF.

3. With ADO, each reference to RecordsetClone returns a new instance, while
with DAO each reference returns the same instance. Therefore with ADO you
must assign the RecordsetClone to a Recordset variable.

So, your code should look like this:

Private Sub cmdFind_Click()
Dim strHome_Phone_Number As String
Dim rsc As ADODB.Recordset
strHome_Phone_Number = InputBox( _
"Enter the Home phone Number for the Customer you want to locate")
Set rsc = Me.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
Set rsc = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Here is the latest code!

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for the Customer you want to locate")
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.Open "Select * from tblCustomers"
With Me.RecordsetClone
.FindFirst "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With






'Me.Recordset.Find "LastName = " & strCustomerName, Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Customer Name " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Graham,
I actually do want to compare phone numbers but was doing some debugging
on
my own and changed it to last name for a search. I will use phone number,
but
when I try you code I get a metthod error.
Object does not support this property or method.
Whats up with that?

Actually, having read a bit more of your original post, I can't see how
the
code is going to work at all. You are trying to equate bookmarks from
two
entirely different recordsets. If it's an MDB application then
furthermore
you are equating bookmarks from an ADODB recordset and a DAO recordset.

You don't say if this is an MDB or an ADP. If it's an MBD, then try
this
code:

With Me.RecordsetClone
.FindFirst "[HomePhoneNumber] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With

Once again, note the single quotes.

This might also work for an ADP but I'm not sure.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Robert

For a start, you appear to be trying to compare the phone number that
has
been entered with the LastName field, not the HomePhoneNumber field:

strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"

Also, you are not actually comparing with what the user has typed in,
but
with the string "strHome_Phone_Number". Presumably there are no
records
with that as a phone number!

So, probably what you want is something like this:


strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '"
_
& strHome_Phone_Number & "';"

(Note the two single quotes - one after the = and one before the
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
message
Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input
something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone
Number
for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Guest]

Graham,
You are a genius. Thank you for your help. I was really stumped. I now have
a supplemental question. I have to figure out how to input the find. I am
using an Input mask, and so teh data looks like this (203) 334-9086 when i
just search for the numbers it fails to find a match. If i input it as (203)
334-9086 it works. How do I get around this?
Thanks in advance???
Bob Smith

Hi Robert

You didn't answer my question about whether this is an MDB or an ADP. I am
now assuming the latter.

This means that the form's Recordset and RecordsetClone properties will be
ADODB Recordsets, not DAO Recordsets. The two objects have markedly
different properties and methods. Specifically, the differences that are
pertinent to your code are:

1. DAO has a FindFirst method, while with ADO you must use MoveFirst
followed by Find.

2. DAO has a NoMatch property, while with ADO you must use EOF.

3. With ADO, each reference to RecordsetClone returns a new instance, while
with DAO each reference returns the same instance. Therefore with ADO you
must assign the RecordsetClone to a Recordset variable.

So, your code should look like this:

Private Sub cmdFind_Click()
Dim strHome_Phone_Number As String
Dim rsc As ADODB.Recordset
strHome_Phone_Number = InputBox( _
"Enter the Home phone Number for the Customer you want to locate")
Set rsc = Me.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
Set rsc = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Here is the latest code!

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for the Customer you want to locate")
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.Open "Select * from tblCustomers"
With Me.RecordsetClone
.FindFirst "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With






'Me.Recordset.Find "LastName = " & strCustomerName, Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Customer Name " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Graham,
I actually do want to compare phone numbers but was doing some debugging
on
my own and changed it to last name for a search. I will use phone number,
but
when I try you code I get a metthod error.
Object does not support this property or method.
Whats up with that?


:

Actually, having read a bit more of your original post, I can't see how
the
code is going to work at all. You are trying to equate bookmarks from
two
entirely different recordsets. If it's an MDB application then
furthermore
you are equating bookmarks from an ADODB recordset and a DAO recordset.

You don't say if this is an MDB or an ADP. If it's an MBD, then try
this
code:

With Me.RecordsetClone
.FindFirst "[HomePhoneNumber] = '" & strHome_Phone_Number & "'"
If .NoMatch Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With

Once again, note the single quotes.

This might also work for an ADP but I'm not sure.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Robert

For a start, you appear to be trying to compare the phone number that
has
been entered with the LastName field, not the HomePhoneNumber field:

strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"

Also, you are not actually comparing with what the user has typed in,
but
with the string "strHome_Phone_Number". Presumably there are no
records
with that as a phone number!

So, probably what you want is something like this:


strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '"
_
& strHome_Phone_Number & "';"

(Note the two single quotes - one after the = and one before the
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
message
Hi,
Im trying to do a record find in access on a form i created.
It seems to work fine when i click the find button and input
something to
search for, but it doesnt seem to change to the correct record.
here is my form code

Private Sub cmdFind_Click()
'Dim strClientID As String
Dim varBookmark As Variant
Dim strHome_Phone_Number As Variant
Dim strsql As String

'Store the book of the current record
varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
strHome_Phone_Number = InputBox("Enter the Customers Home phone
Number
for
the Customer you want to locate")

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
rst.Open strsql
MsgBox ("I am here")



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
If Me.Recordset.EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
Else
Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Thanks for anyones help in advance.
My email is
(e-mail address removed)

 

[Graham Mandeno]

Hi Robert

Probably the most elegant way around this is to create your own InputBox.
(I always avoid InputBox anyway, except for something quick and dirty,
because it's limited and ugly.)

Create a small unbound form (frmSearchPhone) with a textbox for the phone
number (txtSearchPhone) and use the same input mask as you have for the
actual field. Add two buttons - cmdFind and cmdCancel - and set the Default
property of the first and the Cancel property of the second.

Turn off all the default stuff like record selectors and navigation buttons
and control menu.

For cmdCancel, simply close the current form:

DoCmd.Close acForm, Me.Name

Use the same code as below for cmdFind, with the following changes:

Private Sub cmdFind_Click()
Dim frm as Form
Dim rsc As ADODB.Recordset
Set frm = Forms("name of your main form")
Set rsc = frm.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtSearchPhone & "'"
If .EOF Then
MsgBox "Home Phone Number " & txtSearchPhone & " Not Found!!"
txtSearchPhone.SetFocus
Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If
End With
Set rsc = Nothing
Set frm = Nothing
End Sub

On your main form, all the command button needs to do is open the new form
modally:

DoCmd.OpenForm "frmSearchPhone", WindowMode:=acDialog
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


"(e-mail address removed)"

Graham,
You are a genius. Thank you for your help. I was really stumped. I now
have
a supplemental question. I have to figure out how to input the find. I am
using an Input mask, and so teh data looks like this (203) 334-9086 when i
just search for the numbers it fails to find a match. If i input it as
(203)
334-9086 it works. How do I get around this?
Thanks in advance???
Bob Smith

Hi Robert

You didn't answer my question about whether this is an MDB or an ADP. I
am
now assuming the latter.

This means that the form's Recordset and RecordsetClone properties will
be
ADODB Recordsets, not DAO Recordsets. The two objects have markedly
different properties and methods. Specifically, the differences that are
pertinent to your code are:

1. DAO has a FindFirst method, while with ADO you must use MoveFirst
followed by Find.

2. DAO has a NoMatch property, while with ADO you must use EOF.

3. With ADO, each reference to RecordsetClone returns a new instance,
while
with DAO each reference returns the same instance. Therefore with ADO
you
must assign the RecordsetClone to a Recordset variable.

So, your code should look like this:

Private Sub cmdFind_Click()
Dim strHome_Phone_Number As String
Dim rsc As ADODB.Recordset
strHome_Phone_Number = InputBox( _
"Enter the Home phone Number for the Customer you want to
locate")
Set rsc = Me.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
Set rsc = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Guest]

Gramham,
Thanks you very much for your help. It works perfectly. I may call on you
again. i am try to do so additional things with it, but I may be fine on my
own.
I want to nest teh if to check all three phone numbers home, work, and cell
before saying teh record is not there. I have some ideas, and it may work,
but if not I will post a help message.

Again thanks you very much
you are truly a MVP

Hi Robert

Probably the most elegant way around this is to create your own InputBox.
(I always avoid InputBox anyway, except for something quick and dirty,
because it's limited and ugly.)

Create a small unbound form (frmSearchPhone) with a textbox for the phone
number (txtSearchPhone) and use the same input mask as you have for the
actual field. Add two buttons - cmdFind and cmdCancel - and set the Default
property of the first and the Cancel property of the second.

Turn off all the default stuff like record selectors and navigation buttons
and control menu.

For cmdCancel, simply close the current form:

DoCmd.Close acForm, Me.Name

Use the same code as below for cmdFind, with the following changes:

Private Sub cmdFind_Click()
Dim frm as Form
Dim rsc As ADODB.Recordset
Set frm = Forms("name of your main form")
Set rsc = frm.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtSearchPhone & "'"
If .EOF Then
MsgBox "Home Phone Number " & txtSearchPhone & " Not Found!!"
txtSearchPhone.SetFocus
Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If
End With
Set rsc = Nothing
Set frm = Nothing
End Sub

On your main form, all the command button needs to do is open the new form
modally:

DoCmd.OpenForm "frmSearchPhone", WindowMode:=acDialog
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


"(e-mail address removed)"

Graham,
You are a genius. Thank you for your help. I was really stumped. I now
have
a supplemental question. I have to figure out how to input the find. I am
using an Input mask, and so teh data looks like this (203) 334-9086 when i
just search for the numbers it fails to find a match. If i input it as
(203)
334-9086 it works. How do I get around this?
Thanks in advance???
Bob Smith

Hi Robert

You didn't answer my question about whether this is an MDB or an ADP. I
am
now assuming the latter.

This means that the form's Recordset and RecordsetClone properties will
be
ADODB Recordsets, not DAO Recordsets. The two objects have markedly
different properties and methods. Specifically, the differences that are
pertinent to your code are:

1. DAO has a FindFirst method, while with ADO you must use MoveFirst
followed by Find.

2. DAO has a NoMatch property, while with ADO you must use EOF.

3. With ADO, each reference to RecordsetClone returns a new instance,
while
with DAO each reference returns the same instance. Therefore with ADO
you
must assign the RecordsetClone to a Recordset variable.

So, your code should look like this:

Private Sub cmdFind_Click()
Dim strHome_Phone_Number As String
Dim rsc As ADODB.Recordset
strHome_Phone_Number = InputBox( _
"Enter the Home phone Number for the Customer you want to
locate")
Set rsc = Me.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
Set rsc = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Guest]

Graham,
I'm back to working on this, and found a piece of code to do something else
i wanted on this form. It works fine in Northwind, but when I add the code to
my form it pop's up dialog looking for input to query on. Kind of like a
parameter query.
I found found the code here.

http://msdn.microsoft.com/archive/d...e/en-us/dnaraccessdev/html/ODC_SynchForms.asp

i added it to Northwind to test it works perfectly. here is the form code.
i will attach as much as i can. cmdorders.click event is where it pops up
this dialog
I cant paste the dialog, but here is the code.
Thanks in advance.

Bob

Option Compare Database
Option Explicit

Private Sub cmdAdd_Click()
Dim testmsg As Integer
testmsg = MsgBox("Do you want to add a record?", 1, "Add Record?")
If testmsg = 1 Then
'Add a new row to the recordset
Me.Recordset.AddNew
Me.Recordset("LastName") = " "

Me.Recordset.Update

'Move to the row that was added
Me.Bookmark = Me.Recordset.Bookmark
Else
End If
End Sub

Private Sub cmdDelete_Click()
Dim intAnswer As Integer

'Ask user if they really want to delete the row
intAnswer = MsgBox("Are You Sure???", _
vbYesNo + vbQuestion, _
"Delete Current Record?")

'If they respond yes, delete the row and
'move to the next row
If intAnswer = vbYes Then
Me.Recordset.Delete
Call cmdNext_Click
Me.Refresh
End If
End Sub

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub cmdFind_Click()

'Dim strClientID As String
' Dim varBookmark As Variant
' Dim strHome_Phone_Number As Variant
' Dim strsql As String

'Store the book of the current record
'varBookmark = Me.Recordset.Bookmark

'Attempt to locate another client
'strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
for the Customer you want to locate")

' Dim rst As ADODB.Recordset
' Set rst = New ADODB.Recordset
'strsql = "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')"
' Establish the connection and cursor type,
' and open active recordset
' rst.ActiveConnection = CurrentProject.Connection
'rst.CursorType = adOpenDynamic
'rst.LockType = adLockOptimistic

'rst.Open "Select * from tblCustomers WHERE [LastName] =
('strHome_Phone_Number')", Options:=adCmdText
' rst.Open "Select * from tblCustomers "
'rst.Open strsql

'With Me.RecordsetClone
'.FindFirst "[Home_Phone] = '" & strHome_Phone_Number & "'"
'.FindFirst "[Home_Phone] = ('strHome_Phone_Number')"
'MsgBox ("I am here")
'If .NoMatch Then
' MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"

'MsgBox ("Now I am here")
'Else
' Me.Bookmark = .Bookmark
'MsgBox ("No I really here")
'End If
'End With



'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1

'If client not found, display a message and return to
'the original record
' If Me.Recordset.EOF Then
' MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
' Me.Recordset.Bookmark = varBookmark

'If client found, synchronize the form with the
'underlying recordset
' Else
' Me.Bookmark = Me.Recordset.Bookmark
'End If
'End Sub
'this section was removed to add a home made input box for the phone number
please see frmSearchPhone
'Private Sub cmdFind_Click()
' Dim strHome_Phone_Number As String
' Dim rsc As ADODB.Recordset
' strHome_Phone_Number = InputBox( _
' "Enter the Home phone Number for the Customer you want to locate")
'Set rsc = Me.RecordsetClone
'With rsc
' .MoveFirst
' .Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
' If .EOF Then
' MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
' Else
' Me.Bookmark = .Bookmark
'End If
'End With
'Set rsc = Nothing
'This section was added to call frmsearchphone
DoCmd.OpenForm "frmSearchPhone", WindowMode:=acDialog
End Sub




Private Sub cmdNext_Click()

'Move to the next record in the recordset
Me.Recordset.MoveNext

'If at EOF, move to the previous record
If Me.Recordset.EOF Then
Me.Recordset.MovePrevious
MsgBox "Already at Last Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

Private Sub cmdOrders_Click()
Dim strWhereCond
strWhereCond = "ID = Forms!frmCustomers_info!ID"
DoCmd.OpenForm "tblCustomers_orders_Query",
wherecondition:=strWhereCond
'strWhereCond = "ID = Forms!frm_Customerinfo!ID"
'DoCmd.OpenForm "tblCustomers_orders_Query", wherecondition:=strWhereCond
End Sub

Private Sub cmdPrevious_Click()

'Move to the next record in the recordset
Me.Recordset.MovePrevious

'If at BOF, move to the next record
If Me.Recordset.BOF Then
Me.Recordset.MoveNext
MsgBox "Already at First Record!!"
End If

'Set the bookmark of the form to the bookmark
'of the recordset underlying the form
Me.Bookmark = Me.Recordset.Bookmark
End Sub

'Private Sub Form_Current()

' Dim strWhereCond
' strWhereCond = "ID = Forms!frm_Customerinfo!ID"
' 'strWhereCond = "CustomerID = Forms!Customers!CustomerID"
'If IsLoaded("tblCustomers_orders_Query") Then
' DoCmd.OpenForm "tblCustomers_orders_Query",
wherecondition:=strWhereCond
'End If
'End Sub




Private Sub Form_Load()

'Declare and instantiate a recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

'Establish the Connection, Cursor Type, and
'Lock Type and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.CursorLocation = adUseClient
rst.LockType = adLockOptimistic
rst.Open "Select * from tblCustomers", Options:=adCmdText

'Set the form's recordset to the recordset just created
Set Me.Recordset = rst
End Sub

Hi Robert

Probably the most elegant way around this is to create your own InputBox.
(I always avoid InputBox anyway, except for something quick and dirty,
because it's limited and ugly.)

Create a small unbound form (frmSearchPhone) with a textbox for the phone
number (txtSearchPhone) and use the same input mask as you have for the
actual field. Add two buttons - cmdFind and cmdCancel - and set the Default
property of the first and the Cancel property of the second.

Turn off all the default stuff like record selectors and navigation buttons
and control menu.

For cmdCancel, simply close the current form:

DoCmd.Close acForm, Me.Name

Use the same code as below for cmdFind, with the following changes:

Private Sub cmdFind_Click()
Dim frm as Form
Dim rsc As ADODB.Recordset
Set frm = Forms("name of your main form")
Set rsc = frm.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtSearchPhone & "'"
If .EOF Then
MsgBox "Home Phone Number " & txtSearchPhone & " Not Found!!"
txtSearchPhone.SetFocus
Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If
End With
Set rsc = Nothing
Set frm = Nothing
End Sub

On your main form, all the command button needs to do is open the new form
modally:

DoCmd.OpenForm "frmSearchPhone", WindowMode:=acDialog
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


"(e-mail address removed)"

Graham,
You are a genius. Thank you for your help. I was really stumped. I now
have
a supplemental question. I have to figure out how to input the find. I am
using an Input mask, and so teh data looks like this (203) 334-9086 when i
just search for the numbers it fails to find a match. If i input it as
(203)
334-9086 it works. How do I get around this?
Thanks in advance???
Bob Smith

Hi Robert

You didn't answer my question about whether this is an MDB or an ADP. I
am
now assuming the latter.

This means that the form's Recordset and RecordsetClone properties will
be
ADODB Recordsets, not DAO Recordsets. The two objects have markedly
different properties and methods. Specifically, the differences that are
pertinent to your code are:

1. DAO has a FindFirst method, while with ADO you must use MoveFirst
followed by Find.

2. DAO has a NoMatch property, while with ADO you must use EOF.

3. With ADO, each reference to RecordsetClone returns a new instance,
while
with DAO each reference returns the same instance. Therefore with ADO
you
must assign the RecordsetClone to a Recordset variable.

So, your code should look like this:

Private Sub cmdFind_Click()
Dim strHome_Phone_Number As String
Dim rsc As ADODB.Recordset
strHome_Phone_Number = InputBox( _
"Enter the Home phone Number for the Customer you want to
locate")
Set rsc = Me.RecordsetClone
With rsc
.MoveFirst
.Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
If .EOF Then
MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
Set rsc = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Bob

It's a bit hard to read your code with so many lines that have been
commented out

However, I think that instead of:

strWhereCond = "ID = Forms!frmCustomers_info!ID"

you should have:

strWhereCond = "ID = " & Me!ID

(this is assuming the ID is numeric. If it is text then enclose the value in
quotes:

strWhereCond = "ID = '" & Me!ID & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

 

[Guest]

Thanks, that still doesn't work. I need to rethink my logic. It seems it may
work if i use a differect key field. The maybe phoone number would work, but
i really want the releation to happen automatically.

I will clean up my code so you can read it better.
then maybe you will have a idea of why its faqiling.

Thanks
Bob smith

Hi Bob

It's a bit hard to read your code with so many lines that have been
commented out

However, I think that instead of:

strWhereCond = "ID = Forms!frmCustomers_info!ID"

you should have:

strWhereCond = "ID = " & Me!ID

(this is assuming the ID is numeric. If it is text then enclose the value in
quotes:

strWhereCond = "ID = '" & Me!ID & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Graham,
I'm back to working on this, and found a piece of code to do something
else
i wanted on this form. It works fine in Northwind, but when I add the code
to
my form it pop's up dialog looking for input to query on. Kind of like a
parameter query.
I found found the code here.

http://msdn.microsoft.com/archive/d...e/en-us/dnaraccessdev/html/ODC_SynchForms.asp

i added it to Northwind to test it works perfectly. here is the form code.
i will attach as much as i can. cmdorders.click event is where it pops up
this dialog
I cant paste the dialog, but here is the code.
Thanks in advance.

Bob

 

[Graham Mandeno]

Hi Bob

How is it failing now? It should certainly not be giving you a query
parameter popup, unless ID is a text field.

Is ID a field in the recordsource of the form containing the "Orders"
command button?

Is ID also a key field in the recordsource of the form you are trying to
open?

More information, please
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Thanks, that still doesn't work. I need to rethink my logic. It seems it
may
work if i use a differect key field. The maybe phoone number would work,
but
i really want the releation to happen automatically.

I will clean up my code so you can read it better.
then maybe you will have a idea of why its faqiling.

Thanks
Bob smith

Hi Bob

It's a bit hard to read your code with so many lines that have been
commented out

However, I think that instead of:

strWhereCond = "ID = Forms!frmCustomers_info!ID"

you should have:

strWhereCond = "ID = " & Me!ID

(this is assuming the ID is numeric. If it is text then enclose the value
in
quotes:

strWhereCond = "ID = '" & Me!ID & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Graham,
I'm back to working on this, and found a piece of code to do something
else
i wanted on this form. It works fine in Northwind, but when I add the
code
to
my form it pop's up dialog looking for input to query on. Kind of like
a
parameter query.
I found found the code here.

http://msdn.microsoft.com/archive/d...e/en-us/dnaraccessdev/html/ODC_SynchForms.asp

i added it to Northwind to test it works perfectly. here is the form
code.
i will attach as much as i can. cmdorders.click event is where it pops
up
this dialog
I cant paste the dialog, but here is the code.
Thanks in advance.

Bob

 

[Guest]

You are absolutly correct. It throw a a debug window. Must not of liked
something. I did some looking and was thinking along the same lines as you.
The field ID is created in a query which releates the customers to the orders
table. the both key on id and customerID as the releated field. When both are
numiris fields. the strange thig is if i run it teh way i had it, and the
prameter query pops i can fill in a customer number, and it will bring up
that customers records, so its only failing to automatically find that
customer number.

If you want to send me your email i'll send tou the entire mdb, but you'll
probably think i dadabase is a hack job. I apreciate anmy pointers i can get.
My girl friend need needs this database, and i keep getting pressured by her.
Maybe you could alos recommend a good book. I have lots of experience but
access is auch a bear to understand.

Thanks

Bob Smith

Hi Bob

How is it failing now? It should certainly not be giving you a query
parameter popup, unless ID is a text field.

Is ID a field in the recordsource of the form containing the "Orders"
command button?

Is ID also a key field in the recordsource of the form you are trying to
open?

More information, please
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Thanks, that still doesn't work. I need to rethink my logic. It seems it
may
work if i use a differect key field. The maybe phoone number would work,
but
i really want the releation to happen automatically.

I will clean up my code so you can read it better.
then maybe you will have a idea of why its faqiling.

Thanks
Bob smith

Hi Bob

It's a bit hard to read your code with so many lines that have been
commented out

However, I think that instead of:

strWhereCond = "ID = Forms!frmCustomers_info!ID"

you should have:

strWhereCond = "ID = " & Me!ID

(this is assuming the ID is numeric. If it is text then enclose the value
in
quotes:

strWhereCond = "ID = '" & Me!ID & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
Graham,
I'm back to working on this, and found a piece of code to do something
else
i wanted on this form. It works fine in Northwind, but when I add the
code
to
my form it pop's up dialog looking for input to query on. Kind of like
a
parameter query.
I found found the code here.

http://msdn.microsoft.com/archive/d...e/en-us/dnaraccessdev/html/ODC_SynchForms.asp

i added it to Northwind to test it works perfectly. here is the form
code.
i will attach as much as i can. cmdorders.click event is where it pops
up
this dialog
I cant paste the dialog, but here is the code.
Thanks in advance.

Bob

 

[Graham Mandeno]

Hi Bob

I hope we can solve this without you emailing me your entire database!

You are very economical with your information. So, a debug window pops up.
What is the message? What is the line of code it stops on?

I suspect from what you say that your Orders form has a field named ID, but
the form you are clicking from has not.

The code should be like this:
strWhereCond = "[aaaa] = " & Me![bbbb]
where:
aaaa is the name of the ID field in the form you are opening
bbbb is the name of the ID field in the form containing the command
button

You might like to try:
strWhereCond = "ID = " & Me!CustomerID
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

You are absolutly correct. It throw a a debug window. Must not of liked
something. I did some looking and was thinking along the same lines as
you.
The field ID is created in a query which releates the customers to the
orders
table. the both key on id and customerID as the releated field. When both
are
numiris fields. the strange thig is if i run it teh way i had it, and the
prameter query pops i can fill in a customer number, and it will bring up
that customers records, so its only failing to automatically find that
customer number.

If you want to send me your email i'll send tou the entire mdb, but you'll
probably think i dadabase is a hack job. I apreciate anmy pointers i can
get.
My girl friend need needs this database, and i keep getting pressured by
her.
Maybe you could alos recommend a good book. I have lots of experience but
access is auch a bear to understand.

Thanks

Bob Smith

Hi Bob

How is it failing now? It should certainly not be giving you a query
parameter popup, unless ID is a text field.

Is ID a field in the recordsource of the form containing the "Orders"
command button?

Is ID also a key field in the recordsource of the form you are trying to
open?

More information, please
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Thanks, that still doesn't work. I need to rethink my logic. It seems
it
may
work if i use a differect key field. The maybe phoone number would
work,
but
i really want the releation to happen automatically.

I will clean up my code so you can read it better.
then maybe you will have a idea of why its faqiling.

Thanks
Bob smith

:

Hi Bob

It's a bit hard to read your code with so many lines that have been
commented out

However, I think that instead of:

strWhereCond = "ID = Forms!frmCustomers_info!ID"

you should have:

strWhereCond = "ID = " & Me!ID

(this is assuming the ID is numeric. If it is text then enclose the
value
in
quotes:

strWhereCond = "ID = '" & Me!ID & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
Graham,
I'm back to working on this, and found a piece of code to do
something
else
i wanted on this form. It works fine in Northwind, but when I add
the
code
to
my form it pop's up dialog looking for input to query on. Kind of
like
a
parameter query.
I found found the code here.

http://msdn.microsoft.com/archive/d...e/en-us/dnaraccessdev/html/ODC_SynchForms.asp

i added it to Northwind to test it works perfectly. here is the form
code.
i will attach as much as i can. cmdorders.click event is where it
pops
up
this dialog
I cant paste the dialog, but here is the code.
Thanks in advance.

Bob

 

[Guest]

Sorry, I don't mean to be scant with my info. I have to revisit the error.
I'm at work, and the most recent .mdb is at home. I beleive it was failing on
the line of code you had sent me last. Of course VB's debugging leaves
something to be desired. I will try your last comment, if it fail, i will
send you more info.

Thanks again for your help.

Bob Smith

Hi Bob

I hope we can solve this without you emailing me your entire database!

You are very economical with your information. So, a debug window pops up.
What is the message? What is the line of code it stops on?

I suspect from what you say that your Orders form has a field named ID, but
the form you are clicking from has not.

The code should be like this:
strWhereCond = "[aaaa] = " & Me![bbbb]
where:
aaaa is the name of the ID field in the form you are opening
bbbb is the name of the ID field in the form containing the command
button

You might like to try:
strWhereCond = "ID = " & Me!CustomerID
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

You are absolutly correct. It throw a a debug window. Must not of liked
something. I did some looking and was thinking along the same lines as
you.
The field ID is created in a query which releates the customers to the
orders
table. the both key on id and customerID as the releated field. When both
are
numiris fields. the strange thig is if i run it teh way i had it, and the
prameter query pops i can fill in a customer number, and it will bring up
that customers records, so its only failing to automatically find that
customer number.

If you want to send me your email i'll send tou the entire mdb, but you'll
probably think i dadabase is a hack job. I apreciate anmy pointers i can
get.
My girl friend need needs this database, and i keep getting pressured by
her.
Maybe you could alos recommend a good book. I have lots of experience but
access is auch a bear to understand.

Thanks

Bob Smith

Hi Bob

How is it failing now? It should certainly not be giving you a query
parameter popup, unless ID is a text field.

Is ID a field in the recordsource of the form containing the "Orders"
command button?

Is ID also a key field in the recordsource of the form you are trying to
open?

More information, please
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
Thanks, that still doesn't work. I need to rethink my logic. It seems
it
may
work if i use a differect key field. The maybe phoone number would
work,
but
i really want the releation to happen automatically.

I will clean up my code so you can read it better.
then maybe you will have a idea of why its faqiling.

Thanks
Bob smith

:

Hi Bob

It's a bit hard to read your code with so many lines that have been
commented out

However, I think that instead of:

strWhereCond = "ID = Forms!frmCustomers_info!ID"

you should have:

strWhereCond = "ID = " & Me!ID

(this is assuming the ID is numeric. If it is text then enclose the
value
in
quotes:

strWhereCond = "ID = '" & Me!ID & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
Graham,
I'm back to working on this, and found a piece of code to do
something
else
i wanted on this form. It works fine in Northwind, but when I add
the
code
to
my form it pop's up dialog looking for input to query on. Kind of
like
a
parameter query.
I found found the code here.

http://msdn.microsoft.com/archive/d...e/en-us/dnaraccessdev/html/ODC_SynchForms.asp

i added it to Northwind to test it works perfectly. here is the form
code.
i will attach as much as i can. cmdorders.click event is where it
pops
up
this dialog
I cant paste the dialog, but here is the code.
Thanks in advance.

Bob

 

[Graham Mandeno]

Hi Bob

Did you manage to get this working?

--
Best regards,

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

 

[Guest]

Well yes and no. I got it to pop up the correct table, but it doesn't find
the correct record.
I've been messing with it, but still haven't gotten it correct.
here is the code now.
Private Sub cmdOrders_Click()
Dim strWhereCond
strWhereCond = "CustomerID = " & Me!ID
DoCmd.OpenForm "tblCustomers_orders_Query",
wherecondition:=strWhereCond
End Sub
The other thing is that when i change the field in the form to
=tblCustomers!ID it throws a debug error
It says i'm using the wrong syntax.
Here is the error
Run Time Error '2447'
There is an invalid use of the dot (.) or ! operator or a invalid parentheses.

When i make it just ID it works but brings up the wrong record.
But it does bring up the correct form with data populated just that it does
not releate to the customer ID in the order form.
Strange

Thanks Again for you help

Bob

Hi Bob

Did you manage to get this working?

--
Best regards,

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Sorry, I don't mean to be scant with my info. I have to revisit the error.
I'm at work, and the most recent .mdb is at home. I beleive it was failing
on
the line of code you had sent me last. Of course VB's debugging leaves
something to be desired. I will try your last comment, if it fail, i will
send you more info.

Thanks again for your help.

Bob Smith

 

[Graham Mandeno]

Hi Bob

Can you please confirm that:

1. The primary key field in tblCustomers is named "ID"

2. This field is present in the record source of the form which contains the
code below.

3. The foreign key field in your Orders table is named "CustomerID".

4. This field is present in the record source of the form
"tblCustomers_orders_Query".

I don't understand what you mean when you say:

The other thing is that when i change the field in the form to
=tblCustomers!ID it throws a debug error

Do you mean that you are trying to set the controlsource of a textbox to
that expression? There should be no need to do that. Simply bind the
textbox to the field by setting its ControlSource to "ID".

Also, do you really have forms whose names start with "tbl"? If you don't
find this confusing, then someone else certainly will, assuming that name is
for a table. If you wish to use naming conventions with prefixes (and it's
a very good idea to do so), then I suggest you prefix your form names with
"frm".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



"(e-mail address removed)"

Well yes and no. I got it to pop up the correct table, but it doesn't find
the correct record.
I've been messing with it, but still haven't gotten it correct.
here is the code now.
Private Sub cmdOrders_Click()
Dim strWhereCond
strWhereCond = "CustomerID = " & Me!ID
DoCmd.OpenForm "tblCustomers_orders_Query",
wherecondition:=strWhereCond
End Sub
The other thing is that when i change the field in the form to
=tblCustomers!ID it throws a debug error
It says i'm using the wrong syntax.
Here is the error
Run Time Error '2447'
There is an invalid use of the dot (.) or ! operator or a invalid
parentheses.

When i make it just ID it works but brings up the wrong record.
But it does bring up the correct form with data populated just that it
does
not releate to the customer ID in the order form.
Strange

Thanks Again for you help

Bob

Hi Bob

Did you manage to get this working?

--
Best regards,

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Sorry, I don't mean to be scant with my info. I have to revisit the
error.
I'm at work, and the most recent .mdb is at home. I beleive it was
failing
on
the line of code you had sent me last. Of course VB's debugging leaves
something to be desired. I will try your last comment, if it fail, i
will
send you more info.

Thanks again for your help.

Bob Smith

 

[Guest]

Excellent, I'll try all you suggest, and provide you teh infor you asked
later. I do try to name my froms correctly, but i think i messed up one, The
rest i need to review. I will shoot you off a response later today. It's
midday here, and I need to do some yard work. you know my honey do list..

Cheers!
Bob

Hi Bob

Can you please confirm that:

1. The primary key field in tblCustomers is named "ID"

2. This field is present in the record source of the form which contains the
code below.

3. The foreign key field in your Orders table is named "CustomerID".

4. This field is present in the record source of the form
"tblCustomers_orders_Query".

I don't understand what you mean when you say:

The other thing is that when i change the field in the form to
=tblCustomers!ID it throws a debug error

Do you mean that you are trying to set the controlsource of a textbox to
that expression? There should be no need to do that. Simply bind the
textbox to the field by setting its ControlSource to "ID".

Also, do you really have forms whose names start with "tbl"? If you don't
find this confusing, then someone else certainly will, assuming that name is
for a table. If you wish to use naming conventions with prefixes (and it's
a very good idea to do so), then I suggest you prefix your form names with
"frm".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



"(e-mail address removed)"

Well yes and no. I got it to pop up the correct table, but it doesn't find
the correct record.
I've been messing with it, but still haven't gotten it correct.
here is the code now.
Private Sub cmdOrders_Click()
Dim strWhereCond
strWhereCond = "CustomerID = " & Me!ID
DoCmd.OpenForm "tblCustomers_orders_Query",
wherecondition:=strWhereCond
End Sub
The other thing is that when i change the field in the form to
=tblCustomers!ID it throws a debug error
It says i'm using the wrong syntax.
Here is the error
Run Time Error '2447'
There is an invalid use of the dot (.) or ! operator or a invalid
parentheses.

When i make it just ID it works but brings up the wrong record.
But it does bring up the correct form with data populated just that it
does
not releate to the customer ID in the order form.
Strange

Thanks Again for you help

Bob

Hi Bob

Did you manage to get this working?

--
Best regards,

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"
Sorry, I don't mean to be scant with my info. I have to revisit the
error.
I'm at work, and the most recent .mdb is at home. I beleive it was
failing
on
the line of code you had sent me last. Of course VB's debugging leaves
something to be desired. I will try your last comment, if it fail, i
will
send you more info.

Thanks again for your help.

Bob Smith