Find Record Question

[Bongard]

Hi, I am trying to insert a find record option within one of my forms.
I have a combo box that lists all the loan numbers in the table and
allows the user to select which loan number he wants to select. Once
this number is selected I want the user to be brought straight to that
record.
My code looks like this:

Private Sub findcmd_AfterUpdate()

DoCmd.FindRecord findwhat = [findcmd]
Me.[Loan Number].SetFocus

End Sub

where findcmd is the name of the combo box that gets filled in by the
user. However, when you selct a loan number from the little dropdown it
gives me the error "A macro set to one of the current field's
properties failed because of an error in a FindRecord action argument."

I am not really sure what this means or how to go about solving my
problem.

Thanks in advance!

 

[ruralguy via AccessMonster.com]

Let the Wizard create another ComboBox on your form that can "find a record".
I think you will find it works better than your current one.

Hi, I am trying to insert a find record option within one of my forms.
I have a combo box that lists all the loan numbers in the table and
allows the user to select which loan number he wants to select. Once
this number is selected I want the user to be brought straight to that
record.
My code looks like this:

Private Sub findcmd_AfterUpdate()

DoCmd.FindRecord findwhat = [findcmd]
Me.[Loan Number].SetFocus

End Sub

where findcmd is the name of the combo box that gets filled in by the
user. However, when you selct a loan number from the little dropdown it
gives me the error "A macro set to one of the current field's
properties failed because of an error in a FindRecord action argument."

I am not really sure what this means or how to go about solving my
problem.

Thanks in advance!

 

[Bongard]

I have tried this but it doesn't seem to do anything. I tie it to the
field Loan Number but this is the code that goes into the after update
when I finish the wizard.

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DATE] = #" & Format(Me![Combo31], "mm\/dd\/yyyy") &
"#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I don't know why it pins the code to the key field (DATE). Do you know
how I can manipulate the code or any other method of inserting this
findrecord action into my form?

Thanks in advance!
Brian

 

[ruralguy via AccessMonster.com]

What version of Access are you using? This should be an UNBOUND ComboBox!
Are you sure you are not picking the date field when you are answering
questions from the wizard?

I have tried this but it doesn't seem to do anything. I tie it to the
field Loan Number but this is the code that goes into the after update
when I finish the wizard.

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DATE] = #" & Format(Me![Combo31], "mm\/dd\/yyyy") &
"#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I don't know why it pins the code to the key field (DATE). Do you know
how I can manipulate the code or any other method of inserting this
findrecord action into my form?

Thanks in advance!
Brian

 

[Bongard]

RuralGuy thanks for your posts. I am using Access 2003 and I am sure
that I am choosing loan number as the field when going through the
combo box wizard. I tried it a couple more times and it still gives me
this in vba for the after update event of the combo box

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DATE] = #" & Format(Me![Combo31], "mm\/dd\/yyyy") &
"#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Any other ideas? I really need to get this to work somehow because
within this form view our mortgage people are going to be making small
changes to all the different loans. When the database finally begins
being used in production, there will likely be a couple hundred loans
in the file. This findrecord I feel is the only logical way for them to
be able to navigate to a certain record.

Thanks!
Brian

 

[ruralguy via AccessMonster.com]

That's weird! What is the chance of zipping up a sample of your db and
sending me a copy at Rural Guy at Wild Blue Dot Net (without the spaces of
course)? Strip any sensitive data but leave enough sample data to
demonstrate the issue.

RuralGuy thanks for your posts. I am using Access 2003 and I am sure
that I am choosing loan number as the field when going through the
combo box wizard. I tried it a couple more times and it still gives me
this in vba for the after update event of the combo box

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DATE] = #" & Format(Me![Combo31], "mm\/dd\/yyyy") &
"#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any other ideas? I really need to get this to work somehow because
within this form view our mortgage people are going to be making small
changes to all the different loans. When the database finally begins
being used in production, there will likely be a couple hundred loans
in the file. This findrecord I feel is the only logical way for them to
be able to navigate to a certain record.

Thanks!
Brian

 

[Bongard]

I sent it to you so let me know if it doesn't get there.


Thanks man,
-brian

 

[ruralguy via AccessMonster.com]

It's here! I'll look at it and get back to you.

I sent it to you so let me know if it doesn't get there.

Thanks man,
-brian

 

[ruralguy via AccessMonster.com]

Brian had a date field as the primary key of the table and I suspect the
Wizard *REALLY* wants to look up records by the primary key. I didn't know
that of the Wizard. I hand coded a Combo box to look up records by the [Loan
Number] and it works just fine.