Pre-filling certain fields on new record in form (1 Viewer)

ajd2598

New member
Joined
Dec 14, 2023
Messages
5
Hello,

I've reviewed a number of other posts on the topic of "OpenArgs" and other methods for passing data between forms when using command buttons, however I've been unable to resolve my issue and would appreciate any assistance you can provide.

Overview of relevant portions of DB Structure
I'm obligated to sanitize this a bit due to my business purpose, but I think the below should encompass the relevant table structure:

Code:
TABLE: tblPerson
ID (autonumber)
FK_Employer (Foreign Key to an employers table)
Person_Last (Var)
Person_First (Var)
Person_Title (Var)
Person_Flag1 (T/F)
Person_Flag2 (T/F)
Person_DateAdded (Date)
Person_Summary (LongText)

TABLE: tblEvent
ID
FK_Person (FK for the Person Table)
. . .
Other Fields

Forms in Use
I have a "Person Detail" form which can be filtered/scoped based on search terms users are looking for, no issues there. The Person Detail form lists the data provided from the relevant table, and then has a sub form which contains a datasheet with a summary of the "events" tied to that person.

I've been successfully able to create a VBA script which pops up a "Event Detail" form when a user clicks the ID number of an event listed in the datasheet sub form for each person. No issues here.

The Issue
The issue I'm having, is that in addition to allowing my users to view existing events by clicking the ID number in the Inquiries Datasheet sub form, I've also placed a command button on the "Person Detail" form entitled 'New Event'; The behavior I'd like to have occur is to have the 'Event Detail' form open, start a new record that can be populated by the user, but have the "FK_Person" field/control already be populated to the Person the user was viewing on the "Person Detail" page.

I'd like to do this, because the DB will eventually have hundreds of users and each of those users may have multiple events; I'd like to avoid having to make my data entry users select the person associated with a new event each time they add an event, since they'll only be able to add new events while looking at an existing person.

What I've Tried So Far
I've found multiple posts about the "OpenArgs" method that is part of the DoCmd.OpenForm function. I've added the following code to my "Person Detail" Class Object:

Code:
Private Sub btnNewEvent_Click()
    Dim curRecord As Integer
    curRecord = Me.ID
    DoCmd.OpenForm "frmEventDetail", acNormal, , , acFormAdd, acWindowNormal, curRecord
End Sub

Then, in the Class Object for the "Event Detail", I have the following:

Code:
Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        Debug.Print (Me.OpenArgs)
        Me.FK_Person = Me.OpenArgs
    End If
End Sub

I'm able to see in the immediate window that the argument IS getting passed to the new form, however depending on how I structure the code, I get various errors about either not being able to edit the field; or if I put the "Me.txtSubID" control ID in the place of the actual field, I get an error about needing to commit the record before being able to update it.

I've tried just about every combination of field and/or control ID, and I've tried some other blocks of code that I've found in similar questions, all to no avail, I'm unable to essentially "set" the FK_Person ID field at the time of prompting the user to create a new event.

Any insight would be appreciated!
 
Last edited:
You should not dirty the record yourself.
Set the ID/FK in the form BeforeInsert event.
No need for CurRecord, just use Me.ID.
If the control has an expression you cannot edit that control value.
 
For whatever reason, whenever I attempt to post any code or anything of actual value to you, the forum spam filter won't let me post. So, I screenshotted my preview, sorry to make you have to view it this way.
 

Attachments

  • Capture.PNG
    Capture.PNG
    33.8 KB · Views: 68
Hi @ajd2598. Welcome to AWF!

It might be easier for you to use a form/subform setup for entering the events for each person. Rather than using OpenArgs, you would simply use the WhereCondition argument to filter the popup form/subform to the person the user clicked on. Then, you won't have to worry about populating the FK yourself, because Access will do it for you.
 
Hi theDBguy,

That's an interesting idea, I hadn't considered a "sub form" that appears as a pop-up rather than as an embedded form. I appreciate that perspective, I'll look into restructuring it that way.

I have to admit though, coming from PHP/MySQL, it's pretty frustrating not to be able to rely upon an understanding of MVC architecture, and thinking I should be able to modify the view before the user commits anything to the DB. But, I'm in the environment I'm in, and I appreciate the insight.
 
For whatever reason, whenever I attempt to post any code or anything of actual value to you, the forum spam filter won't let me post. So, I screenshotted my preview, sorry to make you have to view it this way.
Code was posted correctly before?
 
@Gasman -- Can't speak to it. Reached out to a moderator by PM, but basically have been foiled at any message of length or substance.
 
 
Part of your problem MAY be that we have a "newbie" filter that currently would class you as such, based on current post count. The posting problem might go away in a few more posts. Not sure why it won't let you post code but then again, I didn't have a problem posting something similar to what you provided.

Out of curiosity, what are you using for a back-end database?
 
Part of your problem MAY be that we have a "newbie" filter that currently would class you as such, based on current post count. The posting problem might go away in a few more posts. Not sure why it won't let you post code but then again, I didn't have a problem posting something similar to what you provided.

Out of curiosity, what are you using for a back-end database?
Doc, O/P posted code in their first post?
 

Users who are viewing this thread

Top Bottom