passthrough acFormEdit example? (1 Viewer)

christine407

New member
Local time
Today, 09:20
Joined
Jan 18, 2021
Messages
13
First, I want to say thank you to everyone/anyone who has read or responded to my past post. So helpful! Along the same lines, I’m very thankful for those who have answered other post as I am reading as much as I can.

I think I need help finding an example using pass through acFormEdit (I hope I'm using the right words here)?

I have a filtered form where the unique ID (well number) is displayed hyperlinked. When clicking the hyperlink well number, I want the user to be able to view and edit an associated form (frmWellReview).

Because I’m not totally sure that I’m using the right terms, I’ll provide an example. Let’s say I have a list of authors. I want the user to be able to click the hyperlinked author name and have it go to another form of books associated to the particular author (in form view, so the user only see information on one book at a time). The user can update the information on the book (ex: Name, publisher, ex) and/or the user can hit the > arrow button and review another book and/or add a new book (with the author name automatically being filled in) and hit save and save a record in tablebooks (or in my case tblWellReview). If the author has no books associated to it yet, I want the form to open to a blank form (with the author name already filled in).

Does anyone know of example code in a working access file that I can review?

I think I got something going on with the following code:

Code:
Private Sub WellNumber_Click()
'WellNumber is the hyperlink unique ID
   Dim WellID As String
'Some of the well numbers start with 0, so I couldn't use Long
   WellID = Me!WellID
   DoCmd.Close acForm, "frmWellSearch"
   DoCmd.OpenForm "frmWellReview", OpenArgs:=WellID
End Sub

Private Sub Form_Open(Cancel as Integer)
'Opening of frmWellReview
If nz(Me.OpenArgs) = 0 Then
    me.RecordSource = "tblWellReview"
Else
    me.RecordSource = "Select tblWellReview.* FROM tblWellReview WHERE (((tblWellReview.Well_Number) =" & me.OpenArgs & "));"
End If
End Sub

My current error with the following code is that it is asking for "Enter a parameter value" and prompting for the well number and not accepting the open argument?

Additionally, based on other post, I’m thinking maybe I should be using acFormEdit somehow (so I can work add multiple well reviews for one well). It is my hope that it will also auto fill in the well number when I add new (or if this is the first well review entered). I potentially want to carry over more than just the well number and maybe the location details on the frmWellReview but one step at a time.

Thank you again!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:20
Joined
Oct 29, 2018
Messages
21,358
Hi. You could also try using the WhereCondition argument of the OpeForm method, so you won't need any code in the Open event of the other form.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:20
Joined
May 7, 2009
Messages
19,169
just curious.
you Declared WellID as String on the Click Event.
you should use String concatenation on from frmWellReview

me.RecordSource = "Select tblWellReview.* FROM tblWellReview WHERE (((tblWellReview.Well_Number) ='" & me.OpenArgs & "'));"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:20
Joined
Feb 19, 2002
Messages
42,970
"passthrough" is a specific type of query. That isn't what is needed here. What you have is a 1-m relationship and instead of implementing the many-side as a subform, you want to open a new form. That's fine but it means that you need to handle setting the foreign key for the many-side record. You can get around this though by making your popup form a mainform with two subforms. The mainform will be bound to the author table. The left subform will be a DS view that lists books by that author. Just the name should do. The right subform will show the details for the book. I have imbedded a picture of one that actually has THREE subforms. The left is the list of all the endorsements for the product (books for the author), the right is the single view of the selected endorsement (book) and the bottom right (which you probably don't need) list the data entry fields required for adding this endorsement to a policy. If this would work for you, I'll post more and explain how it works.
ThreeSubforms.JPG
 

christine407

New member
Local time
Today, 09:20
Joined
Jan 18, 2021
Messages
13
just curious.
you Declared WellID as String on the Click Event.
you should use String concatenation on from frmWellReview

me.RecordSource = "Select tblWellReview.* FROM tblWellReview WHERE (((tblWellReview.Well_Number) ='" & me.OpenArgs & "'));"
Hi Arnelgp,

You bring up a very very good point! I made the change but I was still prompted for a well ID number. I did go back and check my tblWells (which technically is a query using ODBC) and I found the the table from where the Well ID is pulled from for the query tblWells, the data type is short text (I cannot change this as it is an ODBC table). Would this create a data mismatch at all? My Well ID is a 12 digit number, sometimes starting with 0.
Thank you!
Christine
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:20
Joined
May 7, 2009
Messages
19,169
re-check the field again.
do you have correct spelling?
does it have underscore?
is it "ID" and not "Number"?
 

christine407

New member
Local time
Today, 09:20
Joined
Jan 18, 2021
Messages
13
"passthrough" is a specific type of query. That isn't what is needed here. What you have is a 1-m relationship and instead of implementing the many-side as a subform, you want to open a new form. That's fine but it means that you need to handle setting the foreign key for the many-side record. You can get around this though by making your popup form a mainform with two subforms. The mainform will be bound to the author table. The left subform will be a DS view that lists books by that author. Just the name should do. The right subform will show the details for the book. I have imbedded a picture of one that actually has THREE subforms. The left is the list of all the endorsements for the product (books for the author), the right is the single view of the selected endorsement (book) and the bottom right (which you probably don't need) list the data entry fields required for adding this endorsement to a policy. If this would work for you, I'll post more and explain how it works.
View attachment 89004
Hi Mr. Hartman,

You are very correct, I am working with a one to many relationship (one being the wells and many being the "many" reviews associated to one well). For most of these wells, no "review" exist yet, but the goal is build something to store the multiple "reviews". I built the review table to have an auto unique ID along with the well number ID.

The technique you mention would actually probably work for me. If possible, I'd make the left table show the repeating WellIDs (that would all be the same) and the date of the review and some other field. I want to avoid forcing unnecessary data entry (specifically having the user type in the well number ID when they've clicked on it in a previous form). Put another way, avoid having the user type in the author name on the right top form in your example if the user clicked on the author on the previous form.

Thank you!
 

christine407

New member
Local time
Today, 09:20
Joined
Jan 18, 2021
Messages
13
re-check the field again.
do you have correct spelling?
does it have underscore?
is it "ID" and not "Number"?
HEY!!!! It took a while but I found something that was misspelled and corrected it! THANK YOU for suggesting!

At this point, I am successfully going to a blank review form after clicking a hyperlink WellID. I need to figure out how to autopopulate the Well ID number on the review form. I also need to set up save and return buttons and some fake "review" entries to see if it works recognizes wells with with no, single and multiple reviews.

Do you know if my general approach is safe for one (wells) to many (reviews) relationships? I am confused if I need to be focused on getting the acFormEdit somehow in?

I don't know if this matters, but I didn't include the code for opening the well form.

Code:
Private Sub Form_Open (Cancel as Integer)
'Opening the list of wells
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
If Nz(Me.OpenArgs)<>0 Then
Me!WellID.SetFocus
DoCmd.FindRecord Me.OpenArgs
End If
End Sub

Thank you again!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:20
Joined
May 7, 2009
Messages
19,169
you need to Move your code to the Load event of the form.
on the Open event, the controls are not yet initialized
so your find will fail.
Code:
Private Sub Form_Load()
'Opening the list of wells
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
If Nz(Me.OpenArgs, 0) <> 0 Then
    With Me.RecordsetClone
        .FindFirst "WellID = " & Me.OpenArgs
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
End If

End Sub
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:20
Joined
May 7, 2009
Messages
19,169
you can also create a "filtered" query and use it in your code.
create a query:

select * from tblWellReview where WellID = [p1];"

Code:
Private Sub Form_Load()
Dim db As DAO.Database
Set db = CurrentDb
With db.QueryDefs("yourQuery")
    .Parameters(0) = Me.OpenArgs
    Set Me.Recordset = .OpenRecordset(dbOpenDynaset, dbSeeChanges)
End With
Set db = Nothing
End Sub
 

christine407

New member
Local time
Today, 09:20
Joined
Jan 18, 2021
Messages
13
you can also create a "filtered" query and use it in your code.
create a query:

select * from tblWellReview where WellID = [p1];"

Code:
Private Sub Form_Load()
Dim db As DAO.Database
Set db = CurrentDb
With db.QueryDefs("yourQuery")
    .Parameters(0) = Me.OpenArgs
    Set Me.Recordset = .OpenRecordset(dbOpenDynaset, dbSeeChanges)
End With
Set db = Nothing
End Sub
May I ask where you are suggesting I use this? Or clarify what this code will implement?

I was able to get help in setting up some filters for my well (author's table) and that has been implemented (see first thread I made on this site). I was very unsuccessful in exporting a filtered well table (see 2nd thread I've started) despite many helpful suggestions (some suggestions slightly mirror what you provided here). I haven't been able to get anything related to DAO to work yet. I'm half convinced I have the wrong settings in my access file. Under tools>references>I do have Microsoft Office 16.0 Access database engine Object Library selected. I'm still working my way through Access 2019 Programming which I hope will help, but honestly, the most helpful things I've learned from are example projects available in Microsoft Access and a Udemy course I've been taking and reading this website!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:20
Joined
Feb 19, 2002
Messages
42,970
I need to figure out how to autopopulate the Well ID number on the review form

1. Do NOT dirty the popup form before the user does.
2. Put the code into the form's on Dirty event.

Using the Load event dirties the form before the user types anything and that could confuse him if you raise a validation error when Access attempts to save the record or result in blank records being created if you don't do any validation. Using the Dirty event prevents this problem. Just make sure to check to make sure that you only do this for new records.
INI:
If Me.NewRecord Then
    Me.CustID = Me.OpenArgs
End IF


The additional benefit of using the correct event is that if your process allows the user to make multiple records, using the on Dirty event will result in ALL records getting the FK applied not just the first record.
 

christine407

New member
Local time
Today, 09:20
Joined
Jan 18, 2021
Messages
13
1. Do NOT dirty the popup form before the user does.
2. Put the code into the form's on Dirty event.

Using the Load event dirties the form before the user types anything and that could confuse him if you raise a validation error when Access attempts to save the record or result in blank records being created if you don't do any validation. Using the Dirty event prevents this problem. Just make sure to check to make sure that you only do this for new records.
INI:
If Me.NewRecord Then
    Me.CustID = Me.OpenArgs
End IF


The additional benefit of using the correct event is that if your process allows the user to make multiple records, using the on Dirty event will result in ALL records getting the FK applied not just the first record.
That works, thanks!

However it is a little confusing that the user has to dirty the form for the CustID to show up. I added the code in both the on dirty event and the load event and it seems to work. Is that ok?

If I wanted to I want to automatically display the corresponding Lat/Long associated with the unique ID (CustID in your code), would I use a variation of

Code:
Me.Long_Textbox.Value = DLookup("[longitude]", "Wells", "[WellID] = '" & Me.WellID.Value & "'")

in the on load and dirty event?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:20
Joined
Feb 19, 2002
Messages
42,970
I added the code in both the on dirty event and the load event and it seems to work. Is that ok?
I do NOT recommend dirtying the form before the user does. It just confuses the user if they close the form without entering any data and you complain because something is "missing". In their minds they didn't do anything so why are you bugging them.

If the user is only ever able to add one record, then using the load event will not cause a problem other than dirtying the record when it should not be dirtied. However, you MUST remove the scroll capability from the form by removing the record selectors. Then put code in the form's Current event to make sure the CustID is present just in case they manage to scroll to an empty record.

If you are OK with the CustID not being populated on the second and subsequent records (because the load event only runs ONCE), then I don't understand why you are not OK with it initially. If the user is confused, tell him why you are not showing the custID immedately.
 

Users who are viewing this thread

Top Bottom