 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2006Open Form To Record (or VBA 101 - I've thought myself into paralysis)
Okay, I know this is VBA 101, but I've been away from it a long time and having brainfreeze. Here is the scenario: I have a form with a subform. Subform is a datasheet view of a table. I want user to select a record in subform and click Edit button and have a new form open with just that record displayed. I know it simple, but I've been so doing so many more advanced things, I have forgotten a basic and can't find it in the help in a meaningful way. Subform Recordsource is : SELECT tblInvoiceHeader.Recno, tblInvoiceHeader.PatientNbr, tblInvoiceHeader.InvoiceDate, tblInvoiceHeader.InvoiceNbr, tblInvoiceHeader.Fname, tblInvoiceHeader.lname FROM tblInvoiceHeader ORDER BY tblInvoiceHeader.Recno DESC; So when user clicks the Edit button, I wanted it to read the tblInvoiceHeader.Recno value and use it to find/filter the table. I can set Recordsource for EditForm to "tblInvoiceHeader", but how do I get to the record ? I tried coding filter in the docmd.OpenForm but either screwed that up or that isn't the right way to go. > Okay, I know this is VBA 101, but I've been away from it a long time > and having brainfreeze. Here is the scenario: [quoted text clipped - 18 lines] > I get to the record ? I tried coding filter in the docmd.OpenForm > but either screwed that up or that isn't the right way to go. What form is it that you want to open? Is the Edit button on the subform, or on the main form? If it's on the main form, you need to tell us the name of the subform control on the main form; that is, the name of the control that displays the subform.  SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) Main Form is called "frmMain". Edit Button is on the Main Form Subform (Called "InvoiceHeader Subform") is datasheet. I want Edit Button to open a new form called "frmEdit" focused on the record selected in "InvoiceHeader Subform". >> Okay, I know this is VBA 101, but I've been away from it a long time >> and having brainfreeze. Here is the scenario: [quoted text clipped - 24 lines] > main form, you need to tell us the name of the subform control on the > main form; that is, the name of the control that displays the subform. > Main Form is called "frmMain". Edit Button is on the Main Form > Subform (Called "InvoiceHeader Subform") is datasheet. > > I want Edit Button to open a new form called "frmEdit" focused on the > record selected in "InvoiceHeader Subform". Here's sample code for the Edit button: '----- start of code ----- Private Sub Edit_Click() Dim strCriteria As String With Me![InvoiceHeader Subform]!Recno If Not IsNull(.Value) Then strCriteria = "Recno = " & .Value End If End With DoCmd.OpenForm "frmEdit", WhereCondition:=strCriteria End Sub '----- end of code ----- That code is a little more elaborate than absolutely necessary because it allows for the possibility that the subform may be on a new record, and the Recno field is Null. In that case, the code opens the edit form to show all records. If you don't want that, you could easily modify the code to just exit the procedure and not open the form at all. The fact that frmEdit also has a subform should be irrelevant. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) Thanks Dirk. Did the trick.... knew I was overcomplicating it (bookmarks and stuff) Regards, Ron >> Main Form is called "frmMain". Edit Button is on the Main Form >> Subform (Called "InvoiceHeader Subform") is datasheet. [quoted text clipped - 27 lines] > > The fact that frmEdit also has a subform should be irrelevant. Let me add that frmEdit has a Master/Detail Subform on it as well so when I open the form I need to be pointing to a tblInvoiceHeader record so that the linked tblinvoiceDetail data on the subform displays. Regards, Ron >> Okay, I know this is VBA 101, but I've been away from it a long time >> and having brainfreeze. Here is the scenario: [quoted text clipped - 24 lines] > main form, you need to tell us the name of the subform control on the > main form; that is, the name of the control that displays the subform. really what you are trying to get as is the primary key for the selected record in the subform. Presuming frmEdit is bound to the a similar data source as the subform: docmd.openform "frmEdit", , ,"[Recno] = " & forms!frmMain![SubformName]!Recno ...or something like that. I have a feeling that's not 100%, but it's probably close enough to get you there. or if you want to have frmEdit unbound and do the edits with dao/ado recordsets then you can pass the primary key as an openarg and go from there >Let me add that frmEdit has a Master/Detail Subform on it as well [...] didnt see that - it's relatively simple though, if your've set up your relationships properly then the subform will only ever display relevant results based on the form/subform child/master primary/foreign keys. Indeed, the Where condition did the trick and the detail dropped right in. Thanks Bill! > >Let me add that frmEdit has a Master/Detail Subform on it as well [...] > > didnt see that - it's relatively simple though, if your've set up your > relationships properly then the subform will only ever display relevant > results based on the form/subform child/master primary/foreign keys. I love it when a plan comes together :) welcome |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.