stuck on find and goto VBA

[dubsdj]

Hi I'm trying to search for a surname on a form
from a table called students. I want the result to goto the record on the form.

The tablename is Students and the field I want to search is called Surname

I wrote this code:

Dim Search As String
Search = InputBox("Please enter the Surname", "OrderSearch")
DoCmd.OpenForm "Pastoral record form", acNormal
DoCmd.FindRecord "[Surname] =" & Search, acEntire, False, acSearchAll, , acCurrent, True


But I get some weird error about macros... does anybody know what I have done wrong?

thanks

 

[jal]

Post the exact error message please.

 

[namliam]

The open form opens your form, but the subsequent FindRecord is NOT executed on that form, rather on the current one.

If you JUST want to show those surnames, check out the Openform in the access help. In particular the "Filter" and/or "Where" components where you can (easily) limit the values showed by the form you are opening.

 

[dubsdj]

Hi this is the error im getting:

A macro set to one of the current fields properties failed because one of the arguments in the findrecord action argument.

The record that im trying to find is on the current form, I have a find button which the user clicks and it asks for a surname. I then just want it to go to the record when it searches the surname.

I'm sure i must be close, but still no luck.

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

Dim Search As String
Search = InputBox("Please enter the Surname", "OrderSearch")
DoCmd.FindRecord "[Surname] =" & Search, acEntire, False, acSearchAll, , acCurrent, True

 

[namliam]

Text strings have to be enclosed by quotes....

DoCmd.FindRecord "[Surname] ='" & Search & "'", acEntire, False, acSearchAll, , acCurrent, True

If it were a number no quotes would have been needed.
If it were a date, the quotes have to be replaced by #

 

[dubsdj]

Dim Search As String

Search = InputBox("Please enter the Surname", "OrderSearch")
DoCmd.GoToRecord , acGoTo, "[Students].[Surname] = " & Search


How is this?

I'm getting a type mismatch though...

I'm trying to goto the record which is on the current form by searching a table and its fieldname..

any closer?

 

[namliam]

Text strings have to be enclosed by quotes....

What part of that did you miss?? Let me repeat...

Text strings have to be enclosed by quotes....

Now go back to my previous post and find out how to fix it.

 

[dubsdj]

I didn't miss what you said and i really appreciate how helpful you have been, I typed this:

Private Sub Command96_Click()
On Error GoTo Err_Command96_Click

Dim Search As String

Search = InputBox("Please enter the Surname", "OrderSearch")
DoCmd.GoToRecord , acGoTo, "[Students].[Surname] = '" & Search & "'"


but I still get a type mismatch..

Surname is in the students table and its plain text

 

[namliam]

You cannot use toto to search on anything, you have to specify a record number to jump to or a number of records to jump from here.

If you want to "find" something.... use findrecord like you were doing.

 

[dubsdj]

ok thanks now I get an error that says

A macro set to one of the current fields properties failed because one of the arguments in the findrecord action argument.

when I use this:
DoCmd.FindRecord "[Students].[surname] ='" & Search & "'", acEntire, False, acSearchAll, , acCurrent, True

 

[namliam]

I think you cannot use the "True" in your statement if the surname field is not selected.

Try adding
YourSurnameField.Setfocus
Before your findrecord command.

I ussually dont use these commands but search in the recordset instead.

 

[dubsdj]

Ok, did that and pointed the focus to the studentID field in the form. I now get a nice message saying:

Microsoft access cant move the focus to the control studentID


is this because its greyed out on the form? if so is there any way of ungraying it? sorry , this database wasn't made by me so I'm trying to fight my way through it.

The studentID field is based on a query in its properties. I tried enabling it but its still greyed out..

I think i have come to a dead end...


bedankt

 

[namliam]

If you are searching for SURNAME, WHY o WHY would you move focus to the ID? And if its grayed then it cannot get focus...

Try some code like this:

Dim Rs as DAO.Recordset
set rs = Me.RecordsetClone
rs.findFirst "[surname] ='" & Search & "'"
me.bookmark = rs.bookmark
rs.close
set rs  = nothing

Note: this is aircode, may contain errors
Note2: If you get an error on th DAO.Recordset line you need to activate the reference. Search the forum to fin out how to activate it.


Good luck

 

[dubsdj]

I created a field called surname which is enabled.

Dim search as string

Surname.SetFocus

Search = InputBox("Please enter the Surname", "OrderSearch")
acSearchAll, , acCurrent, True

if I type this, then it works and it finds the name Allen and goes to the page on the form:

'DoCmd.FindRecord "Allen", , , , , acAll


But if I use the variable from my search box, it doesn't work..

DoCmd.FindRecord '"& Search &"'", , , , , acAll

 

[namliam]

DoCmd.FindRecord "'"& Search &"'", , , , , acAll

Lacking the starting "

 

[dubsdj]

this is driving me insane..


Surname.SetFocus

Search = InputBox("Please enter the Surname", "OrderSearch")

DoCmd.FindRecord "'" & Search & "'", , , , , acAll

but it displays no results!

I did a 'MsgBox (Search) to see if it was grabbing my variable and it displayed what I had typed so the string has definetly got a value.

 

[dubsdj]

DoCmd.FindRecord "'" & Search & "'", , , , , acAll

didn't work so I did this instead:

DoCmd.FindRecord "" & Search & "", , , , , acAll


and it worked!!


dank je wel voor u helpen.