Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

HowTo Select Datasheet Record and Open a Single Record Form.

HowTo Select Datasheet Record and Open a Single Record Form.

HowTo Select Datasheet Record and Open a Single Record Form.

I have a table in which I keep all software titles.  I frequently search this table and perform some action on the found record.  I would like to make searching easier by selecting a record in datasheet view and automatically displaying the same record in single record form view.  Does anyone have a sample or can tell me how to do this?

RE: HowTo Select Datasheet Record and Open a Single Record Form.

Sure, here the overview.
But first you MUST remove any Primary keys on your table or this Will NOT work.  You can sort the fields but you can't have a primary key.

Here is a link to my WEB site which has a screen capture of something similar.

OK, Create a new form, from your table using the form Wizard
Open the new form in design view and move all of the fields down a few inches to allow for a subform.
Add a subform and make its recordsource the exact same as your main form.

Open the subform in design view and open the On_Click event of the sub form
Add this code:

    Dim SyncCriteria As String
    Dim f As Form, rs As Recordset
    'Define the from object and recordset object for the AutoCAD form
    Set f = Forms(Screen.ActiveForm.FormName)
    Set rs = f.RecordsetClone
    ' define the criteria used for the sync
    SyncCriteria = "[YourField]='" & Me![YourField] & "'"
    ' find the corresponding record in the Parts table
    rs.FindFirst SyncCriteria
    f.Bookmark = rs.Bookmark

Save the sub and close it
Now open the main form in design view
You should see all of the records in the sub form
Click in the little gray square to the far left of the record you want.
If you look at my Image above, you will see a black arrow in the square I’m talking about.
This and only this will trip the On-Click event of the sub form.
Next you should see the main form setting on that same record.

I make 99.5% of all my Access forms like this
Users absolutely love it.

DougP, MCP
 Ask me how Bar-codes can help you be more productive.
 Or visit my WEB site www.barcodeone.com

RE: HowTo Select Datasheet Record and Open a Single Record Form.

Thanks DougP,

I followed you instructions and created the forms.  This was helpful, but I want to create a datasheet view of a table that I can navigate through and when I click on a particular record another form opens in single record form view to allow me to view/modify field contents.  I think I will play around with what you have already given me to see if I can modify the process.
Thanks again for your help.  btw-In you above mentioned procedure, you left out the fact that the subform can not be linked to the main form via Parent-child fields which creates a one-to-one relationship.

RE: HowTo Select Datasheet Record and Open a Single Record Form.

I suggest you do something like this
Create the form with the fields you want
on the form. Place a command button on the form
in the buttons on click event place this code
docmd.runcommand accmddatasheetview

then on the field that you want to use to view a single form
place this in the onclick event

docmd.runcommand accmdformview
in the forms default view propery select datasheet.
When the form opens you will see a table view
scroll through click on the field you have the code behind and it opens to a single form with the record you clicked on. Hit the command button to go back to data sheet view
hope this is what you had in mind

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close