open form from search

[spock1971]

I have a database where I'm allowing a user to update a record in a table using a form.

I want the user to be able to search by name to find therir unique reference number if they don't know it already. I've built a query and based a form on the query so the user gets a personal profile for every 'Smith' come back. They can use the navigation buttons to scroll through all the smith's until they find the one they want.

I want the user then to click on a button which will open up the main form when they find the correct person. I've tried using code like:

Open form where payroll number=forms![name search].[payroll number].current but it's not working.

Any ideas?

 

[ccox]

I would place the search on the main form you want to update.

Create an unbound combo box on the main form. The data source should be the name field from the table/query. The name can then be chosen from a list in the combo box. Then create an “after update” event for the combo box.

Sub Combo21_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[FieldName] = " & Me![Combo]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

 

[spock1971]

Is that the only way?

It's a bit messy if the user finds the correct record,makes a note of the number, goes back to the main screen and then picks the number from a dropdown box.

I've already got it going back to the navigation screen for them to enter the number as a work around.

Thought there'd be some way of telling access the current record data.

Cheers

 

[ccox]

I guess I misunderstood, sorry. I was thinking you could use the search on the main form to take you directly to the record on the main form in lieu of using your current query method.

 

[spock1971]

ok - cheers anyway.

I've got access to recognise the current payroll number now but can't set it to equal the query parameter so I'm opening up the main form and pre-populating it with the chosen payroll number. The user just needs to click 'Go' now and it works.

One extra step - but the company are happy with it.