Select a single record from a subform for editing.

[Guest]

I can't seem to get this feature to work.

I have a form that filters the database on criteria specified in a mainform
(frmSearchDatabase) then displays the filtered results in a subform
(fsubRecordSearch). I would liket to be able to somehow select a record from
the results and have another form automatically open up with all the various
fields populated with that records specific data. (ie. Last Name, First name,
etc.) Then I could make any necessary changes to that record.

Thanks!

 

[Damon Heron]

I like using the double-click event on a record field (like the record name)
and add this code:
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.yourcontrol
stDocName = "yourform"
DoCmd.openform stDocName, , , "YourID = " & stLinkCriteria

This calls up a form that has as a record source the table used for the
subform. I guess you are doing this for the same reason I am- not all
fields are shown on your subform, right? otherwise, there would be no
reason to call up a form to fill in data that was already available on your
subform.


Damon

 

[Guest]

That is just what I needed, and you are correct not all of my fields are
shown in the subform. Now I am going to figure out a way to enable / disable
the double-click event based on the user level permissions. If the user has
modify permissions then double-click will be enabled. Thanks for your help.

 

[Guest]

I spoke too soon. I am still having issues. I couldn't get your code to call
up the form. I changed the code to

Dim stLinkCriteria As String
stLinkCriteria = Me.txtRecordID
DoCmd.openform "frmEditAssignment", , , "txtRecordID = " & stLinkCriteria

Now this opens the form, but none of the controls on my form are visible.
Yet if I open the form directly they are there? What exactly is the purpose
of

Dim stDocName As String
sDocName = "frmEditAssignment"

Rather than calling the form directly as I have done above? Excuse me if
this is obvious, but I am very new to coding. I'm not sure if my problem lies
with the control source for the form I'm trying to call up or the coding? I
have used the same tables and queries that are used on subform as the control
source?

Any thoughts?

 

[Damon Heron]

The table field that contains the ID is called what? txtRecordID on your
called form has a record source - maybe "recordID".
so the line would read: DoCmd.openform "frmEditAssignment", , , "RecordID =
" & stLinkCriteria

As to the controls not visible, does your form have the table as the record
source? Is the table empty?

Damon

 

[Damon Heron]

Your other question having to do with using a variable for the form name- no
difference one way or the other. Its just a
habit with me, because I might use similar code in a variety of ways, and
just seems easier somehow to assign a value.

Damon

 

[Guest]

Problem solved. My txtRecordID record source was Assignment Number and I
forgot to place it in [] brackets. Now it works great. Thank for all the help!