Open Form To Record (or VBA 101 - I've thought myself into paralysis)

  • Thread starter Thread starter Ron
  • Start date Start date
R

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:

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.
 
Ron said:
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.
Click to expand...

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.
 
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".
 
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
 
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.
 
Ron said:
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".
Click to expand...

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.
 
Thanks Dirk. Did the trick.... knew I was overcomplicating it (bookmarks
and stuff)
Regards,
Ron

Dirk Goldgar said:
Ron said:
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".
Click to expand...

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Indeed, the Where condition did the trick and the detail dropped right in.

Thanks Bill!

BillCo said:
Let me add that frmEdit has a Master/Detail Subform on it as well [...]
Click to expand...

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.
Click to expand...
 
Back
Top