Combo Search issues

Okay here's the issue. On one form I have two search fields set up one to
search by name and another to search by project number. After Update they
work beautifully to move the user to the correct project. the code for these
are as follows:

Private Sub cboProject_AfterUpdate()

DoCmd.GoToRecord
Me!JobName.SetFocus
DoCmd.FindRecord Me!cboProject

'Set value of combo box equal to an empty string
Me!cboProject.Value = ""
End Sub

Private Sub CboProjectNo_AfterUpdate()

DoCmd.GoToRecord
Me!Project.SetFocus
DoCmd.FindRecord Me!CboProjectNo

'Set value of combo box equal to an empty string
Me!CboProjectNo.Value = ""
End Sub


As I said these work beautifully however here is the problem. If a user
searches for a specific project (regardless of whether it is by name or
number) they can easily move to the next project UNLESS they make a change
and update the current record. If they update a record and then try to move
to the next record they receive an error stating that "You Can't Go to the
Specified Record." If an attempt is made to search for the next record, it
will search fine but it will not go to the new search item. The user gets a
Run Time Error that states "You Can't Go to the Specified Record" and it
gives them the option to debug.

How do we get around this? Is it essential that they close the form after
every search that requires an update?
--
Lori A. Pong

I would try to save the record as soon as it is updated, either with code or
a command button. It probably doesn't want to go to the next record until the
current record, and any changes, is saved.

"Lori" wrote:

Okay here's the issue. On one form I have two search fields set up one to
search by name and another to search by project number. After Update they
work beautifully to move the user to the correct project. the code for these
are as follows:

Private Sub cboProject_AfterUpdate()

DoCmd.GoToRecord
Me!JobName.SetFocus
DoCmd.FindRecord Me!cboProject

'Set value of combo box equal to an empty string
Me!cboProject.Value = ""
End Sub

Private Sub CboProjectNo_AfterUpdate()

DoCmd.GoToRecord
Me!Project.SetFocus
DoCmd.FindRecord Me!CboProjectNo

'Set value of combo box equal to an empty string
Me!CboProjectNo.Value = ""
End Sub


As I said these work beautifully however here is the problem. If a user
searches for a specific project (regardless of whether it is by name or
number) they can easily move to the next project UNLESS they make a change
and update the current record. If they update a record and then try to move
to the next record they receive an error stating that "You Can't Go to the
Specified Record." If an attempt is made to search for the next record, it
will search fine but it will not go to the new search item. The user gets a
Run Time Error that states "You Can't Go to the Specified Record" and it
gives them the option to debug.

How do we get around this? Is it essential that they close the form after
every search that requires an update?
--
Lori A. Pong

To force the record to be saved before the find, try something like:

Private Sub cboProject_AfterUpdate()

' save the record if changed
If Me.Dirty Then Me.Dirty = False

DoCmd.GoToRecord
Me!JobName.SetFocus
DoCmd.FindRecord Me!cboProject

'Set value of combo box equal to an empty string
Me!cboProject.Value = ""

End Sub


--
Boyd Trimmell aka HiTech Coach
http://www.officeprogramming.com
http://www.hitechcoach.com


"Lori" wrote:

Okay here's the issue. On one form I have two search fields set up one to
search by name and another to search by project number. After Update they
work beautifully to move the user to the correct project. the code for these
are as follows:

Private Sub cboProject_AfterUpdate()

DoCmd.GoToRecord
Me!JobName.SetFocus
DoCmd.FindRecord Me!cboProject

'Set value of combo box equal to an empty string
Me!cboProject.Value = ""
End Sub

Private Sub CboProjectNo_AfterUpdate()

DoCmd.GoToRecord
Me!Project.SetFocus
DoCmd.FindRecord Me!CboProjectNo

'Set value of combo box equal to an empty string
Me!CboProjectNo.Value = ""
End Sub


As I said these work beautifully however here is the problem. If a user
searches for a specific project (regardless of whether it is by name or
number) they can easily move to the next project UNLESS they make a change
and update the current record. If they update a record and then try to move
to the next record they receive an error stating that "You Can't Go to the
Specified Record." If an attempt is made to search for the next record, it
will search fine but it will not go to the new search item. The user gets a
Run Time Error that states "You Can't Go to the Specified Record" and it
gives them the option to debug.

How do we get around this? Is it essential that they close the form after
every search that requires an update?
--
Lori A. Pong

I'm not sure that it has anything to do with your problem, but the line

DoCmd.GoToRecord

that appears in both of your subs isn't doing anything, as you're not telling
Access what record to go to with it. I'd try commenting them out for now.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200804/1

HELP!!!

I've tried all three suggestions, I tried code to save after a change is
made, I've tried including a new button to save the record and I've tried the
code suggested by Linq. Nothing helps.

I've also tried to refresh the form hoping that it would clear the initial
search. Nothing works.

Any other ideas would be appreciated.
--
Lori A. Pong


"Boyd Trimmell aka HiTechCoach" wrote:

To force the record to be saved before the find, try something like:

Private Sub cboProject_AfterUpdate()

' save the record if changed
If Me.Dirty Then Me.Dirty = False

DoCmd.GoToRecord
Me!JobName.SetFocus
DoCmd.FindRecord Me!cboProject

'Set value of combo box equal to an empty string
Me!cboProject.Value = ""

End Sub


--
Boyd Trimmell aka HiTech Coach
http://www.officeprogramming.com
http://www.hitechcoach.com


"Lori" wrote:

Okay here's the issue. On one form I have two search fields set up one to
search by name and another to search by project number. After Update they
work beautifully to move the user to the correct project. the code for these
are as follows:

Private Sub cboProject_AfterUpdate()

DoCmd.GoToRecord
Me!JobName.SetFocus
DoCmd.FindRecord Me!cboProject

'Set value of combo box equal to an empty string
Me!cboProject.Value = ""
End Sub

Private Sub CboProjectNo_AfterUpdate()

DoCmd.GoToRecord
Me!Project.SetFocus
DoCmd.FindRecord Me!CboProjectNo

'Set value of combo box equal to an empty string
Me!CboProjectNo.Value = ""
End Sub


As I said these work beautifully however here is the problem. If a user
searches for a specific project (regardless of whether it is by name or
number) they can easily move to the next project UNLESS they make a change
and update the current record. If they update a record and then try to move
to the next record they receive an error stating that "You Can't Go to the
Specified Record." If an attempt is made to search for the next record, it
will search fine but it will not go to the new search item. The user gets a
Run Time Error that states "You Can't Go to the Specified Record" and it
gives them the option to debug.

How do we get around this? Is it essential that they close the form after
every search that requires an update?
--
Lori A. Pong