HowTo Select Datasheet Record and Open a Single Record Form. 1

[bsupport]

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?
Thanks

 

[DougP]

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.

http://www.barcodeone.com/winsoft/images/wpe1.gif

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
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.
Or visit my WEB site

http://www.barcodeone.com

 

[bsupport]

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.

 

[braindead2]

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