Record Find Problem

[robert.f.smith]

Ok,
I reviewed all this, and I'll try to answer in order.

First
The Primary key is ID in the tblCustomers.
Hey guess i was reviewing your questions one by one, and it works now. I
think it may have worked after making the changes you suggested before, or
maybe after I made sure that the customer ID bound correctly to the the
tblcustomers.
I also may have had garbage data. I really only had teat data, so i made
some changes to the data and I think i'm goo now. I stoill havce more to do,
I will probably be calling one you soon again.

My next problem is modifing the ophone search routine. i want to put an if
in place to test the input number against all of the phone number fields. If
it finds a match in any field it should bring up that customer. Do you
suggesgt an if or a case statement, I will show you what i've done so far.
It bombs for some reason.

I need to find it and I'll send it on over.

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

 

[robert.f.smith]

Her ewis the code I wrote for the phone search, but it wont find every number
i input in all there fields.
Dim frm As Form
Dim rsc As ADODB.Recordset
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone

With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
If .EOF Then
'MsgBox "Phone Number " & txtsearchphone & " Not Found Do you
want to Search Cell Number Y/N?"
Cell_search = MsgBox("Search Cell Number", 1, "Test")
MsgBox ("I am here")
If Cell_search = 1 Then
.MoveFirst
MsgBox ("I am also here")
.Find "[Cell_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
Else
MsgBox ("Number not found")
End If


txtsearchphone.SetFocus

Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If


End With
Set rsc = Nothing
Set frm = Nothing
End Sub

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

 

[Graham Mandeno]

Hi Bob

Try doing this with a loop, to check each phone field in turn...

Private Sub cmdFind_Click()
Dim frm as Form
Dim rsc As ADODB.Recordset
Dim i as Integer
Dim sField as String
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone
With rsc
For i = 1 to 3
sField = Choose( i, "Home_Phone", "Cell_Phone", "Work_Phone")
.MoveFirst
.Find "[" & sField & "] = '" & txtSearchPhone & "'"
If Not .EOF then Exit For ' found
Next i
If .EOF Then
MsgBox "Phone Number " & txtSearchPhone & " Not Found!!"
txtSearchPhone.SetFocus
Else
MsgBox "Match found in " & sField
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If
End With
Set rsc = Nothing
Set frm = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Her ewis the code I wrote for the phone search, but it wont find every
number
i input in all there fields.
Dim frm As Form
Dim rsc As ADODB.Recordset
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone

With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
If .EOF Then
'MsgBox "Phone Number " & txtsearchphone & " Not Found Do you
want to Search Cell Number Y/N?"
Cell_search = MsgBox("Search Cell Number", 1, "Test")
MsgBox ("I am here")
If Cell_search = 1 Then
.MoveFirst
MsgBox ("I am also here")
.Find "[Cell_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
Else
MsgBox ("Number not found")
End If


txtsearchphone.SetFocus

Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If


End With
Set rsc = Nothing
Set frm = Nothing
End Sub

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

 

[robert.f.smith]

Graham,
You are the best this worked perfectly. I guess i have to learn to think out
of the box. The id of using an iff and an array never crossed my mind.
Thanks so much.
I am continuing on with my project. I will let you know how I make out.

Thanks Again.

Bob Smith

Hi Bob

Try doing this with a loop, to check each phone field in turn...

Private Sub cmdFind_Click()
Dim frm as Form
Dim rsc As ADODB.Recordset
Dim i as Integer
Dim sField as String
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone
With rsc
For i = 1 to 3
sField = Choose( i, "Home_Phone", "Cell_Phone", "Work_Phone")
.MoveFirst
.Find "[" & sField & "] = '" & txtSearchPhone & "'"
If Not .EOF then Exit For ' found
Next i
If .EOF Then
MsgBox "Phone Number " & txtSearchPhone & " Not Found!!"
txtSearchPhone.SetFocus
Else
MsgBox "Match found in " & sField
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If
End With
Set rsc = Nothing
Set frm = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Her ewis the code I wrote for the phone search, but it wont find every
number
i input in all there fields.
Dim frm As Form
Dim rsc As ADODB.Recordset
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone

With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
If .EOF Then
'MsgBox "Phone Number " & txtsearchphone & " Not Found Do you
want to Search Cell Number Y/N?"
Cell_search = MsgBox("Search Cell Number", 1, "Test")
MsgBox ("I am here")
If Cell_search = 1 Then
.MoveFirst
MsgBox ("I am also here")
.Find "[Cell_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
Else
MsgBox ("Number not found")
End If


txtsearchphone.SetFocus

Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If


End With
Set rsc = Nothing
Set frm = Nothing
End Sub

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

 

[Graham Mandeno]

That's excellent, Bob. Glad to hear it worked so well.

Yep, "the box" is not a good place to confine yourself when you're doing
this kind of stuff

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Graham,
You are the best this worked perfectly. I guess i have to learn to think
out
of the box. The id of using an iff and an array never crossed my mind.
Thanks so much.
I am continuing on with my project. I will let you know how I make out.

Thanks Again.

Bob Smith

Hi Bob

Try doing this with a loop, to check each phone field in turn...

Private Sub cmdFind_Click()
Dim frm as Form
Dim rsc As ADODB.Recordset
Dim i as Integer
Dim sField as String
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone
With rsc
For i = 1 to 3
sField = Choose( i, "Home_Phone", "Cell_Phone", "Work_Phone")
.MoveFirst
.Find "[" & sField & "] = '" & txtSearchPhone & "'"
If Not .EOF then Exit For ' found
Next i
If .EOF Then
MsgBox "Phone Number " & txtSearchPhone & " Not Found!!"
txtSearchPhone.SetFocus
Else
MsgBox "Match found in " & sField
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If
End With
Set rsc = Nothing
Set frm = Nothing
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"(e-mail address removed)"

Her ewis the code I wrote for the phone search, but it wont find every
number
i input in all there fields.
Dim frm As Form
Dim rsc As ADODB.Recordset
Set frm = Forms("frmCustomer_info")
Set rsc = frm.RecordsetClone

With rsc
.MoveFirst
.Find "[Home_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
If .EOF Then
'MsgBox "Phone Number " & txtsearchphone & " Not Found Do you
want to Search Cell Number Y/N?"
Cell_search = MsgBox("Search Cell Number", 1, "Test")
MsgBox ("I am here")
If Cell_search = 1 Then
.MoveFirst
MsgBox ("I am also here")
.Find "[Cell_Phone] = '" & txtsearchphone & "'"
txtsearchphone.SetFocus
Else
MsgBox ("Number not found")
End If


txtsearchphone.SetFocus

Else
frm.Bookmark = .Bookmark
DoCmd.Close acForm, Me.Name
End If


End With
Set rsc = Nothing
Set frm = Nothing
End Sub

:

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)"
message
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